October 14, 2009 at 11:37 am
I have the following data, I want TO calculate the MTD(MONTH specified) AND TTM(Trailing Tweleve months) discounts offered FOR a particular product FOR a given MONTH AND YEAR.
--DROP TABLE #Temp
CREATE TABLE #Temp
(ID INT IDENTITY(1,1),
MONTH VARCHAR(20),
YEAR VARCHAR(10),
ProductID VARCHAR(20),
discounts MONEY)
INSERT INTO #Temp(MONTH, YEAR, ProductID,discounts)
SELECT 'Jan','2008','Prod1',100.00
UNION
SELECT 'Feb','2008','Prod21',10.00
UNION
SELECT 'Mar','2008','Prod36',20.00
UNION
SELECT 'Apr','2008','Prod44',30.00
UNION
SELECT 'May','2008','Prod50',46.00
UNION
SELECT 'Jun','2008','Prod69',55.00
UNION
SELECT 'Jul','2008','Prod77',66.00
UNION
SELECT 'Aug','2008','Prod8',87.00
UNION
SELECT 'Sep','2008','Prod9',110.00
UNION
SELECT 'Oct','2008','Prod030',22.00
UNION
SELECT 'Nov','2008','Prod110',34.00
UNION
SELECT 'Dec','2008','Prod320',190.00
UNION
SELECT 'Jan','2009','Prod1',10.00
UNION
SELECT 'Feb','2009','Prod21',50.00
UNION
SELECT 'Mar','2009','Prod36',20.00
UNION
SELECT 'Apr','2009','Prod44',33.00
UNION
SELECT 'May','2009','Prod50',46.00
UNION
SELECT 'Jun','2009','Prod69',55.00
UNION
SELECT 'Jul','2009','Prod77',69.00
UNION
SELECT 'Aug','2009','Prod8',87.00
UNION
SELECT 'Sep','2009','Prod9',119.00
UNION
SELECT 'Oct','2009','Prod030',22.00
UNION
SELECT 'Nov','2009','Prod110',34.00
SELECT * FROM #Temp
October 14, 2009 at 11:59 am
This looks like something that can be solved with Sum() and Group By. Are you not familiar with those, or am I missing something?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2009 at 1:05 pm
Couple of observations:
1. You don't have daily data, yet you say you want to calculate MTD (month-to-date). Did you mean to say YTD (year-to-date)?
2. You don't have month numbers, which would make this much simpler. Was that by design?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 1:20 pm
Bob Hovious 24601 (10/14/2009)
Couple of observations:1. You don't have daily data, yet you say you want to calculate MTD (month-to-date). Did you mean to say YTD (year-to-date)?
2. You don't have month numbers, which would make this much simpler. Was that by design?
I was trying to update my post, but for some reason it keeps timing out.
Here's what I have:
I have multiple products for different months, like for example:
In Jan 2008: I have prod1,prod2,prod3 with different discounts
In Feb 2008:I have prod1,prod2,prod3 with different discounts
In Mar 2008:I have prod1,prod2,prod3 with different discounts
In Apr 2008:I have prod1,prod2,prod3 with different discounts
For a given month,year and prod:
I need for that prod: discounts for the Month and
discounts for TTM(last 12 months)
October 14, 2009 at 2:56 pm
I'm still not quite clear. Would you please do us a favor? Amend your sample data (which was very nicely scripted and we appreciate it!!) to add a couple more rows with different product ids for a month or so, then show us by example what a correct answer would look like. Keep it simple so you can use a spreadsheet to calculate the values you expect to see. Visual examples are much easier for us to work with than verbal descriptions. Thank you for your time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply