InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:03:25 GMT20http://www.sqlservercentral.com/Forums/Topic1366856-391-1.aspx11Tue, 02 Oct 2012 09:02:25 GMTdeep_patel1http://www.sqlservercentral.com/Forums/Topic1366856-391-1.aspx[code="sql"]--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 @across 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;[/code]Tue, 02 Oct 2012 05:52:15 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366856-391-1.aspx[code="sql"]DECLARE @Table1 TABLE (Month2Date VARCHAR(15), Yesterday VARCHAR(15), PrevMonth VARCHAR(15))INSERT INTO @Table1SELECT 'Month to Date', 'Yesterday', 'PrevMonth'SELECT * FROM @Table1[/code]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.Tue, 02 Oct 2012 04:30:28 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366856-391-1.aspx[quote][b]deep_patel1 (10/1/2012)[/b][hr]I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..[/quote]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.Mon, 01 Oct 2012 22:26:16 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1366856-391-1.aspxI am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..Mon, 01 Oct 2012 21:41:48 GMTdeep_patel1