BobRosas
asked on
Combine 3 SQL reports into one.
I have code for 3 different reports in Reporting Services attached to SQL db. (see attached code)
1)Tardy (late to work)
2)Break (late returning from break)
3)Lunch(late returning from lunch)
They all 3 work. They all require the same 3 parameters
1)From Date
2)To Date
3)Supervisor
Now I’ve been asked to combine the 3 into one report. What’s the easiest way to do that?
I adjusted the reports so that they all have the same fields in the output result. I thought then I could union them. But the code for each report is so different I don’t even know where to start.
Is a union the best way? Is there a better/easier way?
Would you please provide details on how I should go about this?
1)Tardy (late to work)
2)Break (late returning from break)
3)Lunch(late returning from lunch)
They all 3 work. They all require the same 3 parameters
1)From Date
2)To Date
3)Supervisor
Now I’ve been asked to combine the 3 into one report. What’s the easiest way to do that?
I adjusted the reports so that they all have the same fields in the output result. I thought then I could union them. But the code for each report is so different I don’t even know where to start.
Is a union the best way? Is there a better/easier way?
Would you please provide details on how I should go about this?
--Tardy
SELECT Supervisor,
'Start' as 'Type',
a.EmployeeId,
a.Name,
a.Expected,
a.Actual,
CONVERT(varchar, FLOOR(a.Tardy / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, a.Tardy % 60),2) AS HrMin,
n.ShiftNote
FROM
(SELECT
CONVERT(varchar(20), s.TimeIn, 100) AS Expected,
s.TimeIn,
l.LastName,
l.FirstName,
l.EmployeeId,
l.ManagerUID,
u.UserName as Supervisor,
l.LastName + ', ' + l.FirstName AS Name,
CONVERT(varchar(20), MIN(h.TimeIn), 100) AS Actual,
CASE WHEN s.TimeIn < MIN(h.TimeIn) THEN DATEDIFF(MINUTE, s.TimeIn, ISNULL(MIN(h.TimeIn), s.TimeIn)) END AS Tardy,
MIN(h.RecordId) AS RecordId
FROM dbo.EmployeeHours AS h
INNER JOIN dbo.EmployeeSchedules AS s ON h.EmployeeId = s.EmployeeId
AND DATEADD(day, 0, DATEDIFF(DAY, 0, s.TimeIn)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0,h.TimeIn))
INNER JOIN dbo.EmployeeList AS l ON s.Company = l.Company AND s.EmployeeId = l.EmployeeId
LEFT JOIN dbo.UserList AS u ON l.ManagerUID = u.UID and l.Company = U.Company
WHERE (l.Suspend = 0)
AND u.UserName = @Supervisor
AND (DATEADD(DAY, 0, DATEDIFF(DAY, 0, h.TimeIn))
BETWEEN @From AND @To)
GROUP BY l.EmployeeId, l.ManagerUID, s.TimeIn, u.UserName, l.LastName, l.FirstName) AS a
--LEFT OUTER JOIN dbo.UserList AS u ON a.ManagerUID = u.UID
LEFT OUTER JOIN dbo.EmployeeShiftNotes AS n ON a.RecordId = n.RecordId AND a.EmployeeId = n.EmployeeId
WHERE (a.Tardy >= 0)
ORDER BY
Name,
a.EmployeeId,
a.TimeIn
--Break…
select Supervisor,
'Break' as 'Type',
EmployeeID,
Name,
convert(varchar(24), TS_Day,100) as TimeOut,
convert(varchar(24), (dateadd(ss,sum(ts_seconds),TS_Day)),100) as TimeIn,
convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+
substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,
ShiftNote
from
(SELECT
TS_Day,
L.EmployeeId,
L.LastName + ', ' + L.FirstName AS Name,
U.UserName AS Supervisor,
N.ShiftNote,
L.ManagerUID,
Sum(TS_Seconds) as ts_Seconds
FROM
(
SELECT company, --do I need company here?
employeeid,
recordid,
convert(datetime,convert(varchar(20),[timeOut],100)) as TS_Day,
timein,
[timeout],
BreakFlag,
IsNull (
( SELECT TOP 1 case when t1.Breakflag = 1 THEN
CASE WHEN
datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 960 then
datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) else 0 end else 0 end
FROM EmployeeHours t2
WHERE t2.employeeid = t1.employeeid
AND t2.company=t1.company
AND convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein) -- must be same day
AND (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
ORDER BY
t2.timein asc,
t2.timeout asc,
recordid asc),0) as ts_seconds
FROM EmployeeHours t1
) times
INNER JOIN EmployeeCustomFieldsData C ON (times.EmployeeId = C.EmployeeId) AND (times.Company = C.Company)
INNER JOIN EmployeeList L ON (C.EmployeeId = L.EmployeeId) AND (C.Company = L.Company)
LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
LEFT OUTER JOIN EmployeeShiftNotes N ON L.RecordId = N.RecordId AND L.EmployeeId = n.EmployeeId
WHERE convert(datetime,convert(char(8),times.TimeIn,112))
BETWEEN @From and @To
AND L.Suspend = 0
AND C.CustomFieldData in ('DH', 'CL', 'EX')
AND times.Company=1
AND U.UserName = @Supervisor
GROUP BY times.employeeid, L.EmployeeId, L.LastName + ', ' + L.FirstName, U.UserName, N.ShiftNote, ManagerUID, TS_Day, times.recordid
) daily
GROUP BY employeeid, name, Supervisor, ShiftNote, ManagerUID, TS_Day, ts_seconds
HAVING (sum(ts_seconds) / 60) >16
ORDER BY
name,
employeeid,
TS_Day
--Lunch
SELECT Supervisor,
'Lunch' as 'Type',
EmployeeID,
Name,
CONVERT(varchar(24), TS_Day,100) as TimeOut,
convert(varchar(24), (dateadd(ss,sum(ts_seconds),TS_Day)),100) as TimeIn,
CONVERT(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+
substring((CONVERT(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,
ShiftNote
FROM
(
SELECT TS_Day,
L.EmployeeId,
L.LastName + ', ' + L.FirstName AS Name,
U.UserName,
U.UserName as Supervisor,
N.ShiftNote,
Sum(TS_Seconds) as ts_Seconds
FROM
(
SELECT company,
employeeid,
recordid,
convert(datetime,convert(varchar(20),[timeOut],100)) as TS_Day,
timein,
[timeout],
BreakFlag,
IsNull (
( SELECT TOP 1 case
WHEN t1.Breakflag = 2 THEN case
WHEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 1860 THEN
datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) else 0 end else 0 end
FROM EmployeeHours t2
WHERE t2.employeeid = t1.employeeid
AND t2.company=t1.company
AND convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein) -- must be same day
AND (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
ORDER BY t2.timein asc, t2.timeout asc, recordid asc),0) as ts_seconds
FROM EmployeeHours t1
) times
INNER JOIN EmployeeCustomFieldsData C ON (times.EmployeeId = C.EmployeeId) AND (times.Company = C.Company)
INNER JOIN EmployeeList L ON (C.EmployeeId = L.EmployeeId) AND (C.Company = L.Company)
LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
LEFT JOIN EmployeeShiftNotes N ON L.RecordId = N.RecordId AND L.EmployeeId = N.EmployeeId
WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @To
AND L.Suspend = 0
AND C.CustomFieldData in ('DH', 'CL', 'EX')
AND times.Company=1
AND U.UserName = @Supervisor
GROUP BY times.employeeid,
L.EmployeeId,
L.LastName + ', ' + L.FirstName,
U.UserName,
N.ShiftNote,
TS_Day,
times.recordid
) daily
GROUP BY name,
employeeid,
Supervisor,
Shiftnote,
TS_Day,
ts_seconds
HAVING (sum(ts_seconds) / 60) >31
ORDER BY
name,
employeeid,
TS_DayASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's what I needed! I did not know that about the parameters! Now I just need to figure out the code to insert into a temporary table and I think I have enough to get started.
Thank you for all your help!
Thank you for all your help!
ASKER
Choice #2, using procedures sounds like the way to go. Can you tell me more about how I'd do the parameters? If you run 1 procedure at a time and insert into temporary table, wouldn't that mean the user would have to enter the parameters 3 times?
I'm gusesing the answer is no, so could you tell me more about how that part is done?