By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Dynamic weekly date range in crosstab report

ollyb303
P: 74
Hello,

I am using a dynamic crosstab report to track performance statistics for my company and I have hit a problem.

I would like the option to track stats daily (for the last 7 complete days), weekly (for the last 6 weeks) and monthly (for the last 6 complete months).

Daily and monthly are not causing me a problem - I have used the following code to construct the query:

Expand|Select|Wrap|Line Numbers
  1. strXT = "TRANSFORM Sum(Query2.STAT) AS SumOfSTAT " & _
  2. "SELECT Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
  3. "FROM Query2 " & _
  4. "GROUP BY Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
  5. strPivot & ";"
And the following to create the "strPivot" string:

Daily:
Expand|Select|Wrap|Line Numbers
  1. strPivot = "PIVOT [DATE]"
Monthly:
Expand|Select|Wrap|Line Numbers
  1.     If Format(strStopDate, "MMM") = "Jan" Then
  2.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"")"
  3.     End If
  4.     If Format(strStopDate, "MMM") = "Feb" Then
  5.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"")"
  6.     End If
  7.     If Format(strStopDate, "MMM") = "Mar" Then
  8.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"")"
  9.     End If
  10.     If Format(strStopDate, "MMM") = "Apr" Then
  11.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"")"
  12.     End If
  13.     If Format(strStopDate, "MMM") = "May" Then
  14.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"")"
  15.     End If
  16.     If Format(strStopDate, "MMM") = "Jun" Then
  17.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"")"
  18.     End If
  19.     If Format(strStopDate, "MMM") = "Jul" Then
  20.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"")"
  21.     End If
  22.     If Format(strStopDate, "MMM") = "Aug" Then
  23.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"")"
  24.     End If
  25.     If Format(strStopDate, "MMM") = "Sep" Then
  26.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"")"
  27.     End If
  28.     If Format(strStopDate, "MMM") = "Oct" Then
  29.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"")"
  30.     End If
  31.     If Format(strStopDate, "MMM") = "Nov" Then
  32.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"")"
  33.     End If
  34.     If Format(strStopDate, "MMM") = "Dec" Then
  35.         strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"")"
  36.     End If
I am now stuck with how to create the pivot for a dynamic weekly range. Ideally I would like the user to select an end date (any date up to and including yesterday) and the report would give a total for each of the last 6 weeks - preferably the week could run from any day, but if I have to use Mon-Sun that would also be acceptable. Can anyone help me with this? I've googled like there's no tomorrow and I'm not having any luck!

I can supply any more information/code if required.

Many thanks,

Olly
Feb 4 '09 #1
Share this Question
Share on Google+
11 Replies


ollyb303
P: 74
Just a little bump! Can anyone help me with this? Please....?
Feb 5 '09 #2

Expert 100+
P: 1,287
What about [DATE] BETWEEN (EndDate - 42) AND EndDate
Feb 5 '09 #3

ollyb303
P: 74
Thanks Chip, but that would give me a daily total for each day in the 6 week date range (if used in the pivot). What I'm after is a weekly total for each of 6 weeks.

Any other ideas?

Many thanks
Feb 6 '09 #4

ollyb303
P: 74
Still at a loss with this one.... anyone?
Feb 9 '09 #5

ollyb303
P: 74
So I'm guessing there's no way to do what I want to do in this case :(

Think I may have to give up on this idea unless anyone wants to chime in?
Feb 11 '09 #6

Expert Mod 2.5K+
P: 2,545
Hi Olly. You can calculate a weekly interval in the base query on which you are totalling - for example by using

DateDiff("w", [your transaction date], date()).

If you put a where clause of <=6 on this you will restrict the query to the last 6 complete weeks. You can then pivot the data on the number of weeks - 0 is the current week (which is generally incomplete of course), 1 the previous week and so on, totalling the transactions for each week as appropriate.

I leave it for you try this out on your own data and to do some form of week-beginning calculation for your pivot headers. I've tried this approach on a simple transaction count basis (producing the number of transactions received in each week) and it works fine for me (without fancy weekdate headers).

-Stewart
Feb 11 '09 #7

Expert Mod 2.5K+
P: 2,545
... and further to the above here is a function which will return the start date of the current week. It can be used to provide custom date headers for use with your datediff-based calculations:

Expand|Select|Wrap|Line Numbers
  1. Public Function fStartofWeek(somedate, Optional StartDayNo = 2)
  2.     'Returns the date of the first day of the current week
  3.     'Default is to use the system-defined start day no of 2
  4.     '(corresponding to Monday). Range of 1 to 7 for this value.
  5.     '
  6.     Dim intDayOfWeek As Integer, intAdjustDays As Integer
  7.     If Not IsNull(somedate) Then
  8.         intDayOfWeek = Weekday(somedate)
  9.         intAdjustDays = intDayOfWeek - StartDayNo
  10.         If intAdjustDays < 0 Then
  11.             intAdjustDays = intAdjustDays + 7
  12.         End If
  13.         fStartofWeek = CDate(somedate - intAdjustDays)
  14.     End If
  15. End Function
An example query based on a test table (no relation to your own), which uses a custom format for the date to order the crosstab correctly:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(tblTemp.CallTime) AS CountOfCallTime
  2. SELECT "Test" AS TestGroup
  3. FROM tblTemp
  4. WHERE (((DateDiff("w",fStartOfWeek([CallDate]),Date()))<=6))
  5. GROUP BY "Test"
  6. PIVOT Format(fStartofWeek([CallDate]),"yyyy-mm-dd");
And the result of that query:

Expand|Select|Wrap|Line Numbers
  1. TestGroup 2008-12-22 2008-12-29 2009-01-05 2009-01-12 2009-01-19 2009-01-26 2009-02-02 2009-02-09
  2. Test              78        219        227        221        206        232        187         85
-Stewart
Feb 11 '09 #8

ollyb303
P: 74
Stewart, thank you so much!

As I'm sure you can tell, I'd pretty much given up hope of this working!

Haven't tried it out yet, but it looks very promising - I'll let you know how I get on.

Thanks again,

Olly
Feb 11 '09 #9

ollyb303
P: 74
@Stewart Ross Inverness
Hi Stewart,

I've tried this and I have a new problem...

I should have mentioned that my initial query is actually a SQL pass-through to an Oracle db, so (as I now realise) DateDiff won't work.

Do you know how to write this:
DateDiff("w", [your transaction date], date()).

In a syntax Oracle will accept?

Many thanks.
Feb 11 '09 #10

Expert Mod 2.5K+
P: 2,545
Hi Olly. I can only suggest that you feed the result of the pass-through query to an Access query (using the pass-through to provide a view of the Oracle data) and perform the calculations in Access - once the data has been obtained from the Oracle back-end DB Access should be able to process it as normal.

The original Oracle query would not be able to handle the date function I have suggested either I guess...

-Stewart
Feb 11 '09 #11

ollyb303
P: 74
Good idea. Thanks very much for your help.
Feb 12 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.