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 |
|
jszulc
Yak Posting Veteran
66 Posts |
 Posted - 2011-08-10 : 15:53:27
|
| I need to show aging items in my database based on time ranges. Whant to be absolutely sure that the time ranges are calculated based on today's date. Ex. 1 -3 mos starting now, or past 2-4 years starting today. Query shoudl be done on m.macd_submit_dt (datetime)Sample output.age Team1 Team2 Team3Past 2 to 4 Years 0 0 226 Past 1 to 2 Years 1 0 998 Past 0.5 to 1 Year 1 0 1224 Past 3 to 6 Months 0 0 214 Past 1 to 3 Months 35 0 1844 Past 10 to 30 days 345 355 3453attached is what I started to write but I am not sure about results...m.QUEUE_QUEUE_NAME = q.QUEUE_QUEUE_NAME and (YEAR(m.macd_submit_dt) - YEAR(getdate()) =-2 or YEAR(m.macd_submit_dt) - YEAR(getdate()) <-4) |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-08-10 : 16:49:13
|
| It depends on your definition of YEAR (or MONTH) is. Your current logic will calculate if the calendar year has changed. This implies that December 31, 2010 and the following day are one year apart. If you want year to be one trip around the sun then you could use the DATEADD function, a la: m.macd_submit_dt BETWEEN DATEDIFF(YEAR, -4, GetDate()) and DATEDIFF(YEAR, -2, GetDate())However, we still need to further clarify the year to account for the time portion of your dates. If you run this at noon, for instance, the which is exactly four years earlier will return mixed results. The morning of that day would faqll outside the window though the afternoon of that same day would fall inside the window. Is thaqt what you want?=======================================I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642) |
 |
|
|
jszulc
Yak Posting Veteran
66 Posts |
Posted - 2011-08-10 : 17:10:36
|
| Bustaz: this condition seems to work for me. returns correct #Can i use it? how do i handle days etc.WHERE m.QUEUE_QUEUE_NAME = q.QUEUE_QUEUE_NAME and m.MACD_SUBMIT_DT BETWEEN DATEADD(year,-4,GETDATE()) AND DATEADD(year,-2,GETDATE())AND q.LOB_TOWER = 'CTS' |
|
|
|
|
|
|
|
|
Time ranges