sql dates

Hi

I have a table that shows sales for the year and I need to return from it sum of sales for each day...

Currently the table shows each sale made therefore yesterday we had 20 orders so the select query returns 20 lines when I run the following query:-

select date,sum (case when (left(element2,2))=('00') then (amount) end)  as sales
from sales
where year='11'
group by date,element2
having date >='01-nov-2010'
order by date

I am trying to return a total sales value for each day...the above query does not work- any ideas what I am doing wrong?

Thanks for the help!

Sagar
sagarhAsked:
Who is Participating?
 
Pratima PharandeCommented:
select distinct date,
sum (case when (left(element2,2))=('00') then (amount)else 0 end)  as sales,
sum (case when (left(element2,2)) in ('01','02','03','04') then (amount) else 0 end) as cost_of_sales
from sales
where year=11
group by date
having jdate >='01-nov-2010'
order by date
0
 
Lee SavidgeCommented:
Any error messages?

I'll assume the date field is a datetime datatype. Is year a string field or an integer field? if it's an integer you need to remove the single quotes from around the 11.

select date,sum (case when (left(element2,2))=('00') then (amount) end)  as sales
from sales
where year='11'
group by date,element2
having date >=cast('01-nov-2010' as datetime)
order by date
0
 
s_chilkuryCommented:
Two queries:

1) When you have Year = '11' then why do we need having date >= '01-Nov-2010'. I suppose when the year is 11 then it will fetch you only the 2011 year's record

2) Why do you need the CASE statement .. when you can put that in the WHERE clause ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sagarhAuthor Commented:
Hi

I have the case field because I am buildin the query I will also be returning the cost of sales and the overhead costs values from the table as well as seperate columns....

the year ciolumn is used to strip out any credits/sales (normally adjustments) that refer to last year but got posted this year, thus the date would be greater than nov-1-2010 but would refer to last year sales....

thanks
0
 
Alpesh PatelAssistant ConsultantCommented:
select OrderDate,sum (case when (left(Freight,1))=('1') then ShipVia else Freight end)  as sales
from Northwind.dbo.Orders
--where year='11'
group by OrderDate,Freight,ShipVia
order by OrderDate
0
 
GhunaimaCommented:
Provide else value in case & drop element2 from groupby clause.

Try This

 
select date,sum (case when (left(element2,2))=('00') then (amount) else 0 end)  as sales
from sales
where year='11'
group by date
having date >='01-nov-2010'
order by date

Open in new window

0
 
sagarhAuthor Commented:
Hi

I have updated my query it now reads as follows:-

select distinct date,
sum (case when (left(element2,2))=('00') then (amount)else 0 end)  as sales,
sum (case when (left(element2,2)) in ('01','02','03','04') then (amount) else 0 end) as cost_of_sales
from sales
where year=11
group by date,element2
having jdate >='01-nov-2010'
order by date


and the output is as follows when I run it:-

journal_date                            sales      cost_of_sales
2010-11-01 00:00:00.000      0.0      0.0
2010-11-02 00:00:00.000      -2966.8200000000002      0.0
2010-11-02 00:00:00.000      -2500.0      0.0
2010-11-02 00:00:00.000      -2267.0      0.0
2010-11-02 00:00:00.000      -1969.9099999999999      0.0
2010-11-02 00:00:00.000      -1878.5999999999999      0.0
2010-11-02 00:00:00.000      -1354.3700000000001      0.0
2010-11-02 00:00:00.000      -251.02000000000001      0.0
2010-11-02 00:00:00.000      -164.18000000000001      0.0
2010-11-02 00:00:00.000      -113.23999999999999      0.0
2010-11-02 00:00:00.000      -38.0      0.0
2010-11-02 00:00:00.000      0.0      0.0
2010-11-02 00:00:00.000      0.0      29.739999999999998
2010-11-02 00:00:00.000      0.0      33.380000000000003
2010-11-02 00:00:00.000      0.0      49.289999999999999
2010-11-02 00:00:00.000      0.0      98.780000000000001
2010-11-02 00:00:00.000      0.0      183.55000000000001
2010-11-02 00:00:00.000      0.0      876.75
2010-11-02 00:00:00.000      0.0      1250.8299999999999
2010-11-02 00:00:00.000      0.0      1498.77
2010-11-02 00:00:00.000      0.0      1616.6999999999998
2010-11-02 00:00:00.000      0.0      1873.4000000000001
2010-11-02 00:00:00.000      0.0      1990.0
2010-11-02 00:00:00.000      125.0      0.0
2010-11-03 00:00:00.000      -15266.650000000001      0.0
2010-11-03 00:00:00.000      -10326.959999999999      0.0
2010-11-03 00:00:00.000      -9527.0500000000011      0.0

I am trying to get all the sales values for the 2 nov 2010 to show as a total and the same for the cost of sales preferably on the same line....

thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.