SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 store proc beginner-question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theKid27
Starting Member

20 Posts

Posted - 05/22/2013 :  04:36:37  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/22/2013 :  04:55:11  Show Profile  Reply with Quote

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



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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000