Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Constraint Violating Yak Guru

252 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  
 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.03 seconds. Powered By: Snitz Forums 2000