SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with query again please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skylimitsql
Starting Member

8 Posts

Posted - 08/10/2014 :  11:28:17  Show Profile  Reply with Quote
Sample data

declare @Product table(ProductID int, Product varchar(90), Cost decimal(18,2))
insert into @Product
select 1, 'Product1', 50.50 union all
select 2, 'Product2', 150.50 union all
select 3, 'Product3', 250.50

select * from @Product

declare @Sale table(SaleID int, SaleDate Datetime, Quantity int, ProductID int)
insert into @Sale
select 1, '2014-01-01', 2, 1 union all
select 2, '2014-02-01', 3, 1 union all
select 3, '2014-03-01', 4, 2 union all
select 4, '2014-04-01', 5, 3

Please can you help me with this

A query that will produce a report on the total sales per month over the period from the beginning of last year to date. Only amounts which total over £200 must be reported.

I tried many possible things but not been able to write a correct SQL

Many Thanks

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 08/10/2014 :  12:31:50  Show Profile  Reply with Quote
declare @Product table(ProductID int, Product varchar(90), Cost decimal(18,2))
insert into @Product
select 1, 'Product1', 50.50 union all
select 2, 'Product2', 150.50 union all
select 3, 'Product3', 250.50


declare @Sale table(SaleID int, SaleDate Datetime, Quantity int, ProductID int)
insert into @Sale
select 1, '2014-01-01', 2, 1 union all
select 2, '2014-02-01', 3, 1 union all
select 3, '2014-03-01', 4, 2 union all
select 4, '2014-04-01', 5, 3


Declare @SalesDat datetime = '1/1/2014'

-- base of your query to work with the needed results
SELECT * ,s.Quantity*p.Cost TotalSale FROM @Sale S
INNER JOIN @Product P ON S.productID = P.ProductID
WHERE S.Quantity * P.Cost >= 200
AND SaleDate >= @SalesDat

This will sum by sales date --assuming all your sales are on the 1st - it will be by month: if not you will need to use DatePart to pull a month and perhaps year.
SELECT s.SaleDate ,SUM(s.Quantity*p.Cost) TotalSale FROM @Sale S
INNER JOIN @Product P ON S.productID = P.ProductID
WHERE S.Quantity * P.Cost >= 200
AND SaleDate >= @SalesDat
Group by s.SaleDate

Go to Top of Page

skylimitsql
Starting Member

8 Posts

Posted - 08/10/2014 :  13:02:28  Show Profile  Reply with Quote
Brilliant! Many Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000