January 3, 2012 at 3:52 pm
I'm new to this forum so I'm not sure if this question belongs here or how I could better explain what I'm trying to do, but here goes.
Basically I'm trying to find a way to more efficiently make a dataset that contains many different types of data groupings.
I'm thinking I need to create a stored procedure or use declared variables, but I can't find a way to get SQL Server Mgt Studio to group by a declared variable.
Currently I used this method where I create 2 temp tables with the only difference being 1 of my group bys and then I stack the temp tables on top of eachother using a union. This is simple and contains only 2 groupings, but I end up with alot of repetitive code if I need to group on about 20 things).
---------------------------------------------------------------------------
DROP TABLE #TTF_WEEK
SELECT
LOB,
[Period],
[Bin Type] = 'Time to Fund',
[Bin] = [Time to Fund Bin]),
Applications = SUM([Application Ind])
INTO #TTF_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[Time to Fund Bin]
WITH CUBE
---------------------------------------------------------------------------
DROP TABLE #APPSOURCE_WEEK
SELECT
LOB,
[Period],
[Bin Type] = 'App Source',
[Bin] = [App Source Bin],
Applications = SUM([Application Ind])
INTO #APPSOURCE_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[App Source Bin]
---------------------------------------------------------------------------
DROP TABLE #FINAL
SELECT * INTO #FINAL FROM
(
SELECT * FROM #TTF_WEEK
UNION
SELECT * FROM #APPSOURCE_WEEK
) AS t
January 3, 2012 at 4:01 pm
I think there's an easier solution to do it. But I'd like to test it before posting to make sure the code works as intended.
Therefore I'd like you to provide some ready to use sample data in a layout as described in the first link in my signature.
This would help me and others to focus on the solution.
January 3, 2012 at 4:44 pm
/Edit/ I know the dataset contains all the unique groupings, but pretend it doesn't and they are randon. Was just trying to get a simple dataset for example.
Thanks for the quick response. This should make for some good sample data.
IF OBJECT_ID('TempDB..#APPS','U') IS NOT NULL
DROP TABLE #APPS
CREATE TABLE #APPS
(
LOB VARCHAR(25),
[App Week] VARCHAR(25),
[App Source Bin] VARCHAR(25),
[Time to Fund Bin] VARCHAR(25),
[Applications] INT
)
INSERT INTO #APPS
(LOB, [App Week], [App Source Bin], [Time to Fund Bin], [Applications])
SELECT 'OCS','2011 W1','Online','01 to 05 Days','51' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','97' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','94' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','30' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','62' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','70' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','27' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','71' UNION ALL
SELECT 'OCS','2011 W1','Online','01 to 05 Days','71' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','59' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','42' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','97' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','24' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','27' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','8' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','38' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','82' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','75' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','15' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','12' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','10' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','68' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','88' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','69' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','91' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','13' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','36' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','24' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','55' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','48' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','39' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','16'
---------------------------------------------------------------------------
DROP TABLE #TTF_WEEK
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = 'Time to Fund',
[Bin] = [Time to Fund Bin],
Applications = SUM(Applications)
INTO #TTF_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[Time to Fund Bin]
WITH CUBE
---------------------------------------------------------------------------
DROP TABLE #APPSOURCE_WEEK
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = 'App Source',
[Bin] = [App Source Bin],
Applications = SUM(Applications)
INTO #APPSOURCE_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[App Source Bin]
---------------------------------------------------------------------------
DROP TABLE #FINAL
SELECT * INTO #FINAL FROM
(
SELECT * FROM #TTF_WEEK
UNION
SELECT * FROM #APPSOURCE_WEEK
) AS t
January 4, 2012 at 2:15 pm
Here's the "straight forward" approach without the temp tables:
;
WITH cte AS
(
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = 'Time to Fund',
[Bin] = [Time to Fund Bin],
Applications = SUM(Applications)
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[Time to Fund Bin]
WITH CUBE
UNION ALL
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = 'App Source',
[Bin] = [App Source Bin],
Applications = SUM(Applications)
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[App Source Bin]
)
SELECT *
FROM cte
ORDER BY lob, period,[Bin Type],bin
January 4, 2012 at 2:30 pm
Thanks for the quick response. Not quite what I'm looking for though. My problem is that I'm trying to stack about 20 temp tables together and what I want to do is develop some code where I only have write the temp table once, but then have it be kind of like a macro in SAS where I can easily create another temp table grouped by something else without rewriting the whole statement. Then at the end I want to stack them altogether in one dataset.
Kind of like this.
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = 123,
[Bin] = [Time to Fund Bin],
Applications = SUM(Applications)
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
xyz
WITH CUBE
I want a way to easily tell it to put in the place of xyz, [Time to Fund Bin], [App Source Bin] and any other bins I can come up with while not having to write again the entire code to build a temp table. Also I need [Bin Type] to be equal to xyz (or xyz less the ' Bin' part on the end).
It's easy enough to copy and paste and then find and replace the variables repeatedly, but I find I have to do this alot and then the query takes a long time to execute. If I could find another way, it would make the queries faster while cutting down on the amount of code.
Thanks again for the response and any ideas of a better way would be appreciated.
/edit/ Currently I have some code where I want to change 2 of the groupings and I end up writing 45 temp tables and stacking them. Just get tired of the repetition and since the code is inefficient, I lose alot of time when I have to rerun. I'm thinking there must be a better way. I researched on my own and couldn't find a way so ended up here.
January 4, 2012 at 3:04 pm
The problem you describe seems to different to the code you've posted.
In the code provided the two sections don't have enough in common: one is using the CUBE operator whereas the other one doesn't.
What's the rule when to apply CUBE and when not?
January 4, 2012 at 3:10 pm
I always want to apply WITH CUBE. Sorry for the confusion. WHen I initially posted the code I removed alot of things to make it simple. I simply didn't notice I left the WITH CUBE in on one and not the other. Basically the tables will be identical except for one of the GROUP BY variables and [Bin Type] needs to change.
Looking for a way to easily do that without having to write all of the code to create the temp table.
January 4, 2012 at 3:12 pm
Here's what the code actually looks like. I'm trying to streamline the creation of the temp tables. I have 45 of these in my code.
---------------------------------------------------------------------------
DROP TABLE #HOMEOWNER_WEEK
SELECT
Section = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END + ' - ' +
CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END + ' - ' +
CASE WHEN GROUPING([Homeowner Bin]) = 1 THEN 'Total' ELSE [Homeowner Bin] END,
LOB = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END,
[Period] = CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END,
[Bin Type] = 'Homeowner',
[Bin] = CASE WHEN GROUPING([Homeowner Bin]) = 1 THEN 'Total' ELSE [Homeowner Bin] END,
Applications = SUM([Application Ind]),
[Approval Rate] = SUM([Approval Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Capture Rate] = SUM([Contract Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Closure Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Book to Approve Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Approval Ind]) > 0 THEN SUM([Approval Ind]) ELSE NULL END
INTO #HOMEOWNER_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[Homeowner Bin]
WITH CUBE
HAVING
GROUPING(a.LOB) <> 1
AND GROUPING(a.[App Week]) <> 1
---------------------------------------------------------------------------
DROP TABLE #TTF_WEEK
SELECT
Section = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END + ' - ' +
CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END + ' - ' +
CASE WHEN GROUPING([Time to Fund Bin]) = 1 THEN 'Total' ELSE [Time to Fund Bin] END,
LOB = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END,
[Period] = CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END,
[Bin Type] = 'Time to Fund',
[Bin] = CASE WHEN GROUPING([Time to Fund Bin]) = 1 THEN 'Total' ELSE [Time to Fund Bin] END,
Applications = SUM([Application Ind]),
[Approval Rate] = SUM([Approval Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Capture Rate] = SUM([Contract Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Closure Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Book to Approve Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Approval Ind]) > 0 THEN SUM([Approval Ind]) ELSE NULL END
INTO #TTF_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[Time to Fund Bin]
WITH CUBE
HAVING
GROUPING(a.LOB) <> 1
AND GROUPING(a.[App Week]) <> 1
---------------------------------------------------------------------------
DROP TABLE #APPSOURCE_WEEK
SELECT
Section = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END + ' - ' +
CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END + ' - ' +
CASE WHEN GROUPING([App Source Bin]) = 1 THEN 'Total' ELSE [App Source Bin] END,
LOB = CASE WHEN GROUPING(a.LOB) = 1 THEN 'Total' ELSE a.LOB END,
[Period] = CASE WHEN GROUPING([App Week]) = 1 THEN 'Total' ELSE [App Week] END,
[Bin Type] = 'App Source',
[Bin] = CASE WHEN GROUPING([App Source Bin]) = 1 THEN 'Total' ELSE [App Source Bin] END,
Applications = SUM([Application Ind]),
[Approval Rate] = SUM([Approval Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Capture Rate] = SUM([Contract Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Closure Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Application Ind]) > 0 THEN SUM([Application Ind]) ELSE NULL END,
[Book to Approve Rate] = SUM([Fund Ind]) / CASE WHEN SUM([Approval Ind]) > 0 THEN SUM([Approval Ind]) ELSE NULL END
INTO #APPSOURCE_WEEK
FROM #APPS AS a
GROUP BY
a.LOB,
[App Week],
[App Source Bin]
WITH CUBE
HAVING
GROUPING(a.LOB) <> 1
AND GROUPING(a.[App Week]) <> 1
January 4, 2012 at 3:20 pm
Would you please change the sample data you posted to reflect the columns you're using in your latest reply (e.g. [Homeowner Bin])?
January 4, 2012 at 3:30 pm
See below. If you want to keep it simple, just remove the WITH CUBE part from the previous code and use [Time to Fund Bin], and [App Source Bin]. Was just throwing [Homeowner Bin] for illustration purposes.
IF OBJECT_ID('TempDB..#APPS','U') IS NOT NULL
DROP TABLE #APPS
CREATE TABLE #APPS
(
LOB VARCHAR(25),
[App Week] VARCHAR(25),
[App Source Bin] VARCHAR(25),
[Time to Fund Bin] VARCHAR(25),
[Homeowner Bin] VARCHAR(25),
[Applications] INT
)
INSERT INTO #APPS
(LOB, [App Week], [App Source Bin], [Time to Fund Bin], [Homeowner Bin], [Applications])
SELECT 'OCS','2011 W1','Online','01 to 05 Days','Rent','3' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','Rent','15' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','Rent','44' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','Rent','12' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','Rent','38' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','Rent','31' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','Rent','20' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','Rent','29' UNION ALL
SELECT 'OCS','2011 W1','Online','01 to 05 Days','Rent','28' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','Rent','5' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','Rent','21' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','Rent','10' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','Rent','59' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','Rent','81' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','Rent','65' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','Rent','45' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','Rent','74' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','Rent','29' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','Rent','44' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','Rent','54' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','Rent','18' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','Rent','93' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','Rent','4' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','Rent','70' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','Rent','60' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','Rent','35' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','Rent','77' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','Rent','20' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','Rent','36' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','Rent','9' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','Rent','1' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','Rent','48' UNION ALL
SELECT 'OCS','2011 W1','Online','01 to 05 Days','Own','56' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','Own','94' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','Own','46' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','Own','66' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','Own','35' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','Own','30' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','Own','26' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','Own','24' UNION ALL
SELECT 'OCS','2011 W1','Online','01 to 05 Days','Own','59' UNION ALL
SELECT 'RL','2011 W1','Online','01 to 05 Days','Own','52' UNION ALL
SELECT 'OCS','2011 W2','Online','01 to 05 Days','Own','70' UNION ALL
SELECT 'RL','2011 W2','Online','01 to 05 Days','Own','53' UNION ALL
SELECT 'OCS','2011 W1','In Store','01 to 05 Days','Own','77' UNION ALL
SELECT 'RL','2011 W1','In Store','01 to 05 Days','Own','9' UNION ALL
SELECT 'OCS','2011 W2','In Store','01 to 05 Days','Own','20' UNION ALL
SELECT 'RL','2011 W2','In Store','01 to 05 Days','Own','76' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','Own','19' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','Own','13' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','Own','33' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','Own','6' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','Own','94' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','Own','12' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','Own','3' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','Own','16' UNION ALL
SELECT 'OCS','2011 W1','Online','06 to 10 Days','Own','38' UNION ALL
SELECT 'RL','2011 W1','Online','06 to 10 Days','Own','38' UNION ALL
SELECT 'OCS','2011 W2','Online','06 to 10 Days','Own','88' UNION ALL
SELECT 'RL','2011 W2','Online','06 to 10 Days','Own','96' UNION ALL
SELECT 'OCS','2011 W1','In Store','06 to 10 Days','Own','62' UNION ALL
SELECT 'RL','2011 W1','In Store','06 to 10 Days','Own','82' UNION ALL
SELECT 'OCS','2011 W2','In Store','06 to 10 Days','Own','18' UNION ALL
SELECT 'RL','2011 W2','In Store','06 to 10 Days','Own','55'
January 5, 2012 at 4:15 am
I think I finally understood what you're looking for...
Will this be closer?
;
WITH cte AS
(
SELECT
LOB,
[Period] = [App Week],
[Bin Type] = BinTypes.BinType,
[Bin] = -- one row per original temp table
CASE
WHEN BinTypes.BinType ='Time to Fund' THEN [Time to Fund Bin]
WHEN BinTypes.BinType ='App Source' THEN [App Source Bin]
ELSE NULL END,
Applications
FROM #APPS AS a
CROSS APPLY
(VALUES -- one row per original temp table
('Time to Fund'),
('App Source')
) BinTypes(BinType)
)
SELECT LOB,
[Period] ,
[Bin Type],
[Bin] ,
SUM(Applications) AS Applications
FROM cte
GROUP BY
LOB,
[Period],
[Bin Type],
[Bin]
WITH cube
Edit: the only way I can think of to avoid writing the BinType values twice in this code would be DynamicSQL...
January 5, 2012 at 9:41 am
Yeah this looks like what I need. Thanks alot for the help. I'm gonna test soon, but if it works it should save me a lot of time, I'll let you know how it works out. I want to even make it to where I can make the period variable change like the Bin variable.
January 6, 2012 at 2:48 pm
It works! Thank you very much. This will be a big help for me.
January 6, 2012 at 3:20 pm
Glad it finally worked out for you!! 🙂
And sorry for not getting there earlier... I've been on the wrong track at the beginning :blush:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply