gordonagogo
asked on
Query Multiple Date Ranges
I have a table which holds information on scanned in invoices. Data held includes the invoice number (field1) which is unique and the scanned in date and time(ScanDateTime). I have written the following query to tell me how many invoices are scanned in on a given date range.
select count (field1) as 'Week 1'
from import_scanfiles
where ScanDateTime >= '21 feb 05'
and ScandateTime <= '25 feb 05'
This returns the following result
Week 1
7902
I'd like to be able to run a query which returns the results for multiple date ranges allowing results similar to those below.
Week 1 Week 2 Week 3
7902 6502 7634
The ultimate aim is to use the query within Microsoft Reports Server (Which I think is a brillant bit of kit). I have multiple querys of this type I'd like to run which is why I'm offering maximum points.
Cheers
Stephen
select count (field1) as 'Week 1'
from import_scanfiles
where ScanDateTime >= '21 feb 05'
and ScandateTime <= '25 feb 05'
This returns the following result
Week 1
7902
I'd like to be able to run a query which returns the results for multiple date ranges allowing results similar to those below.
Week 1 Week 2 Week 3
7902 6502 7634
The ultimate aim is to use the query within Microsoft Reports Server (Which I think is a brillant bit of kit). I have multiple querys of this type I'd like to run which is why I'm offering maximum points.
Cheers
Stephen
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear All,
There really is nothing like this site :-)
All answers are valid and I'll be splitting the points up to show this. KarinLoos, is there anyway to make the week numbers match our financial year instead of the calendar year? We run from Apr to Apr an example of this is as follows
05 - 11 feb Wk41
12 - 18 Feb Wk42
19 - 25 Feb Wk43
26 Feb - 05 Mar Wk44
The answers are already given but this would make my life a lot easier.
Cheers
Stephen
There really is nothing like this site :-)
All answers are valid and I'll be splitting the points up to show this. KarinLoos, is there anyway to make the week numbers match our financial year instead of the calendar year? We run from Apr to Apr an example of this is as follows
05 - 11 feb Wk41
12 - 18 Feb Wk42
19 - 25 Feb Wk43
26 Feb - 05 Mar Wk44
The answers are already given but this would make my life a lot easier.
Cheers
Stephen
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Create Procedure GetWeekCount
(
@StartDate DATETIME,
@EndDate DATERTIME
)
AS
SELECT
DatePart(wk, ScanDateTime ) - DatePart(wk, @StartDate)+1 AS Week,
Count(Field1) AS CountInWeek,
FROM
import_scanfiles
WHERE
ScanDateTime BETWEEN @StartDate AND @EndDate