June 15, 2008 at 6:22 pm
G'day all,
I was conflicted whether I should tag this question as TSQL or Reporting Services, but I figure the solution is going to be an SQL one, so here we go 🙂
I will outline the job, and then outline how I _think_ the best way to solve it is. I am totally open to new ways of approaching this one!
I do reporting work for schools, and I am attempting to automate some report delivery using SQL Reporting Services 2000 and the Data Driven Subscription method.
What I need to do, is have the job fire, understand what the date is, and retrieve the results from last week (all data entry having been caught up on by admin staff) and forwarded to exec teams etc.
I have a proprietary database to work with which has its own weird schema that needs to get compiled and verified which means I cant just go and add/remove things on a whim. I am wondering if I can get the functionality I want without having to add another column.
What I have to work from is a table called KCC (calendar table)
column example
kcckey 2008-06-16
daytoday monday
day_type S (school day)
julian 168 (168th day of the year)
school_day_squence 87 (87th school day of the year)
semester 1 (two semesters in a year)
term 2 (2 terms in a semseter, 4 terms a year)
week 10 (10 weeks a term, 20 a semester, 40 a year)
Ill try and explain what I'm doing.. if this is the data... (cut down to what Im focusing on to try and solve)
kcckey daytoday semester term week
2008-06-12 thursday 1 2 9
2008-06-13 friday 1 2 9
2008-06-14 saturday 0 0 0
2008-06-15 sunday 0 0 0
2008-06-16 monday 1 2 10
2008-06-17 tuesday 1 2 10
2008-06-18 wednesday 1 2 10
I want the job to fire tuesday 2008-06-17 and then understand: "oh! I'm in semester 1 term 2 week 10. Sooo.. I need to report on semester 1 term 2 week 9"
I've scratched my head for a good while and thought I might be able to get the current week value and then hard code a -1 then week 10 becomes week 9 right? That would work, but then fail when the term cycled over and start a new round of 10 weeks. ie: week 1 minus 1 week is not week 10 of the last term.
My thinking is... I add a new column.. reportID or something like that.
reportID kcckey daytoday semester term week
19 2008-06-12 thursday 1 2 9
19 2008-06-13 friday 1 2 9
2008-06-14 saturday 0 0 0
2008-06-15 sunday 0 0 0
20 2008-06-16 monday 1 2 10
20 2008-06-17 tuesday 1 2 10
20 2008-06-18 wednesday 1 2 10
That way there is a clear nomination of what "dates" kcckeys belong to what period so I _should_ be able to write a query that uses getdate() finds out the current corresponding ReportID for the time it fires, -1 and then fetch the semester/term/week information for my data driven subscription.
Thats how I am seeing it anyhow, but I would love a different perspective.
Any other ideas on how to skin this cat? Or am I doing it the best way?
Many thanks in advance!
- Damien
June 15, 2008 at 8:05 pm
You should have another column in your Calendar table - No of Term Week.
It will hold all term weeks in sequence, 1st week of next term will continue the sequence from the last week of previous term.
For example, Week 1 of Term 2 of Year 2008 has TermWeekNo = 123 (since the beginning of times for your system). Then if you happen to run you query on a day within that week you need just select the data within the data range where TermWeekNo = 122.
Hope it's not too much confusing.
_____________
Code for TallyGenerator
June 15, 2008 at 8:07 pm
I guess I am not seeing the problem - since kcckey is a date column, couldn't you just do something like:
DECLARE @current_sunday datetime
,@previous_sunday datetime;
SET @previous_sunday = DATEADD(day, DATEDIFF(day, 0, getdate()) / 7 * 7 - 1, 0);
-- Now in your query you can use the following:
SELECT column list
FROM your table
WHERE kcckey >= @previous_sunday
AND kcckey < DATEADD(week, 1, @previous_sunday);
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 8:34 pm
As the replies come through I'm starting to understand my problem more. I am struggling with this problem because the report doesnt need to fire off at a single regular interval. Sometimes there is a 4 week gap between terms for Holidays, sometimes there is a 1 week gap for holidays..
So I _also_ need to build in checking that stops this report firing when the holidays are on. It sounds pretty hardcore.. maybe I will simply have a separate schedule for each term..
June 15, 2008 at 8:42 pm
So I _also_ need to build in checking that stops this report firing when the holidays are on. It sounds pretty hardcore.. maybe I will simply have a separate schedule for each term..
Damien, read my post again.
It solves this problem as well.
If you run query
WHERE TermWeekNo = TermWeerNo(currentDate) - 1
then if current date is not within any TermWeek than TermWeekNo will be NULL and no rows will be returned.
Easy and pretty solid.
_____________
Code for TallyGenerator
June 15, 2008 at 10:53 pm
Thanks Sergiy,
I don't specifically understand exactly how you're using your syntax, but I do understand the significance of adding another column that gives relevance to the datetime field kcckey.
Thank you for your help and also everyone else that answered. Your thoughts are very much appreciated 🙂
- Damien
June 16, 2008 at 6:58 pm
I don't specifically understand exactly how you're using your syntax,
I don't. 🙂
It was not actual syntax, of course.
What I meant is you need to join table to itself
ON T1.TermWeekNo = T2.TermWeekNo + 1
"T1" will containt single record
WHERE Date = dbo.DateOnly(GetDate()) -- it's a function cutting off time portion from datetime
"T2" will have the dates you need to include into your report.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply