Help with query please ?

Question

Help with query please ?

swenri

SSC-Addicted

Points: 469
More actions
August 8, 2012 at 9:05 am

#264796

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. 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
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 All...............
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
-- To grant the Procedure to public when ever it drops and creates.--07-27-2012
GO
GRANT EXECUTE ON [dbo].[MMC_SP_LABTOCOLLECT_700AM] TO [Public]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Please don't start a new thread for the same topic. Please direct all replies here. http://www.sqlservercentral.com/Forums/Topic1341216-392-1.aspx

_______________________________________________________________

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/