November 26, 2012 at 8:53 am
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.
201101
201202
201203
.....
......
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.
November 26, 2012 at 9:04 am
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;
November 26, 2012 at 9:05 am
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 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/
November 26, 2012 at 9:57 am
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.
November 26, 2012 at 10:05 am
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 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/
November 26, 2012 at 10:41 am
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.
November 26, 2012 at 11:59 am
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 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/
November 27, 2012 at 2:42 am
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.
November 27, 2012 at 7:53 am
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/[/url]
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 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/
November 27, 2012 at 8:25 am
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/[/url]
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
November 27, 2012 at 8:44 am
Yeah I know; I wasn't the one that wrote it though.
But you COULD be the one to fix it. Your solution is still using a horribly slow way of doing this. You have successfully minimized the amount of data but you still have the triangular join. Someday you will have to come back and fix it correctly.
_______________________________________________________________
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/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply