May 20, 2010 at 3:59 pm
I think this is a crosstab query, but I'm not sure.
I have a table:
Transactions:
TranId TranDate DeptCode Amount
==============================
1 5/1/2009 001 10.00
2 6/2/2009 001 100.00
3 6/5/2009 002 279.00
4 4/4/2009 002 189.00
...
I need a query that results in something like this
Dept Mar09 Apr09 May09 Jun09......Jan10 Feb10 Mar10
============================================
001 1 1
002 1 1
So I need to count the transactions per month, and group them by department. I'm not sure where to start with this one. I'm trying to resist my overwhelming urge to use brute force and start coding up cursors! 😀
Thanks!
(sorry I can't get these sample tables to line up correctly. Hope it makes sense)
.
May 20, 2010 at 5:34 pm
May 20, 2010 at 7:29 pm
Yea, that's what I need alright. In my situation, it looks like I would essentially create my month counters in the same way the PIVOT example creates the employeeId counters. If the PIVOT example is the best approach, I guess I have to hard code my counters. I guess I was expecting a more clever solution than that, but if that's the best approach, that's what I'll do.
Thanks. This is a perfect example.
.
May 20, 2010 at 11:22 pm
BSavoie (5/20/2010)
Yea, that's what I need alright. In my situation, it looks like I would essentially create my month counters in the same way the PIVOT example creates the employeeId counters. If the PIVOT example is the best approach, I guess I have to hard code my counters. I guess I was expecting a more clever solution than that, but if that's the best approach, that's what I'll do.Thanks. This is a perfect example.
IMHO, the PIVOT approach actually isn't the best approach. Further, nothing has to be hardcoded if you don't want it to be (and probably shouldn't be here). Take a gander at the following two articles... the first article tells you how to use both a Cross Tab and a Pivot as well as doing some performance tests that show PIVOT is slower. The second article tells you how to easily make dynamic Cross Tabs.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
{edit} And, if you were to provide some sample data in the format outlined in the article at the first link in my signature line below, we could probably hook you up with a working code example. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 12:04 am
Thanks Jeff. I should have cleaned that sample crap up. Sorry. Here's some TSQL that creates more realistic data. The idea here is I need a query that will return the number of checkouts per hotel, per month. Each month should constitute a column and each row should constitute a hotel. I've included a screen print of the actual xls I got along with this request. See attachment.
I will definitely check out the links you sent me. If you want to give me a couple code examples, that would rock.
Here's the code that builds the data:
DECLARE @GuestData TABLE (
GuestDataId int,
HotelName varchar(25),
GuestName varchar(25),
ArrDate DateTime,
ChkOutDate DateTime
)
INSERT INTO @GuestData SELECT 1, 'Bobs Inn', 'Jack', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 2, 'Bobs Inn', 'Sam', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 3, 'Bobs Inn', 'Ray', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 4, 'Bobs Inn', 'Dave', '5/10/09', '5/11/09'
INSERT INTO @GuestData SELECT 5, 'Bobs Inn', 'Bob', '5/19/09', '5/26/09'
INSERT INTO @GuestData SELECT 6, 'Bobs Inn', 'Mark', '7/1/09', '7/4/09'
INSERT INTO @GuestData SELECT 7, 'Do Drop Inn', 'Ann', '2/1/09', '2/3/09'
INSERT INTO @GuestData SELECT 8, 'Do Drop Inn', 'Kim', '2/1/09', '2/3/09'
INSERT INTO @GuestData SELECT 9, 'Do Drop Inn', 'April', '8/1/09', '8/11/09'
INSERT INTO @GuestData SELECT 10, 'Do Drop Inn', 'Cheryl', '9/10/09', '9/11/09'
INSERT INTO @GuestData SELECT 11, 'Do Drop Inn', 'Spring', '9/19/09', '9/26/09'
INSERT INTO @GuestData SELECT 12, 'Do Drop Inn', 'Deb', '12/1/09', '12/4/09'
INSERT INTO @GuestData SELECT 13, 'Notel Motel', 'Steve', '1/1/10', '1/3/10'
INSERT INTO @GuestData SELECT 14, 'Notel Motel', 'Bill', '2/1/10', '2/3/10'
INSERT INTO @GuestData SELECT 15, 'Notel Motel', 'Arturo', '3/1/10', '3/11/10'
INSERT INTO @GuestData SELECT 16, 'Notel Motel', 'Kay', '10/10/10', '10/11/10'
INSERT INTO @GuestData SELECT 17, 'Notel Motel', 'Jenna', '11/19/10', '11/26/10'
INSERT INTO @GuestData SELECT 18, 'Notel Motel', 'Jessica', '12/21/10', '12/24/10'
SELECT * FROM @GuestData
.
May 21, 2010 at 7:49 am
Thanks for the test data... I'll take a whack at it after I get home from work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 8:54 pm
Heh... rookie mistake on my part. I don't normally do SAVEs of code I'm working on for the forum because it normally just doesn't take that long and, if something goes wrong, SSMS is pretty good about recovering the work. But, leave it to me, I got an unhandled exception error, it threw me out of SSMS, and when I went back in, no recovery. :crazy:
So... I just wanted to let you know that I'm working on this and I've had a setback.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 10:16 pm
Here you go... as usual, the details are in the comments...
--===== Original test data setup from the post. This is NOT a part of the solution.
DECLARE @GuestData TABLE (
GuestDataId int,
HotelName varchar(25),
GuestName varchar(25),
ArrDate DateTime,
ChkOutDate DateTime
)
INSERT INTO @GuestData SELECT 1, 'Bobs Inn', 'Jack', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 2, 'Bobs Inn', 'Sam', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 3, 'Bobs Inn', 'Ray', '4/1/09', '4/3/09'
INSERT INTO @GuestData SELECT 4, 'Bobs Inn', 'Dave', '5/10/09', '5/11/09'
INSERT INTO @GuestData SELECT 5, 'Bobs Inn', 'Bob', '5/19/09', '5/26/09'
INSERT INTO @GuestData SELECT 6, 'Bobs Inn', 'Mark', '7/1/09', '7/4/09'
INSERT INTO @GuestData SELECT 7, 'Do Drop Inn', 'Ann', '2/1/09', '2/3/09'
INSERT INTO @GuestData SELECT 8, 'Do Drop Inn', 'Kim', '2/1/09', '2/3/09'
INSERT INTO @GuestData SELECT 9, 'Do Drop Inn', 'April', '8/1/09', '8/11/09'
INSERT INTO @GuestData SELECT 10, 'Do Drop Inn', 'Cheryl', '9/10/09', '9/11/09'
INSERT INTO @GuestData SELECT 11, 'Do Drop Inn', 'Spring', '9/19/09', '9/26/09'
INSERT INTO @GuestData SELECT 12, 'Do Drop Inn', 'Deb', '12/1/09', '12/4/09'
INSERT INTO @GuestData SELECT 13, 'Notel Motel', 'Steve', '1/1/10', '1/3/10'
INSERT INTO @GuestData SELECT 14, 'Notel Motel', 'Bill', '2/1/10', '2/3/10'
INSERT INTO @GuestData SELECT 15, 'Notel Motel', 'Arturo', '3/1/10', '3/11/10'
INSERT INTO @GuestData SELECT 16, 'Notel Motel', 'Kay', '10/10/10', '10/11/10'
INSERT INTO @GuestData SELECT 17, 'Notel Motel', 'Jenna', '11/19/10', '11/26/10'
INSERT INTO @GuestData SELECT 18, 'Notel Motel', 'Jessica', '12/21/10', '12/24/10'
--SELECT * FROM @GuestData
--=====================================================================================================================
-- Solution
--=====================================================================================================================
--===== Declare the necessary variables. The ones that start with @p could be parameters in a stored procedure
DECLARE @pStartMonthDate DATETIME,
@pEndMonthDate DATETIME,
@Months INT,
@sql VARCHAR(MAX)
;
--===== Set the parameters for the desired date range (1 year in this case)
SELECT @pStartMonthDate = 'May 2009',
@pEndMonthDate = 'Apr 2010'
;
--===== Force all date parameters to the first of the month and calculate the number of months we need the Tally
-- table to create months for.
SELECT @pStartMonthDate = DATEADD(mm,DATEDIFF(mm,0,@pStartMonthDate),0),
@pEndMonthDate = DATEADD(mm,DATEDIFF(mm,0,@pEndMonthDate),0),
@Months = DATEDIFF(mm,@pStartMonthDate,@pEndMonthDate)
;
--===== Preaggregate the data for additional performance
SELECT HotelName,
DATEADD(mm,DATEDIFF(mm,0,ChkOutDate),0) AS ChkOutDate,
COUNT(*) AS CheckOuts
INTO #PreAgg
FROM #GuestData
WHERE ChkOutDate >= @pStartMonthDate
AND ChkOutDate < DATEADD(mm,1,@pEndMonthDate)
GROUP BY HotelName,
DATEADD(mm,DATEDIFF(mm,0,ChkOutDate),0)
;
--===== Build all the dynamic SQL
SELECT @sql = 'SELECT ISNULL(h.HotelName,''Total''),' +CHAR(10)
+ ( --=== This creates the "non static" part of the SELECT list that forms the Cross Tab rows
SELECT 'MAX(CASE WHEN ChkOutDate = '''+MonthDate+''' THEN CheckOuts ELSE 0 END) AS ['+MonthDate+'],' + CHAR(10)
FROM ( --=== This creates all the necessary month dates in the MMM YYYY format
SELECT SUBSTRING(CONVERT(CHAR(11),DATEADD(mm,t.N,@pStartMonthDate),113),4,11) AS MonthDate
FROM Tally t
WHERE t.N BETWEEN 0 AND @Months
) months
FOR XML PATH('')
)
+ 'SUM(ISNULL(CheckOuts,0)) AS Total' + CHAR(10)
+ 'FROM #PreAgg agg' + CHAR(10)
+ 'RIGHT OUTER JOIN (SELECT DISTINCT HotelName FROM #GuestData) h' + CHAR(10)
+ 'ON agg.HotelName = h.HotelName' + CHAR(10)
+ 'GROUP BY h.HotelName WITH ROLLUP' + CHAR(10)
;
--===== Produce the dynamic report with row and column totals
EXEC (@SQL)
;
--===== Cleanup so we can run again if we want
DROP TABLE #PreAgg
;
The code needs the use of a Tally table that starts at zero. Here's how to build one.
--===== Create and populate the Tally table on the fly.
-- This ISNULL function makes the column NOT NULL
-- so we can put a Primary Key on it
SELECT TOP 11001
ISNULL(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID)-1,0) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
If you don't know how a Tally table works to produce certain WHILE Loops, please see the following article. I also recommend you read the other two articles I posted links for so you understand how Cross Tabs work and how to make them dynamic.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 11:06 pm
AWESOME! This is not the first problem you've solved for me Jeff. Thank you! This is absolutely the best SQL Server forum out there thanks to people like you. I am going to go over the links you referenced tonight. Somehow I've managed to dodge cross tab queries for most of my carer. It's time plug that hole in my skills.
😀
.
May 22, 2010 at 9:53 am
BSavoie (5/21/2010)
AWESOME! This is not the first problem you've solved for me Jeff. Thank you! This is absolutely the best SQL Server forum out there thanks to people like you. I am going to go over the links you referenced tonight. Somehow I've managed to dodge cross tab queries for most of my carer. It's time plug that hole in my skills.😀
Thanks for the incredible feedback. I'm just sorry these things sometimes take so long. I get wrapped up in some pretty hairy T-SQL posts and they take a while to resolve. Thanks for your patience.
I also want to thank you for taking the time to make up the test data like you did. Like I said, I'm pretty busy and when someone shows that they're interested enough in their own problem to post some readily consumable data along with a decent problem description, then a lot of folks on this forum will jump through flaming hoops to help that person. Well done.
Yeah... crosstabs are pretty useful especially if you can pull off the dynamic ones. I've not used Reporting Services but I understand they have a "matrix" that works similar to crosstabs. As you say, I've somehow managed to dodge the bullet of learning SSRS but I see the writing on the wall.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply