Interesting Group By Week Dilemma 1

[LittlBUGer]

Hello all. I'm working on sort of a time keeping program for the place I work. Everything is done except for a few administrative reports. One of the reports needs to grab certain information from the SQL database based on each employee's hours (worked hours) entries that they input. This report is grabbing the employee's name, the entry date, the project worked on, the comment left by employee, and the hours worked. Based on a certain selected criteria, the SQL can change slightly and thus either display information for a single employee for all projects within a given date range or for all employees for a single project for a given date range. Either way, there's a certain date range.

What I need to do is not only grab each of the hours entries, but create subtotals of the hours worked based on day and on week (a week being Sun - Sat). I have a SQL query that does all of this fine up through the day subtotal, but I can't quite figure out how to get the week subtotal.

Here's my query if it will help:

Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
Where hours.EmpID='3' and not Hours.complete='D' AND wdate BETWEEN '8/1/2006' AND '8/31/2006' 
	UNION ALL Select username, wdate, 'ZZY', '998', 'ZZZ', '999', 'ZZZ', '999', '', '', 'DaySubTotal', SUM(SumDayQuery.whours) 
	FROM (Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
		From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
		Where hours.EmpID='3' and not Hours.complete='D' AND wdate BETWEEN '8/1/2006' AND '8/31/2006' ) As SumDayQuery 
	Group By username, wdate 
	UNION ALL Select username, wdate, 'ZZZ', '999', 'ZZZ', '999', 'ZZZ', '999', '', '', 'WeekSubTotal', SUM(SumWeekQuery.whours) 
	FROM (Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
		From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
		Where hours.EmpID='3' and not Hours.complete='D' AND wdate BETWEEN '8/1/2006' AND '8/31/2006' ) As SumWeekQuery 
	Group By username, wdate 
Order By 1, 2, 3, 4, 5, 6, 7, 8, 12

I know it looks confusing, but it's quite simple. The things that can change are the EmpID (the employee's ID) and the data range in the Where clause. The 2 UNION ALL's up above are giving the subtotal line, with the first union doing it by day, the 2nd union doing it by week (though right now it's just doing the same thing as the day union). Then later in the code I change those subtotal lines to be formatted nicer as the rest of the code is in ASP.NET (the triple 9's and Z's are just so it orders correctly).

Thus, if I'm creating a report for the month (August is shown above), then it will show each hours entry for that employee and a day subtotal for hours after each day. I then also need a subtotal of hours for each week with a week being from Sun. to Sat. Thus if the date range doesn't start on a Sun., the first week needs to be from the start date to the next Sat., and so on. If the end date isn't on a Sat., then the week subtotal needs to be from the previous Sun. to the end date. Pretty simple idea but difficult to implement, at least that's how I see it.

If anyone understands what I'm trying to do, maybe there's something I'm missing and you can help me? Thank you for your time.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[PWise]

if you want to have totals for each week group by

DATEPART ( ww , date )

 

[AlexCuse]

I am not sure what you are trying to do here. Are you trying to group by week in your query or your front end?

To group by week (in your query), rather than WDate you would want to select (and group by)

datepart(week, WDate)

instead of WDate in your query. If you add

datepart(month, Wdate)

To your select and group by, this will split your data by month as well, accounting for 'split weeks' (because it is grouped by month as well as week).

I could be way off here, but it seems to me like this is something like what you are trying to do.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[PWise]

From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
        Where hours.EmpID='3' and not Hours.complete='D' AND wdate BETWEEN '8/1/2006' AND '8/31/2006'[COLOR=red]
group by datepart(ww,wdate)[/color] ) As SumWeekQuery

 

[LittlBUGer]

Alright, after I added that to the Select and Group by, it seems to work OK, but now all of those entries are at the very top instead of ordered by weeks in the output (or rather almost every 7 day subtotals the week subtotal will be there too). That and I need to figure out how to grab those week range dates so I can use them elsewhere in my code. Any more suggestions? Thank you for your help thus far.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[AlexCuse]

I think you don't want to order by your date field. datepart(week, wdate) will return an integer, and this will always be placed before your date fields.

In order to get the start/end dates for a week, I would look at adding a calendar table to your DB. Then it would be as simple as

select min(date), max(date) from calendar 
where year = year 
and week = week

I don't know of any other way to do this.

Check out this link for more info, and instructions to create a calendar table:

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[LittlBUGer]

Well, I can't create another table, but the hours table does not only have the wdate (full date) field, but a day, a month, and a year field as well, if that could help anything.

If I can just figure out how to grab the date range that DatePart is returning, then I can fix the format and sorting outside the SQL query in other code. Otherwise there's gotta be a way with the query to do this I would think, or at least I hope. I mean if it's easy enough for doing subtotals for entire days, why not weeks as well?

Thanks for the extra info.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[AlexCuse]

You are easily getting the subtotal by week, it is simply a matter of displaying the start and end date. The problem is that you cannot display date and group by week.

If I were you I would think about trying to total on your front end (did you say it's ASP somewhere??). If so you may be able to cobble something together using MS's calendar and date functions.

Let me know what you end up doing, this is an interesting problem.

Alex



A wise man once said
"The only thing normal about database guys is their tables".

 

[LittlBUGer]

I don't need to display the dates in the week subtotals just from the SQL query. I modify what shows up in the datagrid so most of those colums of information either go away or get re-arranged somehow, so if I can still group by the week and sort/order by it properly without having to display it as well, then that would be great. If not, I'll see what I can do with the code outside the query. And yes, I'm coding in ASP.NET.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[AlexCuse]

If it's just an issue of ordering properly, one idea might be to include the datepart (week) in all segments of your union. Also include space for date.

Populate week the same in both queries.

Then in the date field, for your weekly total section select a date as a text string. Use the maximum date value that your data type permits.

Then order by year, month, week, date

Hope this helps you out.

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[LittlBUGer]

OK, I figured it out. Instead of using the DatePart function like was suggested, I needed to use the DateAdd function. By itself, it looks like this:

dateadd(day, 7-datepart(weekday, wdate), wdate)

For the entire query, here's what it looks like:

Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
	From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
	where hours.EmpID='49' and not Hours.complete='D' AND wdate BETWEEN '10/1/2006' AND '10/31/2006' 
UNION ALL Select username, wdate, 'ZZY', '998', 'ZZZ', '999', 'ZZZ', '999', '', '', 'DaySubTotal', SUM(SumDayQuery.whours) 
	FROM (Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
		From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
		where hours.EmpID='49' and not Hours.complete='D' AND wdate BETWEEN '10/1/2006' AND '10/31/2006' ) As SumDayQuery 
	Group By username, wdate 
UNION ALL Select username, [COLOR=red][b]dateadd(day, 7-datepart(weekday, wdate), wdate)[/b][/color], 'ZZZ', '999', 'ZZZ', '999', 'ZZZ', '999', '', '', 'WeekSubTotal', SUM(SumWeekQuery.whours) 
	FROM (Select (lastname + ', ' + firstname) as username, wdate, projectname, projectid, phasename, phaseid, taskname, taskid, clin.clin, clin.clindesc, comment, whours 
		From (SELECT DISTINCT clin, clindesc FROM Clin) AS Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID 
		where hours.EmpID='49' and not Hours.complete='D' AND wdate BETWEEN '10/1/2006' AND '10/31/2006' ) As SumWeekQuery 
	Group By username, [COLOR=red][b]dateadd(day, 7-datepart(weekday, wdate), wdate)[/b][/color] 
Order By 1, 2, 3, 4, 5, 6, 7, 8, 12

Thanks again for all of your help!

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[AlexCuse]

Glad you got it sorted out. I like the solution you came up with, I had not thought of that option. That will make it easy enough to return the start date as well.

Thanks for posting back your fix!

Alex

A wise man once said
"The only thing normal about database guys is their tables".