SQL Help - Past 6 months - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help - Past 6 months

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-01 : 16:13:36

I'm trying to query the past 6 months of data. Looks like I am only get the first day for Nov, and the other months are fine. How can I get all of the records for Nov?

SELECT SUM(Sls_Amt) AS SalesAmount, DATENAME([MONTH], Inv_Dt) AS pMonth, MONTH(Inv_Dt) AS MonthOrder, SUM(Cost_Amt) AS cost
FROM dash_product_sales
WHERE (Inv_Dt BETWEEN DATEADD([month], - 7, GETDATE()) AND DATEADD([month], - 1, GETDATE())) AND (Prod_Cat IN ('o1')) AND (Slspsn_No IN ('101', '102',
'103', '104', '105', '106', '107', '108', '110', '116', '117'))
GROUP BY DATENAME([MONTH], Inv_Dt), MONTH(Inv_Dt)
ORDER BY MONTH(Inv_Dt)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-01 : 16:20:40

don't use [] around [month].
i'm surprised this doesn't return you an error. or is this access??

DATEADD([month], - 1, GETDATE())) will return you 1.11.2004 (dmy) so that will be your top date.

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-01 : 16:38:55

I'm using SQL Server. It keeps adding the [] back around month

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-01 : 21:05:58

WHERE (Inv_Dt BETWEEN DATEADD([month], - 7, GETDATE()) AND DATEADD([month], - 1, GETDATE()))
if it's run on 1 dec 2004 this will give
WHERE (Inv_Dt BETWEEN [1 jun 2004] AND [1 nov 2004]

try
WHERE Inv_Dt BETWEEN convert(varchar(8),DATEADD(mm, - 7, GETDATE()), 112) + '01' AND convert(varchar(8),DATEADD(mm, - 1, GETDATE()), 112) + '01'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources