?
Solved

DATEDIFF SQL Statement Problem

Posted on 2004-09-29
9
Medium Priority
?
431 Views
Last Modified: 2012-05-05
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.







0
Comment
Question by:DTRON04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
9 Comments
 
LVL 15

Accepted Solution

by:
justinbillig earned 1600 total points
ID: 12183092
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
 
LVL 15

Expert Comment

by:justinbillig
ID: 12183100
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
 

Author Comment

by:DTRON04
ID: 12183283
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Assisted Solution

by:mcp111
mcp111 earned 400 total points
ID: 12183503
in most cases it should work the same in Access and SQL.
0
 

Author Comment

by:DTRON04
ID: 12183769
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
 
LVL 6

Expert Comment

by:mcp111
ID: 12185032
if you create this statement as a query in access and execute it, does it work properly?
0
 

Author Comment

by:DTRON04
ID: 12190168
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
 
LVL 6

Expert Comment

by:mcp111
ID: 12436111
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question