SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Date Range in a query!!		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satheesh
Posting Yak Master

United Kingdom
124 Posts
Posted - 03/22/2012 :  13:14:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 03/22/2012 :  13:35:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/22/2012 :  15:15:46  Show Profile  Reply with Quote
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/
Edited by - visakh16 on 03/22/2012 15:17:31
Go to Top of Page

satheesh
Posting Yak Master

United Kingdom
124 Posts
Posted - 03/27/2012 :  10:04:12  Show Profile  Reply with Quote
Thanks you very much.

Sorry sunitabeck,

I need to display vertical one by one

|Date Range_1| - March then
|Date Range_2| - Feb
|Date Range_2| - Jan

How to modify your query.I tried but it keep on exectuing and also duplicating the figures.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/27/2012 :  11:54:33  Show Profile  Reply with Quote
the group by required field (i think month in your case and add dateranges in where

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

satheesh
Posting Yak Master

United Kingdom
124 Posts
Posted - 03/28/2012 :  06:19:30  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/28/2012 :  11:33:02  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000