Urgent help please !!

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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'

  • 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

  • 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'

  • Lynn, thank you very much for the detailed reply. Ya learn somethin' new everyday šŸ˜‰

  • 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;

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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