asked on

DATEDIFF SQL Statement Problem

I have the following SQL statement:

SELECT A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, B.STAT_NEXT_DT AS Scheduled, A.REP_DUE_DT,
A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STATUS_DESC AS Description, DATEDIFF(day, A.EXP_DT, A.REP_DUE_DT) AS AGEDATE
FROM (SELECT dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
dattbl_Case.INS_PHONE
FROM dattbl_Case INNER JOIN
dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
WHERE (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
(SELECT CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
FROM dattbl_Status
WHERE (STAT_CODE = 78 OR
STAT_CODE = 79 OR
STAT_CODE = 80 OR
STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID
ORDER BY B.STAT_NEXT_DT

I want to add an argument that returns 2 DATEDIFF values by days old.

1st argument for A.EXP_DT to A.REP_DUE_DT
2nd argument for A.EXP_DT to now

Could some one show me where to put this in my statement?
Your help is greatly appreciated.

ASKER CERTIFIED SOLUTION

justinbillig

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

justinbillig

SELECT DateDiff( "D", A.EXP_DT, A.REP_DUE_DT ) AS 'FirstDateDiff', DateDiff( "D", A.EXP_DT, GetDate( ) ) as 'SecondDateDiff', A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, B.STAT_NEXT_DT AS Scheduled, A.REP_DUE_DT,
A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STATUS_DESC AS Description, DATEDIFF(day, A.EXP_DT, A.REP_DUE_DT) AS AGEDATE
FROM (SELECT dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
dattbl_Case.INS_PHONE
FROM dattbl_Case INNER JOIN
dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
WHERE (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
(SELECT CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
FROM dattbl_Status
WHERE (STAT_CODE = 78 OR
STAT_CODE = 79 OR
STAT_CODE = 80 OR
STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID
ORDER BY B.STAT_NEXT_DT

missing comma in last post

DTRON04

ASKER

I am looking @ this now to see if i can get it to work.
i did want to add that this a vb.net app running against a Access dbase not SQL.

Forgot to mention that. Will Access allow me to use the same syntax i would normally use against SQL?

SOLUTION

Partha Mandayam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DTRON04

ASKER

this is giving me problems so i simplified by adding just one of the DateDiff arguments
Quotes were changed because that was the only way query builder would take it.

when trying to fill the dataset i get the following error:

"Provider could not determine the Int32 value"
here is my statement

SELECT DateDiff('day', A.EXP_DT, A.REP_DUE_DT) AS FirstDateDiff, A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT,
A.SENT_DT, B.STAT_NEXT_DT AS Scheduled, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STATUS_DESC AS Description
FROM (SELECT dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
dattbl_Case.INS_PHONE
FROM dattbl_Case INNER JOIN
dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
WHERE (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
(SELECT CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
FROM dattbl_Status
WHERE (STAT_CODE = 78 OR
STAT_CODE = 79 OR
STAT_CODE = 80 OR
STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID
ORDER BY B.STAT_NEXT_DT

Partha Mandayam

if you create this statement as a query in access and execute it, does it work properly?

DTRON04

ASKER

hey it it works now
not sure why it didn't @ first, i think i had my datagrid column configured wrong and it was failing on that.

one more thing, how would i add an argument like this

if ASSIGN_DT > EXP_DT then
DATEDIFF('d', A.ASSIGN_DT, A.REP_DUE_DT) AS Age
else
DATEDIFF('d', A.EXP_DT, A.REP_DUE_DT) AS Age

Thanks again for the help.

Partha Mandayam

select age=case
when ASSIGN_DT > EXP_DT then
DATEDIFF(d, A.ASSIGN_DT, A.REP_DUE_DT)
else
DATEDIFF(d, A.EXP_DT, A.REP_DUE_DT)
end