Output Individual Months w/ Total for Date Range
-
Hi,
I have a requirement to create a report that returns a list of months w/ running totals for each month for a date range. The totals would be related to a specific type of entry
An example is shown below:
I assume that some type of pivoting and grouping would be used to get this result. My current query is below which at this time doesn't have the months or totals grouped as I need it.
SELECT
CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END AS [Entry Type],
A.PUNCHDTM,
A.LABORACCTID,
SUM(CASE WHEN A.PUNCHDTM IS NOT NULL THEN 1 ELSE 0 END) AS 'Totals'
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND A.PUNCHDTM ---- THIS WILL BE TH E MONTH COLUMN AS SEEN IN EXAMPLE
BETWEEN DATEADD(mm,-1,GETDATE()) AND GETDATE()
GROUP BY C.LABORLEV1DSC,
C.LABORLEV2DSC,
C.LABORLEV3DSC, A.PUNCHDTM,B.FUNCTSTR,A.LABORACCTID
Any help is appreciated,
Thanks
-
SELECT
CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END AS [Entry Type],
SUM(CASE WHEN Month(A.PUNCHDTM ) = 1 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 2 THEN 1 ELSE 0 END) AS February
SUM(CASE WHEN Month(A.PUNCHDTM ) = 3 THEN 1 ELSE 0 END) AS March,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 4 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 5 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 6 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 7 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 8 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 9 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 10 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 12 THEN 1 ELSE 0 END) AS December
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND A.PUNCHDTM ---- THIS WILL BE TH E MONTH COLUMN AS SEEN IN EXAMPLE
BETWEEN DATEADD(mm,-1,GETDATE()) AND GETDATE()
GROUP BY
(CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END )
it may be better to pivot by Event Type and switch it back on the client but it is up to you!
Cheers
* Noel -
That worked really well. What do the think would be the best way to limit the month output to the current month - 1? I need to show a range instead of a set of static months...
Thanks again for your assistance,
-
Like I said before:
Wouldn't it be the same if you report the data the other way around?
Month TimeClock Schedule People Editor ......
February 12345 2345 456678 ...
March 12345 2345 456678 ..
April 12345 2345 456678 ...
That way you will get what you need changing the pivoting action like:
Select
Datename(Month, A.PUNCHDTM) As Month,
SUM(CASE(WHEN B.FUNCTSTR = 'A' THEN 1 ELSE 0 END) ) AS 'Smart Scheduler',
SUM(CASE(WHEN B.FUNCTSTR = 'B' THEN 1 ELSE 0 END) ) AS 'People Editor',
SUM(CASE(WHEN B.FUNCTSTR = 'C' THEN 1 ELSE 0 END) ) AS 'DCM',
SUM(CASE(WHEN B.FUNCTSTR = 'D' THEN 1 ELSE 0 END) ) AS 'Time Card Editor',
SUM(CASE(WHEN B.FUNCTSTR = 'F' THEN 1 ELSE 0 END) ) AS 'Mobile Time',
SUM(CASE(WHEN B.FUNCTSTR = 'G' THEN 1 ELSE 0 END) ) AS 'Group Edit',
SUM(CASE(WHEN B.FUNCTSTR = 'I' THEN 1 ELSE 0 END) ) AS 'System Import',
SUM(CASE(WHEN B.FUNCTSTR = 'L' THEN 1 ELSE 0 END) ) AS 'Labor Manager',
SUM(CASE(WHEN B.FUNCTSTR = 'M' THEN 1 ELSE 0 END) ) AS 'Employee Maintenance',
SUM(CASE(WHEN B.FUNCTSTR = 'P' THEN 1 ELSE 0 END) ) AS 'Time Clock',
SUM(CASE(WHEN B.FUNCTSTR = 'S' THEN 1 ELSE 0 END) ) AS 'Schedule',
SUM(CASE(WHEN B.FUNCTSTR = 'T' THEN 1 ELSE 0 END) ) AS 'Teletime (IVR)',
SUM(CASE(WHEN B.FUNCTSTR = 'U' THEN 1 ELSE 0 END) ) AS 'Systems Upgrade',
SUM(CASE(WHEN B.FUNCTSTR = 'W' THEN 1 ELSE 0 END) ) AS 'Workforce Web entry'
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND A.PUNCHDTM ---- THIS WILL BE TH E MONTH COLUMN AS SEEN IN EXAMPLE
BETWEEN DATEADD(mm,-1,GETDATE()) AND GETDATE()
GROUP BY Datename(Month, A.PUNCHDTM)
if you can't change the llayout then you will have to use Dynamic sql
* Noel -
Unfortunately, I can't change the layout. However, the project requirements have changed and now I will be able to use a stored proc. So I'll take your original query and work it to output the date range as required: count back 12 months from previous month...should be fun...
Do you think it would be best to create variables to store the months from the range and then output those?
Thanks again for your help...
-
Ok if you what to have alook at a dynamic version here it is !!
declare @STR varchar(8000)
select @STR = 'SELECT
CASE(B.FUNCTSTR)
WHEN ''A'' THEN ''Smart Scheduler''
WHEN ''B'' THEN ''People Editor''
WHEN ''C'' THEN ''DCM''
WHEN ''E'' THEN ''Time Card Editor''
WHEN ''F'' THEN ''Mobile Time''
WHEN ''G'' THEN ''Group Edit''
WHEN ''I'' THEN ''System Import''
WHEN ''L'' THEN ''Labor Manager''
WHEN ''M'' THEN ''Employee Maintenance''
WHEN ''P'' THEN ''Time Clock''
WHEN ''S'' THEN ''Schedule''
WHEN ''T'' THEN ''Teletime (IVR)''
WHEN ''U'' THEN ''Systems Upgrade''
WHEN ''W'' THEN ''Workforce Web entry''
ELSE ''No value''
END AS [Entry Type] '
'SUM(CASE WHEN Month(A.PUNCHDTM ) = ' + cast(number as varchar(2)) + 'THEN 1 ELSE 0 END) AS ' datename(month,'2005' + right ('00' + cast(number as varchar(2)), 2) + '01' )
from master..spt_values where number between month( @yourmindate) and month( @yourmaxdate)
order by number
select @STR = @STR + ' FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
AND A.PUNCHDTM ---- THIS WILL BE TH E MONTH COLUMN AS SEEN IN EXAMPLE
BETWEEN @yourmindate AND @yourmaxdate
GROUP BY
(CASE(B.FUNCTSTR)
WHEN ''A'' THEN ''Smart Scheduler''
WHEN ''B'' THEN ''People Editor''
WHEN ''C'' THEN ''DCM''
WHEN ''E'' THEN ''Time Card Editor''
WHEN ''F'' THEN ''Mobile Time''
WHEN ''G'' THEN ''Group Edit''
WHEN ''I'' THEN ''System Import''
WHEN ''L'' THEN ''Labor Manager''
WHEN ''M'' THEN ''Employee Maintenance''
WHEN ''P'' THEN ''Time Clock''
WHEN ''S'' THEN ''Schedule''
WHEN ''T'' THEN ''Teletime (IVR)''
WHEN ''U'' THEN ''Systems Upgrade''
WHEN ''W'' THEN ''Workforce Web entry''
ELSE ''No value''
END ) '
exec (@str)
Enjoy!
* Noel -
Wow, maybe someday I'll be as good @ sql as I am at Flash programming...Im impressed...
Thanks again, I assume you're a dba? TSQL is fairly new to me, in my last job I was lucky enough to be able to hand my requirements off to the dba..
Neways thanks again,
-
Yes I am ,
It takes time to get used to the "new thinking" but once you get the gist of it you'll find it fun
* Noel -
The requirements have changed a bit so I removed the date range clause and inserted the data into a flat table that looks like this:
Division
District
Facility
Entry Year
Entry Type
Jan
Feb
Mar
April
May
June
July
Aug
Sep
Oct
Nov
Dec
1
5
Test
2005
Time Clock
54
4
3
34
56
65
8
8
0
46
6
56
1
5
Test
2004
Time Clock
4
45
7
1
5848
113
87
57
98
80
0
0
2
78
Test 2
2005
Time Clock
7
876
71
57
57
52
7
60
0
0
46
87
The query to insert into the table is below (your previous suggestion, with some minor mods)
SELECT
cast(C.LABORLEV1DSC + '(' + C.LABORLEV1NM + ')' as varchar(255))AS Division,
cast(C.LABORLEV2DSC + '(' + C.LABORLEV2NM + ')' as varchar(255))AS District,
cast(C.LABORLEV3DSC + '(' + C.LABORLEV3NM + ')' as varchar(255))AS Facility,
DATEPART(yy,A.PUNCHDTM) AS [Entry Year],
CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END AS [Entry Type],
SUM(CASE WHEN Month(A.PUNCHDTM ) = 1 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 2 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 3 THEN 1 ELSE 0 END) AS March,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 4 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 5 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 6 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 7 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 8 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 9 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 10 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN Month(A.PUNCHDTM ) = 12 THEN 1 ELSE 0 END) AS December
FROM PUNCHEVENT A
INNER JOIN LABORACCT C ON A.LABORACCTID=C.LABORACCTID
INNER JOIN DATASOURCE B ON A.DATASOURCEID=B.DATASOURCEID
WHERE A.DELETEDSW=0
GROUP BY
C.LABORLEV1DSC, C.LABORLEV1NM,
C.LABORLEV2DSC, C.LABORLEV2NM,
C.LABORLEV3DSC, C.LABORLEV3NM,
DATEPART(yy,A.PUNCHDTM),
(CASE(B.FUNCTSTR)
WHEN 'A' THEN 'Smart Scheduler'
WHEN 'B' THEN 'People Editor'
WHEN 'C' THEN 'DCM'
WHEN 'E' THEN 'Time Card Editor'
WHEN 'F' THEN 'Mobile Time'
WHEN 'G' THEN 'Group Edit'
WHEN 'I' THEN 'System Import'
WHEN 'L' THEN 'Labor Manager'
WHEN 'M' THEN 'Employee Maintenance'
WHEN 'P' THEN 'Time Clock'
WHEN 'S' THEN 'Schedule'
WHEN 'T' THEN 'Teletime (IVR)'
WHEN 'U' THEN 'Systems Upgrade'
WHEN 'W' THEN 'Workforce Web entry'
ELSE 'No value'
END )
Basically this table should contain the various entry types, the counts by month as related to a specific Division, District, Facility. From the look of it, i have accomplished this.
However, I need to do the date range on the new table. I can't see a good way to do that since the year is separated from the months listed for the entry types.
I would need to pass in the curyear\curmonth-1 and see if it is between a mindate and maxdate.
Do you think in order to do the range. I should get rid of the individual month columns and make the entry year into entry year\month? The issue w/ that is that I will return muliple records for the same entry type even if I group by year\month.
The output should be as I mentioned in my first, i guess the only difference I have now is that instead of setting the date range in my sql, its going to be handled in an external source (Crystal)
Let me know what you think, thanks again for your advice,
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply