First, last day of month as SP default parameters

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 2005 Forums

Transact-SQL (2005)

First, last day of month as SP default parameters

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 18:29:15

I created this stored procedure and have highlighted the affected rows. I have succussfully allowed the user to input the team, but not the 2 dates. As a temporary work-around, I have set the dates with a function inside the SP. But I want the user to be able to select them and if they skip it, have it default to first and last day of month. I am getting an error if I set the default to the date function right away, so I was trying to figure out how to assign it in the code. The Green lines work as they should. The red lines show the problem area and the orange lines are the new parameters I ended up creating in the hopes that I could assign them later after determining whether they were null or not. I would have liked to have been able to set the date values right at the beginning like 'Team'. Maybe I have a mental block, but if you could take a look at it, I would appreciate it:

ALTER PROCEDURE [dbo].[usp_IHC_Report_Hospice_Admissions] 
	-- Add the parameters for the stored procedure here
	-- These defaults are the first and last moment of last month
	@Team Varchar(36) = NULL,
	@iBeginDate DateTime = NULL,
	@iEndDate DateTime = NULL
	--@BeginDate DateTime = DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0)), 
	--@EndDate DateTime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @BeginDate DateTime
	DECLARE @EndDate DateTime
	SET @BeginDate = DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
	SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
	
SELECT DISTINCT PT_BASIC.PATIENT_ID, 
                PT_BASIC.PATIENT_CODE,
                SQ.DESCRIPTION DESCRIPTION, 
                PT_BASIC.NAME_FULL, 
                PT_ADMISSION.ADMIT_DATE, 
                PT_ADMISSION.TERMINATION_DATE, 
                C_DIAGNOSIS.DIAGNOSIS, 
                C_DIAGNOSIS.ICD9_CODE, 
                O_DATASET.DATASET_NAME, 
                PT_PAY_SOURCE.INSURANCE_CODE, 
                PT_PAY_SOURCE.INSURANCE_COMPANY, 
                O_TEAM.TEAM_DESCRIPTION, 
                VW_RESOURCES_2.ORGANIZATION_NAME, 
                Case when PT_PAY_SOURCE.INSURANCE_COMPANY is null then A_INSURANCE_TYPE.DESCRIPTION else A_INSURANCE_COMPANY.COMPANY_NAME end Case_when_PT_PAY_SOURCE_I, 
                Case when VW_RESOURCES.ORGANIZATION_NAME is null then VW_RESOURCES.NAME_LAST + ', ' + VW_RESOURCES.NAME_FIRST else VW_RESOURCES.ORGANIZATION_NAME end Case_when_VW_RESOURCES_OR
