Author |
Topic |
theKid27
Starting Member
21 Posts |
Posted - 2013-05-22 : 04:36:37
|
Hi experts/members, I'm have a question about store procedure; where i would like to move all the data if the data is > 3 years. from the table name tableA[TableA]Field name[Qtr]- Y209 Y309 Y409 Y210 Y410 Y111 Y211 Y311 Y411 Y112 Y212 Y312 Y412 Y113 Y213 Y313 The meaning for Y110 is [1]= Quarter [10] = year (2010)As below is my stored procedure.Eg. curQtr = Y313DECLARE @subCurrYear NCHAR(5), @subCurrQtr NCHAR(5), @curQtr NCHAR(5)SELECT @subCurrYear = SUBSTRING (@curQtr, CHARINDEX('Y',@curQtr)+2,2) SELECT @subCurrQtr = SUBSTRING (@curQtr, CHARINDEX ('Y',@curQtr)+1,1) SELECT * FROM tableAWHERE ((SELECT [Quarter] = SUBSTRING ([Quarter], CHARINDEX ('Y',[Quarter])+2,2) )< @subCurrYear) AND ((SELECT [Quarter] = SUBSTRING ([Quarter], CHARINDEX ('Y',[Quarter])+1,1) )< @subCurrQtr)From the stored procedure above i can only able to get Y111 Y112 Y209 Y210 Y211 Y212 Which is incorrect by right i should get the below as i need >3 years data. Y209 Y309 Y409 Y210 Y410 Y111 Y211 Y311 Y411 Y112 Y212 Y312 Y412 Kindly hope that anyone can give me some advices or ideas on this. Appreciate for the help. Best regards, thekid |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-22 : 04:55:11
|
[code]DECLARE @Per varchar(4)='Y312'SELECT uFROM(SELECT u,DATEADD(qq,SUBSTRING(u,2,1)-1,DATEADD(yy,CASE WHEN 50 - RIGHT(u,2) >0 THEN 100 ELSE 0 END + RIGHT(u,2),0)) AS YrValFROM(VALUES ('Y209'),('Y309'),('Y409'),('Y210'),('Y410'),('Y111'),('Y211'),('Y311'),('Y411'),('Y112'),('Y212'),('Y312'),('Y412'),('Y113'),('Y213'),('Y313'))t(u))rWHERE YrVal < DATEADD(qq,SUBSTRING(@Per,2,1)-1,DATEADD(yy,CASE WHEN 50 - RIGHT(@Per,2) >0 THEN 100 ELSE 0 END + RIGHT(@Per,2),0))output------------------------------------Y209Y309Y409Y210Y410Y111Y211Y311Y411Y112Y212[/code]The logic in blue is the critical part------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|