Multiple Date Range in a query!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Date Range in a query!!

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-22 : 13:14:41

Dear All,

I want to create a single query with multiple date range for ex: datecreted from the 01/03/2012 to 21/03/2012 , 01/02/2012 to 21/02/2012, and 01/01/2012 to 21/01/2012
and need to display the result in a single result/report

For eg i need the out put be

|Date Range_1| |Date Range_2|

a_id itemcount amount a_id itemcount amount
73 1 117.00 12 23 456.78

select policy.a_id,count(policy.P_Id) as itemcount,SUM(policy.price)as amount
from Policy
inner join Agent on policy.A_Id = Agent.A_Id
where (policy.datecreated between '2012-03-01 00:00:00.000' AND '2012-03-21 23:59:59.997')
group by policy.A_Id

The above query gives the date range_1 result.I need to add the other two daterange and need to get the output as above ie in horizontal way.

Any help regarding this is highly appreciated.

Thanks

Regards,
SG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-22 : 13:35:42

One way to do this would be to use case expression in your aggregate functions like what I am showing below:

SELECT
	policy.a_id,
	COUNT(CASE WHEN policy.datecreated >= '20120301'  AND policy.datecreated < '20120322' THEN  policy.P_Id END ) 
		AS Period1_itemcount,
	SUM(CASE WHEN policy.datecreated >= '20120301'  AND policy.datecreated < '20120322' THEN  policy.price END ) 
		AS Period1_amount
	-- similar counts and sums for other date ranges
FROM
	Policy
	INNER JOIN Agent
		ON  policy.A_Id = Agent.A_Id
WHERE
	-- date range that covers all the periods of interest
    policy.datecreated >= '20120101' AND policy.datecreated < '20120322'
GROUP BY
	policy.A_Id

I am only showing columns for one period, you can add other periods as required. I also changed the way the date ranges are specified (using a >= and a <). That is better for a few reasons.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:15:46

why not make a procedure to populate a temporary table having same structure as resultset using required start and end date ranges and then finally retrieve from it

one question is can the date range pairs increase?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-27 : 10:04:12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 11:54:33

the group by required field (i think month in your case and add dateranges in where

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2012-03-28 : 06:19:30

Thanks for your replay visakh16

Its working.But it pulls all the date from Jan,Feb and Upto march 22.
I need count only on these data ranges 01/03/2012 to 21/03/2012 , 01/02/2012 to 21/02/2012, and 01/01/2012 to 21/01/2012 ie 1st - 21nd of every month.I think i am doing some thing wrong here!!

Any idea to count the data exactly on these date range in all the month.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 11:33:02

how have you given where condition?

it should be


...
where daterange1
or daterange2
or daterange3
group by daterange

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources