Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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?
--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_Day

Open in new window

0
BobRosas
Asked:
BobRosas
  • 2
  • 2
2 Solutions
 
TempDBACommented:
You can go with two different ways here:-
1. Either you can create 3 different datasets  with your query's and then in your report can create 3 different table and then associate different dataset with different tables. Here when you will view the report, you can see three different tables there.

2. If you want to show everything in one single table in the report, then just use union all between your queries. Or if you are confused then create a procedure(which any way will be better as its plan will be cached in the server) rather than queries. INsert into the temporary table, the result of your 1st query, followed by insertion from 2nd and then 3rd. Now, fetch the record from the temporary table.
0
 
BobRosasAuthor Commented:
Thank you for your response!  
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?
0
 
TempDBACommented:
The same parameters will be applied to all the queries, right? That's what I made from your statement during the post.
Now, when you create a procedure, you can pass the parameters. You can use the parameter n number of times inside the procedure. I mean to say that the scope of parameter(variable) is there till when the procedure is not completed. So, you can use the same parameter for all the three queries.
0
 
BobRosasAuthor Commented:
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!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now