Output two columns for 1 field for different date ranges?

Question

Hi,

I have a SQL table "ITM_SLS" with the following fields:

ITEM
DESCRIPTION
TRANSACTION #
DATE
QTY SOLD

I want to be able to output QTY SOLD for a one month value and a year to date value so that the output would look like this:

ITEM, DESCRIPTION, QTY SOLD MONTH, QTY SOLD YEAR TO DATE

Is this possible?

Answer 1

You could calculate the total quantity sold using group by in a subquery. For example

select a.Item, a.Description, b.MonthQty, c.YearQty
from (
    select distinct Item, Description from TheTable
) a
left join (
    select Item, sum(Qty) as MonthQty
    from TheTable 
    where datediff(m,Date,getdate()) <= 1
    group by Item
) b on a.Item = b.Item
left join (
    select Item, sum(Qty) as YearQty
    from TheTable 
    where datediff(y,Date,getdate()) <= 1
    group by Item
) c on a.Item = c.Item

The method to limit the subquery to a particular date range differs per DBMS, this example uses the SQL Server datediff function.

Answer 2

This will give you an idea of what you can do:

select 
    ITEM,
    DESCRIPTION, 
    QTY SOLD as MONTH, 
    ( select sum(QTY SOLD)
      from ITM_SLS
      where ITEM = I.ITEM
        AND YEAR = i.YEAR
     ) as YEAR TO DATE
from ITM_SLS I

Answer 3

Assuming the "one month" is last month...

select item
       , description
       , sum (case when trunc(transaction_date, 'MM') 
                           = trunc(add_months(sysdate, -1), 'MM') 
                   then qty_sold
                   else 0
                   end) as sold_month
       , sum(qty_sold) as sold_ytd
from itm_sls
where transaction_date >= trunc(sysdate, 'yyyy')
group by item, description
/