Sum Command?

  • I have an code that breaks down sales by state and compares totals from last year with this year, per month with a YTD total comparison. I need the totals of each month calculated.

    this is an example code with only one month.

    I have tried a couple of different things but cant get it to display the information I need.

    Anyone have nay advice?

    set transaction isolation level read uncommitted

    set nocount on

    SELECT (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END) AS DomesticInternational, h.ShipToDeliveryLocationState,

    DATEPART(yyyy, h.InvoiceDate) AS theYear,

    SUM(CASE WHEN DatePart(MM, h.InvoiceDate) = 1 THEN (CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END) ELSE 0 END) AS January,

    SUM(CASE WHEN DatePart(MM, h.InvoiceDate) <= DatePart(MM, GetDate()) THEN (CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END) ELSE 0 END) AS YTDTotal

    FROM FS_ARInvoiceLine l

    -- join invoice lines

    INNER JOIN FS_ARInvoiceHeader h (nolock)

    ON h.ARInvoiceHeaderKey = l.ARInvoiceHeaderKey

    -- join customer master

    INNER JOIN FS_Customer c (nolock)

    ON c.CustomerKey = l.CustomerKey

    -- join order lines

    INNER JOIN FS_ARCustomerOrderHeader o (nolock)

    ON o.ARCustomerOrderHeaderKey = l.ARCustomerOrderHeaderKey

    -- join item master

    LEFT OUTER JOIN FS_Item i (nolock)

    ON i.ItemKey = l.ItemKey

    -- join customer description

    INNER JOIN STSS_CustomerClassDescription CC (nolock)

    ON CC.CustomerClass1 = c.CustomerClass1

    WHERE (DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) OR

    DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 1 OR

    DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 0 OR

    DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 0) AND (i.ItemReference2 = '55') AND (h.InvoiceStatus IN ('N', 'P', 'Y')) AND

    (dbo.STSS_GetSaleType(c.CustomerID, c.CustomerClass2) = 'Distinguished Sales')

    GROUP BY (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END), h.ShipToDeliveryLocationState, DATEPART(yyyy, h.InvoiceDate)

    ORDER BY (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END), h.ShipToDeliveryLocationState, DATEPART(yyyy, h.InvoiceDate) DESC

  • There are a few options to look at. First thought is that you need to incorporate the month component into the grouping, or you will be generating one number at a time. something like - group by YYYYMM of the date (makes your compares to previous year easy.)

    Second - what's the expected output needed? I'm seeing two options:

    Year Month monthlytotal LastYearMonth

    2007 01 12345 23456

    .....

    YTD 99999 888888

    OR

    YEAR MONTH monthlytotal lastyearmonthly YTD LastYearYTD

    Finally - try to post some specific DDL info about the relevant data.

    I hope you're not averse to pregenerating some data (even if you refresh on the fly), cause this could really use it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • D or I State theyear YTD Totals Jan Feb Mar Apr May June

    DomesticAL2007 4 276 1 3 15

    DomesticAL2006 1 001 0 0 0

    Totals NEEDED

    This is the output of y query as of now.

    I have the YTD - totals of each year, but what I also need the totals for YTD and each month.

    any suggestions?

  • not sure I fully understand all of the criteria on qualifying sales, but here's a rough sketch.

    The current best way to do this is to pre-generate the monthly and YTD totals, then correlate them.

    Create table salesbymonth (CustClass1 char, locstate char(2),yr int, YearMonth int, PrevYearMonth int,MonthTotal Money,YTDTotal money)

    --create the monthly total

    insert into salesbymonth (CustClass1, locstate,yr, YearMonth, PrevYearMonth ,MonthTotal )

    select case when c.customerclass1='I' then 'I' else 'D' end, h.ShipToDeliveryLocationState,datepart(yy,invoicedate),left(convert(char,InvoiceDate,112),6),left(convert(char,dateadd(yy,-1,InvoiceDate),112),6), sum(CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END)

    from

    FROM FS_ARInvoiceLine l

    -- join invoice lines

    INNER JOIN FS_ARInvoiceHeader h (nolock)

    ON h.ARInvoiceHeaderKey = l.ARInvoiceHeaderKey

    -- join customer master

    INNER JOIN FS_Customer c (nolock)

    ON c.CustomerKey = l.CustomerKey

    -- join item master

    LEFT OUTER JOIN FS_Item i (nolock)

    where

    (h.InvoiceStatus IN ('N', 'P', 'Y')) AND

    (dbo.STSS_GetSaleType(c.CustomerID, c.CustomerClass2) = 'Distinguished Sales')

    --get set up to create the YTD values

    Create index SalesByMonth_ix on SalesByMonth(CustClass,locstate,yearmonth)

    Create index SalesByMonth_pix on SalesByMonth(CustClass,locstate,Prevyearmonth)

    declare @previd int

    declare @runsales money

    set @previd=0

    set @runsales=0

    --set up the YTD values

    update salesbymonth

    set @runsales=YTDTotal=case when @previd=yr then @runsales else 0 end+MonthTotal,

    @previd=yearmonth

    from salesbymonth with (index(SalesByMonth_ix),tablock)

    ---and now - for the results

    select

    curr.customerclass1,curr.yearmonth,curr.monthtotal CurrentMonthtotal, prev.monthtotal PrevMonthTotal,curr.YTDTotal currentYTD, prev.YTDTotal PrevYTDTotal

    from salesbymonth curr inner join salesbymonth prev

    on curr.customerclass1=prev.customerclass1 and curr.locstate=prev.locstate and curr.prevmonth=prev.yearmonth

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)

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