Help with query please !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with query please !!!

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2012-08-07 : 09:07:42

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 date value ie; either yesterday or tomorrow. 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 09:55:49

where's scheduled order field? i cant even see it in select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources