sql dates

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

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 ?

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

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

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

Select allOpen in new window

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