Results to show differences in items purchased versus sold

  • 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.

  • 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

  • matak (6/3/2014)


    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

    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 .

  • 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/

  • 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

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply