Month query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Month query

Author

Topic

daphnie96757
Starting Member

3 Posts

Posted - 2008-07-16 : 10:03:51

Review Month +
Review Date
Month Range(Days) 1 2 3 4 5 6 7 8 9 10 11
1 0-30 N/V N/V N/V N/V N/V N/V N/V N/V N/V N/V N/V
2 31-60 1 N/V N/V N/V N/V N/V N/V N/V N/V N/V N/V
3 61-90 2 1 N/V N/V N/V N/V N/V N/V N/V N/V N/V
4 91-120 3 2 1 N/V N/V N/V N/V N/V N/V N/V N/V
5 121-150 4 3 2 1 N/V N/V N/V N/V N/V N/V N/V
6 151-180 5 4 3 2 1 N/V N/V N/V N/V N/V N/V
7 181-210 6 5 4 3 2 1 N/V N/V N/V N/V N/V
8 211-240 7 6 5 4 3 2 1 N/V N/V N/V N/V
9 241-270 8 7 6 5 4 3 2 1 N/V N/V N/V
10 271-300 9 8 7 6 5 4 3 2 1 N/V N/V
11 301-330 10 9 8 7 6 5 4 3 2 1 N/V
12 331-360 11 10 9 8 7 6 5 4 3 2 1

Here is the logic that is needed however; I'm not sure how to do this.
The query is based on a user input date and needs to span the year past. N/V = not valid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 10:06:02

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

daphnie96757
Starting Member

3 Posts

Posted - 2008-07-16 : 16:50:19

Server 2000 - here is what I have however; I end up with alot of 8's.

DROP TABLE #tmpData

CREATE TABLE #tmpData

(RowType int ,
clientID int,
StartDate datetime,
enddate datetime,
startMonth int,
diff int,
validation varchar (12)
)

INSERT INTO #tmpData
(clientID, startDate, endDate, startMonth, diff, validation)
SELECT cssid_fk, effectiveDate, lapseDate, 0, 0, 'XXXXXXXXXXXX'
FROM clients..ConsumerStatus
WHERE effectivedate between '4/28/2007' and '5/1/2008'

DECLARE @revStart smalldatetime
SET @revStart = '5/1/2007'

UPDATE #tmpData
SET startDate = @revStart
WHERE startDate <= @revStart AND endDate >@revStart

UPDATE #tmpData
SET endDate = @revStart + 365
WHERE endDate > @revStart + 365

DELETE FROM #tmpData
WHERE endDate < @revStart

UPDATE #tmpData
SET startMonth = DatePart(mm, startDate),
diff = DATEDIFF(dd, startDate, endDate)

DECLARE @revStart smalldatetime
SET @revStart = '5/1/2007'
DECLARE @revMonth int
SET @revMonth = datepart(mm, @revStart)

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 1
WHEN diff between 31 and 60 THEN 2
WHEN diff between 61 and 90 THEN 3
WHEN diff between 91 and 120 THEN 4
WHEN diff between 121 and 150 THEN 5
WHEN diff between 151 and 180 THEN 6
WHEN diff between 181 and 210 THEN 7
WHEN diff between 211 and 240 THEN 8
WHEN diff between 241 and 270 THEN 9
WHEN diff between 271 and 300 THEN 10
WHEN diff between 301 and 330 THEN 11
WHEN diff > 330 THEN 12
ELSE 0 END)
WHERE startMonth = @revMonth

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 2
WHEN diff between 31 and 60 THEN 3
WHEN diff between 61 and 90 THEN 4
WHEN diff between 91 and 120 THEN 5
WHEN diff between 121 and 150 THEN 6
WHEN diff between 151 and 180 THEN 7
WHEN diff between 181 and 210 THEN 8
WHEN diff between 211 and 240 THEN 9
WHEN diff between 241 and 270 THEN 10
WHEN diff between 271 and 300 THEN 11
WHEN diff > 300 THEN 11
ELSE 0 END)
WHERE startMonth = @revMonth + 1

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 3
WHEN diff between 31 and 60 THEN 4
WHEN diff between 61 and 90 THEN 5
WHEN diff between 91 and 120 THEN 6
WHEN diff between 121 and 150 THEN 7
WHEN diff between 151 and 180 THEN 8
WHEN diff between 181 and 210 THEN 9
WHEN diff between 211 and 240 THEN 10
WHEN diff > 241 THEN 10
ELSE 0 END)
WHERE startMonth = @revMonth + 2

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 4
WHEN diff between 31 and 60 THEN 5
WHEN diff between 61 and 90 THEN 6
WHEN diff between 91 and 120 THEN 7
WHEN diff between 121 and 150 THEN 8
WHEN diff between 151 and 180 THEN 9
WHEN diff >181 THEN 9
ELSE 0 END)
WHERE startMonth = @revMonth + 3

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 5
WHEN diff between 31 and 60 THEN 6
WHEN diff between 61 and 90 THEN 7
WHEN diff between 91 and 120 THEN 8
WHEN diff >121 THEN 8
ELSE 0 END)
WHERE startMonth = @revMonth + 4

Update #tmpData
SET validation =
(CASE WHEN diff between 1 and 30 THEN 6
WHEN diff between 31 and 60 THEN 7
WHEN diff >61 THEN 7
ELSE 0 END)
WHERE startMonth = @revMonth + 5

SELECT * FROM #tmpData
Order by startmonth

daphnie96757
Starting Member

3 Posts

Posted - 2008-08-21 : 17:26:33

This is what we came up with:
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'AOOOOOOOOOOO'
WHEN diff between 31 and 60 THEN 'ABOOOOOOOOOO'
WHEN diff between 61 and 91 THEN 'ABCOOOOOOOOO'
WHEN diff between 92 and 120 THEN 'ABCDOOOOOOOO'
WHEN diff between 121 and 150 THEN 'ABCDEOOOOOOO'
WHEN diff between 151 and 182 THEN 'ABCDEFOOOOOO'
WHEN diff between 183 and 210 THEN 'ABCDEFGOOOOO'
WHEN diff between 211 and 240 THEN 'ABCDEFGHOOOO'
WHEN diff between 241 and 273 THEN 'ABCDEFGHIOOO'
WHEN diff between 274 and 300 THEN 'ABCDEFGHIJOO'
WHEN diff between 301 and 334 THEN 'ABCDEFGHIJKO'
WHEN diff > 334 THEN 'ABCDEFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN @revMonth >12 THEN @revMonth-12
ELSE @revMonth END)

--set validation code for date ranges when startmonth is revmonth +1
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OBOOOOOOOOOO'
WHEN diff between 31 and 60 THEN 'OBCOOOOOOOOO'
WHEN diff between 61 and 91 THEN 'OBCDOOOOOOOO'
WHEN diff between 92 and 120 THEN 'OBCDEOOOOOOO'
WHEN diff between 121 and 150 THEN 'OBCDEFOOOOOO'
WHEN diff between 151 and 182 THEN 'OBCDEFGOOOOO'
WHEN diff between 183 and 210 THEN 'OBCDEFGHOOOO'
WHEN diff between 211 and 240 THEN 'OBCDEFGHIOOO'
WHEN diff between 241 and 273 THEN 'OBCDEFGHIJOO'
WHEN diff between 274 and 300 THEN 'OBCDEFGHIJKO'
WHEN diff > 300 THEN 'OBCDEFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+1) >12 THEN (@revMonth+1)-12
ELSE (@revMonth+1) END)

--set validation code for date ranges when startmonth is revmonth +2
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOCOOOOOOOOO'
WHEN diff between 31 and 60 THEN 'OOCDOOOOOOOO'
WHEN diff between 61 and 91 THEN 'OOCDEOOOOOOO'
WHEN diff between 92 and 120 THEN 'OOCDEFOOOOOO'
WHEN diff between 121 and 150 THEN 'OOCDEFGOOOOO'
WHEN diff between 151 and 182 THEN 'OOCDEFGHOOOO'
WHEN diff between 183 and 210 THEN 'OOCDEFGHIOOO'
WHEN diff between 211 and 240 THEN 'OOCDEFGHIJOO'
WHEN diff between 241 and 273 THEN 'OOCDEFGHIJKO'
WHEN diff > 273 THEN 'OOCDEFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+2) >12 THEN (@revMonth+2)-12
ELSE (@revMonth+2) END)

--set validation code for date ranges when startmonth is revmonth +3
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOODOOOOOOOO'
WHEN diff between 31 and 60 THEN 'OOODEOOOOOOO'
WHEN diff between 61 and 91 THEN 'OOODEFOOOOOO'
WHEN diff between 92 and 120 THEN 'OOODEFGOOOOO'
WHEN diff between 121 and 150 THEN 'OOODEFGHOOOO'
WHEN diff between 151 and 182 THEN 'OOODEFGHIOOO'
WHEN diff between 183 and 210 THEN 'OOODEFGHIJOO'
WHEN diff between 211 and 240 THEN 'OOODEFGHIJKO'
WHEN diff > 240 THEN 'OOODEFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+3) >12 THEN (@revMonth+3)-12
ELSE (@revMonth+3) END)

--set validation code for date ranges when startmonth is revmonth +4
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOEOOOOOOO'
WHEN diff between 31 and 60 THEN 'OOOOEFOOOOOO'
WHEN diff between 61 and 91 THEN 'OOOOEFGOOOOO'
WHEN diff between 92 and 120 THEN 'OOOOEFGHOOOO'
WHEN diff between 121 and 150 THEN 'OOOOEFGHIOOO'
WHEN diff between 151 and 182 THEN 'OOOOEFGHIJOO'
WHEN diff between 183 and 210 THEN 'OOOOEFGHIJKO'
WHEN diff > 210 THEN 'OOOOEFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+4) >12 THEN (@revMonth+4)-12
ELSE (@revMonth+4) END)

--set validation code for date ranges when startmonth is revmonth +5
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOFOOOOOO'
WHEN diff between 31 and 60 THEN 'OOOOOFGOOOOO'
WHEN diff between 61 and 91 THEN 'OOOOOFGHOOOO'
WHEN diff between 92 and 120 THEN 'OOOOOFGHIOOO'
WHEN diff between 121 and 150 THEN 'OOOOOFGHIJOO'
WHEN diff between 151 and 182 THEN 'OOOOOFGHIJKO'
WHEN diff > 182 THEN 'OOOOOFGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+5) >12 THEN (@revMonth+5)-12
ELSE (@revMonth+5) END)

--set validation code for date ranges when startmonth is revmonth +6
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOOGOOOOO'
WHEN diff between 31 and 60 THEN 'OOOOOOGHOOOO'
WHEN diff between 61 and 91 THEN 'OOOOOOGHIOOO'
WHEN diff between 92 and 120 THEN 'OOOOOOGHIJOO'
WHEN diff between 121 and 150 THEN 'OOOOOOGHIJKO'
WHEN diff > 150 THEN 'OOOOOOGHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+6) >12 THEN (@revMonth+6)-12
ELSE (@revMonth+6) END)

--set validation code for date ranges when startmonth is revmonth +7
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOOOHOOOO'
WHEN diff between 31 and 60 THEN 'OOOOOOOHIOOO'
WHEN diff between 61 and 91 THEN 'OOOOOOOHIJOO'
WHEN diff between 92 and 120 THEN 'OOOOOOOHIJKO'
WHEN diff > 120 THEN 'OOOOOOOHIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+7) >12 THEN (@revMonth+7)-12
ELSE (@revMonth+7) END)

--set validation code for date ranges when startmonth is revmonth +8
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOOOOIOOO'
WHEN diff between 31 and 60 THEN 'OOOOOOOOIJOO'
WHEN diff between 61 and 91 THEN 'OOOOOOOOIJKO'
WHEN diff > 91 THEN 'OOOOOOOOIJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+8) >12 THEN (@revMonth+8)-12
ELSE (@revMonth+8) END)

--set validation code for date ranges when startmonth is revmonth +9
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOOOOOJOO'
WHEN diff between 31 and 60 THEN 'OOOOOOOOOJKO'
WHEN diff > 60 THEN 'OOOOOOOOOJKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+9) >12 THEN (@revMonth+9)-12
ELSE (@revMonth+9) END)

--set validation code for date ranges when startmonth is revmonth +10
Update Reports
SET validation =
(CASE WHEN diff between 1 and 30 THEN 'OOOOOOOOOOKO'
WHEN diff > 30 THEN 'OOOOOOOOOOKL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+10) >12 THEN (@revMonth+10)-12
ELSE (@revMonth+10) END)

--set validation code for date ranges when startmonth is revmonth +11
Update Reports
SET validation =
(CASE WHEN diff > 0 THEN 'OOOOOOOOOOOL'
ELSE 'OOOOOOOOOOOO' END)
WHERE startMonth = (CASE WHEN (@revMonth+11) >12 THEN (@revMonth+11)-12
ELSE (@revMonth+11) END)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources