Pivot by Date Range

  • Thread starter Thread starter WAstarita
  • Start date Start date
W

WAstarita

I have a payroll database that daily I have to check if everyone's time
sheet has been submitted. I would like to automate the query by being
about to supply a range of dates to pivot on. For example


Select * From TimeSheets
....
.....
Pivot on Between #11/12/2006# and #11/19/2006#

Output
11/12/2006 11/13/2006 11/14/2006

Tech1 Yes No Yes
Tech2 Yes Yes No
Tech3 No Yes No

I know I can build the query in VBA easily but I was hoping for a SQL
way of doing it, just passing parameters.

Any suggestions, thanks
 
You actually want the dates to show as the column headings, so it is the
date you are pivoting on.

It looks like you want a Yes as the value if the tech has one or more
(somethings) on that date, and a No as the value if they did not. The
crosstab would therefore pivot on the date, group by the techy, and count
the number of entries as the value. Then test if the count is null
(indicating there were no records.)

This kind of thing:

TRANSFORM (Count(tblInvoice.InvoiceID) Is Not Null) AS TheValue
SELECT tblInvoice.ClientID
FROM tblInvoice
GROUP BY tblInvoice.ClientID
PIVOT tblInvoice.InvoiceDate;
 
Right, but I want to look at a specific 2 week range at time and don't
want to manually program:
Pivot 11/13/2006, 11/14/2006, ect.

The underlying problem is I want it to show dates that may not exist at
all, for example, timesheets are entered for half the guys on the 1st,
none on the 2nd and a few on the third, the 2nd will never show up on
the pivot unless i manually add it in, which is why my dream scenario
is

Pivot Between 11/1/2006 and 11/13/2006
or better yet
Pivot Between [StartDate] and [StartDate]+13 and show column headings
for all dates between the two, whether or not they exist
 
Right. The dates have to come from somewhere, so you will need a table of
all the possible dates. You can populate such a table programmatically to
avoid typing them in. The function below illustrates how to do that.

Once you have this table of dates, you can outer join it to your exising
table, so it shows all records from the table of dates, and any matches from
the tech's data table. (To get the outer join, duble-click the line between
the 2 tables in query design.)

Next you want to add criteria. To do that:
1. Drag the date field from the table of dates into the query grid.
2. Choose Where in the Total row.
3. Enter the date range in the Criteria row.

If you want to use paramters for the dates, you can do that as long as you
declare them (Parameters on Query menu.) You can even use parameters that
refer to the text boxes on the form, as long as you put exactly the same
names into the Parameters dialog.

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
Yeah, I was hoping to avoid that but if you don't know I guess it can't
be done SQL'ly

Since I only ever want to look at 2 weeks at a time I'm going to set
the Pivot to read like this

Pivot [ParamDate], [ParamDate]+1, [ParamDate]+2, ect...

Thanks
 
"WAstarita"wrote:
Yeah, I was hoping to avoid that but if you don't know I guess it can't
be done SQL'ly

Since I only ever want to look at 2 weeks at a time I'm going to set
the Pivot to read like this

Pivot [ParamDate], [ParamDate]+1, [ParamDate]+2, ect...
Click to expand...

PMFBI

one alternative:

tblNum (one field "Num" type Long)

where field values {at least } go from
1 to 14.....

qryEnum2Wks

PARAMETERS StartDate DateTime;
SELECT tblNum.Num, [StartDate]+[Num]-1 AS EnumDate
FROM tblNum
WHERE (((tblNum.Num) Between 1 And 14));

LEFT JOIN this query to your table....

one advantage is designing a report for Day1, Day2, ....

PIVOT "Day" & qryEnum2Wks.Num

then show range of max and min in report header
or footer....
 
Back
Top