Recordset problem and comparison

woow
04-15-2004, 01:26 PM
I have a recordset I have shown the SQL string below

SELECT * FROM dbo_tistiact INNER JOIN dbo_tistilnk ON (dbo_tistiact.act_no = dbo_tistilnk.pred_act_no) AND (dbo_tistiact.order_no = dbo_tistilnk.order_no) WHERE (((dbo_tistilnk.succ_act_no)='" + act + "')) AND ((dbo_tistiact.actual_finish) = NULL))

This has details about promised_end_date and actual_end_date

I want to compare these 2 columns to find out whether a task is done on time or not and if delayed from how many days.

Also i need to output the Promised_date, Actual finished date

date format is mm/dd/yyyy and data type is date

How can i do these how can i compare these 2 items.

Thanks in advance.

is there anyway that we can get the details in to 2 recordset and do this. If so how can i compare items and calculate certain things.

Regards

HardCode
04-15-2004, 02:15 PM
What database are you using?

woow
04-15-2004, 02:20 PM
What database are you using?

Right now Access eventually Oracle. Thankas hardCode for all the previouse helps also :D

HardCode
04-15-2004, 02:38 PM
Well, Access is going to be different from Oracle, but for now, you can learn how to do it and then learn to convert later :)

You can use the IIF statement. You will create a field that will return 0 or 1 (or, Access' YES or NO but I hate using them). Basically, you will take the promised_end_date field and check it against actual_end_date. If actual is greater than promised then make it a 1. Otherwise make it a zero. All records with a 1 in this field were late.

IIF works like this:

IIF(<condition>,<this value if true>,<this value if false>) As Expr1


'

SELECT *, IIF(actual_end_date > promised_end_date,1,0) AS LateOrNot
FROM dbo_tistiact INNER JOIN dbo_tistilnk ON (dbo_tistiact.act_no = dbo_tistilnk.pred_act_no) AND (dbo_tistiact.order_no = dbo_tistilnk.order_no)
WHERE (((dbo_tistilnk.succ_act_no)='" + act + "')) AND ((dbo_tistiact.actual_finish) = NULL))

'

woow
04-15-2004, 03:16 PM
HardCode thanks for the help.

I got the idea now. So What does LaterorNot do actually. Does the Date difference data get passed to LaterorNot. if so can we use that as another Recordset. I am confused in that

Can't we just say

SELECT *, IIF(actual_finish > target_finish,1,0) From ,...............


Please let me know

HardCode
04-15-2004, 05:04 PM
LateOrNot is the alias name for the field. If you were going through a recordset, you would use:

If RS.Fields("LateOrNot").Value = 1 then
' Code to do if late
else
' Code if not late
end if


If you don't use the "As LateOrNot", Access should name the field "Expr1" for you, which is not very descriptive.

MKoslof
04-16-2004, 06:09 AM
Well, if you truly want to learn the syntax for something like this that is CROSS-PLATFORM based, you should drop the IIF. This is not avaiable in Oracle or SQL Server. The most powerful tool in query building is the CASE statement. You want to review this, instead of playing with a function that is only available in Access

woow
04-16-2004, 08:28 AM
Well, if you truly want to learn the syntax for something like this that is CROSS-PLATFORM based, you should drop the IIF. This is not avaiable in Oracle or SQL Server. The most powerful tool in query building is the CASE statement. You want to review this, instead of playing with a function that is only available in Access

Hey MKoslof,

Could please give me some references regarding this. I would greatly appreciate it.

Regards.

woow
04-16-2004, 10:02 AM
Can someone help me with this I am going to use Oracle Database. Can someone help me how to do the quary. I am really lost how about to do this. I am new to whole SQL world.

Thanks

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum