Thursday, 15 August 2013

Loop + Where statement (SQL server)

Loop + Where statement (SQL server)

I'm trying to figure out how to use a loop in a where statement in SQL. I
have this code, where I want only the file names from the last 7 days. It
works so far, I just wanted to make the code more dynamic/nicer :)
select a, b
from data1
where a in
(
'File_'+CAST(YEAR(GETDATE()-1)*10000+MONTH(GETDATE()-1)*100+DAY(GETDATE()-1)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-2)*10000+MONTH(GETDATE()-2)*100+DAY(GETDATE()-2)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-3)*10000+MONTH(GETDATE()-3)*100+DAY(GETDATE()-3)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-4)*10000+MONTH(GETDATE()-4)*100+DAY(GETDATE()-4)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-5)*10000+MONTH(GETDATE()-5)*100+DAY(GETDATE()-5)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-6)*10000+MONTH(GETDATE()-6)*100+DAY(GETDATE()-6)
AS VARCHAR)+'.TXT'
,
'File_'+CAST(YEAR(GETDATE()-7)*10000+MONTH(GETDATE()-7)*100+DAY(GETDATE()-7)
AS VARCHAR)+'.TXT'
)
order by a
The file names are called File_YYYYMMDD.txt.
I searched other places and read a little about looping, but all I could
figure out was something like this, but it doesn't work:
DECLARE @i int = 0
WHILE @i < 7 BEGIN
SET @i = @i + 1
END
select a, b
from data1
where a in
(
'File_'+CAST(YEAR(GETDATE()-@i)*10000+MONTH(GETDATE()-@i)*100+DAY(GETDATE()-@i)
AS VARCHAR)+'.TXT'
)
order by a
Thank you!

No comments:

Post a Comment