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)
 Help with Stored Procedure

Author  Topic 

ccrespo
Yak Posting Veteran

59 Posts
Posted - 2007-03-08 : 08:34:57
I'm having trouble with one of the date ranges. I was wondering if it was possible to do a range for a date that is converted in CHAR format. I'm still kind of new to stored procedures.

SET NOCOUNT ON
CREATE TABLE #ELIG (
AppointmentsId int,
ApptSetId int,
ApptSetName varchar(120),
ApptChainId int,
ApptChainName varchar(120),
InsCaseName varchar(120),
Date DateTime,
StrtHour varchar(2),
StrtMin varchar(2),
StpHour varchar(2),
StpMin varchar(2),
Facility varchar(120),
ResourceStart DateTime,
ApptStop DateTime,
DoctorId int,
DoctorName varchar(120),
ApptStatus varchar(120),
ApptType varchar(120),
PatientName varchar(120),
PatientId int,
PatientProfileId int,
Phone1 varchar(50),
Birthdate DateTime,
Phone2 varchar(50),
Phone1Type varchar(6),
Phone2Type varchar(6),
Resource varchar(120),
Notes varchar (500),
Financial varchar(120),
RefSource varchar(120),
Flag varchar (6),
InsuranceCarrier1 varchar(150),
InsuranceCarrier2 varchar(150),
Ins1Phone varchar(50),
Ins2Phone varchar(50),
InsuredID1 varchar(75),
InsuredID2 varchar(75),
Ins1EligVerified smallint,
Ins2EligVerified smallint,
Ins1EligVerifiedDate DateTime, -- *The column is created as a DateTime field
Ins2EligVerifiedDate DateTime,
Ins1EligVerifiedBy varchar(75),
Ins2EligVerifiedBy varchar(75),
Ins1DeduuctibleRemaining varchar (10),
Ins2DeduuctibleRemaining varchar (10),
Ins1DeductibleDate DateTime,
Ins2DeductibleDate DateTime,
Ins1CardEffectiveDate DateTime,
Ins2CardEffectiveDate DateTime,
Ins1CardTerminationDate DateTime,
Ins2CardTerminationDate DateTime,
Ins1Inactive smallint,
Ins2Inactive smallint,
Ins1Auth varchar(50),
Ins2Auth varchar(50),
Ins1AuthIssueDate DateTime,
Ins2AuthIssueDate DateTime,
Ins1AuthExpDate DateTime,
Ins2AuthExpDate DateTime,
ApptKind smallint
)

CREATE TABLE #INS1 (
PatientProfileId int,
InsuranceCarrier1 varchar(50),
Ins1Phone varchar(50),
InsuredID1 varchar(25),
Ins1EligVerified smallint,
Ins1EligVerifiedDate DateTime, -- *The column is still a DateTime field
Ins1EligVerifiedBy varchar(25),
Ins1DeduuctibleRemaining varchar (10),
Ins1DeductibleDate DateTime,
Ins1CardEffectiveDate DateTime,
Ins1CardTerminationDate DateTime,
Ins1Inactive smallint,
Ins1Auth varchar(50),
Ins1AuthIssueDate DateTime,
Ins1AuthExpDate DateTime
)

CREATE TABLE #INS2 (
PatientProfileId int,
InsuranceCarrier2 varchar(50),
Ins2Phone varchar(50),
InsuredID2 varchar(25),
Ins2EligVerified smallint,
Ins2EligVerifiedDate DateTime,
Ins2EligVerifiedBy varchar(25),
Ins2DeduuctibleRemaining varchar (10),
Ins2DeductibleDate DateTime,
Ins2CardEffectiveDate DateTime,
Ins2CardTerminationDate DateTime,
Ins2Inactive smallint,
Ins2Auth varchar(50),
Ins2AuthIssueDate DateTime,
Ins2AuthExpDate DateTime
)

INSERT INTO #ELIG
SELECT a.AppointmentsId,
aset.ApptSetId,
dbo.asGetApptSetName(ISNULL(aset.ApptSetId,0),a.AppointmentsId) ApptSetName,
ac.ApptChainId,
ac.Name ApptChainName,
c.Name,
Date=convert(datetime,convert(char(12),a.ApptStart,1)),
StrtHour=convert(varchar(2),datepart(hour,a.ApptStart)),
StrtMin=convert(varchar(2),datepart(minute,a.ApptStart)),
StpHour=convert(varchar(2),datepart(hour,a.ApptStop)),
StpMin=convert(varchar(2),datepart(minute,a.ApptStop)),
Facility= dff.Listname,
convert(datetime,a.ApptStart) AS ResourceStart,
a.ApptStop,
a.DoctorId,
dfd.Listname AS DoctorName,
mlas.Description AS Status,
at.Name AS Type,
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', '+ ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) AS PatientName,
pp.PatientId,
pp.PatientProfileId,
dbo.FormatPhone(pp.Phone1,1) AS Phone1,
pp.Birthdate,
dbo.FormatPhone(pp.Phone2,1) AS Phone2,
substring(pp.Phone1Type,1,1) AS Phone1Type,
substring(pp.Phone2Type,1,1) AS Phone2Type,
dfr.Listname AS Resource,
convert(varchar(255), a.Notes) AS Notes,
CASE WHEN ISNULL(a.CasesId,0) = 0 THEN mlfc.Description ELSE mlfcc.Description END AS Financial,
mlrs.Description AS RefSource,
Flag = convert(varchar(50), 'Time'),
NULL AS InsuranceCarrier1,
NULL AS InsuranceCarrier2,
NULL AS Ins1Phone,
NULL AS Ins2Phone,
NULL AS InsuredID1,
NULL AS InsuredID2,
NULL AS Ins1EligVerified,
NULL AS Ins2EligVerified,
NULL AS Ins1EligVerifiedDate,
NULL AS Ins2EligVerifiedDate,
NULL AS Ins1EligVerifiedBy,
NULL AS Ins2EligVerifiedBy,
NULL AS Ins1DeduuctibleRemaining,
NULL AS Ins2DeduuctibleRemaining,
NULL AS Ins1DeductibleDate,
NULL AS Ins2DeductibleDate,
NULL AS Ins1CardEffectiveDate,
NULL AS Ins2CardEffectiveDate,
NULL AS Ins1CardTerminationDate,
NULL AS Ins2CardTerminationDate,
NULL AS Ins1Inactive,
NULL AS Ins2Inactive,
NULL AS Ins1Auth,
NULL AS Ins2Auth,
NULL AS Ins1AuthIssueDate,
NULL AS Ins2AuthIssueDate,
NULL AS Ins1AuthExpDate,
NULL AS Ins2AuthExpDate,
ApptKind = 1

FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId,0) = ISNULL(ci.CasesId,0) AND ci.PatientProfileId = pp.PatientProfileId
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId AND pi.OrderForClaims = 1
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
LEFT JOIN Medlists mlfc ON pp.FinancialClassMId = mlfc.MedlistsId
LEFT JOIN Cases c ON a.CasesId = c.CasesId
LEFT JOIN Medlists mlfcc ON c.FinancialClassMId = mlfcc.MedlistsId
LEFT JOIN Medlists mlrs ON pp.ReferenceSourceMID = mlrs.MedlistsId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND (pi.inactive <> 1 OR pi.inactive is NULL) AND pp.patientprofileid = pi.patientprofileid
AND --Filter on Date
a.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
AND --Filter on doctor
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND a.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND a.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND (?APPTTYPE.ITEMDATA.U? = 1 OR ?APPTTYPE.ITEMDATA.U? = 2)

