| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2007-11-01 : 11:41:34
|
| hi, how can I diplay date periods when a date falls between a date range it equals a period..eg, 2007-11-01 , if the date falls between 2007-10-20 and 2007-11-19 then period 11.if date between 2007-11-20 and 2007-12-19 then period 12..rather than typing in a load of case statements is there a clever way of doing this ?thank youjami |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 11:43:44
|
| Jamie,How do you define periods? From your sample above, i am assuming you mean the Month of the second date. Do you have a table defining periods such as Start Date and End Date for each period? How do you come up with the ranges for each period?Jeremy W. Oldham |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-11-01 : 11:45:14
|
| the date range is always between the 20th and the 19th of the month.There is no table with the date ranges.. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 11:51:07
|
| I am making a stab here (untested), but how about:Case When Datepart(dd, columnA) > 19 then datepart(mm,dateadd(mm, 1,columnA)) Else datepart(mm, columnA)EndJeremy W. Oldham |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-11-01 : 11:54:53
|
| thanks Jeremy, that may work.however, I have one problem a record doesnt stay in one period..eg, this month a record with a date 2007-11-01 will be in period 11, and priorty 11, but next month the record will be in period 11 but priorty 10..I think this is much harder than first thought.. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 11:58:37
|
| Define Priority? It wasn't in your original post. If you can describe how you set this and based on what, I might be able to help.Jeremy W. Oldham |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-11-01 : 12:07:33
|
| I am trying to work out the number of helpdesk calls.calls still open after a certain date I want to display, first showing their period and then their priority for when they should be dealt with.sorry, I think I should have opened 2 queries, as this might not be related to the period..If a call is 1 period old then its priority 1, if 2 periods old then priority is 2, and so on..hope that makes sense.thank you. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 12:14:16
|
| So you want to base the priority on period of the current date and the period returned from the record?How about:Case When Datepart(dd, columnA) > 19 then datepart(mm,dateadd(mm, 1,columnA)) Else datepart(mm, columnA)End Period, Case When Datepart(dd, getdate()) > 19 then datepart(mm,dateadd(mm, 1,getdate())) Else datepart(mm, getdate()) End- Case When Datepart(dd, columnA) > 19 then datepart(mm,dateadd(mm, 1,columnA)) Else datepart(mm, columnA) End +1 PriorityJeremy W. Oldham |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-11-01 : 12:19:12
|
| Wow ! I thinkmight do it !cheers Jeremy, much apreciated. I will run some tests to make sure, but all looks good ! thanks again. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 12:20:46
|
| Let me know for sure if it works correctly or not. If not, what tweaks you made. ;)Jeremy W. Oldham |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 14:09:18
|
quote:
Originally posted by joldham
I am making a stab here (untested)
Haven't tested it Brett so no haven't looked at the plan. Is there a better way to do it that has a more efficient plan? I am guessing that you have experience with longer execution times this way? I always willing to learn so I would be interested in your input even though it is not my original post.Jeremy W. Oldham |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-12-10 : 11:27:41
|
| hi,I thought this worked, but it doesn't take into account the year..CaseWhen Datepart(dd, columnA) > 19 then datepart(mm,dateadd(mm, 1,columnA))Else datepart(mm, columnA)End Periodrather than displaying the month + 1 is it possible to show the month and year on one line ? |
|
|
|
|
date periods