August 7, 2012 at 7:02 am
Hi All,
I've a question regarding a problem that Iām facing. I need to get the orders that were placed between dbo.BVActions.orderedTime BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE() -1,101))+'02:00:00 AM') and have the schedule time as ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE()-1,101)+'07:00:00 AM') i.e; for yesterday, but , if the order date is placed
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'00:00:00 AM') Then the scheduled order has to move to tomorrow. So Then I have to do the 2nd Sql Select in the Union query.
I want to create a temptable and insert the values into that , so that I can split the query accordingto the values and then use a use case or If then else statement as it gives me a both the values for the 2 different dates , where I need only one value. Can anybody let me know how do I go about with CASE or If then else, create a temp table and insert values into it.
Below is the sql query that I was using.
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE()-1,101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE() -1,101))+'02:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'00:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
Thank you,
Su
August 7, 2012 at 7:58 am
what are the are datatypes of dbo.BVActions.orderedTime and dbo.BVActions.ScheduledTime
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 7, 2012 at 8:12 am
If I understand, you are getting 2 dates for some patients, & you only want one.
You want to choose which to display when there are 2.
You can join the 2 result sets & keep whichever single one appears, or choose where there are 2 - this always keeps A.
If you want something else let me know & I'll look into it.
The basic layout would be like this:
SELECT ISNULL(A.Patients_Name, B.Patients_Name) as Patients_Name,
ISNULL(A.MR#, B.MR#) as MR#,
ISNULL(A.Required_Lab_Test, B.Required_Lab_Test) as Required_Lab_Test,
ISNULL(A.Lab_Time, B.Lab_Time) as Lab_Time,
ISNULL(A.Location_Name, B.Location_Name) as Location_Name
FROM
(
Query1 A
)
full outer join
(
Query2 B
)
ON A.key = B.key
SELECT ISNULL(A.Patients_Name, B.Patients_Name) as Patients_Name,
ISNULL(A.MR#, B.MR#) as MR#,
ISNULL(A.Required_Lab_Test, B.Required_Lab_Test) as Required_Lab_Test,
ISNULL(A.Lab_Time, B.Lab_Time) as Lab_Time,
ISNULL(A.Location_Name, B.Location_Name) as Location_Name
FROM
(
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE()-1,101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE() -1,101))+'02:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) A
FULL OUTER JOIN
(
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'00:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) B
ONA.Patients_Name = B.Patients_Name
August 7, 2012 at 8:27 am
Another point - it would be worth setting the date selection parameters outside the select statement like so:
DECLARE @DateParam DateTime;
select @DateParam = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM');
-- Also: It might be tidier to use Varchar(30) instead of Char(30) & put a space in front of the time:
select ltrim(CONVERT(varchar(30),DATEADD(d,-1,getdate()),101))+ ' 02:00:01 AM'
Using functions in the where clause disables the use of indexes & can slow queries down
This would give you:
WHERE dbo.BVActions.orderedTime
BETWEEN @DateParam1
AND @DateParam2
August 7, 2012 at 8:48 am
You should NEVER use BETWEEN with datetime data. Your query assumes a precision of 1 second when the actual precision of datetime data is 3 milliseconds, so you are missing everything between 00:00:00.003 and 00:00:00.997 and between 00:02:00.003 and 00:02:00.997. You are better off using semi-open date ranges (usually with the lower end closed and the top end open) such as
WHERE YourDateField >= '2012-08-07 00:02:00'
AND YourDateField < '2012-08-08 00:02:00'
We encountered this at a former job, when the developer didn't take this into account. Fortunately, we were able to work around the problem.
Drew
PS: "Never" may be a bit extreme, but just barely.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 7, 2012 at 9:28 am
You should NEVER use BETWEEN with datetime data.
Do you have any more material on this or is it purely anecdotal? For instance, what's wrong with this?
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/8/2012'
If my business day starts at 8 AM and ends at 8 PM, then I get everything for today right? Of course, I'd prefer a variable or a parameterized stored procedure in practice but "NEVER use BETWEEN with datetime data" seems kind of absolute.
What about this? Many of our reporting queries resolve out to this when a between is chosen in the application:
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/7/2012 23:59:59.999'
August 7, 2012 at 9:31 am
Here's one I prepared earlier:
DECLARE @Lab_Time DateTime,
@StartDate_Yesterday DateTime,
@Two_AM_Yesterday DateTime,
@EndDate_Yesterday DateTime;
-- Set date values:
-- Use >= & < to check dates - this way you don't miss any & you don't count any twice!
SELECT @Lab_Time = CONVERT(VARCHAR(30),GETDATE()-1,101)+' 07:00:00 AM',
@StartDate_Yesterday = DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())),
@Two_AM_Yesterday = CONVERT(DATETIME,ltrim(CONVERT(VARCHAR(30),GETDATE() -1,101))+' 02:00:00 AM'),
@EndDate_Yesterday = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
SELECT ISNULL(A.Patients_Name, B.Patients_Name) as Patients_Name,
ISNULL(A.MR#, B.MR#) as MR#,
ISNULL(A.Required_Lab_Test, B.Required_Lab_Test) as Required_Lab_Test,
ISNULL(A.Lab_Time, B.Lab_Time) as Lab_Time,
ISNULL(A.Location_Name, B.Location_Name) as Location_Name
FROM
(
-- Yesterday midnight - 2 a.m.
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime, @Lab_Time) AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics
LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE (dbo.BVActions.orderedTime >= @StartDate_Yesterday
AND dbo.BVActions.orderedTime < @Two_AM_Yesterday)
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) A
FULL OUTER JOIN
(
-- Yesterday 2 a.m. onwards
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime, @Lab_Time) AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics
LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE (dbo.BVActions.orderedTime >= @Two_AM_Yesterday
AND dbo.BVActions.orderedTime < @EndDate_Yesterday)
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) B
ONA.Patients_Name = B.Patients_Name
August 7, 2012 at 10:45 am
Scott D. Jacobson (8/7/2012)
You should NEVER use BETWEEN with datetime data.
Do you have any more material on this or is it purely anecdotal? For instance, what's wrong with this?
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/8/2012'If my business day starts at 8 AM and ends at 8 PM, then I get everything for today right? Of course, I'd prefer a variable or a parameterized stored procedure in practice but "NEVER use BETWEEN with datetime data" seems kind of absolute.
What about this? Many of our reporting queries resolve out to this when a between is chosen in the application:
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/7/2012 23:59:59.999'
Run the following:
select cast('8/7/2012 23:59:59.999' as datetime)
The result returned for me is 2012-08-08 00:00:00.000. This means that using between in this case would actually return any records where SomeDateColumn contained the value 2012-08-08 00:00:00.000. In this case, what you really want is this:
SELECT
Col1,Col2,Col3
FROM
SomeTable
WHERE
SomeDateColumn >= '8/7/2012' AND -- should use '20120807'
SomeDateColumn < '8/8/2012'; -- should use '20120808'
August 7, 2012 at 11:50 am
Lynn, thank you very much for the detailed reply. Ya learn somethin' new everyday š
August 7, 2012 at 12:02 pm
Also, computing date ranges becomes easier if you use a closed end comparision on the lower end and an open end comparision on the upper end.
For example, you want all records entered for the previous month, in this case July.
declare @StartDate datetime,
@EndDate datetime;
select @StartDate = dateadd(mm, datediff(mm, 0, getdate()) - 1, 0), @EndDate = dateadd(mm, datediff(mm, 0 ,getdate()), 0);
select @StartDate, @EndDate; -- display the start and end dates
select
mt.* -- would actually list the columns to be returned
from
dbo.MyTable mt
where
mt.MyDateColumn >= @StartDate and
mt.MyDateColumn < @EndDate;
August 7, 2012 at 12:11 pm
Scott D. Jacobson (8/7/2012)
You should NEVER use BETWEEN with datetime data.
Do you have any more material on this or is it purely anecdotal? For instance, what's wrong with this?
The issue is easily demonstrated. I'm not sure what more material you need.
WITH CTE AS (
SELECT CAST('2012-08-07' AS DATETIME) AS TestDate
UNION
SELECT '2012-08-07 00:00:00.5'
UNION
SELECT '2012-08-07 00:00:01'
)
SELECT TestDate, CASE WHEN TestDate BETWEEN '2012-08-06 00:02:01' AND '2012-08-07' THEN 'Yesterday'
WHEN TestDate BETWEEN '2012-08-07 00:00:01' AND '2012-08-07 00:02:00' THEN 'Early Today'
ELSE 'This case is currently unhandled and will be excluded.'
END
FROM CTE
SELECT Col1,Col2,Col3 FROM SomeTable WHERE SomeDateColumn BETWEEN '8/7/2012' AND '8/8/2012'
What is wrong with that is that it can double count items that fall precisely at midnight.
WITH CTE AS (
SELECT CAST('2012-08-07' AS DATETIME) AS TestDate
)
SELECT TestDate, 'Yesterday''s process'
FROM CTE
WHERE TestDate BETWEEN '2012-08-06' AND '2012-08-07'
UNION ALL
SELECT TestDate, 'Today''s process'
FROM CTE
WHERE TestDate BETWEEN '2012-08-07' AND '2012-08-08'
If my business day starts at 8 AM and ends at 8 PM, then I get everything for today right? Of course, I'd prefer a variable or a parameterized stored procedure in practice but "NEVER use BETWEEN with datetime data" seems kind of absolute.
Can you guarantee that? At my old job, I thought that we worked standard business hours, but one of my users was working from home and entered data between 23:59:59 PM and 00:00:00.
Even if you can, can you guarantee that it will stay that way? This same job later added a campus in Tokyo, Japan. We had to modify some of our processes that ran off hours, because they were running during the business hours in Tokyo.
The problem is clearly identifiable, and the fix is easy. To put it quite bluntly, if you are aware of the problem and you do not take steps to prevent and fix the problem, it's negligence. Yes, you might be able to escape consequences of that negligence, but why take the chance?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 7, 2012 at 5:39 pm
CELKO (8/7/2012)
Does your boss make you program without any documentation or specs?
Actually... YES! That's what they hired me for. š
On the subject of scratch tapes and temp tables... SQL Server frequently uses temp tables behind the scenes and it sometimes doesn't do such a good job especially for all-in-one queries that use a scad of derived tables, CTEs, and other such tools. What it frequently boils down to is either you use a temp table to store interim results in or let SQL Server do it. Like I said, SQL Server doesn't always do it right.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2012 at 9:02 am
Thank you for your help .. But, I need one more help from you...
I have 2 more conditions to satisfy for this report.
I need patients who have lab orders that have been ordered between WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101)) + '12:31:00 PM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'06:30:59 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)' apart from the AMPostPartumOrders which is working fine with the qry that you sent.
also
the other condition that needs to be satisfied is if in the ForeveryText is dbo.BVActions.[ForEveryText] = ' (Lab 7:00)' then the orders have to have a scheduled date for today, I need to check that
AND dbo.BVActions.ScheduleTime IS NOT NULL
AND dbo.BVActions.itemstatus <> 'Cancelled'
How do I combine this query without using a Union and get the resultset ?
Thank you,
Su
Below is the actual original query that I used with union.
CREATE PROC [dbo].[MMC_SP_LABTOCOLLECT_700AM]
--(
--@NumberOfResults INTEGER OUTPUT -- To get the number of rowcount for the #of results --)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'LAB TO COLLECT ORDERS 7:00 AM'
DECLARE @Location varchar(255)
if (ISNULL(@Location,'')='')
begin
--*** Checking for the AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab) and Ordered Date'
---***AND dbo.BVActions.[cancelledSig] IS NULL ******************--
--** and if the order is not cancelled
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
--** If the scheduleTime is Null then replace it with Getdate ** 07-31-2012
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101)) + '12:31:00 PM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'06:30:59 AM')
--AND dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[cancelledSig] IS NULL --** Checking that there are no cancelled orders in the qry ** --
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
--*** Checking for the AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
--*** AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1') ******************--
--** and if the order is not cancelled
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:01:00 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'11:59:59 PM')
--dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
--OR dbo.BVActions.itemstatus <> 'Cancelled'
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
--(dbo.BLLocation_Group.location_group_name),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
--**Check for Orderdate between '00:00:00 AM' & '06:30:00 AM' for previous day place them for lab collect of same day **--
--** and if the order is not cancelled **--
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
--MIN(dbo.BLLocation_Group.location_group_name) AS UNIT,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
--(dbo.BVActions.scheduleTime) AS Lab_Time,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE()-1,101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:00 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE()-1,101))+'02:00:59 AM')
--dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
--AND dbo.BVActions.[scheduleString] LIKE ('%at AM PostPartum day 1%')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
--(dbo.BLLocation_Group.location_group_name),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
--*** Checking for the dbo.BVActions.[cancelledSig] IS NULL & chck that the Patient exit time is null **** ----
--** and if the order is not cancelled
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
--** If the scheduleTime is Null then replace it with Getdate ** 07-31-2012
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
WHERE dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
--WHERE dbo.BVActions.ScheduleTime = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Lab 7:00)'
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
--*** Checking the dbo.BVActions.[itemstatus] is not Cancelled ****----
--** and if the order is not cancelled 08-06-2012
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
--** If the scheduleTime is Null then replace it with Getdate ** 07-31-2012
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
-- Getting the records for scheduled date for todays date 7:00 AM instead of previous day --07-20-2012
WHERE dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
--WHERE dbo.BVActions.ScheduleTime = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Lab 7:00)'
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.itemstatus <> 'Cancelled'
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
--(dbo.BLLocation_Group.location_group_name),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
UNION
--** Checking for dbo.BVActions.ScheduleTime IS NOT NULL ** 07-20-2012
--** and if the order is not cancelled 08-06-2012
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
--MIN(dbo.BLLocation_Group.location_group_name) AS UNIT,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
--(dbo.BVActions.scheduleTime) AS Lab_Time,
--** If the scheduleTime is Null then replace it with Getdate ** 07-31-2012
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.ScheduleTime = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'07:00:00 AM')
--WHERE dbo.BVActions.ScheduleTime = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '07:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Lab 7:00)'
AND dbo.BVActions.ScheduleTime IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
--(dbo.BLLocation_Group.location_group_name),
(dbo.BVActions.[IPR_Display]),
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
ORDER BY
--(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) ,
--(dbo.MO_Demographics.MRN),
--(dbo.BLLocation_Group.location_group_name),
--(dbo.BVActions.[IPR_Display]),
--(dbo.BVActions.scheduleTime) ,
(dbo.bllocation.[location_name]) ASC
END
--SET @NumberOfResults = @@ROWCOUNT
END
SET NOCOUNT OFF
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
August 8, 2012 at 9:35 am
Still no ddl or sample data? Honestly how do you expect anybody to be able to decipher this from what you have posted? We can't see the tables, we don't the business, and we can't read this. If you are unable or unwilling to post the required details for somebody to answer you are not going to get much of anything resembling an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply