Query calculating days

[topone]

Hello I am supposed to write a query that gets the total of drugs dispensed in a day and also the total of the week (ends sunday) for a given period.
I can get the first part of the query without problems.

SELECT [Filldate], Count([Filldate]) AS itemused From CNumber WHERE RegionId=1 And [CNumber].[Filldate] Between Format(#01/09/04 #,'mm/dd/yyyy')
And Format(#15/12/04#,'mm/dd/yyyy') GROUP BY [Filldate]);

The problem starts when I want to get the total for the weeks as well. I found out that I can use the weekday function to get the number of week in the given period but still no luck. Any help would be very much appreciated.for example with data like this
06/01 drugs used 4
08/01 drugs used 6
12/01 drugs used 4 the query should return

06/01 4
08/01 6
09/01 10
12/01 4 where 09/01 is sunday and therefore the end of the week and it sums the drugs dispensed from monday to saturady (sunday it is closed).


Thanks
Paolo

 

[mik18]

The DatePart Function allows you to specify the start of the week. In your case Monday. You want to use this function on the FillDate.

DatePart("ww", Filldate, 2) this will return the week number the FillDate occured in.
If you group on this field and count the number of scripts you have what you need to a point. You still need the date in 12/01.
To do this you need to calculate the date based on the week number and year. I'm not sure how to do this right now but I'll look or someone else will let you know before me.

mike

 

[mik18]

OK. This was a question I posted in a crystal forum asking relativily the same question. You will have to modify this slightly for your use but it gets the idea.

mik18 (MIS) Jun 24, 2004
Is there any way to take a date in the form of a week and year (ex. Week 25, 2004) and find the start and end date of that week?

Thanks
mike

lbass (TechnicalUser) Jun 24, 2004
//{@Start date}:
dateadd("ww",val(mid({table.weekandyear},6,2))-1, Date(val(right({table.weekandyear},4)),01,01)-dayofweek(Date(val(right({table.weekandyear},4)),01,01))+1)

//{@End Date}:
dateadd("ww",val(mid({table.weekandyear},6,2))-1, Date(val(right({table.weekandyear},4)),01,01)-dayofweek(Date(val(right({table.weekandyear},4)),01,01))+1)+6

-LB

 

[topone]

Thanks for your quick reply. It looks like it should work.