Summary problem

[cybercop23]

Hi all.
I need to sumarize sales by day for the past month. The issue is that my date field is smalldatetime since I get updated sales every 15 minutes.
Here's my select statement:

select Str,CONVERT(varchar(10), [TimeStamp], 101) as SDate,sum(NetSalesAmount) as Sales
from SaleUpdates
where TimeStamp >= getdate()-30
group by Str,TimeStamp
order by Str,SDate

Even if I do this, it still won't work:

group by Str,CONVERT(varchar(10), [TimeStamp], 101)

I know I can create a separate view and base my query off of that view, but I'm thinking there has to be a way to do this in one shot. Any ideas?

Thanks much.
AB

 

[Pattycake245]

What are the results you are getting?

 

[mercwrought]

what about

select Str,dateadd(dd, 0, datediff(dd, 0,timestamp))as SDate,sum(NetSalesAmount) as Sales
from SaleUpdates
where TimeStamp >= getdate()-30
group by Str,dateadd(dd, 0, datediff(dd, 0,timestamp))
order by 1,2

 

[cybercop23]

Mercwrought, that worked, except that it gives me the date as mm/dd/yy 00:00:00:0000. If I add the cast to remove the zeros, it again complains that I can't have a cast in my group by.

Pattycake245,
MS SQL is complaining about the cast or convert in the group by. Since I have numerous records per day and want to aggregate them by day and sum up all the sales, I need to include the date as part of the group by clause.

Making progress, but no cigar yet.

 

[Pattycake245]

How about this:

select Str, SDate, sum(NetSalesAmount) Sales
from
(select Str,CONVERT(varchar(10), [TimeStamp], 101) as SDate,NetSalesAmount
from SaleUpdates
where convert(varchar(10),TimeStamp,101) >= convert(varchar(10),dateadd(dd,-30,getdate()),101)) as results
group by Str,Sdate
order by Str,SDate

Tim

 

[maswien]

Rewrite your original query a little bit, it should work.

select Str,CONVERT(varchar(10), [TimeStamp], 101) as SDate,sum(NetSalesAmount) as Sales
from SaleUpdates
where TimeStamp >= getdate()-30
group by Str,CONVERT(varchar(10), [TimeStamp], 101)
order by Str,CONVERT(varchar(10), [TimeStamp], 101)

 

[cybercop23]

That did it. Thanks.