Results 1 to 2 of 2

Thread: SQL Query - comparing item sales over a specfic date range with qty and dollar amount

  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Question SQL Query - comparing item sales over a specfic date range with qty and dollar amount

    I'm no SQL expert but I've made a very simple query (copied below) that grabs qty and sales amounts for a specific date range for a specific vendor - what I'd like is a single query that compares that same date range from this year to last year.

    SELECT
    V.ItemCode,
    V.ItemSupplierPartNum,
    V.Description,
    SUM(V.QtyShipped) As UnitsSold,
    SUM(V.Amount) As DollarsSold
    FROM vCustomerInvoiceDetail V
    WHERE V.InvoiceDate between '2014-5-1' and '2014-5-31'
    and V.SupplierAcct ='KRUGE100'
    GROUP BY V.ItemCode, V.ItemSupplierPartNum, V.Description

    I can use the UNION operator and build a query that gets all the data (copied below) but it just lays all the data out with double entries for items with sales in each date period. what I would like is for the query to generate columns with a single entry for the three main descpritors ItemCode, ItemSupplierPartNum, Description, plus seperate columns for QtyShipped (for 2014), Amount(for 2014), QtyShipped (for 2013), and Amount(for 2013)

    SELECT
    V.ItemCode,
    V.ItemSupplierPartNum,
    V.Description,
    SUM(V.QtyShipped) As UnitsSold,
    SUM(V.Amount) As DollarsSold
    FROM vCustomerInvoiceDetail V
    WHERE V.InvoiceDate between '2014-5-1' and '2014-5-31'
    and V.SupplierAcct ='KRUGE100'
    GROUP BY V.ItemCode, V.ItemSupplierPartNum, V.Description
    UNION
    SELECT
    V.ItemCode,
    V.ItemSupplierPartNum,
    V.Description,
    SUM(V.QtyShipped) As UnitsSold,
    SUM(V.Amount) As DollarsSold
    FROM vCustomerInvoiceDetail V
    WHERE V.InvoiceDate between '2014-5-1' and '2014-5-31'
    and V.SupplierAcct ='KRUGE100'
    GROUP BY V.ItemCode, V.ItemSupplierPartNum, V.Description
    ORDER BY V.ItemCode, V.ItemSupplierPartNum, V.Description


    Any help is fully appreciated!!!

  2. #2
    Join Date
    Aug 2014
    Posts
    2
    I ended up calling and received some help from our software companies resident SQL expert - below is the query:

    SELECT
    V.ItemCode,
    V.ItemSupplierPartNum,
    V.Description,
    coalesce((select SUM(PY.QtyShipped) from vCustomerInvoiceDetail PY
    where PY.ItemID = V.ItemID
    and PY.InvoiceDate between '2013-5-1' and '2013-5-31')
    ,0) as PYQty,
    coalesce((select SUM(PY.Amount) from vCustomerInvoiceDetail PY
    where PY.ItemID = V.ItemID
    and PY.InvoiceDate between '2013-5-1' and '2013-5-31')
    ,0) as PYDollars,
    SUM(V.QtyShipped) As CurUnitsSold,
    SUM(V.Amount) As CurDollarsSold
    FROM vCustomerInvoiceDetail V
    WHERE V.InvoiceDate between '2014-5-1' and '2014-5-31'
    and V.SupplierAcct ='KRUGE100'
    Group By V.ItemID, V.ItemCode, V.ItemSupplierPartNum, V.Description

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •