[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DATEDIFF SQL Statement Problem

Posted on 2004-09-29
9
Medium Priority
?
436 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
  • 3
  • 3
  • 2
8 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 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