March 5, 2010 at 7:48 pm
Among several other things.
Honestly, it's one of the things that is 'cool', but I haven't figured out what a practical need would be for it yet. Here's the query I was playing around with today and the results (since you can't run it).
IF OBJECT_ID('TempDB..#GST','U') IS NOT NULL DROP TABLE #GST
CREATE TABLE #GST(
Cust_Numvarchar(10),
CallTypevarchar(10),
Open_Datedatetime,
Amountdecimal(9,2))
INSERT INTO #GST (Cust_Num, CallType, Open_Date, Amount)
VALUES ('AAAAAA','SERVICE','1/1/2006',100),
('AAAAAA','ESTIMATE','1/1/2007',100),
('AAAAAA','PM','1/1/2008',100),
('AAAAAA','SERVICE','1/1/2006',100),
('AAAAAA','ESTIMATE','1/1/2007',100),
('AAAAAA','PM','1/1/2008',100),
('AAAAAA','SERVICE','1/1/2009',100),
('BBBBBB','SERVICE','1/1/2006',100),
('BBBBBB','ESTIMATE','1/1/2008',100),
('BBBBBB','PM','1/1/2009',100),
('BBBBBB','SERVICE','1/1/2005',100),
('CCCCCC','ESTIMATE','1/1/2005',100),
('BBBBBB','PM','1/1/2009',100),
('BBBBBB','SERVICE','1/1/2005',100),
('CCCCCC','ESTIMATE','1/1/2005',100),
('BBBBBB','PM','1/1/2009',100),
('BBBBBB','SERVICE','1/1/2005',100),
('CCCCCC','ESTIMATE','1/1/2005',100),
('CCCCCC','SERVICE','1/1/2008',100),
('CCCCCC','SERVICE','1/1/2009',100)
SELECT Cust_Num, CallType, YEAR(Open_Date) ODYear, SUM(Amount) Total,
CASEWHEN Cust_Num IS NOT NULL AND CallType IS NULL AND YEAR(Open_Date) IS NULL THEN '3 Customer Total'
WHEN CallType IS NOT NULL AND Cust_Num IS NULL AND YEAR(Open_Date) IS NULL THEN '4 CallType Total'
WHEN YEAR(Open_Date) IS NOT NULL AND Cust_Num IS NULL AND CallType IS NULL THEN '5 ODYear Total'
WHEN Cust_Num IS NOT NULL AND CallType IS NOT NULL AND YEAR(OPEN_Date) IS NULL THEN '1 CustType Total'
WHEN Cust_Num IS NOT NULL AND CallType IS NOT NULL AND YEAR(OPEN_Date) IS NOT NULL THEN '2 CustTypeYear Total'
ELSE '6 Grand Total'
END TotalType
FROM #GST
GROUP BY GROUPING SETS(
(Cust_Num),
(Cust_Num, CallType),
(Cust_Num, CallType, YEAR(Open_Date)),
(YEAR(Open_Date)),
(CallType),
())
ORDER BY TotalType, Cust_Num
Results:
AAAAAAESTIMATENULL200.001 CustType Total
AAAAAAPMNULL200.001 CustType Total
AAAAAASERVICENULL300.001 CustType Total
BBBBBBPMNULL300.001 CustType Total
BBBBBBESTIMATENULL100.001 CustType Total
BBBBBBSERVICENULL400.001 CustType Total
CCCCCCSERVICENULL200.001 CustType Total
CCCCCCESTIMATENULL300.001 CustType Total
AAAAAAESTIMATE2007200.002 CustTypeYear Total
AAAAAAPM2008200.002 CustTypeYear Total
AAAAAASERVICE2006200.002 CustTypeYear Total
AAAAAASERVICE2009100.002 CustTypeYear Total
BBBBBBSERVICE2005300.002 CustTypeYear Total
BBBBBBSERVICE2006100.002 CustTypeYear Total
BBBBBBPM2009300.002 CustTypeYear Total
BBBBBBESTIMATE2008100.002 CustTypeYear Total
CCCCCCESTIMATE2005300.002 CustTypeYear Total
CCCCCCSERVICE2008100.002 CustTypeYear Total
CCCCCCSERVICE2009100.002 CustTypeYear Total
AAAAAANULLNULL700.003 Customer Total
BBBBBBNULLNULL800.003 Customer Total
CCCCCCNULLNULL500.003 Customer Total
NULLSERVICENULL900.004 CallType Total
NULLESTIMATENULL600.004 CallType Total
NULLPMNULL500.004 CallType Total
NULLNULL2005600.005 ODYear Total
NULLNULL2006300.005 ODYear Total
NULLNULL2007200.005 ODYear Total
NULLNULL2008400.005 ODYear Total
NULLNULL2009500.005 ODYear Total
NULLNULLNULL2000.006 Grand Total
March 5, 2010 at 8:29 pm
Garadin (3/5/2010)
Honestly, it's one of the things that is 'cool', but I haven't figured out what a practical need would be for it yet. Here's the query I was playing around with today and the results (since you can't run it).
I'll sometimes use such a thing (ie. WITH ROLLUP) to preaggregate data for a report. Think of it as a "mini-datamart".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2010 at 9:10 am
Chris,
Wherever did you get the idea to use an alias name of iTVF?
:laugh:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 2:32 am
Paul White (3/6/2010)
Chris,Wherever did you get the idea to use an alias name of iTVF?
:laugh:
Paul
Hey Paul, it was from a great article I was reading last week - about transgenderism π
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 8, 2010 at 6:15 am
Chris Morris-439714 (3/8/2010)
Hey Paul, it was from a great article I was reading last week - about transgenderism π
:laugh: Very funny!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply