help me i need dynamic sql query for this code to update dynamically every year

Question

Post reply

help me i need dynamic sql query for this code to update dynamically every year

maheendra.puttareddy

SSC Enthusiast

Points: 119
More actions
May 15, 2015 at 7:04 am

#317502

UPDATE Tablename
SET Year= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Will something like this get you started?

SELECT TN.MSDATE, CASE WHEN TN.MSDATE > T.StartDate AND TN.MSDATE < T.EndDate THEN DATEPART(yyyy, T.EndDate) END

FROM Tablename TN

INNER JOIN

(SELECT '06/30/' + CONVERT(varchar(4), DATEPART(yyyy, MSDATE)) StartDate, '07/01/' + CONVERT(varchar(4), DATEPART(yyyy, MSDATE) + 1) EndDate

FROM Tablename

GROUP BY '06/30/' + CONVERT(varchar(4), DATEPART(yyyy, MSDATE)), '07/01/' + CONVERT(varchar(4), DATEPART(yyyy, MSDATE) + 1) ) T ON TN.MSDATE >= StartDate AND TN.MSDATE <= T.EndDate

ORDER BY TN.MSDATE

The < and > comparisons do not fill in values for 7/1

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

maheendra.puttareddy (5/15/2015)
UPDATE Tablename
SET Year= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End

Try this:

SELECT YEAR(DATEADD(DAY,184,MSDate))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

+ 100.

I did not have enough caffeine today! As soon as I saw your post I hit myself on the head!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Michael L John (5/15/2015)
+ 100.
I did not have enough caffeine today! As soon as I saw your post I hit myself on the head!

There won't be a person on here reading this who hasn't done exactly the same thing at least once before :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

maheendra.puttareddy SSC Enthusiast Points: 119 More actions · Answer 5

Thank you very much this is really helpful to me

Regards

Maheendra Reddy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

maheendra.puttareddy (5/18/2015)
Thank you very much this is really helpful to me
Regards
Maheendra Reddy

You're very welcome, and thank you for the nice feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Given the recent post at http://www.sqlservercentral.com/Forums/Topic1689004-392-1.aspx, I take it you still need a handl with the fiscal years. You could encapsulate the logic into a function that returns both the calendar and fiscal year information, which you could then use elsewhere. If the rules change, your updates would be limited to one location. Here's a start at the function, which uses Chris's solution above to determine the fiscal year, plus Lynn's always-useful date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.

if OBJECT_ID('dbo.YearInfo', 'if') is not null drop function dbo.YearInfo;

go

CREATE FUNCTION dbo.YearInfo(@dtm Datetime) RETURNS TABLE WITH SCHEMABINDING

AS

RETURN (

SELECT CalendarYearStart = DATEADD(year, DATEDIFF(year, 0, @dtm), 0),

CalendarYearEnd = DATEADD(ms, -3, DATEADD(year, DATEDIFF(year, 0, @dtm) + 1, 0)),

FiscalYear = YEAR(DATEADD(DAY, 184, @dtm))

);

go

You could add in whatever else you need to know and then use it to select the columns you need.

SELECT CalendarYearStart, CalendarYearEnd, FiscalYear FROM dbo.YearInfo('06/04/2015');

SELECT CalendarYearStart, CalendarYearEnd, FiscalYear FROM dbo.YearInfo('07/04/2015');

Better yet, this can be used in a CROSS APPLY in your query to return the columns based on a datetime column that determines what year the transaction was in.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions