loop through query and assign to variables

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

SQL Server 2012 Forums

Transact-SQL (2012)

loop through query and assign to variables

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-01-07 : 02:35:27

I have 5 variables

declare @last5 as datetime
,@last4 as datetime
,@last3 as datetime
,@last2 as datetime
,@last1 as datetime

then I do a query that returns me a recordset with 5 dates
How can I set the first result as @last5 ,second as @last4 etc..

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 02:39:12

Can you post your query which u wrote?

--
Chandu

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-01-07 : 02:59:03

See below
I need to fill in all the last5 variables


 declare @last5 as datetime
 ,@last4 as datetime
 ,@last3 as datetime
 ,@last2 as datetime
 ,@last1 as datetime

declare @dt as datetime
select @dt=dbo.getdateonly(getdate())
if datepart(dw,@dt)=7 
	select @dt=dateadd(day,-1,@dt)
if datepart(dw,@dt)=1 
	select @dt=dateadd(day,-2,@dt)
;with datecte
as
(

select ROW_NUMBER()over(order by (select 1))rid,
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<=@dt
and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Sunday' and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Saturday'
)
,cte
as
(
select rid,date from datecte where date=@dt
)

select date from datecte where rid between(select rid-4 from cte)and (select rid from cte)


select @last5=min(date) from datecte where rid between(select rid-4 from cte)and (select rid from cte)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 03:52:40

To get last 5 working days from given date, use below query


declare @dt as datetime
select @dt=CAST(getdate() AS DATE)
;with Numbers as
(
	select 0 as Num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
)
select DATEADD(dd,-Num,DATEDIFF(dd,0,@dt)) as Date from Numbers where ( DATEDIFF(dd,0,@dt)-Num )%7 in (0,1,2,3,4)
ORDER BY DATE DESC

--
Chandu

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-01-07 : 04:05:18

How do I get them into the variables? - I want this as part of a stored procedure and I want to save them into variables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 10:32:18

if you want to assign values to variables just use OUTPUT parameters like


CREATE PROC GetLast5Dates
@DateVal datetime,
@last5 datetime OUTPUT,
@last4 datetime OUTPUT,
@last3 datetime OUTPUT,
@last2 datetime OUTPUT,
@last1 datetime OUTPUT
AS
;With Dates (dateval)
AS
(
SELECT DATEADD(dd,-1,@DateVal)
UNION ALL
SELECT DATEADD(dd,-1,dateval)
FROM Dates
WHERE DATEADD(dd,-1,dateval) < = DATEADD(dd,-5,@DateVal)
)

SELECT @Last5=MAX(CASE WHEN ID=1 THEN dateval END),
@Last4=MAX(CASE WHEN ID=2 THEN dateval END),
@Last3=MAX(CASE WHEN ID=3 THEN dateval END),
@Last2=MAX(CASE WHEN ID=4 THEN dateval END),
@Last1=MAX(CASE WHEN ID=5 THEN dateval END)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY dateval) AS ID,dateval 
FROM Dates
)t
GO


then execute like

DECLARE @dateval datetime,@last1 datetime,@last2 datetime,@last3 datetime,@last4 datetime,@last5 datetime
SET @dateval=GETDATE()
EXEC GetLast5Dates @dateval,@last5 OUT,@last4 OUT,@last3 OUT,@last2 OUT,@last1 OUT

SELECT @last5,@last4,@last3,@last2,@last1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 10:34:32

another way is retrieve them as resultset and then populate a table with values using INSERT.....EXEC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources