Group by Date from Parameter Range 1

[dleewms]

Hi,

I've read several posts on grouping on a range of dates entered through start and end date parameters and if I understand correctly this works well when grouping only by the date range. However, I need three groups in my report. I'm grouping by manager, then employee id and then dates within the start and end dates. Anybody know if this is possible? Below is a sample of what I need if start and end dates are 08/01/2011 and 08/03/2011.

Brown, Buster
Adams, Clyde
08/01/2011
08/02/2011
08/03/2011
Jones, Katrina
08/01/2011
08/02/2011
08/03/2011

Smith, Daniel
Watson, Lisa
08/01/2011
08/02/2011
08/03/2011

 

[lbass]

All you need to do is limit the dates to that range in the record selection formula. Then group on date on change of day.

Or are you saying that there is only one record with a start date and an end date and you want to create a separate display of dates within the range AS IF there were separate records per day? If so, is this just for display of those dates or are you then going to want to do something with each individual date like add other data fields or something?

-LB

 

[dleewms]

I'm not sure if I understand your question, but I think the correct answer may be the latter. These dates are for display only purposes.

Here is why I need them. I have a view of all employees with a left outer join to their work schedules. Users will enter in a two week date range and I want to sum the hours for every day within that range. I will then link our payroll data, which is imported from an outside source and has no relationship to the work_sched table, to the Employee table and sum how many hours they were clocked in for each day during the time period. Here is the current state of my view.

Select Mgr.first_name MgrFirstName, Mgr.Last_name MgrLastName, Mgr.Emp_code MgrEmpCode,
Emp.Emp_id, Emp.Emp_code, Emp.branch_code, Emp.first_name, Emp.last_name,
Emp.User_def2, tech_type, v.vehicle_code,
ws.sched_sts, ws.prev_sched_sts, ws.arv_time,
ws.dis_time, ws.cmp_time, ws.labor_qty, ws.travel_qty,
DPH.Start_time
from Employee Emp
LEFT OUTER JOIN Employee Mgr
on Emp.Manager_id = Mgr.Emp_id
LEFT OUTER JOIN technician tech
ON Emp.emp_id = tech.technician_id
LEFT OUTER JOIN tech_vehicle_assignment tva
ON tech.technician_id = tva.technician_id
LEFT OUTER JOIN vehicle v
on tva.vehicle_id = v.vehicle_id
LEFT OUTER JOIN work_sched ws
ON Emp.emp_id = ws.technician_id
AND ws.arv_time>='2007-01-01'
AND (ws.sched_sts in ('BB', 'BI', 'CL', 'CO')
or (ws.sched_sts = 'HO' and ws.prev_sched_sts = 'CO'))
WHERE tech.tech_type = 'FSV'

 

[lbass]

I'm not following the logic of what you are trying to do. Why not just check the hours clocked in when the days they worked fall into the parameter date range? Why do you need some preliminary sum of possible work hours?

I also do not follow why you need to display dates within the parameter range separately. Can you explain whether you would really be working with individual dates from that parameter range or do you just need the calculation of work hours in that range?

-LB

 

[dleewms]

So sorry for the confusion. The challenge is that I had employee hours tracked in three different, and barely related, databases. My task was to create a report where the daily totals (sum of hours) from these three databases displayed for each date in a range which has been specified by the end user.

I wound up doing most of the work in TSQL. I created a stored procedure with all of my linking and calculated summaries and used it as the datasource for the report.

Thank you very much for your efforts to assist me.