I come from a different world in my company, I’m not IT though I have formal training, I speak the lingo, and wear the uniform of Jeans, t-shirt, glasses and ton’s of geekieness in my swagger. I’m what they call Business Intelligence or better known as Shadow IT. Though i’m up front about what we do and the impact it has. In my company I’m the middle person between what the business thinks it wants and what it really needs long term.
Last Friday I got a data request from a group asking for a report to be generated bi-hourly for customer request that were not responded to in a time span of two hours. So looking at the data I noticed that there were two columns that were time stamped one when the request was submitted and another when the request was completed, so this was easy I filtered out where the completed data was null and wrote a where statement using a Datediff syntax to breakdown the input date time stamp by hour and then subtracting two so my sql statement looks like the following example.
As I start to dive deeper into the Business Intelligence area I’ve started putting together a list of my favorite blogs covering this area, in no particular order.
http://www.bidn.com/ http://sqlbi.blogspot.com/ http://cwebbbi.spaces.live.com/ http://denglishbi.spaces.live.com/ http://bi-polar23.blogspot.com/ http://sqlserverbiblog.com/ http://shahfaisalmuhammed.wordpress.com/ http://tomislavpiasevoli.spaces.live.com/default.aspx http://bimatters.spaces.live.com/
This is a shout out to my former team member learning about SSIS packages for the first time and getting ready to move from SQL 2000 to SQL 2008. Here’s some good reference points for how to do the basics. Now before you call me and ask what does SSIS stands it stands for SQL Server Integration Services. So below is a couple links that i found i think you may find helpful in your new adventure.
So the other day my former team member call me asking for help. She had inserted the same data into a table three or more times and needed to get rid of them. For those of you with duplications and need to delete them here’s a little statement i came up with and gave her.
DELETE FROM TBL_Employees
WHERE (EmpID IN
(SELECT MAX(EmpID) AS Expr2
FROM TBL_Employees
GROUP BY FirstName
From time to time i run into problems of where some new table appears on m server and starts eating up all my space. Well i came across this blog posting a while back that does a great job of explaining how to determine the table sizes in a database in SQL 2000 it works in 2005 too. check it out and let me know what you think. http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx
Earlier this month i attended my first SQL meetup called SQL Saturday #31 i’m not sure why it’s numbered 31 cause i realized they don’t go in order. But yeah so it was hosted in Elk Grove so it wasn’t to far away. I knew it was going to be a awesome day when i met this guy in the registration line who reads my site and learned a couple SQL tricks from it.
I got a request for a report the other day so I built this massive 90 line SQL statement to pull all the data requested. So I sent off a copy of the data report and the guy tells me that he needs the date formatted (YYYYMMDD) instead of (YYYY-MM-DD-HHMMSS) and then he was like is that going to be a problem and I was like ha not really. Give me a moment and I’ll send you a simple.
I ran into this problem the other day at work with duplicate rows while trying to merge data from two separate tables into the same table. I couldn’t remember so I just ran a quick search using Google and came across the solution on a Microsoft knowledge base page.
------------------------------------------------------- **_The first step is to identify which rows have duplicate primary key values:_** SELECT col1, col2, count(*)FROM t1GROUP BY col1, col2HAVING count(*) > 1 **_This will return one row for each set of duplicate PK values in the table.
