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.







DTRON04Asked:
Who is Participating?
 
justinbilligConnect With a Mentor Commented:
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
0
 
justinbilligCommented:
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

0
 
DTRON04Author Commented:
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?


 
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mcp111Connect With a Mentor Commented:
in most cases it should work the same in Access and SQL.
0
 
DTRON04Author Commented:
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
0
 
mcp111Commented:
if you create this statement as a query in access and execute it, does it work properly?
0
 
DTRON04Author Commented:
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.



 
0
 
mcp111Commented:
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
0
All Courses

From novice to tech pro — start learning today.