Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2009-11-06 : 14:23:22
|
| I’ve these tables with data for each hour of the day. I need to calculate things like AVG etc. 1) for each 8 hour period in a day2) for each day3) for each month4) for each yearThe structure of the tables are like:HEADER_DATA : [H_NUM (PK), H_TYPE, H_INTERVAL, H_STATION, H_START_DATE, H_END_DATE, H_START_TIME, H_END_TIME]DETAILS_DATA: [D_H_NUM (FK), D_PARAM, D_METHOD, D_UNIT, D_VALUE, D_FLAG]I am planning to use separate queries for each type. For 8 hr values, I can use START_DATE = END_DATE and then START_TIME = 0, END_TIME = 759 OR START_TIME = 800, END_TIME = 1559 OR START_TIME = 1600, END_TIME = 2359.For each day, I can just use START_DATE = END_DATE.But, how can I determine it is the beginning and end of the month, beginning/end of the year? Thanks. |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-11-06 : 16:07:39
|
| select dateadd(mm, datediff(mm, 0, getdate()), 0) as start_of_month(hint: end of month = start of next month - 1 day) |
 |
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2009-11-06 : 16:40:41
|
| But how do I get the 'start of next month'? Also the code you gave me doesn't relate to the Start_Date/End Date values in my table. So, how can I relate that to the data values calculated?Thanks. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-11-09 : 01:54:29
|
| Start of next monthselect dateadd(month,datediff(month,-1,getdate()),0)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
How to calculate data for monthly/yearly period