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
 store proc beginner-question

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 = Y313

DECLARE
@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 tableA
WHERE ((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 u
FROM(
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 YrVal
FROM(
VALUES
('Y209'),
('Y309'),
('Y409'),
('Y210'),
('Y410'),
('Y111'),
('Y211'),
('Y311'),
('Y411'),
('Y112'),
('Y212'),
('Y312'),
('Y412'),
('Y113'),
('Y213'),
('Y313')
)t(u)
)r
WHERE 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
------------------------------------
Y209
Y309
Y409
Y210
Y410
Y111
Y211
Y311
Y411
Y112
Y212

[/code]

The logic in blue is the critical part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -