June 3, 2014 at 6:19 pm
Hello all,
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
, LOCATION VARCHAR (50) NOT NULL
, QTY DECIMAL (38,2) NOT NULL
)
INSERT INTO #ItemLedgerEntry
(
ENTRYNO,ITEMNO,POSTINGDATE,ENTRYTYPE,LOCATION,QTY
)
VALUES
('1','QOB220 SQD','2008-02-02',0,'NY',550)
,('2','QOB330 SQD','2008-02-07',1,'CA',-60)
,('3','CG-6275 AP','2008-02-07',1,'NJ',-4)
,('4','F-5532-1','2008-02-07',0,'TX',10)
,('5','QOB220 SQD','2008-02-08',1,'NY',-500)
,('6','QOB330 SQD','2008-02-08',0,'CA',70)
,('7','CG-6275 AP','2008-02-08',0,'NJ',-10)
,('8','F-5532-1','2008-02-07',1,'TX',-10)
,('9','QOB220 SQD','2008-02-10',0,'TX',100)
,('10','QOB220 SQD','2008-02-10',0,'CA',100)
,('11','QOB220 SQD','2008-02-10',0,'NJ',50)
select
ENTRYNO
,ITEMNO
,POSTINGDATE
,ENTRYTYPE=CASE #ItemLedgerEntry.ENTRYTYPE
WHEN 0 THEN 'PURCHASE'
WHEN 1 THEN 'SALE'
ELSE 'UNDEFINED'
END
,LOCATION
,QTY
FROM #ItemLedgerEntry
drop table #ItemLedgerEntry
The result set would show item QOB220 SQD, Purchased = 4, Quantity Purchased = 800, Sales = 1, Quantity Sold = 500
Item Purchases QtyPurchased Sales QtySold
QOB220 SQD 4 800 1 500
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
Any help or suggestions would be appreciated.
June 3, 2014 at 9:41 pm
Something like this ?
select distinct
itemno as [Item],
count(*) over (partition by ITEMNO) as [Purchases],
sum(case when entrytype = 0 then qty else 0 end) over (partition by ITEMNO) as [QtyPurchased],
sum(case when entrytype = 1 then 1 else 0 end) over (partition by ITEMNO) as [Sales],
sum(case when entrytype = 1 then qty else 0 end) over (partition by ITEMNO) as [QtySold]
from #ItemLedgerEntry
June 4, 2014 at 12:08 pm
matak (6/3/2014)
Something like this ?
select distinctitemno as [Item],
count(*) over (partition by ITEMNO) as [Purchases],
sum(case when entrytype = 0 then qty else 0 end) over (partition by ITEMNO) as [QtyPurchased],
sum(case when entrytype = 1 then 1 else 0 end) over (partition by ITEMNO) as [Sales],
sum(case when entrytype = 1 then qty else 0 end) over (partition by ITEMNO) as [QtySold]
from #ItemLedgerEntry
This logic does not return the right number of purchases. For instance, on CG-6275 AP, the result says returns 2, when there was only 1 .
June 4, 2014 at 12:23 pm
Not sure why the previous posted used partition by on the aggregate columns. That forced them to use a distinct. The logic was 99% done for you. All you need to do is add a case expression inside your count. I dropped the partition by on the sum aggregates to make this a little simpler. I also added the absolute function around QtySold so the value would be positive. 😉
select
itemno as [Item],
count(Case when ENTRYTYPE = 0 then 1 end) as [Purchases],
sum(case when entrytype = 0 then qty else 0 end) as [QtyPurchased],
sum(case when entrytype = 1 then 1 else 0 end) as [Sales],
ABS(sum(case when entrytype = 1 then qty else 0 end)) as [QtySold]
from #ItemLedgerEntry
group by ITEMNO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 6:31 pm
Sean,
That's money!! I'll run it on my actual table in the morning. This way isn't that expensive either when compared to the windowing function. Thanks for your input.
Keith
June 5, 2014 at 10:11 am
kwoznica (6/4/2014)
Sean,That's money!! I'll run it on my actual table in the morning. This way isn't that expensive either when compared to the windowing function. Thanks for your input.
Keith
Keith - glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply