February 2, 2012 at 3:33 am
Hi there,
I have table holding values for hours, and for days as:
CREATE TABLE [dbo].[HoursandDays](
[Hour] [int] NULL,
[Monday] [float] NULL,
[Tuesday] [float] NULL,
[Wednesday] [float] NULL,
[Thursday] [float] NULL,
[Friday] [float] NULL,
[Saturday] [float] NULL,
[Sunday] [float] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL
) ON [PRIMARY]
Data looks like this:
HourMondayTuesdayWednesdayThursdayFridaySaturdaySundayQuarterYear
03333345111
16666789111
29888101113111
311101010121415111
413121212141617111
514141413161920111
617151514182122111
720171715202325111
824212120252528111
930272725303133111
1036343330363638111
1144414036424245111
1249464643494951111
1357525249555556111
1465585956606163111
1572636362656769111
1680686868707476111
1786747375768182111
1893807981838787111
19100868586889294111
201069191919497101111
21111979796100102108111
22116104102101106108114111
23120109107104110114119111
As you can see, there are accumulating values for each day, as the hours progress. Now then, what I'd like to see as an alternative output is the difference (growth) for each hour. So for Monday, in the hour the value would be 4 (120-116). I wonder if anyone knows of an elegant way to calculate these numbers ?
Regards, Greg
February 2, 2012 at 3:40 am
Do a self join on Hour = Hour + 1 and subtract one Monday from the other.
John
February 2, 2012 at 4:15 am
Hi John,
Umm, would you be able to show the syntax for this ? I've had a go !
select * from dbo.table t left join
(select * from dbo.table ) y on
t.hour=y.hour+1 and t.quarter=y.quarter and t.year=y.year
I'm guessing something like the above (don't laugh !). But I now need to get those differences - woudl that just be a massive case statement ?
regards, Greg
February 2, 2012 at 4:26 am
Greg
Your FROM clause is simpler than that:
FROM table t1 INNER JOIN table t2
ON t1.hour = t2.hour + 1
(I've kept it simple since all your sample data is for the same quarter of the same year. If you try to add the year and quarter as well, you'll get strange things happening at year and quarter boundaries.)
Now that we have the FROM clause, your SELECT is simply this:
SELECT t1.hour, t1.Monday - t2.Monday
John
February 2, 2012 at 5:18 am
OK Thanks,
That is mostly working - except where t1.hour is zero. I think this needs treating as a special case, but I'm not sure how to do this.
Regards, Greg
February 2, 2012 at 5:23 am
Greg
Like I said, you're going to have problems on the boundaries, and in part it will depend on your business rules. For example, do you want to compare hour 0 with hour 23 of the previous period?
What I don't understand is that why you were joining on hour, quarter and year. Does that mean that the values in your hour column can go up to 2184 (the number of hours in a quarter)?
John
February 2, 2012 at 5:32 am
Hi John,
Well, let me try to explain.
For a t1.hour of zero, I'd want to show the values that row. It's the only row that doesn't need a subtraction, just literal values there.
If you check out the test data (it's not in a very nice format, sorry) you'll see that the qaurter and year values are just filters really showing 1-4 for the qtr and 11 or 10 for the year.
Hours go 0 through 23 for each qtr.
Does that help ?
February 2, 2012 at 6:14 am
Sorted it all out John, thanks for your guidance.
Greg
February 5, 2012 at 2:03 pm
CELKO (2/2/2012)
If you want to get a running total, then use a VIEW with the new windows clause in the SQL Server 2012 release.
Really??? How'z he going to do that since it's not been released yet? Also, you keep talking about ANSI and ISO standards... please post free links to those standards so we can all follow along with you. Last but not least, you keep talking about a Calendar Table. Since you must have one, please post your ANSI/ISO compliant Calendar Table creation code (or a link to same) so that we can follow along there, as well.
Thanks, Joe. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply