Rollup of Columns
-
Hi,
I'm experiencing a mental block on this issue.
I want to sum all hours worked for each distinct PersonFullName. Currently as can be seen the hours are rolled up in the department related to the PersonFullName. I don't want to report in this fashion. As you can seen in Scenario, 05, this employee has worked in 3 different departments. I want to show the total hours for that employee across all departments that they are associated with...I'm sure it's quite easy but i have mental block on it for some reason...
Unit Department Employee Entry Date Hours
00 LAUNDRY Scenario05, Test 2005-08-03 00:00:00.000 28800
09 ACTIVITIES Scenario05, Test 2005-08-04 00:00:00.000 28800
09 ACTIVITIES Scenario05, Test 2005-08-05 00:00:00.000 28800
09 ACTIVITIES Scenario05, Test 2005-08-08 00:00:00.000 28800
09 DIETARY Scenario05, Test 2005-08-09 00:00:00.000 28800
09 DIETARY Scenario05, Test 2005-08-10 00:00:00.000 21600
09 DIETARY Scenario05, Test 2005-08-11 00:00:00.000 21600
09 DIETARY Scenario05, Test 2005-08-12 00:00:00.000 21600
09 DIETARY Scenario05, Test 2005-08-13 00:00:00.000 21600
09 DIETARY Scenario05, Test 2005-08-14 00:00:00.000 21600
09 DIETARY Scenario05, Test 2005-08-15 00:00:00.000 0
09 DIETARY Scenario05, Test 2005-08-16 00:00:00.000 0
00 DIETARY Scenario06, Test 2005-08-03 00:00:00.000 28800
00 DIETARY Scenario06, Test 2005-08-04 00:00:00.000 28800
00 DIETARY Scenario06, Test 2005-08-05 00:00:00.000 28800
00 DIETARY Scenario06, Test 2005-08-06 00:00:00.000 28800
00 DIETARY Scenario06, Test 2005-08-10 00:00:00.000 25200
00 DIETARY Scenario06, Test 2005-08-11 00:00:00.000 25200
00 DIETARY Scenario06, Test 2005-08-12 00:00:00.000 25200
00 DIETARY Scenario06, Test 2005-08-15 00:00:00.000 28800
00 DIETARY Scenario06, Test 2005-08-16 00:00:00.000 25200
Any advice is appreciated,
Thanks,
-
select Employee, sum(Hours) Total from Table group by Employee
where is the problem ???
* Noel -
I think where the problem was occuring was that I have to select more than column. i.e:
select PERSONFULLNAME,PERSONNUM,Unit,Department,ApplyDate,SUM(Regular) Total from VP_KSS_Overtime
where --ApplyDate >= HomeStartDate
ApplyDate between CURRPAYPERIODSTART and CURRPAYPERIODEND
--AND DateDiff(day,ApplyDate,CurrPayPeriodEnd) < 2
--AND PersonNum='757005'
group by PERSONFULLNAME,PERSONNUM,Unit,Department,ApplyDate
order by PersonFullName
I should have posted the full set of columns in my first post:
PERSONFULLNAME PERSONNUM Unit Department ApplyDate Regular
Scenario05, Test 757005 09 ACTIVITIES 2005-08-04 00:00:00.000 28800
Scenario05, Test 757005 09 ACTIVITIES 2005-08-05 00:00:00.000 28800
Scenario05, Test 757005 09 ACTIVITIES 2005-08-08 00:00:00.000 28800
Scenario05, Test 757005 09 DIETARY 2005-08-09 00:00:00.000 28800
Scenario05, Test 757005 09 DIETARY 2005-08-10 00:00:00.000 21600
Scenario05, Test 757005 09 DIETARY 2005-08-11 00:00:00.000 21600
Scenario05, Test 757005 09 DIETARY 2005-08-12 00:00:00.000 21600
Scenario05, Test 757005 09 DIETARY 2005-08-13 00:00:00.000 21600
Scenario05, Test 757005 09 DIETARY 2005-08-14 00:00:00.000 21600
Scenario05, Test 757005 09 DIETARY 2005-08-15 00:00:00.000 0
Scenario05, Test 757005 09 DIETARY 2005-08-16 00:00:00.000 0
Scenario06, Test 757006 00 DIETARY 2005-08-03 00:00:00.000 28800
Scenario06, Test 757006 00 DIETARY 2005-08-04 00:00:00.000 28800
Scenario06, Test 757006 00 DIETARY 2005-08-05 00:00:00.000 28800
Scenario06, Test 757006 00 DIETARY 2005-08-06 00:00:00.000 28800
Scenario06, Test 757006 00 DIETARY 2005-08-10 00:00:00.000 25200
Scenario06, Test 757006 00 DIETARY 2005-08-11 00:00:00.000 25200
Scenario06, Test 757006 00 DIETARY 2005-08-12 00:00:00.000 25200
Scenario06, Test 757006 00 DIETARY 2005-08-15 00:00:00.000 28800
Scenario06, Test 757006 00 DIETARY 2005-08-16 00:00:00.000 25200
-
Let me try to understand what you expect as output:
PERSONFULLNAME
,PERSONNUM
,Unit
,Department
, TOTAL NUMBER REGARDLESS THE DEPARTMENT ???
if not that can you post an example on how you want the output to look like ?
* Noel -
Yes your correct. I know it sounds a bit strange but it's sql for a crystal report that needs to be tweaked.
I'm thinking that I need an extra column for the total regular hours and keep the existing column for regular hours since I need that for something else.
The output that I need is below:
PERSONFULLNAME PERSONNUM Unit Department ApplyDate Regular TotalReg
Scenario05, Test 757005 09 ACTIVITIES 2005-08-04 00:00:00.000 28800 252000
Scenario05, Test 757005 09 ACTIVITIES 2005-08-05 00:00:00.000 28800 252000
Scenario05, Test 757005 09 ACTIVITIES 2005-08-08 00:00:00.000 28800 252000
Scenario05, Test 757005 09 DIETARY 2005-08-09 00:00:00.000 28800 252000
Scenario05, Test 757005 09 DIETARY 2005-08-10 00:00:00.000 21600 252000
Scenario05, Test 757005 09 DIETARY 2005-08-11 00:00:00.000 21600 252000
Scenario05, Test 757005 09 DIETARY 2005-08-12 00:00:00.000 21600 252000
Scenario05, Test 757005 09 DIETARY 2005-08-13 00:00:00.000 21600 252000
Scenario05, Test 757005 09 DIETARY 2005-08-14 00:00:00.000 21600 252000
Scenario05, Test 757005 09 DIETARY 2005-08-15 00:00:00.000 0 252000
Scenario05, Test 757005 09 DIETARY 2005-08-16 00:00:00.000 0 252000
Scenario06, Test 757006 00 DIETARY 2005-08-03 00:00:00.000 28800 244800
Scenario06, Test 757006 00 DIETARY 2005-08-04 00:00:00.000 28800 244800
Scenario06, Test 757006 00 DIETARY 2005-08-05 00:00:00.000 28800 244800
Scenario06, Test 757006 00 DIETARY 2005-08-06 00:00:00.000 28800 244800
Scenario06, Test 757006 00 DIETARY 2005-08-10 00:00:00.000 25200 244800
Scenario06, Test 757006 00 DIETARY 2005-08-11 00:00:00.000 25200 244800
Scenario06, Test 757006 00 DIETARY 2005-08-12 00:00:00.000 25200 244800
Scenario06, Test 757006 00 DIETARY 2005-08-15 00:00:00.000 28800 244800
Scenario06, Test 757006 00 DIETARY 2005-08-16 00:00:00.000 25200 244800
In a nutshell, I need to show the PersonFullName, PersonNum, Current Unit & Dept that is closest to the currentdate and the Total Regular hours...
The report was easy enought to do originally...I simply broke out the PersonFullName, PersonNum, and Regular horus by Unit and Department in a specified date range. However there could be more than one unit or department for that PersonFullName since they could move between jobs...So the business requirement was then modified to show all hours for specific PersonFullName in the time period but only group it by the current Unit & Dept...
Clear as mud?
-
All you need to do is a join of the the two like:
Select Normal.PERSONFULLNAME,PERSONNUM,Unit,Department,ApplyDate,Total,TotalAccrossDept
FROM
(
select PERSONFULLNAME,PERSONNUM,Unit,Department,ApplyDate,SUM(Regular) Total from VP_KSS_Overtime
where
ApplyDate between CURRPAYPERIODSTART and CURRPAYPERIODEND
group by PERSONFULLNAME,PERSONNUM,Unit,Department,ApplyDate
) Normal
join
(
select PERSONFULLNAME, SUM(Regular) TotalAccrossDept from VP_KSS_Overtime
where
ApplyDate between CURRPAYPERIODSTART and CURRPAYPERIODEND
group by PERSONFULLNAME
) rolledUp on Normal.PERSONFULLNAME = RolledUp.PERSONFULLNAME
* Noel -
Ok, my mental block is gone. I keep forgetting about using derived tables which is so helpfull...
The sql ran well so i'll just incorporate the additional hours columns (overtime, double) etc into my query. VP_KSS_Overtime is a view that I wrote so I may try to incorporate this logic into my view...hopefully i can use this query in Crystal...cause the viwe is pretty big.
Do you think it would be pain to get this logic into my view? See below:
CREATE VIEW VP_KSS_OverTime
AS
SELECT DISTINCT
dbo.VP_RTIMEDTLTOTALS.PERSONFULLNAME, dbo.VP_RTIMEDTLTOTALS.PERSONNUM, dbo.VP_RTIMEDTLTOTALS.ApplyDate,
dbo.LABORACCT.LABORLEV6NM,
case when dbo.VP_RTIMEDTLTOTALS.HOMEACCOUNTSW = 1 then cast(
dbo.LABORACCT.LABORLEV3NM + ' - ' + dbo.LABORACCT.LABORLEV5NM + ' - ' + dbo.LABORACCT.LABORLEV6DSC + ' (' + dbo.LABORACCT.LABORLEV6NM
+ ') - ' + dbo.LABORACCT.LABORLEV7DSC + ' (' + dbo.LABORACCT.LABORLEV7NM + ')' as varchar(255)) Else cast(
LA1.LABORLEV3NM + ' - ' + LA1.LABORLEV5NM + ' - ' + LA1.LABORLEV6DSC + ' (' + LA1.LABORLEV6NM
+ ') - ' + LA1.LABORLEV7DSC + ' (' + LA1.LABORLEV7NM + ')' as varchar(255)) END AS HomeLaborAcc,
(SELECT isnull(SUM(RT.TIMEINSECONDS), 0) AS Reg
FROM dbo.VP_RTIMEDTLTOTALS RT
WHERE RT.EMPLOYEEID = dbo.VP_RTIMEDTLTOTALS.EMPLOYEEID AND
dbo.VP_RTIMEDTLTOTALS.ApplyDate = RT.ApplyDate AND RT.PAYCODEID = 158)
AS Regular,
(SELECT isnull(SUM(RT.TIMEINSECONDS), 0) AS OT
FROM dbo.VP_RTIMEDTLTOTALS RT
WHERE RT.EMPLOYEEID = dbo.VP_RTIMEDTLTOTALS.EMPLOYEEID AND
dbo.VP_RTIMEDTLTOTALS.ApplyDate = RT.ApplyDate AND RT.PAYCODEID = 159)
AS OverTime,
(SELECT isnull(SUM(RT.TIMEINSECONDS), 0) AS Dbl
FROM dbo.VP_RTIMEDTLTOTALS RT
WHERE RT.EMPLOYEEID = dbo.VP_RTIMEDTLTOTALS.EMPLOYEEID AND
dbo.VP_RTIMEDTLTOTALS.ApplyDate = RT.ApplyDate AND RT.PAYCODEID = 160) AS DBL,
(SELECT isnull(SUM(RT.TIMEINSECONDS), 0) AS Dbl
FROM dbo.VP_RTIMEDTLTOTALS RT
WHERE RT.EMPLOYEEID = dbo.VP_RTIMEDTLTOTALS.EMPLOYEEID AND
dbo.VP_RTIMEDTLTOTALS.ApplyDate = RT.ApplyDate AND RT.PAYCODEID = 156)
AS Total, ISNULL
((SELECT PCD.PERSONCSTMDATATXT
FROM dbo.PERSONCSTMDATA PCD
WHERE dbo.PERSON.PERSONID = PCD.PERSONID AND PCD.customdatadefid = 5), 'Unknown') AS Status, ISNULL
((SELECT LEFT(PCD.PERSONCSTMDATATXT, 10)
FROM dbo.PERSONCSTMDATA PCD
WHERE dbo.PERSON.PERSONID = PCD.PERSONID AND PCD.customdatadefid = 2), 'Unknown') AS FTPT,
dbo.VP_RTIMEDTLTOTALS.EMPLOYEEID, case when dbo.VP_RTIMEDTLTOTALS.HOMEACCOUNTSW = 1 then dbo.LABORACCT.LABORLEV6DSC else LA1.LABORLEV6DSC end AS Department, case when dbo.VP_RTIMEDTLTOTALS.HOMEACCOUNTSW = 1 then dbo.LABORACCT.LABORLEV5NM else LA1.LABORLEV5NM end AS Unit,
dbo.VP_RTIMEDTLTOTALS.CURRPAYPERIODSTART, dbo.VP_RTIMEDTLTOTALS.CURRPAYPERIODEND, dbo.VP_RTIMEDTLTOTALS.PREVPAYPERIODSTART, dbo.VP_RTIMEDTLTOTALS.PREVPAYPERIODEND, dbo.VP_RTIMEDTLTOTALS.NEXTPAYPERIODSTART, dbo.VP_RTIMEDTLTOTALS.NEXTPAYPERIODEND,
HA.Effectivedtm as HOMESTARTDATE, HA.Expirationdtm as HOMEENDDATE
FROM dbo.VP_RTIMEDTLTOTALS INNER JOIN
dbo.LABORACCT ON dbo.VP_RTIMEDTLTOTALS.LABORACCTID = dbo.LABORACCT.LABORACCTID INNER JOIN
dbo.PERSON ON dbo.VP_RTIMEDTLTOTALS.PERSONID = dbo.PERSON.PERSONID
JOIN dbo.HOMEACCTHIST HA ON (dbo.PERSON.PERSONID = HA.PERSONID AND
GETDATE() BETWEEN HA.EFFECTIVEDTM AND HA.EXPIRATIONDTM)
JOIN dbo.LABORACCT LA1 ON (LA1.LABORACCTID = HA.LABORACCTID )
WHERE (dbo.VP_RTIMEDTLTOTALS.PAYCODEID = '158') OR
(dbo.VP_RTIMEDTLTOTALS.PAYCODEID = '159') OR
(dbo.VP_RTIMEDTLTOTALS.PAYCODEID = '160')
I'll try to get this work in Crystal first...it was enough of a pain getting this view working...
Thanks again,
-
Hi,
I ended up moving the code in to a new view that references the main one. This code is below:
CREATE VIEW dbo.VP_KSS_HoursRollup
AS
SELECT
Normal.PERSONFULLNAME,Normal.PERSONNUM,
Normal.ApplyDate,Normal.LABORLEV6NM,Normal.HomeLaborAcc,Normal.Status,Normal.FTPT,Normal.EMPLOYEEID,Normal.Department,Normal.Unit,
Normal.CURRPAYPERIODSTART,Normal.CURRPAYPERIODEND,Normal.PREVPAYPERIODSTART,Normal.PREVPAYPERIODEND,Normal.NEXTPAYPERIODSTART,
Normal.NEXTPAYPERIODEND,Normal.HOMESTARTDATE,Normal.HOMEENDDATE,
TotalReg,TotalOT,TotalDBL,cast(TotalReg + TotalOT + TotalDBL AS INTEGER(9))as TotalsRollup
FROM
(
SELECT PERSONFULLNAME,PERSONNUM,ApplyDate,
LABORLEV6NM,HomeLaborAcc,Status,FTPT,
EMPLOYEEID,Department,Unit,
CURRPAYPERIODSTART,CURRPAYPERIODEND,PREVPAYPERIODSTART,
PREVPAYPERIODEND,NEXTPAYPERIODSTART,
NEXTPAYPERIODEND,HOMESTARTDATE,HOMEENDDATE FROM dbo.VP_KSS_Overtime
GROUP BY
PERSONFULLNAME,PERSONNUM,ApplyDate,
LABORLEV6NM,HomeLaborAcc,Status,FTPT,
EMPLOYEEID,Department,Unit,
CURRPAYPERIODSTART,CURRPAYPERIODEND,PREVPAYPERIODSTART,
PREVPAYPERIODEND,NEXTPAYPERIODSTART,
NEXTPAYPERIODEND,HOMESTARTDATE,HOMEENDDATE
) Normal
JOIN
(
SELECT
PERSONNUM,SUM(Regular) TotalReg,Sum(OverTime)TotalOT,
Sum(DBL)TotalDBL FROM dbo.VP_KSS_Overtime
GROUP BY PERSONNUM
) rolledUp on Normal.PERSONNUM = RolledUp.PERSONNUM
This works fine however I have run into one big problem. The hours that are being rolled up are being summed for all records in the view.
In a perfect world, I would be able to use a stored proc that would accept time period params.
Ex. @Current param = 1 would be used to create a where clause that would only grab records in the current time period.
@whereStmt='where ApplyDate between CURRPAYPERIODSTART and CURRPAYPERIODEND'
This would be used as below:
SELECT
PERSONNUM,SUM(Regular) TotalReg,Sum(OverTime)TotalOT,
Sum(DBL)TotalDBL FROM dbo.VP_KSS_Overtime
@whereStmt
GROUP BY PERSONNUM
If i could do this then I would only rollup those hours that are in the current time period or whatever time period was selected by the user.
Does anyone have any idea of how to do this without using a stored proc. The application that generates the Crystal Reports doesn't accept the use of sprocs
Any advice is greatly appreciated, I'm stuck on this on
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply