July 11, 2015 at 8:53 am
Hello all,
Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.
I want to take the Sales Order Total and then count how many times it falls into a specific bucket.
I've included the script where I've left with sample data so you can run in into a tempdb.
I hope to have the end result look like the below summary if you use my sample data.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
Here is the script to set up the tempdb
use tempdb
go
/*
drop table #salesinvoice;
drop table #salesinvoicedetail;
*/
--create the temp tables
create table #salesinvoice
(
OrderIdvarchar (50)not null
,OrderDate datetime
)
create table #salesinvoicedetail
(
OrderIdvarchar (50)not null
, OrderLineintnot null
, LineAmount intnot null
, ProfitAmountintnot null
)
--insert test data
insert into #salesinvoice
(
OrderID,OrderDate
)
Values
('SI201','02/02/2015')
,('SI202','02/03/2015')
,('SI203','02/03/2015')
,('SI204','02/04/2015')
,('SI205','02/04/2015')
,('SI206','02/04/2015')
,('SI207','02/04/2015')
,('SI208','02/05/2015')
,('SI209','02/05/2015')
,('SI210','02/06/2015')
insert into #salesinvoicedetail
(
OrderID,OrderLine,LineAmount,ProfitAmount
)
Values
('SI201','1','500','100')
,('SI201','2','400','50')
,('SI201','1','6000','200')
,('SI201','2','8000','1000')
,('SI201','3','11000','3000')
,('SI201','4','15000','3000')
,('SI201','5','550','50')
,('SI202','1','5500','1050')
,('SI202','2','6100','800')
,('SI203','1','510','120')
,('SI203','2','700','200')
,('SI204','1','15000','2000')
,('SI204','2','1001','205')
,('SI205','1','2750','350')
,('SI206','1','12500','2500')
,('SI207','1','25450','2349')
,('SI208','1','4500','480')
,('SI209','1','3850','320')
,('SI210','1','2575','391')
--review data
SELECT * FROM #salesinvoicedetail
SELECT * FROM #salesinvoice
Below is my query where I hope you can help me change it to perform a count on the summarized totals.
select
si.OrderID as OrderNum
,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K
,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k
,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k
,sum(case when sid.LineAmount >=25000 then 1 else 0 end) as GT25k
,max(OrderLine) as LineCount
From #salesinvoice as si
inner join #salesinvoicedetail as sid
on sid.OrderID = si.OrderID
group by rollup (si.OrderID)
Order by OrderNum
/*****
Here are the order totals.
SI201 = 41450
SI202 = 11600
SI203 = 1210
SI204 = 16001
SI205 = 2750
SI206 = 12500
SI207 = 25450
SI208 = 4500
SI209 = 3850
SI210 = 2575
And the desired result would tell me the below summary. The final query can be column based. I just write this in rows for clarity.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
******/
Thank you in advance for your help.
July 11, 2015 at 10:21 am
an idea
SELECT
sum(case when sumla < 5000 then 1 else 0 end) as LT5K
,sum(case when sumla >=5000 and sumla < 10000 then 1 else 0 end) as GT5kLT10k
,sum(case when sumla >=10000 and sumla < 25000 then 1 else 0 end) as GT10kLT25k
,sum(case when sumla >=25000 then 1 else 0 end) as GT25k
FROM (
SELECT si.OrderId as sumoi, SUM(sid.LineAmount) AS sumla
FROM #salesinvoice AS si INNER JOIN
#salesinvoicedetail AS sid ON si.OrderId = sid.OrderId
GROUP BY si.OrderId) x
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2015 at 3:58 pm
Another option, just in case that you want to have your information returned in a different way.
WITH Groups(GroupID, GroupDesc) AS(
SELECT 1, 'Count Less Than 5K' UNION ALL
SELECT 2, 'Count Greater Than 5K Less Than 10K' UNION ALL
SELECT 3, 'Count Greater Than 10K Less Than 25K' UNION ALL
SELECT 4, 'Count Greater Than 25K'
),
Summary AS(
SELECT CASE
WHEN SUM(sid.LineAmount) < 5000 THEN 1
WHEN SUM(sid.LineAmount) < 10000 THEN 2
WHEN SUM(sid.LineAmount) < 25000 THEN 3
ELSE 4 END GroupID
FROM #salesinvoice si INNER JOIN
#salesinvoicedetail sid ON si.OrderId = sid.OrderId
GROUP BY si.OrderId
)
SELECT g.GroupDesc,
COUNT( s.GroupID)
FROM Groups g
LEFT
JOIN Summary s ON s.GroupID = g.GroupID
GROUP BY g.GroupDesc
August 19, 2015 at 8:25 am
J Livingston's query runs faster. Both solutions work well.
Sorry for the late reply but it really helped me in crunch.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply