SQL QUERY to find all months between 2 dates

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

SQL QUERY to find all months between 2 dates

Author

Topic

sebssg
Starting Member

9 Posts

Posted - 2011-01-26 : 16:13:57

I have a table that has orders for different clients. i want to write a query where I can set the date as 01/25/2011 and then find all the months what the orders till that date for the client.
So the result i am expecting is
Date clientID TotalOrders
01/25/2011 5 100
12/25/2011 5 200
11/25/2011 5 100

The issue i have is that in the orders table if there is no entry for that client for that day then it will skip the month like 31st.
i want November which does not have a 31st to show up with all the orders sum till that date.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-26 : 16:54:35

I'm sorry, but you are going to need to be clearer. I really don't understand your message.

===
http://www.ElementalSQL.com/

sebssg
Starting Member

9 Posts

Posted - 2011-01-26 : 17:30:42

Sorry let me try to explain with examples. i have orders table with orders per client ID and date. I want to select today as a date and then i want to go back 3 months and see for the last 3 months up to this day 26th, how many order were done. So today is jan 26, i want to get dec 26, nov 26, oct 26 and see the orders.
1. Now some clients did not place an order in a certain month so it will show up as 0 but i want to still see the months
2. if the current date was jan 31, then i want to see dec, nov, oct all the order to the last date they have since nov does not have 31.
3. I dont need the day to show up just a day to date count of the orders, what will show up per client is month and total order till that date or last date of the month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 13:09:29

something like


SELECT t.Date,SUM(t1.SalesAmount) AS Total
FROM (SELECT DATEADD(mm,-number,GETDATE()) AS Date
      FROM master..spt_values
      WHERE type='p'
      ANND number BETWEEN 0 AND 3
      ) t
LEFT JOIN YourTAble t1
ON t1.Date >=DATEADD(mm,DATEDIFF(mm,0,t.Date),0) 
AND t1.Date < t.Date + 1 
GROUP BY t.Date

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

sebssg
Starting Member

9 Posts

Posted - 2011-02-02 : 00:59:19

Thanks so much for your Help visakh16. that was exactly what i need.

Thanks Sebastian

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 15:37:51

Sorry Spoke too early. This is is my sql
---------sql 1----------------------
SELECT sum(p.charges),count(p.customerID),t.sdate, t.edate
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm,-number,GETDATE())), 0) AS SDate,
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18
) t
left outer JOIN [customer Information] p
ON p.[Date Rec'd] between t.sdate
AND t.Edate
where ReferredBy=7
GROUP BY t.sDate,t.edate
order by t.sdate,t.edate
-------------sql 2 --------------------
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm,-number,GETDATE())), 0) AS SDate,
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18

my sql 2 is what i use as a sub query in sql 1. When i run sql 2 i get back 19 rows. When i run sql 1 1 get back 15 rows. Which is correct , but i lost 4 rows since there was no data in customer information. Hence sql 1 is a left join and all the records from the left is not there when i run sql 1. What am i doing wrong.

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 15:39:28

ALso the reason why i have the date there as SDate and EDate i want to find the sum of charges from the 1st of a month till the current day of that month.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-03 : 15:40:47

Are the 4 rows ReferredBy = 7?

Jim

Everyday I learn something that somebody else already knew

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 15:52:52

Your ON clause of the join isn't equality, it is a between operator. So if you don't have customer information between those dates, it won't return those records.

You can try the other method to see if you get 19 records

SELECT sum(p.charges),count(p.customerID),t.sdate, t.edate
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm,-number,GETDATE())), 0) AS SDate,
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18
) t ,[Customer Information] p
where ReferredBy=7 and p.[Date Rec'd] between t.sdate AND t.Edate
GROUP BY t.sDate,t.edate
order by t.sdate,t.edate

All records will be returned in the FROM clause. If your criteria in the FROM clause limits the results, then the left side will be limited.

See this article: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

Poor planning on your part does not constitute an emergency on my part.

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 15:59:04

sorry i had modified the original suggestion to get differnet result
but when i tried your suggestion i still get only the records that have a value in customer table

SELECT sum(p.charges),count(p.customerID), t.edate
FROM (
SELECT
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18
) t ,[customer Information] p
where ReferredBy=7 and p.[Date Rec'd]>=DATEADD(mm,DATEDIFF(mm,0,t.EDate),0)
AND p.[Date Rec'd] < t.eDate + 1

GROUP BY t.edate
order by t.edate

the subsquery gave me 19 rows. but on the customer there is no records for certain months. Hence those months are skipped and that is what i need. hope this explains

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 16:08:35

The subquery will ALWAYS return 19 rows. It is specifically asking a values table to return 0 through 18. That isn't really in question or doubt..

Try using this. The grouping and aggregating is maybe eliminating the results on the right side of the join

SELECT sum(COALESCE(p.charges,0)),COUNT(*),t.sdate, t.edate
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm,-number,GETDATE())), 0) AS SDate,
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18
) t
left outer JOIN [customer Information] p
ON p.[Date Rec'd] between t.sdate
AND t.Edate
where ReferredBy=7
GROUP BY t.sDate,t.edate
order by t.sdate,t.edate

Poor planning on your part does not constitute an emergency on my part.

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 16:23:12

unfortunately same results.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 16:32:41

Maybe I am just tired...LOL.

It is quite simply the Where clause as suspected. Since the right side is the only side that matches, you have to change that to

WHERE ReferredBy = 7 (accounts for right side criteria) or ReferredBy is null (or any on the right side with no match)

Poor planning on your part does not constitute an emergency on my part.

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 16:48:21

sorry for the trouble. I know the feeling, i have been racking my brain with this for days. Anyway from what you suggested when we use a where clause in this case referredby=7 we are saying give me all records from left table and try to find a match on right side. so i am not sure what you are trying to tell me to try. sorry fro the confusion.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 17:02:18

Use

Where ReferredBy is Null OR RefferedBy = 7

The ReferredBy = 7 was eliminated the records where there was no match. It will be more visibly obvious when you run it.

I took your query and just for demonstration created this in your format you can run this, remove the red part to see how the results are reduced to only those where RefferedBy = 7 despite the LEFT JOIN. Not sure why I was fiddling about..was pretty obvious actually once I made coffee.

[/code]
Create Table #foo ([date] datetime null,ReferredBy int null,Charges money null)

Insert into #foo (date,referredby,Charges)
Select dateadd(m,number,'20090601'),7,(number*10/3)
FROM master..spt_values
where type = 'p' and number between 0 and 18 and number not in (4,7,9,12)

SELECT sum(COALESCE(p.charges,0)),COUNT(*),t.sdate, t.edate
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(mm,-number,GETDATE())), 0) AS SDate,
DATEADD(mm,-number,GETDATE()) AS EDate
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND 18
) t
left outer JOIN #foo p
ON p.[Date] between t.sdate
AND t.Edate
Where ReferredBy= 7 or ReferredBy is null
GROUP BY t.sDate,t.edate
order by t.sdate,t.edate

Drop table #foo
[/code]

Poor planning on your part does not constitute an emergency on my part.

sebssg
Starting Member

9 Posts

Posted - 2011-02-03 : 17:16:46

That coffee sure did help. YOur query works perfectly but my table does not so that means it is somethign to do with my right table. I will try to debug and see. thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 17:27:59

Adding the OR referredBy is null didn't return 19 results for you?

That would likely be caused by your [Date Rec'd] dates falling outside of the subquery.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources