October 1, 2012 at 9:41 pm
I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..
October 1, 2012 at 10:26 pm
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.
October 2, 2012 at 4:30 am
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 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
October 2, 2012 at 5:52 am
--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;
October 2, 2012 at 9:02 am
11
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply