summarize sales - 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 2005 Forums

Transact-SQL (2005)

summarize sales

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-11-19 : 20:19:13

I have a salesInfo table that contains my productID the dateSold and the QuantitySold. I am trying to write q query that returns a result set of productID and the aggregated quantities of the past 4 months without including the current month. The date sold is stored as datatype char with the format of yyyy-mm-dd.
I've tried something like this but I run into data type conversion issues. Any ideas how to approach this differently?

SELECT productID ,SUM(s.QuantitySold)
FROM salesInfo s
INNER JOIN Dates d ON (s.DateSold=d.DateID)
WHERE YEAR(d.FormattedDate) = 2010
AND CAST(MONTH(d.FormattedDate) as char(2))IN
(SELECT ''''+ CAST(MONTH(GetDate())-1 as char(2)) +''''+','+''''+ CAST(MONTH(GetDate())-2 as char(2)) +''''+','+''''+ CAST(MONTH(GetDate())-3 as char(2))+''''+','+''''+ CAST(MONTH(GetDate())-4 as char(2)))
group by productID

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-11-19 : 20:34:14

I think I found a solution. Still not very elegant though:

SELECT productID ,SUM(s.QuantitySold)
FROM salesInfo s
INNER JOIN Dates d ON (s.DateSold=d.DateID)
WHERE YEAR(d.FormattedDate) = YEAR(GETDATE())
AND MONTH(d.FormattedDate) < MONTH(GetDate())
AND MONTH(d.FormattedDate) > (MONTH(GetDate())-5)
GROUP BY productID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 08:15:32

>> The date sold is stored as datatype char with the format of yyyy-mm-dd
why?

You can use format 121 to indicateyyyy-mm-dd
so convert(datetime,FormattedDate,121) will give the correct daetme value
so
also
select dateadd(mm,datediff(mm,0,getdate()),0)
is the begining of the current month
select dateadd(mm,datediff(mm,0,getdate())-5,0)
is the start of the month 5 months ago

where convert(datetime,FormattedDate,121) < dateadd(mm,datediff(mm,0,getdate()),0)
and convert(datetime,FormattedDate,121) >= dateadd(mm,datediff(mm,0,getdate())-5,0)

It won't use an index on FormattedDate - would be better to hold that as a date or you could use character comparisons.

where datetime,FormattedDate < convert(varchar(8),dateadd(mm,datediff(mm,0,getdate()),0),121)
and datetime,FormattedDate >= convert(varchar(8),dateadd(mm,datediff(mm,0,getdate())-5,0),121)

==========================================
Cursors are useful if you don't know sql.
SSIS 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