mtd, yesterday and previous month data in 1table

  • I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..

  • deep_patel1 (10/1/2012)


    I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..

    Not much we can do with this. Please read the first article I reference below in my signature block regarding asling for help. It will show you what you need to post and how to do it to get the best possible assistance.

  • DECLARE @Table1 TABLE

    (Month2Date VARCHAR(15), Yesterday VARCHAR(15), PrevMonth VARCHAR(15))

    INSERT INTO @Table1

    SELECT 'Month to Date', 'Yesterday', 'PrevMonth'

    SELECT * FROM @Table1

    Not trying to be funny here (OK maybe a little :-P). Just trying to show you how to provide DDL and sample data to help us help you.

    Also needed is a better description of the business requirement and expected results.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • --I am trying to get mtd, yesterday and previos month data in 1 table.

    --Can someone please help me..

    --===== TEST DATA =========

    declare @a table

    (Id int, OrderDate DateTime, OrderQty int );

    INSERT INTO @a

    ( Id, OrderDate, OrderQty )

    VALUES

    (1, '26 Aug 2012', 17),

    (1, '27 Aug 2012', 1),

    (1, '01 Sep 2012', 2),

    (1, '08 Sep 2012', 3),

    (1, '09 Sep 2012', 4),

    (1, '11 Sep 2012', 5),

    (1, '16 Sep 2012', 6),

    (1, '18 Sep 2012', 7),

    (1, '21 Sep 2012', 8),

    (1, '22 Sep 2012', 9),

    (2, '21 Sep 2012', 4),

    (2, '21 Sep 2012', 1),

    (2, '22 Sep 2012', 33);

    --select * from @a

    --===== SPECULATIVE SOLUTION ===========

    declare @today DateTime;

    set @today = '22 Sep 2012';

    select

    Id,

    Yesterday = SUM(CASE WHEN OrderDate = x.Yesterday THEN OrderQty ELSE 0 END),

    mtd = SUM(CASE WHEN OrderDate >= y.FirstDayOfMonth THEN OrderQty ELSE 0 END),

    PrevMth = SUM(CASE WHEN OrderDate >= z.FirstDayOfPrevMonth AND OrderDate < y.FirstDayOfMonth THEN OrderQty ELSE 0 END)

    from @a

    cross apply

    (select DATEADD(DAY, -1, @today)) x (Yesterday)

    cross apply

    (select DATEADD(MONTH,DATEDIFF(MONTH,0,@today),0)) y (FirstDayOfMonth)

    cross apply

    (select DATEADD(MONTH, -1, y.FirstDayOfMonth)) z (FirstDayOfPrevMonth)

    group by id;

  • 11

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

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