Report query woes

[bsingin64]

I'm writing a query for a timesheet report. our company uses an interesting 4 hour minimum per day per person rule, in which if the total time entries on monday this week doesnt total 4, then it adds (if they had 3 it would add 1 ) to the bottom of each persons daily subtotals.

the report looks like this

rows: Associate Name, multiple project numbers (depending on data), 4hour minimum row, and a total displayed in the group footer with the total hours and #of hours over 40

columns: monday,tuesday,.....sunday, Total. The total column doesnt HAVE to show hours for that one project from monday - sunday, but it'd be nice

My problem is, i can get a query to calculate the 4hour minimum, and convince the report to spit out mon-sun, but I cant convince the record set to pad out the horizontally grouped data to include the days that there is no data, so the vertical columns dont line up....from one project to the next, monday should all be lined up, but if someone didnt work on a project on monday, then the detail section of my report gets bumped left, and i end up seeing tues(weds thurs, whichever is first) where i'd want to see monday with a 0 or blank. I dont know how to make that query work.

This is what I have so far:

SELECT Name, EntryDate, Hours, FSRNum, '0' AS ORDERME
FROM dbo.TQSAccPayrollStaging
WHERE (FSRNum IS NOT NULL) AND (FSRNum <> '')
UNION ALL
SELECT Name, EntryDate, 4 - SUM(Hours) AS Hours, '00' AS FSRNum2, '1' AS ORDERME
FROM dbo.TQSAccPayrollStaging
WHERE FSRNum IS NOT NULL AND FSRNum <> ''
GROUP BY Name, EntryDate
HAVING SUM(Hours) < 4

the second half of the union puts the 4hour minimum entries into the result set.

How do i Pad my recordset with blank entries for dates where there is no data?

 

[SQLBill]

What is FSRNum?

I would suggest a CASE in the SELECT...

CASE WHEN Hours IS NULL
       THEN 0
     ELSE 4 - SUM(Hours)
END

Check out the BOL for more information on CASE and how to use it. My suggestion might not be the best way to use it and might not give the results you desire, but it should point out out how to use it. Now it's up to you to test it.

-SQLBill

Posting advice: FAQ481-4875

 

[bsingin64]

problem is, i need to show the actual still. I ended up doing pre processing and filling a temp table with 0 entries for the whole week, and then in the select statment I grouped and summed the hours field, all entries with a 0 still showed on the report, but just showed as a "-" which, interestingly enough, is exactly what i wanted it to do. I'm going to use Coldfusion to pre-process the results, and then use ASP to launch the report viewer through an html request in ColdFusion. that way i can do the processing in easy to read code, and the ASP still drives the universal Report viewer