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 each day of the 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 (through well sized horizontal detail section), 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.

the source data is as follows: Name, Date, Project, Hours = 1 record. so there could be 4 projects he worked on through out the day, and on tuesday, he may have only worked on 3, this would make the report break unless i can figure out how to pad the recordset with blanks for dates that he didnt 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?

P.S. the report structure is groups by username, then by projectNumber, then dumps dates horizontally, and subtotals in the username footer

 

[lbass]

Instead of attempting to manage this entirely in the query, I think you could manage this in the report by creating a manual crosstab with formulas like this:

//{@Mon}:
if dayofweek({table.date}) = 2 then <your hours expression here>

//{@Tue}:
if dayofweek({table.date}) = 3 then <your hours expression here>

Then you could insert a maximum on each detail formula and then suppress the detail section.

-LB

 

[bsingin64]

nice idea. Ill keep that in mind in the future. I've had hard times playing with crosstabs in any reporting software, they always seem a LITTLE too restrictive. But i've never thought of using formula'ed crosstabs...

I ended up doing this though:
Pre processing and filling a temp table with all the "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