INSERT INTO #INS1
SELECT PatientProfileId = pp.PatientProfileId,
InsuranceCarrier = ic.Listname,
dbo.FormatPhone(ic.Phone1,1) AS InsPhone,
InSuredID = pi.InsuredID,
EligibilityVerified = pi.EligibilityVerified,
EligibilityVerifiedDate = convert(char(12),pi.EligibilityVerifiedDate,1), -- Now its converted into CHAR format
EligibilityVerifiedBy = pi.EligibilityVerifiedBy,
DeductibleRemaining = pi.DeductibleRemaining,
DeductibleDate = convert(char(12), pi.DeductibleDate, 1),
InsCardEffectiveDate = convert(char(12), pi.InsCardEffectiveDate,1),
InsCardTerminationDate = convert(char(12), pi.InsCardTerminationDate, 1),
pi.inactive,
ci.Number,
ci.IssuedDate,
c.AuthExpirationDate
FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId,0) = ISNULL(ci.CasesId,0) AND ci.PatientProfileId = pp.PatientProfileId
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId AND pi.OrderForClaims = 1
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
LEFT JOIN Medlists mlfc ON pp.FinancialClassMId = mlfc.MedlistsId
LEFT JOIN Cases c ON a.CasesId = c.CasesId
LEFT JOIN Medlists mlfcc ON c.FinancialClassMId = mlfcc.MedlistsId
LEFT JOIN Medlists mlrs ON pp.ReferenceSourceMID = mlrs.MedlistsId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND (pi.inactive <> 1 OR pi.inactive is NULL) AND pp.patientprofileid = pi.patientprofileid
AND --Filter on Date
a.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
-- I want a filter on EligibilityVerifiedDate RANGE like for "a.ApptStart"
AND --Filter on doctor
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND a.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND a.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND (?APPTTYPE.ITEMDATA.U? = 1 OR ?APPTTYPE.ITEMDATA.U? = 2)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-03-08 : 08:38:33
Please post some sample data and expected output. The more specific you will be, more accurate answers you will get.

And please follow some basic indentation rules and use [code] tags to retain that formatting.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

ccrespo
Yak Posting Veteran

59 Posts
Posted - 2007-03-08 : 09:03:20
Sorry, I didn't realize it got rid of the formatting. I reposted the whole stored procedure. I'm also not really sure how to test the stored procedure within SQL Query Analyzer, it came with our software and I'm just trying to edit it to exlude a certain date range. We report from this with crystal reports. The field that im having trouble with Ins1EligVerifiedDate is in the format '2007/03/01 00:00:00.00', at run time the parameter ?DATERANGE.DATE1? is replaced with a date like, '03/01/2007'

[CODE]
SET NOCOUNT ON
CREATE TABLE #ELIG (
AppointmentsId int,
ApptSetId int,
ApptSetName varchar(120),
ApptChainId int,
ApptChainName varchar(120),
InsCaseName varchar(120),
Date DateTime,
StrtHour varchar(2),
StrtMin varchar(2),
StpHour varchar(2),
StpMin varchar(2),
Facility varchar(120),
ResourceStart DateTime,
ApptStop DateTime,
DoctorId int,
DoctorName varchar(120),
ApptStatus varchar(120),
ApptType varchar(120),
PatientName varchar(120),
PatientId int,
PatientProfileId int,
Phone1 varchar(50),
Birthdate DateTime,
Phone2 varchar(50),
Phone1Type varchar(6),
Phone2Type varchar(6),
Resource varchar(120),
Notes varchar (500),
Financial varchar(120),
RefSource varchar(120),
Flag varchar (6),
InsuranceCarrier1 varchar(150),
InsuranceCarrier2 varchar(150),
Ins1Phone varchar(50),
Ins2Phone varchar(50),
InsuredID1 varchar(75),
InsuredID2 varchar(75),
Ins1EligVerified smallint,
Ins2EligVerified smallint,
Ins1EligVerifiedDate DateTime, --Column is created as DateTime
Ins2EligVerifiedDate DateTime,
Ins1EligVerifiedBy varchar(75),
Ins2EligVerifiedBy varchar(75),
Ins1DeduuctibleRemaining varchar (10),
Ins2DeduuctibleRemaining varchar (10),
Ins1DeductibleDate DateTime,
Ins2DeductibleDate DateTime,
Ins1CardEffectiveDate DateTime,
Ins2CardEffectiveDate DateTime,
Ins1CardTerminationDate DateTime,
Ins2CardTerminationDate DateTime,
Ins1Inactive smallint,
Ins2Inactive smallint,
Ins1Auth varchar(50),
Ins2Auth varchar(50),
Ins1AuthIssueDate DateTime,
Ins2AuthIssueDate DateTime,
Ins1AuthExpDate DateTime,
Ins2AuthExpDate DateTime,
ApptKind smallint
)

CREATE TABLE #INS1 (
PatientProfileId int,
InsuranceCarrier1 varchar(50),
Ins1Phone varchar(50),
InsuredID1 varchar(25),
Ins1EligVerified smallint,
Ins1EligVerifiedDate DateTime, --It is still date time here
Ins1EligVerifiedBy varchar(25),
Ins1DeduuctibleRemaining varchar (10),
Ins1DeductibleDate DateTime,
Ins1CardEffectiveDate DateTime,
Ins1CardTerminationDate DateTime,
Ins1Inactive smallint,
Ins1Auth varchar(50),
Ins1AuthIssueDate DateTime,
Ins1AuthExpDate DateTime
)

CREATE TABLE #INS2 (
PatientProfileId int,
InsuranceCarrier2 varchar(50),
Ins2Phone varchar(50),
InsuredID2 varchar(25),
Ins2EligVerified smallint,
Ins2EligVerifiedDate DateTime,
Ins2EligVerifiedBy varchar(25),
Ins2DeduuctibleRemaining varchar (10),
Ins2DeductibleDate DateTime,
Ins2CardEffectiveDate DateTime,
Ins2CardTerminationDate DateTime,
Ins2Inactive smallint,
Ins2Auth varchar(50),
Ins2AuthIssueDate DateTime,
Ins2AuthExpDate DateTime
)

INSERT INTO #ELIG
SELECT a.AppointmentsId,
aset.ApptSetId,
dbo.asGetApptSetName(ISNULL(aset.ApptSetId,0),a.AppointmentsId) ApptSetName,
ac.ApptChainId,
ac.Name ApptChainName,
c.Name,
Date=convert(datetime,convert(char(12),a.ApptStart,1)),
StrtHour=convert(varchar(2),datepart(hour,a.ApptStart)),
StrtMin=convert(varchar(2),datepart(minute,a.ApptStart)),
StpHour=convert(varchar(2),datepart(hour,a.ApptStop)),
StpMin=convert(varchar(2),datepart(minute,a.ApptStop)),
Facility= dff.Listname,
convert(datetime,a.ApptStart) AS ResourceStart,
a.ApptStop,
a.DoctorId,
dfd.Listname AS DoctorName,
mlas.Description AS Status,
at.Name AS Type,
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', '+ ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) AS PatientName,
pp.PatientId,
pp.PatientProfileId,
dbo.FormatPhone(pp.Phone1,1) AS Phone1,
pp.Birthdate,
dbo.FormatPhone(pp.Phone2,1) AS Phone2,
substring(pp.Phone1Type,1,1) AS Phone1Type,
substring(pp.Phone2Type,1,1) AS Phone2Type,
dfr.Listname AS Resource,
convert(varchar(255), a.Notes) AS Notes,
CASE WHEN ISNULL(a.CasesId,0) = 0 THEN mlfc.Description ELSE mlfcc.Description END AS Financial,
mlrs.Description AS RefSource,
Flag = convert(varchar(50), 'Time'),
NULL AS InsuranceCarrier1,
NULL AS InsuranceCarrier2,
NULL AS Ins1Phone,
NULL AS Ins2Phone,
NULL AS InsuredID1,
NULL AS InsuredID2,
NULL AS Ins1EligVerified,
NULL AS Ins2EligVerified,
NULL AS Ins1EligVerifiedDate,
NULL AS Ins2EligVerifiedDate,
NULL AS Ins1EligVerifiedBy,
NULL AS Ins2EligVerifiedBy,
NULL AS Ins1DeduuctibleRemaining,
NULL AS Ins2DeduuctibleRemaining,
NULL AS Ins1DeductibleDate,
NULL AS Ins2DeductibleDate,
NULL AS Ins1CardEffectiveDate,
NULL AS Ins2CardEffectiveDate,
NULL AS Ins1CardTerminationDate,
NULL AS Ins2CardTerminationDate,
NULL AS Ins1Inactive,
NULL AS Ins2Inactive,
NULL AS Ins1Auth,
NULL AS Ins2Auth,
NULL AS Ins1AuthIssueDate,
NULL AS Ins2AuthIssueDate,
NULL AS Ins1AuthExpDate,
NULL AS Ins2AuthExpDate,
ApptKind = 1

FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId,0) = ISNULL(ci.CasesId,0) AND ci.PatientProfileId = pp.PatientProfileId
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId AND pi.OrderForClaims = 1
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
LEFT JOIN Medlists mlfc ON pp.FinancialClassMId = mlfc.MedlistsId
LEFT JOIN Cases c ON a.CasesId = c.CasesId
LEFT JOIN Medlists mlfcc ON c.FinancialClassMId = mlfcc.MedlistsId
LEFT JOIN Medlists mlrs ON pp.ReferenceSourceMID = mlrs.MedlistsId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND (pi.inactive <> 1 OR pi.inactive is NULL) AND pp.patientprofileid = pi.patientprofileid
AND --Filter on Date
a.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
AND --Filter on doctor
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND a.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND a.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND (?APPTTYPE.ITEMDATA.U? = 1 OR ?APPTTYPE.ITEMDATA.U? = 2)

INSERT INTO #INS1
SELECT PatientProfileId = pp.PatientProfileId,
InsuranceCarrier = ic.Listname,
dbo.FormatPhone(ic.Phone1,1) AS InsPhone,
InSuredID = pi.InsuredID,
EligibilityVerified = pi.EligibilityVerified,
EligibilityVerifiedDate = convert(char(12),pi.EligibilityVerifiedDate,1), --Then its converted into CHAR here
EligibilityVerifiedBy = pi.EligibilityVerifiedBy,
DeductibleRemaining = pi.DeductibleRemaining,
DeductibleDate = convert(char(12), pi.DeductibleDate, 1),
InsCardEffectiveDate = convert(char(12), pi.InsCardEffectiveDate,1),
InsCardTerminationDate = convert(char(12), pi.InsCardTerminationDate, 1),
pi.inactive,
ci.Number,
ci.IssuedDate,
c.AuthExpirationDate
FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId,0) = ISNULL(ci.CasesId,0) AND ci.PatientProfileId = pp.PatientProfileId
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId AND pi.OrderForClaims = 1
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
LEFT JOIN Medlists mlfc ON pp.FinancialClassMId = mlfc.MedlistsId
LEFT JOIN Cases c ON a.CasesId = c.CasesId
LEFT JOIN Medlists mlfcc ON c.FinancialClassMId = mlfcc.MedlistsId
LEFT JOIN Medlists mlrs ON pp.ReferenceSourceMID = mlrs.MedlistsId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND (pi.inactive <> 1 OR pi.inactive is NULL) AND pp.patientprofileid = pi.patientprofileid
AND --Filter on Date
a.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
-- I tried to plug in the above format with pi.EligibilityVerifiedDate and it doesn't return any data
AND --Filter on doctor
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND a.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND a.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND (?APPTTYPE.ITEMDATA.U? = 1 OR ?APPTTYPE.ITEMDATA.U? = 2)


