Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detect change of month and year in datestamp


Detect change of month and year in datestamp

Author
Message
leesider
leesider
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 75
I have a table with datestamp entries as below

SELECT st.startup_Time FROM #KWHtemp st

OUTPUT:

2011-12-31 14:28:37.000
2011-12-31 15:11:04.213
2012-01-01 14:28:37.000
2012-01-01 15:11:04.213
...................................
...................................
2012-02-02 07:21:54.423
2012-02-02 07:37:10.180

(the two lines with ".........." represent a few hundred entries)
As you can see the entries start in December 2011 and continue to January 2012 and then February 2012.
I want to loop through the table and whenever the month and year changes output the year and a number for the month (starting at 1) so I get an output like below.

2011 01
2012 02
2012 03
.....
......

I don't want the number of the month as in 10 for October or 11 for November as I want to count the months as I am going from year to year. In the example I am showing, the table starts in December 2011, so the first month is December 2011 (month 1). The next month is January 2012 (month 2) and so on. I do need to keep track of the years (by the actual year) at the same time though.
I am going to need a cursor, I just don't know how to detect when a year or month datepart changes from date to date.
Any replies are appreciated.
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
leesider (11/26/2012)
I am going to need a cursor, I just don't know how to detect when a year or month datepart changes from date to date.


No cursor required.

Try this: -
SELECT [YEAR] = YEAR(startup_Time),
[MONTH_NUMBER] = DENSE_RANK() OVER(ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, startup_Time), 0)),
startup_Time
FROM #KWHtemp;




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
You do NOT need a cursor for this. It is not totally clear what you want here but ROW_NUMBER should be able to provide you what you need. If you need help with the actual code you will need to give us something to work with (ddl, sample data and desired output). Take a look at the first link 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
leesider
leesider
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 75
Cadavre (11/26/2012)
leesider (11/26/2012)
I am going to need a cursor, I just don't know how to detect when a year or month datepart changes from date to date.


No cursor required.

Try this: -
SELECT [YEAR] = YEAR(startup_Time),
[MONTH_NUMBER] = DENSE_RANK() OVER(ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, startup_Time), 0)),
startup_Time
FROM #KWHtemp;



Apologies, the requirements have changed; the week number has to be which week the startup_Time is in the year not the week starting from zero. I.e. the week of the the year that the date is located in. For example, 2012-04-03 is in the fifteenth week of the year so if that date appears then the week number 15 should appear along side it. Each date has to have the week number of the year that it is located in.

The code you posted is almost what I need but at the same time as that code is executing I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from a column KWH_savings in the inner select.


In pseudocode this would be somthing like below:

Outer select here (year, week number, start_up time)
Inner select (KWH_savings, startup_Time)
when inner startup_Time is in the same week the same as outer startup_Time, sum the KWH_savings and output it for that week.
close inner select
close outer select



I should end up with an output like this:


Year week total_savings
2012 10 72.2571543100001
2012 11 629.06429487
2012 12 1190.04593862
2012 13 1957.97607841


Sorry for the confusion.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
You should look at datepart.


For example, 2012-04-03 is in the fifteenth week of the year so if that date appears then the week number 15 should appear along side it.


You sure about that? I believe it is in the 14th week.


select DATEPART(ww, '2012-04-03')



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
leesider
leesider
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 75
Sean Lange (11/26/2012)
You should look at datepart.


For example, 2012-04-03 is in the fifteenth week of the year so if that date appears then the week number 15 should appear along side it.


You sure about that? I believe it is in the 14th week.


select DATEPART(ww, '2012-04-03')




Possibly, doesn't really matter what the week number is as long as it is the correct one.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
leesider (11/26/2012)
Sean Lange (11/26/2012)
You should look at datepart.


For example, 2012-04-03 is in the fifteenth week of the year so if that date appears then the week number 15 should appear along side it.


You sure about that? I believe it is in the 14th week.


select DATEPART(ww, '2012-04-03')




Possibly, doesn't really matter what the week number is as long as it is the correct one.


OK so to borrow from Cadavre's post...

SELECT [YEAR] = YEAR(startup_Time),
DATEPART(ww, startup_Time) as WeekNumber,
[MONTH_NUMBER] = DENSE_RANK() OVER(ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, startup_Time), 0)),
startup_Time
FROM #KWHtemp;



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
leesider
leesider
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 75
OK, as I said in my last post I need to extend it a little further to include a running total. Cadavre is correct, I don't need a cursor. I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from the column KWH_savings and add it up for each week in the inner select.
What I have below does it except it is too slow:

;with cte as
(
SELECT [YEAR] = YEAR(st.startup_Time),
DATEPART(ww, st.startup_Time) as WeekNumber,

(

SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st
)
SELECT *
FROM cte

It is the inner select that is slowing it down, I don't know if I can do it a different way that is not so slow.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
leesider (11/27/2012)
OK, as I said in my last post I need to extend it a little further to include a running total. Cadavre is correct, I don't need a cursor. I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from the column KWH_savings and add it up for each week in the inner select.
What I have below does it except it is too slow:

;with cte as
(
SELECT [YEAR] = YEAR(st.startup_Time),
DATEPART(ww, st.startup_Time) as WeekNumber,

(

SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st
)
SELECT *
FROM cte

It is the inner select that is slowing it down, I don't know if I can do it a different way that is not so slow.


This is where knowing the entire problem up front would be very helpful. You don't need to do an "inner query" here as you call it. I think what you really want here is a running total. What you have done here is what is known as a triangular join and as you have discovered the performance is hideous. Take a look at this article by Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/68467/

He explains how the running total works and how to make it fast.

If you need help with the actual code you need to post ddl, sample data and desired output based on the article in my signature.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
leesider
leesider
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 75
Sean Lange (11/27/2012)
leesider (11/27/2012)
OK, as I said in my last post I need to extend it a little further to include a running total. Cadavre is correct, I don't need a cursor. I need to execute an inner select from another table that also contains a startup_Time. I want to compare this startup_Time with the startup_Time in the outer select and as it goes from one week to the next, select from the column KWH_savings and add it up for each week in the inner select.
What I have below does it except it is too slow:

;with cte as
(
SELECT [YEAR] = YEAR(st.startup_Time),
DATEPART(ww, st.startup_Time) as WeekNumber,

(

SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st
)
SELECT *
FROM cte

It is the inner select that is slowing it down, I don't know if I can do it a different way that is not so slow.


This is where knowing the entire problem up front would be very helpful. You don't need to do an "inner query" here as you call it. I think what you really want here is a running total. What you have done here is what is known as a triangular join and as you have discovered the performance is hideous. Take a look at this article by Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/68467/

He explains how the running total works and how to make it fast.

If you need help with the actual code you need to post ddl, sample data and desired output based on the article in my signature.


Yeah I know; I wasn't the one that wrote it though. I decided on the solution below. The procedure was slow because everytime it ran it was entering the same data everytime. It only needs to calculate the "newest" week. For example this week is the 48th week of the year. It runs this week and calculates all 48 weeks of the year. Next week it calculates all 48 again and the 49th week. This is unnecessary so I created a pemanent table (TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II) and entered zero into the column for "week".
I select everything from the temporary table KWHtemp that has a "week" value greater than the maximum "week" value in the table TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II.
The first time it runs it will take a while because the maximum "week" value in the table TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II is zero.
The next time it runs it will only add one new week because the maximum week value in TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II will be 48.




;with cte as
(
SELECT
datepart(yyyy,st.startup_Time) as 'Year',
datepart(week,st.startup_Time) as 'Week_Num',
datename(wk,st.startup_Time) as 'Week',
(
SELECT SUM(st2.KWH_savings)
FROM #KWHtemp st2
WHERE
st2.startup_Time <= st.startup_Time
) AS AccountRunningTotal
FROM #KWHtemp st where datepart(week,st.startup_Time)>(select MAX (week) from TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II)


)



insert into dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II(Year,week,total_savings)

SELECT [Year], [week],max(AccountRunningTotal) AS total_savings
FROM cte
GROUP BY [Year], [week], [Week_Num]
ORDER BY [Year], [Week_Num]

delete from dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II where week=0
SELECT *
FROM dbo.TBL_SP_ACCUMLATIVE_WEEK_KWH_ALL_II
GROUP BY [Year], [week], total_savings
ORDER BY [Year], [week],total_savings
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search