FROM PT_BASIC PT_BASIC
      LEFT OUTER JOIN PT_ADMISSION PT_ADMISSION ON 
     (PT_ADMISSION.PATIENT_ID = PT_BASIC.PATIENT_ID)
      LEFT OUTER JOIN PT_STATUS PT_STATUS ON 
     (PT_STATUS.ADMISSION_ID = PT_ADMISSION.ADMISSION_ID)
      AND (PT_STATUS.PATIENT_ID = PT_ADMISSION.PATIENT_ID)
      AND (PT_STATUS.STATUS_DATE = PT_ADMISSION.ADMIT_DATE)
      LEFT OUTER JOIN PTC_DIAGNOSIS PTC_DIAGNOSIS ON 
     (PTC_DIAGNOSIS.PT_DIAGNOSIS_ID = PT_STATUS.PRIMARY_DIAGNOSIS_ID)
      AND (PTC_DIAGNOSIS.PATIENT_ID = PT_STATUS.PATIENT_ID)
      LEFT OUTER JOIN C_DIAGNOSIS C_DIAGNOSIS ON 
     (C_DIAGNOSIS.DIAGNOSIS_ID = PTC_DIAGNOSIS.DIAGNOSIS_ID)
      AND (C_DIAGNOSIS.DIAGNOSIS_SET_ID = PTC_DIAGNOSIS.DIAGNOSIS_SET_ID)
      LEFT OUTER JOIN PTC_CERT_PERIOD PTC_CERT_PERIOD ON 
     (PTC_CERT_PERIOD.PATIENT_ID = PT_BASIC.PATIENT_ID)
      AND (PTC_CERT_PERIOD.CERT_END_DATE > PT_BASIC.LAST_CERTIFICATION_DATE)
      INNER JOIN O_DATASET O_DATASET ON 
     (O_DATASET.DATASET_ID = PT_BASIC.DATASET_ID)
      LEFT OUTER JOIN PT_SERVICE PT_SERVICE ON 
     (PT_SERVICE.PATIENT_ID = PT_BASIC.PATIENT_ID)
      LEFT OUTER JOIN PT_PAY_CONTROL PT_PAY_CONTROL ON 
     (PT_PAY_CONTROL.PATIENT_ID = PT_STATUS.PATIENT_ID)
      AND (PT_PAY_CONTROL.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID)
      LEFT OUTER JOIN PT_PAY_SOURCE PT_PAY_SOURCE ON 
     (PT_PAY_SOURCE.PATIENT_ID = PT_PAY_CONTROL.PATIENT_ID)
      AND (PT_PAY_SOURCE.ADMIN_SET_ID = PT_PAY_CONTROL.ADMIN_SET_ID)
      AND (PT_PAY_SOURCE.PAY_SOURCE_POINTER = PT_PAY_CONTROL.PRIMARY_POINTER)
      LEFT OUTER JOIN A_INSURANCE_TYPE A_INSURANCE_TYPE ON 
     (A_INSURANCE_TYPE.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID)
      AND (A_INSURANCE_TYPE.INSURANCE_CODE = PT_PAY_SOURCE.INSURANCE_CODE)
      LEFT OUTER JOIN A_INSURANCE_COMPANY A_INSURANCE_COMPANY ON 
     (A_INSURANCE_COMPANY.COMPANY_CODE = PT_PAY_SOURCE.INSURANCE_COMPANY)
      AND (A_INSURANCE_COMPANY.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID)
      LEFT OUTER JOIN O_TEAM O_TEAM ON 
     (O_TEAM.TEAM_ID = PT_ADMISSION.TEAM_ID)
      LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES ON 
     (VW_RESOURCES.RESOURCE_ID = PT_ADMISSION.REFERRAL_SOURCE)
      LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES_2 ON 
     (VW_RESOURCES_2.RESOURCE_ID = PT_STATUS.ASSOCIATED_FACILITY_ID)
      AND (VW_RESOURCES_2.ROLE_ID = PT_STATUS.ASSOCIATED_FACILITY_ROLE_ID)

LEFT JOIN
(SELECT PT_DEMOGRAPHIC.PATIENT_ID, 
       A_DEMOGRAPHIC.DESCRIPTION
FROM PT_DEMOGRAPHIC PT_DEMOGRAPHIC
      LEFT OUTER JOIN A_DEMOGRAPHIC A_DEMOGRAPHIC ON 
     (A_DEMOGRAPHIC.DEMO_TYPE_ID = PT_DEMOGRAPHIC.DEMO_TYPE_ID)
      AND (A_DEMOGRAPHIC.DEMO_ID = PT_DEMOGRAPHIC.DEMO_ID)
      AND (A_DEMOGRAPHIC.ADMIN_SET_ID = PT_DEMOGRAPHIC.ADMIN_SET_ID)
WHERE ( PT_DEMOGRAPHIC.DEMO_TYPE_ID = 1 )) SQ
ON SQ.Patient_ID = PT_BASIC.PATIENT_ID

WHERE 
      ( O_DATASET.DATASET_NAME = 'AAA Hospice' )
       AND O_TEAM.TEAM_DESCRIPTION = @Team OR @Team is NULL
       AND ( PT_ADMISSION.ADMIT_DATE BETWEEN CONVERT(DATETIME,@BeginDate,120) AND CONVERT(DATETIME,@EndDate,120) )
       AND ( PT_ADMISSION.ADMIT_DATE IS NOT NULL  )       AND ( O_TEAM.DATASET_ID = O_Dataset.DATASET_ID )
       AND ( PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL  )
ORDER BY PT_BASIC.NAME_FULL

END

Duane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 18:48:56

[code]ALTER PROCEDURE dbo.usp_IHC_Report_Hospice_Admissions
(
@Team VARCHAR(36) = NULL,
@iBeginDate DATETIME = NULL,
@iEndDate DATETIME = NULL
)
AS

SET NOCOUNT ON

IF @iBeginDate IS NULL
SET @BeginDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

IF @iEndDate IS NULL
SET @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

SELECT DISTINCT PT_BASIC.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
SQ.DESCRIPTION DESCRIPTION,
PT_BASIC.NAME_FULL,
PT_ADMISSION.ADMIT_DATE,
PT_ADMISSION.TERMINATION_DATE,
C_DIAGNOSIS.DIAGNOSIS,
C_DIAGNOSIS.ICD9_CODE,
O_DATASET.DATASET_NAME,
PT_PAY_SOURCE.INSURANCE_CODE,
PT_PAY_SOURCE.INSURANCE_COMPANY,
O_TEAM.TEAM_DESCRIPTION,
VW_RESOURCES_2.ORGANIZATION_NAME,
Case
when PT_PAY_SOURCE.INSURANCE_COMPANY is null then A_INSURANCE_TYPE.DESCRIPTION
else A_INSURANCE_COMPANY.COMPANY_NAME
end as Case_when_PT_PAY_SOURCE_I,
Case
when VW_RESOURCES.ORGANIZATION_NAME is null then VW_RESOURCES.NAME_LAST + ', ' + VW_RESOURCES.NAME_FIRST
else VW_RESOURCES.ORGANIZATION_NAME
end as Case_when_VW_RESOURCES_OR
FROM PT_BASIC
LEFT JOIN PT_ADMISSION AS PT_ADMISSION ON PT_ADMISSION.PATIENT_ID = PT_BASIC.PATIENT_ID
LEFT JOIN PT_STATUS ON PT_STATUS.ADMISSION_ID = PT_ADMISSION.ADMISSION_ID
AND PT_STATUS.PATIENT_ID = PT_ADMISSION.PATIENT_ID
AND PT_STATUS.STATUS_DATE = PT_ADMISSION.ADMIT_DATE
LEFT JOIN PTC_DIAGNOSIS ON PTC_DIAGNOSIS.PT_DIAGNOSIS_ID = PT_STATUS.PRIMARY_DIAGNOSIS_ID
AND PTC_DIAGNOSIS.PATIENT_ID = PT_STATUS.PATIENT_ID
LEFT JOIN C_DIAGNOSIS ON C_DIAGNOSIS.DIAGNOSIS_ID = PTC_DIAGNOSIS.DIAGNOSIS_ID
AND C_DIAGNOSIS.DIAGNOSIS_SET_ID = PTC_DIAGNOSIS.DIAGNOSIS_SET_ID
LEFT JOIN PTC_CERT_PERIOD ON PTC_CERT_PERIOD.PATIENT_ID = PT_BASIC.PATIENT_ID
AND PTC_CERT_PERIOD.CERT_END_DATE > PT_BASIC.LAST_CERTIFICATION_DATE
INNER JOIN O_DATASET ON O_DATASET.DATASET_ID = PT_BASIC.DATASET_ID
LEFT JOIN PT_SERVICE ON PT_SERVICE.PATIENT_ID = PT_BASIC.PATIENT_ID
LEFT JOIN PT_PAY_CONTROL ON PT_PAY_CONTROL.PATIENT_ID = PT_STATUS.PATIENT_ID
AND PT_PAY_CONTROL.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID
LEFT JOIN PT_PAY_SOURCE ON PT_PAY_SOURCE.PATIENT_ID = PT_PAY_CONTROL.PATIENT_ID
AND PT_PAY_SOURCE.ADMIN_SET_ID = PT_PAY_CONTROL.ADMIN_SET_ID
AND PT_PAY_SOURCE.PAY_SOURCE_POINTER = PT_PAY_CONTROL.PRIMARY_POINTER
LEFT JOIN A_INSURANCE_TYPE ON A_INSURANCE_TYPE.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID
AND A_INSURANCE_TYPE.INSURANCE_CODE = PT_PAY_SOURCE.INSURANCE_CODE
LEFT JOIN A_INSURANCE_COMPANY ON A_INSURANCE_COMPANY.COMPANY_CODE = PT_PAY_SOURCE.INSURANCE_COMPANY
AND A_INSURANCE_COMPANY.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID
LEFT JOIN O_TEAM ON O_TEAM.TEAM_ID = PT_ADMISSION.TEAM_ID
LEFT JOIN VW_RESOURCES ON VW_RESOURCES.RESOURCE_ID = PT_ADMISSION.REFERRAL_SOURCE
LEFT JOIN VW_RESOURCES AS VW_RESOURCES_2 ON VW_RESOURCES_2.RESOURCE_ID = PT_STATUS.ASSOCIATED_FACILITY_ID
AND VW_RESOURCES_2.ROLE_ID = PT_STATUS.ASSOCIATED_FACILITY_ROLE_ID
LEFT JOIN (
SELECT PT_DEMOGRAPHIC.PATIENT_ID,
A_DEMOGRAPHIC.DESCRIPTION
FROM PT_DEMOGRAPHIC
LEFT JOIN A_DEMOGRAPHIC ON A_DEMOGRAPHIC.DEMO_TYPE_ID = PT_DEMOGRAPHIC.DEMO_TYPE_ID
AND A_DEMOGRAPHIC.DEMO_ID = PT_DEMOGRAPHIC.DEMO_ID
AND A_DEMOGRAPHIC.ADMIN_SET_ID = PT_DEMOGRAPHIC.ADMIN_SET_ID
WHERE PT_DEMOGRAPHIC.DEMO_TYPE_ID = 1
) AS SQ ON SQ.Patient_ID = PT_BASIC.PATIENT_ID
WHERE O_DATASET.DATASET_NAME = 'AAA Hospice'
AND (O_TEAM.TEAM_DESCRIPTION = @Team OR @Team is NULL)
AND (PT_ADMISSION.ADMIT_DATE >= @iBeginDate AND PT_ADMISSION.ADMIT_DATE < @iEndDate)
AND O_TEAM.DATASET_ID = O_Dataset.DATASET_ID
AND PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL
ORDER BY PT_BASIC.NAME_FULL[/code]

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 09:08:13

Thank you for all this. Maybe you can bear with me once more. I am not getting any results from this query. The only way it works, and that is after I put the DECLARE statements back in, I had to execute it this way, EXEC usp_IHC_Report_Hospice_Admissions NULL, '07/01/2009','07/31/2009'. When I put nothing in the date variables, nothing is returned and it does not default to the default dates. I didn't think this would be so difficult.

Duane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 09:18:13

EXEC usp_IHC_Report_Hospice_Admissions NULL, '20090701', '20090731'

I changed the paranthesises around the @Team thingy, because it looked funny.
You can change the paranthesises back and try that too.

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 09:44:23

I don't think the team thing is wrong, because I have this code here, which is at the beginning of the SP and it displays the output of the variables:

ALTER PROCEDURE [dbo].[usp_IHC_Report_Hospice_Admissions] 
	-- Add the parameters for the stored procedure here
	-- These defaults are the first and last moment of last month
(
	@Team VARCHAR(36) = NULL,
	@iBeginDate DATETIME = NULL,
	@iEndDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @BeginDate DateTime
DECLARE @EndDate DateTime
IF @iBeginDate IS NULL 
SET @BeginDate = DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
ELSE SET @BeginDate = @iBeginDate
IF @iEndDate IS NULL
ELSE SET @EndDate = @iEndDate
SELECT @BeginDate, @EndDate

I had to assign @BeginDate and @EndDate to @iBeginDate and @iEndDate with ELSE statements because if they were not NULL then @BeginDate and @EndDate would be unpopulated.
Now, when I run this with the parameters, as at the top of your last post, it runs fine and the dates in the select statement come out fine as 2009-07-01 00:00:00.000 2009-07-31 00:00:00.000 and I get results. When I run it without parameters, or with 3 NULLs, I get the dates 2009-07-01 00:00:00.000 2009-07-31 23:59:59.000 in that select statement, but no results from the query part. I temporarily took out your correction for @EndDate which would have resulted in 8/1/2009, but yours had no results either.

I think it might need a CONVERT function in that statement. How does that sound? And how would I code it since there is a date calculation in the expression?

Duane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 09:48:16

No, your original statement "breaks" at the OR statement.

WHERE 
      ( O_DATASET.DATASET_NAME = 'AAA Hospice' )
       AND O_TEAM.TEAM_DESCRIPTION = @Team 

OR 

      @Team is NULL
       AND ( PT_ADMISSION.ADMIT_DATE BETWEEN CONVERT(DATETIME,@BeginDate,120) AND CONVERT(DATETIME,@EndDate,120) )
       AND ( PT_ADMISSION.ADMIT_DATE IS NOT NULL  )       AND ( O_TEAM.DATASET_ID = O_Dataset.DATASET_ID )
       AND ( PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL  )

As you can see, when @Team variable is NOT NULL (ie having a value), your query doesn't take into account the admit_date filters!
Only when @Team is null you filter by admin_date, but then you don't filter by DATASET_NAME!

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 09:51:08

What datatype is ADMIT_DATE column?

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 09:55:42

It's DateTime

Duane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:14:43

Then there is no need to use CONVERT.
What about the OR thing for @Team variable?

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 10:19:20

This one does run, and does work, both ways. There was some confusion over @BeginDate and @iBeginDate, so I worked that out. The only problem is that when I use your date expression for @EndDate, I get the @EndDate of 08/01/2009 when it should be the last moment of 7/31/2009. Theoretically, ther could be one at midnight, especially if somehow the program dropped off the hours, minutes, and seconds. That is true, even if the user inputs the dates. With my date expression, it defaults properly to the last moment of last month, but when the user supplies the end date as a parameter, for example, 7/31/2009, it brings back records only up until midnight of 7/31, which eliminates any on that day. I want it to go up to 11:59:59 regardless of whether they type it in or not. Here is the code that otherwise works:

ALTER PROCEDURE [dbo].[usp_IHC_Report_Hospice_Admissions] 
	-- Add the parameters for the stored procedure here
	-- These defaults are the first and last moment of last month
(
	@Team VARCHAR(36) = NULL,
	@BeginDate DATETIME = NULL,
	@EndDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON

IF @BeginDate IS NULL 
	SET @BeginDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
--	SET @BeginDate = DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
IF @EndDate IS NULL
	SET @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
--	SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SELECT @BeginDate, @EndDate
SELECT DISTINCT	PT_BASIC.PATIENT_ID, 
                PT_BASIC.PATIENT_CODE,
                SQ.DESCRIPTION DESCRIPTION, 
                PT_BASIC.NAME_FULL, 
                PT_ADMISSION.ADMIT_DATE, 
                PT_ADMISSION.TERMINATION_DATE, 
                C_DIAGNOSIS.DIAGNOSIS, 
                C_DIAGNOSIS.ICD9_CODE, 
                O_DATASET.DATASET_NAME, 
                PT_PAY_SOURCE.INSURANCE_CODE, 
                PT_PAY_SOURCE.INSURANCE_COMPANY, 
                O_TEAM.TEAM_DESCRIPTION, 
                VW_RESOURCES_2.ORGANIZATION_NAME, 
                Case
			when PT_PAY_SOURCE.INSURANCE_COMPANY is null then A_INSURANCE_TYPE.DESCRIPTION
			else A_INSURANCE_COMPANY.COMPANY_NAME
		end as Case_when_PT_PAY_SOURCE_I, 
                Case
			when VW_RESOURCES.ORGANIZATION_NAME is null then VW_RESOURCES.NAME_LAST + ', ' + VW_RESOURCES.NAME_FIRST
			else VW_RESOURCES.ORGANIZATION_NAME
		end as Case_when_VW_RESOURCES_OR
FROM		PT_BASIC
LEFT JOIN	PT_ADMISSION AS PT_ADMISSION ON PT_ADMISSION.PATIENT_ID = PT_BASIC.PATIENT_ID
LEFT JOIN	PT_STATUS ON PT_STATUS.ADMISSION_ID = PT_ADMISSION.ADMISSION_ID
			AND PT_STATUS.PATIENT_ID = PT_ADMISSION.PATIENT_ID
			AND PT_STATUS.STATUS_DATE = PT_ADMISSION.ADMIT_DATE
LEFT JOIN	PTC_DIAGNOSIS ON PTC_DIAGNOSIS.PT_DIAGNOSIS_ID = PT_STATUS.PRIMARY_DIAGNOSIS_ID
			AND PTC_DIAGNOSIS.PATIENT_ID = PT_STATUS.PATIENT_ID
LEFT JOIN	C_DIAGNOSIS ON C_DIAGNOSIS.DIAGNOSIS_ID = PTC_DIAGNOSIS.DIAGNOSIS_ID
			AND C_DIAGNOSIS.DIAGNOSIS_SET_ID = PTC_DIAGNOSIS.DIAGNOSIS_SET_ID
LEFT JOIN	PTC_CERT_PERIOD ON PTC_CERT_PERIOD.PATIENT_ID = PT_BASIC.PATIENT_ID
			AND PTC_CERT_PERIOD.CERT_END_DATE > PT_BASIC.LAST_CERTIFICATION_DATE
INNER JOIN	O_DATASET ON O_DATASET.DATASET_ID = PT_BASIC.DATASET_ID
LEFT JOIN	PT_SERVICE ON PT_SERVICE.PATIENT_ID = PT_BASIC.PATIENT_ID
LEFT JOIN	PT_PAY_CONTROL ON PT_PAY_CONTROL.PATIENT_ID = PT_STATUS.PATIENT_ID
			AND PT_PAY_CONTROL.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID
LEFT JOIN	PT_PAY_SOURCE ON PT_PAY_SOURCE.PATIENT_ID = PT_PAY_CONTROL.PATIENT_ID
			AND PT_PAY_SOURCE.ADMIN_SET_ID = PT_PAY_CONTROL.ADMIN_SET_ID
			AND PT_PAY_SOURCE.PAY_SOURCE_POINTER = PT_PAY_CONTROL.PRIMARY_POINTER
LEFT JOIN	A_INSURANCE_TYPE ON A_INSURANCE_TYPE.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID
			AND A_INSURANCE_TYPE.INSURANCE_CODE = PT_PAY_SOURCE.INSURANCE_CODE
LEFT JOIN	A_INSURANCE_COMPANY ON A_INSURANCE_COMPANY.COMPANY_CODE = PT_PAY_SOURCE.INSURANCE_COMPANY
			AND A_INSURANCE_COMPANY.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID
LEFT JOIN	O_TEAM ON O_TEAM.TEAM_ID = PT_ADMISSION.TEAM_ID
LEFT JOIN	VW_RESOURCES ON VW_RESOURCES.RESOURCE_ID = PT_ADMISSION.REFERRAL_SOURCE
LEFT JOIN	VW_RESOURCES AS VW_RESOURCES_2 ON VW_RESOURCES_2.RESOURCE_ID = PT_STATUS.ASSOCIATED_FACILITY_ID
			AND VW_RESOURCES_2.ROLE_ID = PT_STATUS.ASSOCIATED_FACILITY_ROLE_ID
LEFT JOIN	(
			SELECT		PT_DEMOGRAPHIC.PATIENT_ID, 
					A_DEMOGRAPHIC.DESCRIPTION
			FROM		PT_DEMOGRAPHIC
			LEFT JOIN	A_DEMOGRAPHIC ON A_DEMOGRAPHIC.DEMO_TYPE_ID = PT_DEMOGRAPHIC.DEMO_TYPE_ID
						AND A_DEMOGRAPHIC.DEMO_ID = PT_DEMOGRAPHIC.DEMO_ID
						AND A_DEMOGRAPHIC.ADMIN_SET_ID = PT_DEMOGRAPHIC.ADMIN_SET_ID
			WHERE		PT_DEMOGRAPHIC.DEMO_TYPE_ID = 1
		) AS SQ ON SQ.Patient_ID = PT_BASIC.PATIENT_ID
WHERE		O_DATASET.DATASET_NAME = 'Interim Hospice'
		AND (O_TEAM.TEAM_DESCRIPTION = @Team OR @Team is NULL)
		AND (PT_ADMISSION.ADMIT_DATE >= @BeginDate AND PT_ADMISSION.ADMIT_DATE < @EndDate)
		AND O_TEAM.DATASET_ID = O_Dataset.DATASET_ID
		AND PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL
ORDER BY	PT_BASIC.NAME_FULL
END

Duane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:26:05

You should always use OPEN-ENDED datetime search filter.

Date1 >= '20090101'
AND Date1 < '20100101'

Now ALL records for complete 2009 is filtered in, no matter how many decimals for milliseconds you use.

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 10:42:48

I see your logic as correct, and it is running fine. Thank you.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources