query for creating report group by weeks (with start date and end date of the week)

[newMeg]

Hi,
I'm a bit stuck. I've been breaking my head quite long figuring how to do this.
I need to generate areport that is grouped by week.

i need to display total number of patients who are enrolled in the system. And they are grouped by the weeks in a year(52 weeks).

any patient who falls in the weeks group is then accumulated and the patients in also sorted by the sales rep. I can manage to do the rest but what im stuck at is how to dyanamically display the weeks in a year which is displaying the start of a week(mon) to end of a week (sun).

for now the report is done in excel format and i need to do this online format(web form in asp.net).

Can this be done. I'm hoping to create something like when i pass the value of the year example 2007, and the report is generated by weeks in 2007 or by getting the now year. Because the days in a week changes every year therefore i can't hardcode the sql statement and hardcode the page.

And this report will be generated weekly every year.

I hope my question are clear.

Any help is greatly appreciated. An attachement of the current report is also included.

[davidcairns]

If you are always going to use Monday as day 1 then this function will suffice

datepart(wk, datecolumn)

If you change the start day by counting the 1st of Jan as day 1 you can use maths to do this

datepart(dy, datecolumn) / 7 + 1

If you have a custom setup that changes the start day every year then it may be better to create a table as follows

Year - the standard 4 digit year
StartDayOffset - number of days after 1 Jan to start on, 0 for 1 jan, -1 for 31 Dec, 1 for 2 Jan etc

Then join to it on the year and user

(datepart(dy, datecolumn) + StartDayOffset) / 7 + 1

Regards

D

[newMeg]

If you have a custom setup that changes the start day every year then it may be better to create a table as follows

Year - the standard 4 digit year
StartDayOffset - number of days after 1 Jan to start on, 0 for 1 jan, -1 for 31 Dec, 1 for 2 Jan etc

Then join to it on the year and user

(datepart(dy, datecolumn) + StartDayOffset) / 7 + 1

D

Hi David,

Im not sure if i got the last part right. For my report Could u explain how i can do it ..i mean creating the new table. Based on wat i see, do i have to create two fields in the new table?

You mentioned the year..so do i store the current year? in this case for this year report that means 2007

and for the StartDayOffset can u explain that pls.

Thanks alot...

[davidcairns]

Hi David,

Im not sure if i got the last part right. For my report Could u explain how i can do it ..i mean creating the new table. Based on wat i see, do i have to create two fields in the new table?

You mentioned the year..so do i store the current year? in this case for this year report that means 2007

and for the StartDayOffset can u explain that pls.

Thanks alot...

1. Yes, that is a 2 field table, and the year should include every year to be included in reports. Hence if you have data going back to 1998 then you would start there.

2. Basically with the offset it depends on what you consider the first day of your first week of each year to be. It is a relative number from the 1st of January, hence a value of 0 would represent 1 Jan, negative numbers would be days before that and positive numbers would be days after.

Lets take an example

2008 starts on a Tuesday, but we want the weeks to start from Sunday and end on Saturday. In this case week 1 is 1 Jan to 5 Jan. The virtual start date for week 1 is therefore the previous Sunday, which is 30 Dec, hence the entry in the table is 2008, -2.

Note that using positive offset values can have a detrimental effect if any of your data comes in prior to that date, so with an offset of 2 (3 Jan) any data in 1 or 2 Jan will be in week 0.

Note also that I got that last 2 formula slightly wrong. We should take 1 away from the year day to get the 7th day into the correct week, it should have been

(datepart(dy, datecolumn) - 1) / 7 + 1

Also the offset should have been subtracted, not added
(datepart(dy, datecolumn) - StartDayOffset - 1) / 7 + 1

[newMeg]

Hi David,

Still Stuck on the SQl. Let me explain, I have table fields called doctor d,clinic c,pmr pmr,patient p,course cor
Patient contains patientID,which is a primary key and it appears as foreign key in course.
Each patient is tied to the clinic and each clinic is tied to a PMR(its is a rep).
Course is a table containing patient is in which course and the date the form was received for registeration.

So basically what i need to do is

Is to get in a week starting from the begining of the year to end of the year.
Lets say this year starts on Jan1-7.
So what i need to do is , Lets say i have 3 form received on 01/01/07,03/01/07 and on 05/04/07 for the Rep called Bruce Tham so on the week of Jan 1-7 for the Rep called Bruce Tham the number should be displayed as 3.

This is my current SQL:-

SELECT 
pmr.pmrname,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) between '1/1/2007' and '7/1/2007' THEN 1 ELSE 0 END) AS Jan1To7,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) between '8/1/2007' and '14/1/2007' THEN 1 ELSE 0 END) AS Jan8To14,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) between '15/1/2007' and '21/1/2007' THEN 1 ELSE 0 END) AS Jan15To21,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) between '22/1/2007' and '28/1/2007' THEN 1 ELSE 0 END) AS Jan22To28,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) between '29/1/2007' and '4/2/2007' THEN 1 ELSE 0 END) AS Jan29ToFeb4
FROM
  doctor d,
  clinic c,
  pmr pmr,
  patient p,
  course cor  
 
WHERE
  d.clinicid=c.clinicid 
AND 
  c.pmrid=pmr.pmrid
AND  
  pmrname='Bruce Tham'
AND 
  d.doctorid=p.doctorid 
AND 
  cor.patientid=p.patientid 
AND 
  CONVERT(char(10),formreceiveddate, 103) <> '01/01/1900' 
GROUP BY pmr.pmrname

What Im doing is writing the weeks manually throughout 2007 will make the query long and troublesome as there is 52 weeks in a year. What i want is to dynamically generate the weeks and loop it so it will generate the weeks starting on mon-sun each week..e.g..Jan 1-7,Jan 8-14,Jan 15-21 and so on of 2007 is the first of 2007 and it starts on Mon(1) and end in sun(7).
For next year i will need to do the same but the form received date must be for the year 2008 so i will have to change the sql abit to only select the date which is in 2008 but the weeks must be for example Jan 1-6 because Mon was on 31 Dec 2007. So I can say that my report must start on 01/01 of every year and end on the first week.
Can this be done.

I have already created two tables, as your previous post. one table called FormReceivedYear with a field called FormReceivedYear containing the year as you mentioned (2007) and the other is the offset table with a field name Offset which I set as 0.
But I dont know how to incoporate that to this sql to generate the result.

The sql i use now roughly gives me the result i want but it is not a smart way of doing it As for next year i'll have to rewrite the weeks all over again.
I have attached an image of the SQL result.

Can You help please.
Thanks A lot.

[davidcairns]

First things first then

Change this to allow any indexes to be used. Always do the convert on the scalar data if possible
CONVERT(char(10),formreceiveddate, 103) <> '01/01/1900'
to
formreceiveddate <> CONVERT(datetime,'01/01/1900',103)

Secondly, this will be much neater if you just dump out the data grouped by the sales rep and week and use Excel or ASP to diplay it the way you want. Hence the query will be

SELECT pmr.pmrname, (datepart(cor.formreceiveddate) - 1) / 7 + 1 as [week], count(p.patientid) as [Patients]
FROM
doctor d,
clinic c,
pmr pmr,
patient p,
course cor
WHERE
d.clinicid=c.clinicid
AND
c.pmrid=pmr.pmrid
AND
pmrname='Bruce Tham'
AND
d.doctorid=p.doctorid
AND
cor.patientid=p.patientid
AND
formreceiveddate <> CONVERT(datetime,'01/01/1900',103)
GROUP BY pmr.pmrname, (datepart(cor.formreceiveddate) - 1) / 7 + 1

[newMeg]

Hi David,

Thanks for the reply. But I was wondering why do we use date part as datepart function is to get the date different either in weeks or days or months right?

From the sql u just provided, i'm not sure but i guess it was missing the date type for datepart(dy,wk). I added the dy and there was no error but the result of the query returns a number for the weeks.

Attached is the result of the query.

Hey sorry you had to bear with me, but i sthis how the result should be. return an integer representation of the date part or the weeks date?

[davidcairns]

Sorry about the missing parameter, my mistake. The datepart parameter dy represents the Day of the Year (1 to 366), there are a number of other options for this parameter that get various peices of the date. Using wk will get the
week, but it uses Monday as day 1, so it doesn't start on the 1st of January, in fact in 2006 the 2nd was in week 2.

What you did appears to be correct, assuming your data lay between the 15th and 21st of January. Try adding a few other lines and see what happens