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
 SQL QUERY to find all months between 2 dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sebssg
Starting Member

9 Posts

Posted - 01/26/2011 :  16:13:57  Show Profile  Reply with Quote
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.

Edited by - tkizer on 01/26/2011 16:15:18

RobertKaucher
Posting Yak Master

USA
169 Posts

Posted - 01/26/2011 :  16:54:35  Show Profile  Visit RobertKaucher's Homepage  Reply with Quote
I'm sorry, but you are going to need to be clearer. I really don't understand your message.

===
http://www.ElementalSQL.com/
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 01/26/2011 :  17:30:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/27/2011 :  13:09:29  Show Profile  Reply with Quote
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/

Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/02/2011 :  00:59:19  Show Profile  Reply with Quote
Thanks so much for your Help visakh16. that was exactly what i need.

Thanks Sebastian
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  15:37:51  Show Profile  Reply with Quote
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.
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  15:39:28  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/03/2011 :  15:40:47  Show Profile  Reply with Quote
Are the 4 rows ReferredBy = 7?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/03/2011 :  15:52:52  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  15:59:04  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/03/2011 :  16:08:35  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Edited by - dataguru1971 on 02/03/2011 16:10:23
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  16:23:12  Show Profile  Reply with Quote
unfortunately same results.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/03/2011 :  16:32:41  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  16:48:21  Show Profile  Reply with Quote
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.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/03/2011 :  17:02:18  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

sebssg
Starting Member

9 Posts

Posted - 02/03/2011 :  17:16:46  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/03/2011 :  17:27:59  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.
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.14 seconds. Powered By: Snitz Forums 2000