Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

mtd, yesterday and previous month data in 1table Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 9:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:58 PM
Points: 5, Visits: 61
I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..
Post #1366856
Posted Monday, October 1, 2012 10:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1366861
Posted Tuesday, October 2, 2012 4:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 ). 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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1366941
Posted Tuesday, October 2, 2012 5:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079

--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;


Post #1366975
Posted Tuesday, October 2, 2012 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:58 PM
Points: 5, Visits: 61
11
Post #1367104
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse