March 3, 2008 at 2:55 pm
I would like to know if there is a good way to have subtotal records and a grand total record within a result set. I need to have to do a subtotal after XX records and a grand total at the end. This also needs a final subtotal record before the grand total. This is something I am doing in a previous version through a report in an application. We are going to be rewriting this application and I would like to manipulate the data in a better way. In the old program, the subtotal and grand total records were created when the data was initially generated. However, the user can update the records, but the subtotal/grand total records weren’t updated.
As long as I can be pointed in a good direction, I will run with it and see what I can come up with.
Here is a condensed version of the table I will be working with
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #MyTable (
[Year] [smallint] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Subtotal] [bit] NOT NULL,
[GrandTotal] [bit] NOT NULL,
[Void] [bit] NOT NULL,
[FederalWages] [money] NOT NULL,
[FederalTax] [money] NOT NULL,
[FicaWages] [money] NOT NULL,
[FicaTax] [money] NOT NULL,
CONSTRAINT [PK_FileW2] PRIMARY KEY CLUSTERED
([Year] ASC,
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Currently there are 2 fields (in bold above) that indicate if a record is a subtotal or grand total record.
Here is some sample data:
--===== Insert the test data into the test table
INSERT INTO #mytable (Year, EmployeeID, Subtotal, GrandTotal, Void,
FederalWages, FederalTax, FicaWages, FicaTax)
SELECT '2005','1',' ',' ',' ',17704.85,1911.00,18636.66,1155.50 UNION ALL
SELECT '2005','4',' ',' ',' ',30488.52,1888.00,30187.68,1871.52 UNION ALL
SELECT '2005','67',' ',' ',' ',15070.94,1357.00,15864.16,983.62 UNION ALL
SELECT '2005','3',' ',' ',' ',27930.20,3289.00,29074.89,1800.25 UNION ALL
SELECT '2005','86',' ',' ',' ',23725.78,2798.00,24974.48,1548.52 UNION ALL
SELECT '2005','36',' ',' ',' ',30488.52,3768.00,31697.04,1965.12 UNION ALL
SELECT '2005','68',' ',' ',' ',11198.75,917.00,11772.34,729.87 UNION ALL
SELECT '2005','23',' ',' ',' ',299.73,20.00,299.73,18.58 UNION ALL
SELECT '2005','75',' ',' ',' ',17077.20,1811.00,17976.00,1114.45 UNION ALL
SELECT '2005','52',' ',' ',' ',18488.90,1066.00,19462.00,1206.62 UNION ALL
SELECT '2005','99',' ',' ',' ',14967.59,1494.00,15755.35,976.85 UNION ALL
SELECT '2005','42',' ',' ',' ',2373.54,0.00,2373.54,147.17
Based on the sample data, here are the results I would like (using 5 per subtotal):
SELECT '2005' AS Year,'1' AS EmployeeID,'0' AS Subtotal,'0' AS GrandTotal,' ' AS VOID,
17704.85 As FederalWages,1911.00 AS FederalTaxes, 18636.66 AS FicaWages,
1155.50 AS FicaTaxes UNION ALL
SELECT '2005','4','0','0',' ',30488.52,1888.00,30187.68,1871.52 UNION ALL
SELECT '2005','67','0','0',' ',15070.94,1357.00,15864.16,983.62 UNION ALL
SELECT '2005','3','0','0',' ',27930.20,3289.00,29074.89,1800.25 UNION ALL
SELECT '2005','86','0','0',' ',23725.78,2798.00,24974.48,1548.52 UNION ALL
SELECT '2005','0','1','0',' ',114920.3,11243.00,118737.90,7349.41 UNION ALL --Subtotal
SELECT '2005','36','0','0',' ',30488.52,3768.00,31697.04,1965.12 UNION ALL
SELECT '2005','68','0','0',' ',11198.75,917.00,11772.34,729.87 UNION ALL
SELECT '2005','23','0','0',' ',299.73,20.00,299.73,18.58 UNION ALL
SELECT '2005','75','0','0',' ',17077.20,1811.00,17976.00,1114.45 UNION ALL
SELECT '2005','52','0','0',' ',18488.90,1066.00,19462.00,1206.62 UNION ALL
SELECT '2005','0','1','0',' ',77553.10,7582.00,81207.11,5034.64 UNION ALL --Subtotal
SELECT '2005','99','0','0',' ',14967.59,1494.00,15755.35,976.85 UNION ALL
SELECT '2005','42','0','0',' ',2373.54,0.00,2373.54,147.17 UNION ALL
SELECT '2005','0','1','0',' ',17341.13,1494.00,18128.89,1124.02 UNION ALL --Subtotal
SELECT '2005','0','0','1',' ',209814.50,20319.00,218073.09,13518.07 --Grand total
Please let me know if there are any questions. Thank you for your help.
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
March 3, 2008 at 3:06 pm
look for 'ROLLUP' in the sql server books and read the 'Summarize Data Using ROLLUP' topic.
March 3, 2008 at 4:02 pm
ROLLUP is definitely on the right track to what I want, however it is not 100% there. Unless I am missing something, I don't see a way to ROLLUP a specific number of records. For example, I need to be able to summarize 5 records at a time. Since there is only going to be one record per employee, I can't group on specific field. The only field that will be consistent is the [Year] field.
I hope this helps clarify this a little better.
Thanks,
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
March 3, 2008 at 4:19 pm
Time to ask a question, why the sub-total every 5 records? Seems to me this should probably be done on the application side, not the database side of things it the sub-totals and grand total is for batch controls.
😎
March 3, 2008 at 5:33 pm
Ian Crandell (3/3/2008)
ROLLUP is definitely on the right track to what I want, however it is not 100% there. Unless I am missing something, I don't see a way to ROLLUP a specific number of records. For example, I need to be able to summarize 5 records at a time. Since there is only going to be one record per employee, I can't group on specific field. The only field that will be consistent is the [Year] field.I hope this helps clarify this a little better.
Thanks,
try grouping by ROW_NUMBER() OVER (ORDER BY Year, EmployeeID) / 5 (where 5 is the number of rows). this will give you a total for every 5 records. if you want a running total (subtotal for rows 16-20 records includes total of records 1-20), look at recent threads regarding running balance in this forum.
March 3, 2008 at 6:43 pm
I absolutely agree that this type of formatting should probably be done in the GUI...
... but it's too damned much fun doing it in T-SQL :D:P;):w00t::hehe::)
Ian... I got carried away... if ya wanna get it back to your exact output, we can... but run this bad boy first and see what you think 😀
--===== Supress the auto-display of rowcounts for appearance
SET NOCOUNT ON
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #MyTable
(
[Year] [smallint] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Subtotal] [bit] NOT NULL,
[GrandTotal] [bit] NOT NULL,
[Void] [bit] NOT NULL,
[FederalWages] [money] NOT NULL,
[FederalTax] [money] NOT NULL,
[FicaWages] [money] NOT NULL,
[FicaTax] [money] NOT NULL,
CONSTRAINT [PK_FileW2]
PRIMARY KEY CLUSTERED ([Year] ASC, [EmployeeID] ASC))
--===== Insert the test data into the test table
INSERT INTO #mytable
(Year, EmployeeID, Subtotal, GrandTotal, Void,
FederalWages, FederalTax, FicaWages, FicaTax)
SELECT '2005','1',' ',' ',' ',17704.85,1911.00,18636.66,1155.50 UNION ALL
SELECT '2005','4',' ',' ',' ',30488.52,1888.00,30187.68,1871.52 UNION ALL
SELECT '2005','67',' ',' ',' ',15070.94,1357.00,15864.16,983.62 UNION ALL
SELECT '2005','3',' ',' ',' ',27930.20,3289.00,29074.89,1800.25 UNION ALL
SELECT '2005','86',' ',' ',' ',23725.78,2798.00,24974.48,1548.52 UNION ALL
SELECT '2005','36',' ',' ',' ',30488.52,3768.00,31697.04,1965.12 UNION ALL
SELECT '2005','68',' ',' ',' ',11198.75,917.00,11772.34,729.87 UNION ALL
SELECT '2005','23',' ',' ',' ',299.73,20.00,299.73,18.58 UNION ALL
SELECT '2005','75',' ',' ',' ',17077.20,1811.00,17976.00,1114.45 UNION ALL
SELECT '2005','52',' ',' ',' ',18488.90,1066.00,19462.00,1206.62 UNION ALL
SELECT '2005','99',' ',' ',' ',14967.59,1494.00,15755.35,976.85 UNION ALL
SELECT '2005','42',' ',' ',' ',2373.54,0.00,2373.54,147.17
;WITH cteMyTable AS
(--==== This cte just distributes a grouped row number over ever 5 rows
SELECT STR((ROW_NUMBER() OVER (ORDER BY Year,EmployeeID)-1)/5,10) AS SubGroup,
STR(Year,4) AS Year,
STR(EmployeeID,10) AS EmployeeID,
FederalWages, FederalTax, FicaWages, FicaTax
FROM #MyTable
)
,
cteGrouped AS
(--==== CTE does some substitutions when there's a total according to GROUPING
SELECT SubGroup,
CASE WHEN GROUPING(EmployeeID)=1 THEN '' --Makes the blank line and the empty year on Sub-Totals
ELSE Year
END AS Year,
CASE WHEN GROUPING(Year)=0 AND GROUPING(EmployeeID)=1 THEN 'Sub-Total'
WHEN GROUPING(SubGroup)=0 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN '' --Makes the blank line
WHEN GROUPING(SubGroup)=1 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN 'Grand-Total'
ELSE EmployeeID
END AS EmployeeID,
CASE WHEN GROUPING(SubGroup)=0 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN '' --Makes the blank line
ELSE STR(SUM(FederalWages),12,2)
END AS FederalWages,
CASE WHEN GROUPING(SubGroup)=0 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN '' --Makes the blank line
ELSE STR(SUM(FederalTax),12,2)
END AS FederalTax,
CASE WHEN GROUPING(SubGroup)=0 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN '' --Makes the blank line
ELSE STR(SUM(FicaWages),12,2)
END AS FicaWages,
CASE WHEN GROUPING(SubGroup)=0 AND GROUPING(Year)=1 AND GROUPING(EmployeeID)=1 THEN '' --Makes the blank line
ELSE STR(SUM(FicaTax),12,2)
END AS FicaTax
FROM cteMyTable
GROUP BY SubGroup,Year,EmployeeID WITH ROLLUP
)
--===== Final SELECT hides unwanted columns
SELECT Year, EmployeeID, FederalWages, FederalTax, FicaWages, FicaTax
FROM cteGrouped
{EDIT} My bad... had a small bug in the code and I've repaired it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 8:03 am
Thanks for your input everyone.
Lynn Pettis (3/3/2008)
Time to ask a question, why the sub-total every 5 records? Seems to me this should probably be done on the application side, not the database side of things it the sub-totals and grand total is for batch controls.😎
Jeff Moden (3/3/2008)
I absolutely agree that this type of formatting should probably be done in the GUI...... but it's too damned much fun doing it in T-SQL :D:P;):w00t::hehe::)
Ian... I got carried away... if ya wanna get it back to your exact output, we can... but run this bad boy first and see what you think 😀
I don't have a problem having the application create the totals, I just thought I would check to see if the database could do it (besides, I figured someone like Jeff would have fun creating a solution;)). If possible, I like to have the database do as much data "manipulation" as I think it helps with efficiency.
After looking at Jeff's example (which is cool and has allowed me to learn a few things:cool: ) I suspect it would be a little cumbersome to implement (my actual table has about 64 fields and over half would be subtotaled). Also, the 5 count was just to make the post simpler, the actual count is 41 (per government regulation, I don't understand why that number, besides who are we to question the government:blink: ) I intend to look into what Active Reports can do for me.
Again, thank for your help.
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
March 4, 2008 at 9:36 am
I suspect it would be a little cumbersome to implement
Heh... you got a problem with {ctrl-c}{ctrl-v}???
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 11:26 am
Jeff Moden (3/4/2008)
I suspect it would be a little cumbersome to implement
Heh... you got a problem with {ctrl-c}{ctrl-v}???
Not at all - well maybe only when I cut and paste
and I forget to make one critical change:pinch:
and yet it compiles and runs
and the error takes 2 hours to find and 1 minute to fix.....:crazy:
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
March 4, 2008 at 11:37 am
Heh... how well I know... been there and done that! The "R" in "CPR" stands for "REPLACE" and I frequently forget to do just that... 🙂
--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