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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 loop through query and assign to variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2013 :  02:35:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 01/07/2013 :  02:39:12  Show Profile  Reply with Quote
Can you post your query which u wrote?


--
Chandu
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2013 :  02:59:03  Show Profile  Reply with Quote
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)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 01/07/2013 :  03:52:40  Show Profile  Reply with Quote
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
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2013 :  04:05:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/07/2013 :  10:32:18  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/07/2013 :  10:34:32  Show Profile  Reply with Quote
another way is retrieve them as resultset and then populate a table with values using INSERT.....EXEC

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

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.22 seconds. Powered By: Snitz Forums 2000