INSERT INTO #INS2
SELECT PatientProfileId = pp.PatientProfileId,
InsuranceCarrier = ic.Listname,
dbo.FormatPhone(ic.Phone1,1) AS InsPhone,
InSuredID = pi.InsuredID,
EligibilityVerified = pi.EligibilityVerified,
EligibilityVerifiedDate = convert(char(12),pi.EligibilityVerifiedDate,1),
EligibilityVerifiedBy = pi.EligibilityVerifiedBy,
DeductibleRemaining = pi.DeductibleRemaining,
DeductibleDate = convert(char(12), pi.DeductibleDate, 1),
InsCardEffectiveDate = convert(char(12), pi.InsCardEffectiveDate,1),
InsCardTerminationDate = convert(char(12), pi.InsCardTerminationDate, 1),
pi.inactive,
ci.Number,
ci.IssuedDate,
c.AuthExpirationDate
FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId,0) = ISNULL(ci.CasesId,0) AND ci.PatientProfileId = pp.PatientProfileId
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId AND pi.OrderForClaims = 2
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
LEFT JOIN Medlists mlfc ON pp.FinancialClassMId = mlfc.MedlistsId
LEFT JOIN Cases c ON a.CasesId = c.CasesId
LEFT JOIN Medlists mlfcc ON c.FinancialClassMId = mlfcc.MedlistsId
LEFT JOIN Medlists mlrs ON pp.ReferenceSourceMID = mlrs.MedlistsId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND (pi.inactive <> 1 OR pi.inactive is NULL) AND pp.patientprofileid = pi.patientprofileid
AND -- Filter on Date
a.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
AND --Filter on doctor
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND a.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND a.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND (?APPTTYPE.ITEMDATA.U? = 1 OR ?APPTTYPE.ITEMDATA.U? = 2)

UPDATE #ELIG
SET
InsuranceCarrier1 = ins1.InsuranceCarrier1,
InsuranceCarrier2 = ins2.InsuranceCarrier2,
Ins1Phone = ins1.Ins1Phone,
Ins2Phone = ins2.Ins2Phone,
InsuredID1 = ins1.InsuredID1,
InsuredID2 = ins2.InsuredID2,
Ins1EligVerified = ins1.Ins1EligVerified,
Ins2EligVerified = ins2.Ins2EligVerified ,
Ins1EligVerifiedDate = ins1.Ins1EligVerifiedDate, --I want to exclude the range from #INS1 before it goes into #ELIG
Ins2EligVerifiedDate = ins2.Ins2EligVerifiedDate,
Ins1EligVerifiedBy = ins1.Ins1EligVerifiedBy,
Ins2EligVerifiedBy = ins2.Ins2EligVerifiedBy,
Ins1DeduuctibleRemaining = ins1.Ins1DeduuctibleRemaining,
Ins2DeduuctibleRemaining = ins2.Ins2DeduuctibleRemaining,
Ins1DeductibleDate = ins1.Ins1DeductibleDate,
Ins2DeductibleDate = ins2.Ins2DeductibleDate,
Ins1CardEffectiveDate = ins1.Ins1CardEffectiveDate,
Ins2CardEffectiveDate = ins2.Ins2CardEffectiveDate,
Ins1CardTerminationDate = ins1.Ins1CardTerminationDate,
Ins2CardTerminationDate = ins2.Ins2CardTerminationDate,
Ins1Inactive = ins1.Ins1Inactive,
Ins2Inactive = ins2.Ins2Inactive,
Ins1Auth = ins1.Ins1Auth,
Ins2Auth = ins2.Ins2Auth,
Ins1AuthIssueDate = ins1.Ins1AuthIssueDate,
Ins2AuthIssueDate = ins2.Ins2AuthIssueDate,
Ins1AuthExpDate = ins1.Ins1AuthExpDate,
Ins2AuthExpDate = ins2.Ins2AuthExpDate

FROM #ELIG E
JOIN #INS1 ins1 ON E.patientprofileid = ins1.patientprofileid
LEFT JOIN #INS2 ins2 ON E.patientprofileid = ins2.patientprofileid

SELECT * FROM #ELIG
-- Or maybe I could do the range from #ELIG
-- WHERE Ins1EligVerifiedDate <= '03/01/2007' AND Ins1EligVerifiedDate <'03/01/2007' [/code]
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-03-08 : 09:09:29
Something like this?

