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 12»»

Detect change of month and year in datestamp Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 2:12 AM
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.
Post #1388667
Posted Monday, November 26, 2012 9:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
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;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1388672
Posted Monday, November 26, 2012 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1388673
Posted Monday, November 26, 2012 9:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 2:12 AM
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.



Post #1388697
Posted Monday, November 26, 2012 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1388702
Posted Monday, November 26, 2012 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 2:12 AM
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.
Post #1388723
Posted Monday, November 26, 2012 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1388764
Posted Tuesday, November 27, 2012 2:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 2:12 AM
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.
Post #1389031
Posted Tuesday, November 27, 2012 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1389209
Posted Tuesday, November 27, 2012 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 2:12 AM
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
Post #1389226
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse