Difference between dates

  • Thread starter Thread starter FJB
  • Start date Start date
F

FJB

I have two queries that I need help with which deal with differences
between dates. First, the difference between the value in a field and
the system date resulting in a number of days difference sorted
descending. Second, the difference between the value of two fields
resulting in a number of days. Below are the SQL views of the current
queries which need to be modified. Thanks for your help

Frank

Query 1

SELECT dbo_TBLCASE.CASEID, dbo_TBLCASE.OPENDATE,
dbo_TBLCASE.INVESTIGATORNAME, dbo_TBLCASE.CASECLOSINGNOTES
FROM dbo_LTBSARCHARACTERIZATION1, (dbo_TBLCASE INNER JOIN
dbo_LTBCATEGORY1 ON dbo_TBLCASE.CATEGORYID=dbo_LTBCATEGORY1.CATEGORYID)
INNER JOIN dbo_ITBINVESTIGATOR1 ON
dbo_TBLCASE.INVESTIGATORID=dbo_ITBINVESTIGATOR1.INVESTIGATORID
WHERE (((dbo_TBLCASE.STATUS)="OPN") AND
((dbo_LTBSARCHARACTERIZATION1.DESCRIPTION)="Bank Secrecy
Act/Structuring/Money Laundering") AND
((dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32453 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32454 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32455 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32431));


Query 2

SELECT [SAR Filings].[AMLOC Decision Date], [SAR Filings].[AMLOC
Decision], [SAR Filings].[AMLOC?], [SAR Filings].[Early AMLOC], [SAR
Filings].[Relationship Name], [SAR Filings].[Client Number], [SAR
Filings].[ICMS Case Number], [SAR Filings].[FIU Investigator], [SAR
Filings].[SSN/TIN], [SAR Filings].[Account Number], [SAR Filings].[To
Close By], [SAR Filings].[Number of SARs filed], [SAR Filings].LOB,
[SAR Filings].[Other LOB Relationships], [SAR Filings].[Reporting
Region], [SAR Filings].[SAR Filed Date], [SAR Filings].[Previous SAR
Date 1], [SAR Filings].[Previous SAR Date 2], [SAR Filings].[Case
Category], [SAR Filings].[Case Category 2], [SAR Filings].Rank, [SAR
Filings].[Risk Level], [SAR Filings].[Risk Category], [SAR
Filings].Occupation, [SAR Filings].Employer, [SAR Filings].Comments,
[SAR Filings].[LE Letter], [SAR Filings].Employee, Group_Region.Group,
Group_Region.[Region Name], [SAR Filings].[Cost Center], [SAR
Filings].[Current Relationship Status], [STI Branch Listing-OLD].NAME,
[STI Branch Listing-OLD].CITY, [STI Branch Listing-OLD].STATE, [SAR
Filings].[Prior SAR Category #1], [SAR Filings].[Prior SAR Category
#2], [SAR Filings].[Current Relationship Status], [SAR
Filings].Description, [SAR Filings].[Date Account Opened]
FROM (Group_Region INNER JOIN [SAR Filings] ON Group_Region.[Reporting
Region] = [SAR Filings].[Reporting Region]) INNER JOIN [STI Branch
Listing-OLD] ON [SAR Filings].[Cost Center] = [STI Branch
Listing-OLD].CC
WHERE ((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[AMLOC?])=Yes) AND (([SAR Filings].[To Close By]) Is Null)) OR
((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[Early AMLOC])=Yes))
ORDER BY [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[Relationship Name];
 
You can use the DateDiff function to calculate differences. You didn't specify
which fields you wanted to do this on or what you wanted to calculate - hours,
days, weeks, seconds, years??

DateDiff("d",[OpenDate],Date()) as DaysBetween

DateDiff("d",[SAR Filed Date],[AMLOC Decision Date]) as DaysBetween

I have two queries that I need help with which deal with differences
between dates. First, the difference between the value in a field and
the system date resulting in a number of days difference sorted
descending. Second, the difference between the value of two fields
resulting in a number of days. Below are the SQL views of the current
queries which need to be modified. Thanks for your help

Frank

Query 1

SELECT dbo_TBLCASE.CASEID, dbo_TBLCASE.OPENDATE,
dbo_TBLCASE.INVESTIGATORNAME, dbo_TBLCASE.CASECLOSINGNOTES
FROM dbo_LTBSARCHARACTERIZATION1, (dbo_TBLCASE INNER JOIN
dbo_LTBCATEGORY1 ON dbo_TBLCASE.CATEGORYID=dbo_LTBCATEGORY1.CATEGORYID)
INNER JOIN dbo_ITBINVESTIGATOR1 ON
dbo_TBLCASE.INVESTIGATORID=dbo_ITBINVESTIGATOR1.INVESTIGATORID
WHERE (((dbo_TBLCASE.STATUS)="OPN") AND
((dbo_LTBSARCHARACTERIZATION1.DESCRIPTION)="Bank Secrecy
Act/Structuring/Money Laundering") AND
((dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32453 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32454 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32455 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32431));

Query 2

SELECT [SAR Filings].[AMLOC Decision Date], [SAR Filings].[AMLOC
Decision], [SAR Filings].[AMLOC?], [SAR Filings].[Early AMLOC], [SAR
Filings].[Relationship Name], [SAR Filings].[Client Number], [SAR
Filings].[ICMS Case Number], [SAR Filings].[FIU Investigator], [SAR
Filings].[SSN/TIN], [SAR Filings].[Account Number], [SAR Filings].[To
Close By], [SAR Filings].[Number of SARs filed], [SAR Filings].LOB,
[SAR Filings].[Other LOB Relationships], [SAR Filings].[Reporting
Region], [SAR Filings].[SAR Filed Date], [SAR Filings].[Previous SAR
Date 1], [SAR Filings].[Previous SAR Date 2], [SAR Filings].[Case
Category], [SAR Filings].[Case Category 2], [SAR Filings].Rank, [SAR
Filings].[Risk Level], [SAR Filings].[Risk Category], [SAR
Filings].Occupation, [SAR Filings].Employer, [SAR Filings].Comments,
[SAR Filings].[LE Letter], [SAR Filings].Employee, Group_Region.Group,
Group_Region.[Region Name], [SAR Filings].[Cost Center], [SAR
Filings].[Current Relationship Status], [STI Branch Listing-OLD].NAME,
[STI Branch Listing-OLD].CITY, [STI Branch Listing-OLD].STATE, [SAR
Filings].[Prior SAR Category #1], [SAR Filings].[Prior SAR Category
#2], [SAR Filings].[Current Relationship Status], [SAR
Filings].Description, [SAR Filings].[Date Account Opened]
FROM (Group_Region INNER JOIN [SAR Filings] ON Group_Region.[Reporting
Region] = [SAR Filings].[Reporting Region]) INNER JOIN [STI Branch
Listing-OLD] ON [SAR Filings].[Cost Center] = [STI Branch
Listing-OLD].CC
WHERE ((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[AMLOC?])=Yes) AND (([SAR Filings].[To Close By]) Is Null)) OR
((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[Early AMLOC])=Yes))
ORDER BY [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[Relationship Name];
Click to expand...
 
Thanks for your help. I cannot seem to get this in the right place to
work.I tried adding it to the SQL (above) with no luck. I also tried
the Design View. Help!!

Thanks
 
This should work for you assuming that OPENDATE is a datetime field. It will
probably generate an error is OpenDate is Null. But you could use the following
expression to return zero in the coase where OpenDate is null.


DateDiff("d",NZ([dbo_TBLCASE].[OPENDATE],Date()),Date()) as DaysBetween


SELECT DateDiff("d",[dbo_TBLCASE].[OPENDATE],Date()) as DaysBetween,
dbo_TBLCASE.CASEID, dbo_TBLCASE.OPENDATE,
dbo_TBLCASE.INVESTIGATORNAME, dbo_TBLCASE.CASECLOSINGNOTES
FROM dbo_LTBSARCHARACTERIZATION1, (dbo_TBLCASE INNER JOIN
dbo_LTBCATEGORY1 ON dbo_TBLCASE.CATEGORYID=dbo_LTBCATEGORY1.CATEGORYID)
INNER JOIN dbo_ITBINVESTIGATOR1 ON
dbo_TBLCASE.INVESTIGATORID=dbo_ITBINVESTIGATOR1.INVESTIGATORID
WHERE (((dbo_TBLCASE.STATUS)="OPN") AND
((dbo_LTBSARCHARACTERIZATION1.DESCRIPTION)=
"Bank Secrecy Act/Structuring/Money Laundering") AND
((dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32453 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32454 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32455 Or
(dbo_ITBINVESTIGATOR1.REGIONALMANAGERID)=32431));
 
Many thanks. I used you suggestion and it works like a charm. But i
cannot get it to work in the second scenario. Below is the SQL after I
added the DateDiff. When I run the query, it asks for a value for "d".

SELECT DateDiff("d", [SAR Filed Date], [Previous SAR Date
1],Date()) as DaysBetween, [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[AMLOC Decision], [SAR Filings].[AMLOC?], [SAR Filings].[Early
AMLOC], [SAR Filings].[Relationship Name], [SAR Filings].[Client
Number], [SAR Filings].[ICMS Case Number], [SAR Filings].[FIU
Investigator], [SAR Filings].[SSN/TIN], [SAR Filings].[Account Number],
[SAR Filings].[To Close By], [SAR Filings].[Number of SARs filed], [SAR
Filings].LOB, [SAR Filings].[Other LOB Relationships], [SAR
Filings].[Reporting Region], [SAR Filings].[SAR Filed Date], [SAR
Filings].[Previous SAR Date 1], [SAR Filings].[Previous SAR Date 2],
[SAR Filings].[Case Category], [SAR Filings].[Case Category 2], [SAR
Filings].Rank, [SAR Filings].[Risk Level], [SAR Filings].[Risk
Category], [SAR Filings].Occupation, [SAR Filings].Employer, [SAR
Filings].Comments, [SAR Filings].[LE Letter], [SAR Filings].Employee,
Group_Region.Group, Group_Region.[Region Name], [SAR Filings].[Cost
Center], [SAR Filings].[Current Relationship Status], [STI Branch
Listing-OLD].NAME, [STI Branch Listing-OLD].CITY, [STI Branch
Listing-OLD].STATE, [SAR Filings].[Prior SAR Category #1], [SAR
Filings].[Prior SAR Category #2], [SAR Filings].[Current Relationship
Status], [SAR Filings].Description, [SAR Filings].[Date Account Opened]
FROM (Group_Region INNER JOIN [SAR Filings] ON Group_Region.[Reporting
Region] = [SAR Filings].[Reporting Region]) INNER JOIN [STI Branch
Listing-OLD] ON [SAR Filings].[Cost Center] = [STI Branch
Listing-OLD].CC
WHERE ((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[AMLOC?])=Yes) AND (([SAR Filings].[To Close By]) Is Null)) OR
((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[Early AMLOC])=Yes))
ORDER BY [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[Relationship Name];
 
If it asks for "D" then I suspect you have a typing error in the statement.
Perhaps D without the quotes or D surrounded by []? Your formula for
DateDiff is incorrect. Assuming that both Sar Filed Date and Previous SAR
Date 1 will always have a value, then you need

DateDiff("d", [SAR Filed Date], [Previous SAR Date 1]) as DaysBetween,
 
Back
Top