UPDATE #ELIG
SET
	InsuranceCarrier1 = ins1.InsuranceCarrier1,
	InsuranceCarrier2 = ins2.InsuranceCarrier2,
	Ins1Phone = ins1.Ins1Phone,
	Ins2Phone = ins2.Ins2Phone,
	InsuredID1 = ins1.InsuredID1,
	InsuredID2 = ins2.InsuredID2,
	Ins1EligVerified = ins1.Ins1EligVerified,
	Ins2EligVerified = ins2.Ins2EligVerified ,
	Ins1EligVerifiedDate = ins1.Ins1EligVerifiedDate, --I want to exclude the range from #INS1 before it goes into #ELIG
	Ins2EligVerifiedDate = ins2.Ins2EligVerifiedDate,
	Ins1EligVerifiedBy = ins1.Ins1EligVerifiedBy,
	Ins2EligVerifiedBy = ins2.Ins2EligVerifiedBy,
	Ins1DeduuctibleRemaining = ins1.Ins1DeduuctibleRemaining,
	Ins2DeduuctibleRemaining = ins2.Ins2DeduuctibleRemaining,
	Ins1DeductibleDate = ins1.Ins1DeductibleDate,
	Ins2DeductibleDate = ins2.Ins2DeductibleDate,
	Ins1CardEffectiveDate = ins1.Ins1CardEffectiveDate,
	Ins2CardEffectiveDate = ins2.Ins2CardEffectiveDate,
	Ins1CardTerminationDate = ins1.Ins1CardTerminationDate,
	Ins2CardTerminationDate = ins2.Ins2CardTerminationDate,
	Ins1Inactive = ins1.Ins1Inactive,
	Ins2Inactive = ins2.Ins2Inactive,
	Ins1Auth = ins1.Ins1Auth,
	Ins2Auth = ins2.Ins2Auth,
	Ins1AuthIssueDate = ins1.Ins1AuthIssueDate,
	Ins2AuthIssueDate = ins2.Ins2AuthIssueDate,
	Ins1AuthExpDate = ins1.Ins1AuthExpDate,
	Ins2AuthExpDate = ins2.Ins2AuthExpDate
FROM #ELIG E 	
	JOIN #INS1 ins1 ON E.patientprofileid = ins1.patientprofileid
	LEFT JOIN #INS2 ins2 ON E.patientprofileid = ins2.patientprofileid
Where
        dateadd(day, 0, datediff(day, 0, ins1.Ins1EligVerifiedDate)) <='20070301'


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

ccrespo
Yak Posting Veteran

59 Posts
Posted - 2007-03-08 : 09:30:01
[code]Where
dateadd(day, 0, datediff(day, 0, ins1.Ins1EligVerifiedDate)) <='20070301'[/code]Sorry I had a typo on the last part of my post but I need a range where the dates don't fall within
[code]WHERE Ins1EligVerifiedDate <= '03/01/2007' AND Ins1EligVerifiedDate >'03/03/2007'[/code]But when running this at runtime
dateadd(day, 0, datediff(day, 0, ins1.Ins1EligVerifiedDate)) <=?'DATERANGE.DATE1?'
where ?DATERANGE.DATE1? is in the format '03/01/2007', thats probably where I'm having the trouble. Can i convert that to '20070301'.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-03-08 : 09:41:55
How the date range parameter is getting passed to the SP? What is the data type of the parameter?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

ccrespo
Yak Posting Veteran

59 Posts
Posted - 2007-03-08 : 09:49:12
It is being passed over as text, in the format 'MM/DD/YYYY'. The formula you gave me doesn't it have to be 'YYYYMMDD'?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-03-08 : 12:38:03
Perhaps I missed something, but.. Assuming that the date field in your query is a date, you should be able to CAST or CONVERT the incoming parameter to a DATETIME and just do a date comparison as usual.

-Ryan
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site