Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql dates

Posted on 2011-05-05
7
Medium Priority
?
220 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:sagarh
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35696701
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
 
LVL 9

Expert Comment

by:s_chilkury
ID: 35696714
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
 

Author Comment

by:sagarh
ID: 35696739
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35696779
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
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35696812
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
 

Author Comment

by:sagarh
ID: 35696915
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35696959
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question