?
Solved

Query Multiple Date Ranges

Posted on 2005-03-03
6
Medium Priority
?
963 Views
Last Modified: 2008-01-09
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
Comment
Question by:gordonagogo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 400 total points
ID: 13448367
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
 
LVL 4

Assisted Solution

by:rehand
rehand earned 400 total points
ID: 13448466
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
 
LVL 4

Expert Comment

by:rehand
ID: 13448473
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 13

Accepted Solution

by:
KarinLoos earned 800 total points
ID: 13448644
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
 
LVL 1

Author Comment

by:gordonagogo
ID: 13450979
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 13451036
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question