Need help converting asp to t-sql

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 2000 Forums

Transact-SQL (2000)

Need help converting asp to t-sql

Author

Topic

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-08 : 07:27:39

Hi

I'm not too hot with t-sql and I'm trying to write out a query.
I've been able to write out what needs to be accomplished in asp and
I'm busy writing it in t-sql.

asp code:
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'work out following start date and completion date and if task is overdue
'get original start date
setDate = "2007/1/5"
'get amount of days to complete task
compDate = DATEADD("d", 3, setDate)

'display original start and complete date, if current date past 'original date then get following start date etc
If DateDiff("d", setDate, Now) < 7 Then
Response.Write("Start date: " & Day(setDate) & "/" & Month(Now()) & "/" & Year(Now()) & " ")
Response.Write("Due Date: " & Day(compDate) & "/" & Month(Now()) & "/" & Year(Now()) & " ")
Else
'loop through the start dates
i = 0
For y=0 to 3
i = i + 7

'add days to start date
theDate = DateAdd("d", i, setDate)
compDate = DATEADD("d", 3, theDate)

'this gives us the following start date of the task
If theDate >= Now() Then
If DateDiff("d", Now(), theDate) < 7 Then
Response.Write("Start date: " & Day(theDate) & "/" & Month(theDate) & "/" & Year(theDate) & " ")
Response.Write("Due Date: " & Day(compDate) & "/" & Month(theDate) & "/" & Year(theDate) & " ")
End If
End If
Next
End If
'loop through the start dates END
'work out following start date and completion date and if task is overdue END
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

my sql query:
USE aoms
DECLARE @setDate datetime
DECLARE @compDate int
DECLARE @setCompDate datetime
DECLARE @i int
DECLARE @y int

SET @setDate = (SELECT DISTINCT recurring FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

SET @compDate = (SELECT DISTINCT compDays FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

SET @setCompDate = DATEADD(d, @compDate, @setDate)

SET @i = 0
SET @y = 0

If DATEDIFF(d, @setDate, GetDate()) < 7
BEGIN
PRINT 'Start date: ' + CAST(@setDate AS varchar(20))
PRINT 'Due Date: ' + CAST(@setCompDate AS varchar(20))
END

The problem i'm facing now is how to write the FOR LOOP in t-sql?

Thanks
Shem

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 07:41:00

There is no FOR loop in T-SQL, but you can use WHILE loop to accomplish what you want. The syntax of WHILE is:

WHILE Boolean_expression 
    { sql_statement | statement_block } 
    [ BREAK ] 
    { sql_statement | statement_block } 
    [ CONTINUE ]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-08 : 07:51:06

Yes I've seen the WHILE loops

but can it accomplish the same thing?
What I need to do is add a number of days to a date
and loop through that for as many times as there are days
within the current month.

so say I have 5 days to complete a job, and the start date is Day(5)/Month(1)/2007.

in this case it can only loop 5 times.

If someone could help me write this or point me in the right
direction on how to go about writing that, I would be very gratefull.

Thanks
Shem

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 08:04:07

[code]While Datediff(d, @StartDt, CompDt)>0
Begin
... -- Do processing
Set @StartDt = Dateadd(d, 1, @StartDt)
End[/code]

But if you post the complete code here, somebody may come out with better option than using WHILE loop.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-08 : 08:26:17

Tried what you suggested, but nothing comes up?

my code so far:
USE aoms
DECLARE @setDate datetime
DECLARE @compDate int
DECLARE @setCompDate datetime
DECLARE @i int
DECLARE @y int
DECLARE @StartDt datetime

/* Get the start date of the task */
SET @setDate = (SELECT DISTINCT recurring FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

/* Get the amount of days to complete the task */
SET @compDate = (SELECT DISTINCT compDays FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

/* Get the due date for the task */
SET @setCompDate = DATEADD(d, @compDate, @setDate)

SET @i = 0
SET @y = 0

If DATEDIFF(d, @setDate, GetDate()) < 7
BEGIN
PRINT 'Start date: ' + CAST(@setDate AS varchar(20))
PRINT 'Due Date: ' + CAST(@setCompDate AS varchar(20))
END

WHILE Datediff(d, @setDate, @setCompDate)>0
BEGIN
SET @StartDt = Dateadd(d, @compDate, @StartDt)
PRINT 'Next Start Date: ' + CAST(@StartDt AS varchar(20))
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 08:40:34

That's because you mixed your code with my suggestion without understanding it.

WHILE Datediff(d, @setDate, @setCompDate)>0
BEGIN
SET @setDate= Dateadd(d, @compDate, @setDate)
PRINT 'Next Start Date: ' + CAST(@setDate AS varchar(20))
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-08 : 08:46:48

Thats exactly what I did :(

Thanks for your help and patience with me, I have learned quite a bit today that 2days ago didn't even know I could do:)

Thanks again
Shem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources