Urgent help please !!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Urgent help please !!

15 posts, Page 1 of 2

1 2 »»

Urgent help please !!

Rate Topic

Display Mode

Topic Options

Author

Message

swenri

Posted Tuesday, August 07, 2012 7:02 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 12:47 PM
Points: 17, Visits: 97

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

Post #1341216

J Livingston SQL

Posted Tuesday, August 07, 2012 7:58 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 1,472, Visits: 14,595

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 !
__________________________________________________________________

Post #1341283

laurie-789651

Posted Tuesday, August 07, 2012 8:12 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 278, Visits: 808

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
ON	A.Patients_Name = B.Patients_Name

Post #1341289

laurie-789651

Posted Tuesday, August 07, 2012 8:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 278, Visits: 808

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

Post #1341301

drew.allen

Posted Tuesday, August 07, 2012 8:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

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

Post #1341324

Scott D. Jacobson

Posted Tuesday, August 07, 2012 9:28 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 335, Visits: 844

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'

Post #1341353

laurie-789651

Posted Tuesday, August 07, 2012 9:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 278, Visits: 808

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
ON	A.Patients_Name = B.Patients_Name

Post #1341355

Lynn Pettis

Posted Tuesday, August 07, 2012 10:45 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

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'

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 Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1341408

Scott D. Jacobson

Posted Tuesday, August 07, 2012 11:50 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 335, Visits: 844

Lynn, thank you very much for the detailed reply. Ya learn somethin' new everyday ;)

Post #1341448

Lynn Pettis

Posted Tuesday, August 07, 2012 12:02 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

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;

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1341456

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions