September 6, 2013 at 1:24 pm
Using T-SQL (SS2K8) in a stored procedure, I have created a temp table (#Month) with (Month, Year, Department, Count1, Count2, CurrPerc, and PrevPerc)
I am trying to update that table with data (CurrPerc) from the previous month in the new column (the previous month's data in PrevPerc)
UPDATE #Month
SET [PrevPerc] = [CurrPerc]
WHERE [MONTH] = (IF [MONTH] = 1 THEN 12 ELSE [MONTH] - 1 END
WHEN [MONTH] = 1 THEN T.[YEAR] - 1 ELSE T.[YEAR] END
INNER JOIN #Month P ON [DEPARTMENT] = P.DEPARTMENT
I know my T-Sql is off, so any help would be great. Thanks!
September 6, 2013 at 1:36 pm
Something like this?
UPDATE #Month
SET [PrevPerc] = [CurrPerc],
[MONTH] = case [MONTH] when 1 THEN 12 ELSE [MONTH] - 1 END,
[YEAR] = case [MONTH] when 1 THEN T.[YEAR] - 1 ELSE T.[YEAR] END
If that doesn't get you close enough to figure it out I would recommend you take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 1:58 pm
Thanks Sean - that populated the PrevPerc column with the data from the CurrPerc (but from the same month). I was hoping to capture the previous month's CurrPerc data.
It has gotten me closer though... I'll keep at it.
Jeff
September 6, 2013 at 3:18 pm
UPDATE CurrMonth
SET [PrevPerc] = PrevMonth.[CurrPerc]
FROM #Month CurrMonth
INNER JOIN #Month PrevMonth ON
PrevMonth.[DEPARTMENT] = CurrMonth.DEPARTMENT AND
PrevMonth.[YEAR] = CurrMonth.[YEAR] - CASE WHEN CurrMonth.[MONTH] = 1 THEN 1 ELSE 0 END AND
PrevMonth.[MONTH] = CASE WHEN CurrMonth.[MONTH] = 1 THEN 12 ELSE CurrMonth.[MONTH] - 1 END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2013 at 3:26 pm
That was it! Thank you so much Scott.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy