Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

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
0
gordonagogo
Asked:
gordonagogo
4 Solutions
 
adatheladCommented:
select SUM(CASE WHEN ScanDateTime BETWEEN '16 feb 05' and '20 feb 05' THEN 1 ELSE 0 END) As Week1,
SUM(CASE WHEN ScanDateTime BETWEEN '21 feb 05' and '25 feb 05' THEN 1 ELSE 0 END) As Week2,
SUM(CASE WHEN ScanDateTime BETWEEN '26 feb 05' and '02 mar 05' THEN 1 ELSE 0 END) As Week3
from import_scanfiles
where ScanDateTime BETWEEN '16 feb 05' and '02 mar 05'

hope this helps
0
 
rehandCommented:
Create Procedure XXXXXX
(
@StartDate DATETIME,
@EndDate DATERTIME
)
SELECT
          DatePart(wk, ScanDateTime ) - DatePart(wk, @StartDate)+1  AS Week,
          Count(Field1) AS CountInWeek,
FROM
     import_scanfiles
WHERE
          ScanDateTime BETWEEN @StartDate  AND @EndDate

0
 
rehandCommented:
Sorry let me fix some syntax

 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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
KarinLoosCommented:
could you try :  this will dynamically generate the output you require based on input DateFrom and DateTo
Notice i added an extra aspect YearNo  (as you could be querying across years)

Create Procedure GetYearWeekCounts
(
    @DateFrom DATETIME,
    @DateTo   DATERTIME
)
AS
BEGIN
Declare @Sql  varchar(8000)
create table #tmpWeeks  (YearNr int,  WeekNr smallint, Counter int)
insert #tmpWeeks select Year(scandate), DatePart( ww, scandate), count(fieldid)
             from import_scanfiles
             WHERE ScanDate between @DateFrom and @DateTo
                 group by Year(scandate),DatePart( ww, scandate)


Set @sql = ''
select @sql = @sql + ' SUM( case  when YearNr =  ' + convert(char(4),YearNr) +
                      ' AND WeekNr = ' + convert(char(4),WeekNr) + ' then Counter else 0 end) as WeekNr' + convert(char(2), WeekNr) + ','
from #tmpWeeks
set @sql = Reverse(Substring(Reverse(@sql), 2, len(@sql) ))
set @Sql = 'Select YearNr, ' + @sql  + ' from #tmpWeeks group by YearNr '
exec( @sql )
drop table #tmpWeeks

END
GO
0
 
gordonagogoAuthor Commented:
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
0
 
Scott PletcherSenior DBACommented:
This allows you to specify a starting date; this can be a (global) parameter in Reporting Services, I think.


DECLARE @startDate DATETIME
SET @startDate = '21 Feb 05'  --<<-- adjust date as needed


-- no adjustments needed
SELECT SUM(CASE WHEN ScanDateTime BETWEEN @startDate AND DATEADD(MS, -3, DATEADD(DAY, 5, @startDate))
            THEN 1 ELSE 0 END) AS [Week 1],
      SUM(CASE WHEN ScanDateTime BETWEEN DATEADD(DAY, 7, @startDate) AND DATEADD(MS, -3, DATEADD(DAY, 12, @startDate))
            THEN 1 ELSE 0 END) AS [Week 2],
      SUM(CASE WHEN ScanDateTime BETWEEN DATEADD(DAY, 14, @startDate) AND DATEADD(MS, -3, DATEADD(DAY, 19, @startDate))
            THEN 1 ELSE 0 END) AS [Week 3]      
FROM import_scanfiles
WHERE ScanDateTime >= @startDate
AND ScandateTime <= DATEADD(MS, -3, DATEADD(DAY, 19, @startDate))
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now