Help with query again please - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with query again please

Author

Topic

skylimitsql
Starting Member

8 Posts

Posted - 2014-08-10 : 11:28:17

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 - 2014-08-10 : 12:31:50

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

skylimitsql
Starting Member

8 Posts

Posted - 2014-08-10 : 13:02:28

Brilliant! Many Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources