Solved

DATEDIFF SQL Statement Problem

Posted on 2004-09-29
9
424 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
9 Comments
 
LVL 15

Accepted Solution

by:
justinbillig earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 6

Assisted Solution

by:mcp111
mcp111 earned 100 total points
Comment Utility
in most cases it should work the same in Access and SQL.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:DTRON04
Comment Utility
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
Comment Utility
if you create this statement as a query in access and execute it, does it work properly?
0
 

Author Comment

by:DTRON04
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now