Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Urgent help please !! Expand / Collapse
Author
Message
Posted Tuesday, August 07, 2012 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:03 AM
Points: 17, Visits: 98
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
Posted Tuesday, August 07, 2012 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 1,614, Visits: 16,468
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
Posted Tuesday, August 07, 2012 8:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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
Posted Tuesday, August 07, 2012 8:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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
Posted Tuesday, August 07, 2012 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Posted Tuesday, August 07, 2012 9:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 9:02 PM
Points: 351, Visits: 887
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
Posted Tuesday, August 07, 2012 9:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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
Posted Tuesday, August 07, 2012 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:33 PM
Points: 22,475, Visits: 30,157
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 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
Posted Tuesday, August 07, 2012 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 9:02 PM
Points: 351, Visits: 887
Lynn, thank you very much for the detailed reply. Ya learn somethin' new everyday ;)
Post #1341448
Posted Tuesday, August 07, 2012 12:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:33 PM
Points: 22,475, Visits: 30,157
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

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 #1341456
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse