DateDiff Value in a Query

[jjz0512]

I'm storing into 2 expressions date+time for Issued D/T and Order D/T and using DateDIff to calculate the minutes different. I get all the values, but only need to see the records that are less than 721 minutes apart, or 12 hours. Here's what I have;

1 column is: Issued D/T: Format(([Issued Date]+[ITXM BLOOD Data_Time]),"mm/dd/yy hh:nn")
1 column is: Order D/T: Format(([CXR Order Date]+[HIPD XRAY Data_Time]),"mm/dd/yy hh:nn")

and using another column as: Time Diff: DateDiff("n",[Issued D/T],[CXR Order D/T])

I see Issued D/T of 04/19/15 08:53, Order D/T of 04/19/15 16:47 and Time Diff of 474 which is one of the records i want to see. But, I also get, Issued D/T of 04/18/15 14:12, Order D/T of 04/19/15 14:35 and Time Diff of 1463. I don't want to see this record. How can I exclude this in a query? I don't want any records greater than 721.

Any help would be appreciated!

Thanks,
Jay

[June7]

Format function results in a string value. Why use it in query? Is the DateDiff calculation correct? Post the complete SQL statement.

[jjz0512]

The format function was used to structure the date from 04/19/2015 16:47:00 to 04/19/15 16:47. But the less than wasn't working anyway. Everytime I used, < 721, for the criteria, it was asking for a Issued D/T and Order D/T as a parameter.

[June7]

I repeat: Is the DateDiff calc correct and provide the complete SQL statement.

[jjz0512]

Sorry, Yes, i beleive the DateDiff is correct. Heres the SQL statement. The first 7 elements are needed for reporting, then there's the DateDiff, followed by the 4 elements that make up the DataDiff expression.

SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation, [ITXM BLOOD Data].Text AS [Product Name], [ITXM BLOOD Data].Unit, [ITXM BLOOD Data].Product, Format(([Issued Date]+[ITXM BLOOD Data_Time]),"mm/dd/yy hh:nn") AS [Issued D/T], Format(([CXR Order Date]+[HIPD XRAY Data_Time]),"mm/dd/yy hh:nn") AS [CXR Order D/T], DateDiff("n",[Issued D/T],[CXR Order D/T]) AS [Time Diff], [ITXM BLOOD Data].Date AS [Issued Date], [ITXM BLOOD Data].Time AS [ITXM BLOOD Data_Time], [HIPD XRAY Data].Date AS [CXR Order Date], [HIPD XRAY Data].Time AS [HIPD XRAY Data_Time]
FROM [ITXM BLOOD Data] INNER JOIN [HIPD XRAY Data] ON [ITXM BLOOD Data].[MRN] = [HIPD XRAY Data].[MRN]
WHERE ((([ITXM BLOOD Data].Date)<=[HIPD XRAY Data].[Date]) AND (([ITXM BLOOD Data].Time)<[HIPD XRAY Data].[TIme]) AND (([HIPD XRAY Data].Date)=#4/19/2015#))
ORDER BY [ITXM BLOOD Data].MRN;

[June7]

So the <721 criteria would be under the constructed [Time Diff] field? AFAIK, that should work - assuming the DateDiff is working and returning number values.

Should not use reserved words as field names - Date, Text, Time are reserved words. Also, recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

[ItsMe]

Perhaps, the fields are not of type Date. As mentioned, formatting produces text. If you are comparing an alias that is text, you will not be able to use DateDiff function.

[jjz0512]

I simplfied my SQL statement. But, i'm still having the same problem. Date & Time are a Date/Time data types. So, BBOrderDT would be 4/19/2015 8:53:00 AM, CXROrderDT would be 4/19/2015 4:47:00 PM which gives a result of 474 for Difference. When ever I enter a value in the criteria for Difference I receive a prompt for BBOrderDT and CXROrderDT.


SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation, [ITXM BLOOD Data].Text AS ProductName, [ITXM BLOOD Data].Unit, [ITXM BLOOD Data].Product, [BBOrderDate]+[BBOrderTime] AS BBOrderDT, [CXROrderDate]+[CXROrderTime] AS CXROrderDT, DateDiff("n",[BBOrderDT],[CXROrderDT]) AS Difference, [ITXM BLOOD Data].OrderDate AS BBOrderDate, [ITXM BLOOD Data].OrderTime AS BBOrderTime, [HIPD XRAY Data].OrderDate AS CXROrderDate, [HIPD XRAY Data].OrderTime AS CXROrderTime
FROM [ITXM BLOOD Data] INNER JOIN [HIPD XRAY Data] ON [ITXM BLOOD Data].[MRN] = [HIPD XRAY Data].[MRN]
WHERE ((([ITXM BLOOD Data].OrderDate)<=[HIPD XRAY Data].[OrderDate]) AND (([ITXM BLOOD Data].OrderTime)<[HIPD XRAY Data].[OrderTime]) AND (([HIPD XRAY Data].OrderDate)=#4/19/2015#))
ORDER BY [ITXM BLOOD Data].MRN;

[June7]

WHERE clause doesn't recognize the constructed date fields. Maybe because the DateDiff is referencing alias field names. Might have to repeat the DateDiff calc with the native fields or at least the first tier alias.

WHERE DateDiff("n", [BBOrderDate]+[BBOrderTime], [CXROrderDate]+[CXROrderTime]) < 721

or

WHERE DateDiff("n", [ITXM BLOOD Data].OrderDate + [ITXM BLOOD Data].OrderTime, [HIPD XRAY Data].OrderDate + [HIPD XRAY Data].OrderTime) < 721

or

SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation, [ITXM BLOOD Data].Text AS ProductName, [ITXM BLOOD Data].Unit, [ITXM BLOOD Data].Product,
[BBOrderDate]+[BBOrderTime] AS BBOrderDT, [CXROrderDate]+[CXROrderTime] AS CXROrderDT,

DateDiff("n", [ITXM BLOOD Data].OrderDate + [ITXM BLOOD Data].OrderTime, [HIPD XRAY Data].OrderDate + [HIPD XRAY Data].OrderTime) AS Difference,

[ITXM BLOOD Data].OrderDate AS BBOrderDate, [ITXM BLOOD Data].OrderTime AS BBOrderTime, [HIPD XRAY Data].OrderDate AS CXROrderDate, [HIPD XRAY Data].OrderTime AS CXROrderTime

FROM [ITXM BLOOD Data] INNER JOIN [HIPD XRAY Data] ON [ITXM BLOOD Data].[MRN] = [HIPD XRAY Data].[MRN]

WHERE [ITXM BLOOD Data].OrderDate<=[HIPD XRAY Data].[OrderDate] AND [ITXM BLOOD Data].OrderTime<[HIPD XRAY Data].[OrderTime] AND [HIPD XRAY Data].OrderDate=#4/19/2015# AND Difference < 721

ORDER BY [ITXM BLOOD Data].MRN;

[jjz0512]

The later, DateDiff("n", [ITXM BLOOD Data].OrderDate + [ITXM BLOOD Data].OrderTime, [HIPD XRAY Data].OrderDate + [HIPD XRAY Data].OrderTime) AS Difference, worked fine! It must have been the alias' that was causing the problem.

Thanks everyone for your help!!!
Jay

[jjz0512]

June7,
Thanks again for solving my alias problem. But now I have another, when I do the comparison between dates, I'm look for any data that has a BBOrderDate >= 04/18/2015 12:00 AM with a CXROrderDT <= 04/19/2015 12:59 PM, that also has a diference of 721 minutes. how do I combine this in my critera? Here's my new Sql;

SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation AS [Sub Location], [ITXM BLOOD Data].Text AS [Product Name], [ITXM BLOOD Data].Unit AS [Unit #], [ITXM BLOOD Data].Product AS [Product #], Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn") AS [Issued D/T], Format([HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime],"mm/dd/yy hh:nn") AS [CXR Order D/T], [ITXM BLOOD Data].OrderDate, [HIPD XRAY Data].OrderDate, DateDiff("n",[ITXM BLOOD Data].[OrderDate]+[ITXM BLOOD Data].[OrderTime],[HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime]) AS Difference
FROM [ITXM BLOOD Data] LEFT JOIN [HIPD XRAY Data] ON [ITXM BLOOD Data].[MRN] = [HIPD XRAY Data].[MRN]
WHERE ((([ITXM BLOOD Data].OrderDate)<=[HIPD XRAY Data].OrderDate) And (([ITXM BLOOD Data].OrderTime)<=[HIPD XRAY Data].OrderTime) And (([ITXM BLOOD Data].OrderDate)>=[Start Date?]) And (([HIPD XRAY Data].OrderDate)<=[Report Date?]) And ((DateDiff("n",[ITXM BLOOD Data].OrderDate+[ITXM BLOOD Data].OrderTime,[HIPD XRAY Data].OrderDate+[HIPD XRAY Data].OrderTime))<721))
ORDER BY [ITXM BLOOD Data].MRN, Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn"), Format([HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime],"mm/dd/yy hh:nn");

I should have the first 3 records in my result

ITXM_H1PD
MRN	Sub Location	Product Name	Unit #	Product #	Issued D/T	CXR Order D/T	ITXM BLOOD Data.OrderDate	HIPD XRAY Data.OrderDate	Difference
043	6F	Red Blood Cells (Crossmatch RBC)	W086315	E0262	04/18/15 09:51	04/18/15 12:00	4/18/2015	4/18/2015	129
043	6F	Red Blood Cells (Crossmatch RBC)	W085915	E0366	04/18/15 09:51	04/18/15 12:00	4/18/2015	4/18/2015	129
043	6F	Red Blood Cells (Crossmatch RBC)	W085815	E0283	04/18/15 09:51	04/18/15 12:00	4/18/2015	4/18/2015	129
986	12N	Red Blood Cells (Crossmatch RBC)	W087115	E0382	04/19/15 08:53	04/19/15 16:47	4/19/2015	4/19/2015	474
986	12N	Red Blood Cells (Crossmatch RBC)	W085515	E0382	04/19/15 08:53	04/19/15 16:47	4/19/2015	4/19/2015	474
722	11N	Red Blood Cells (Crossmatch RBC)	W085515	E0382	04/18/15 17:39	04/18/15 22:53	4/18/2015	4/18/2015	314
722	11N	Red Blood Cells (Crossmatch RBC)	W084615	E0382	04/18/15 17:39	04/18/15 22:53	4/18/2015	4/18/2015	314
722	11N	Red Blood Cells (Crossmatch RBC)	W085515	E0382	04/18/15 17:39	04/18/15 23:48	4/18/2015	4/18/2015	369

[June7]

Not seeing attempt to include that date range criteria in the query. Apply date/time parameter to combined date/time data.

[jjz0512]

Sorry, here. But how do I combine the date and time for the Issued D/T column? That's where I need to get the records that have an Issued D/T of 04/18/15 12:00 and 04/19/15 23:59 against CXR Order D/T for 04/19/15 00:01 and 04/19/15 23:59?

SELECT [ITXM BLOOD Data].MRN, [ITXM BLOOD Data].SubLocation AS [Sub Location], [ITXM BLOOD Data].Text AS [Product Name], [ITXM BLOOD Data].Unit AS [Unit #], [ITXM BLOOD Data].Product AS [Product #], Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn") AS [Issued D/T], Format([HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime],"mm/dd/yy hh:nn") AS [CXR Order D/T], [ITXM BLOOD Data].OrderTime, DateDiff("n",[ITXM BLOOD Data].[OrderDate]+[ITXM BLOOD Data].[OrderTime],[HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime]) AS Difference

FROM [ITXM BLOOD Data] LEFT JOIN [HIPD XRAY Data] ON [ITXM BLOOD Data].[MRN] = [HIPD XRAY Data].[MRN]

WHERE ((([ITXM BLOOD Data].OrderDate)<=[HIPD XRAY Data].[OrderDate]) AND (([ITXM BLOOD Data].OrderTime)<=[HIPD XRAY Data].[OrderTime]) AND (([ITXM BLOOD Data].OrderDate)>=#4/18/2015#) AND (([ITXM BLOOD Data].OrderTime)>=#12/30/1899 12:0:0#) AND (([HIPD XRAY Data].OrderDate)<=#4/19/2015#) AND ((DateDiff("n",[ITXM BLOOD Data].[OrderDate]+[ITXM BLOOD Data].[OrderTime],[HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime]))<721))
ORDER BY [ITXM BLOOD Data].MRN, Format([ITXM Blood Data].[OrderDate]+[ITXM Blood Data].[OrderTime],"mm/dd/yy hh:nn"), Format([HIPD XRAY Data].[OrderDate]+[HIPD XRAY Data].[OrderTime],"mm/dd/yy hh:nn");

[June7]

Your query already combines the date and time fields by simply adding them. Just put the combined date/time parameter under those constructed fields.

Although, I would not use Format function in the query and instead do formatting on report.