Prev: Problem Using a UDF in a Constraint to Enforce Non-overlappingDate Periods
Next: very useful info
From: Jay on 20 Jan 2010 18:24
For some reason the date filtering is not working for the below query. Thanks
for any assistance in advance.

SELECT wli.Create_Date, wli.Event_id, p.Full_Name
INTO #WLLegalCases
FROM dbo.evWLlegalintake wli

INNER JOIN Patient_Elg pe ON pe.Patient_Key = wli.Patient_Key --This join
may bring in too many counts for the new cases (CLK 2/11/2009)?
INNER JOIN Patient p ON pe.Patient_key = p.Patient_Key

WHERE pe.Payor_Key = 12


--EAP New Cases (New Definition)
DECLARE @l_cp INT, @l_cy INT, @l_pp INT, @l_py INT
SELECT @l_cp = COUNT(*) FROM #WLLegalCases wli WHERE (wli.Create_Date BETWEEN
'10/1/09' AND '12/31/09')
SELECT @l_cy = COUNT(*) FROM #WLLegalCases wli WHERE (wli.Create_Date BETWEEN
'1/1/09' AND '12/31/09')
SELECT @l_pp = COUNT(*) FROM #WLLegalCases wli WHERE (wli.Create_Date BETWEEN
'10/01/08' AND '12/31/08')
SELECT @l_py = COUNT(*) FROM #WLLegalCases wli WHERE (wli.Create_Date BETWEEN
'1/1/08' AND '12/31/08')

SELECT * FROM #WLLegalCases

DROP TABLE #WLLegalCases

--
Jay

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Plamen Ratchev on 20 Jan 2010 22:17
Can you provide sample data and explain what is not working?

The date format you use in the predicates is not a reliable approach. It is best to use language neutral format like
'YYYYMMDD'. Also, if the create_date column has time portion other than midnight it is best to use half-open interval.
Here is the first query (I assume create_date is date/time data type):

SELECT @l_cp = COUNT(*)
FROM #WLLegalCases
WHERE create_date >= '20091001'
AND create_date < '20100101';

--
Plamen Ratchev
http://www.SQLStudio.com
From: Jay via SQLMonster.com on 21 Jan 2010 12:28
Plamen Ratchev wrote:
>Can you provide sample data and explain what is not working?
>
>The date format you use in the predicates is not a reliable approach. It is best to use language neutral format like
>'YYYYMMDD'. Also, if the create_date column has time portion other than midnight it is best to use half-open interval.
>Here is the first query (I assume create_date is date/time data type):
>
>SELECT @l_cp = COUNT(*)
>FROM #WLLegalCases
>WHERE create_date >= '20091001'
> AND create_date < '20100101';
>

The query returns all rows and disregards the second half of the query. It
does not use the variables declared or any part SELECT @l_cp = COUNT(*) FROM
#WLLegalCases wli WHERE (wli.Create_Date BETWEEN
'10/1/09' AND '12/31/09').

If you need more information please let me know. Thanks again in advance.

--
Jay

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Plamen Ratchev on 21 Jan 2010 12:36
Please provide CREATE TABLE statement for your table, INSERT statements to generate sufficient sample data to
illustrate the problem, and the expected results.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Problem Using a UDF in a Constraint to Enforce Non-overlappingDate Periods
Next: very useful info