Post reply

Combine two working queries to get this month and last years this month numbers shipped

  • March 9, 2016 at 10:26 am

    #313920

    I need to combine these two to get the results as smlPartID, TMT, LMT

    select sl1.smlPartID, sum(sl1.smlQuantityShipped) as TMT

    FROM m1_KF.dbo.ShipmentLines sl1

    where sl1.smlCreatedDate >=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) and smlCreatedDate <=DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

    group by smlPartID

    and

    select sl.smlPartID, sum(sl.smlQuantityShipped) as LMT

    FROM m1_KF.dbo.ShipmentLines sl

    where sl.smlCreatedDate >=DATEADD(month,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ) and sl.smlCreatedDate <=DATEADD(month,-12,DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))))

    group by sl.smlPartID

  • March 9, 2016 at 10:43 am

    #1864067

    This is my best guess with nothing to test it against.

    select

    sl1.smlPartID,

    sum(case when sl1.smlCreatedDate >= DATEADD(month,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ) and

    sl1.smlCreatedDate < DATEADD(month,-11,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) )

    then sl1.smlQuantityShipped

    else 0

    end) LastYearMonth,

    sum(case when sl1.smlCreatedDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) and

    sl1.smlCreatedDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1,0)

    then sl1.smlQuantityShipped

    else 0

    end) ThisYearMonth

    from

    m1_KF.dbo.ShipmentLines sl1

    where

    sl1.smlCreatedDate >= DATEADD(month,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) )

    group by

    sl.smlPartID;

  • March 10, 2016 at 12:28 pm

    #1864367

    For efficiency, the WHERE clause should include just the specific date ranges you need, rather than the entire year. Particularly if m1_KF.dbo.ShipmentLines is clustered first on smlCreatedDate (which it very likely should be, but probably isn't).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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