DateDiff problem - SQL Server Forums

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

Transact-SQL (2000)

DateDiff problem

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-26 : 12:35:45

I need some coding assistance! I need the number of Days between the Date of Service (pv.visit) and the First Filed Date (pv.FirstFiledDate). Currently, this code seperates out the Electronic Claims and Paper Claims. Can anyone look at this code and let me know where I went wrong?! Any help is greatly appreciated.

set nocount on

declare @startdate datetime,
@enddate datetime,
@ticketnumber varchar(20)

set @ticketnumber = CAST(NULL as VARCHAR(20))
set @startdate = ISNULL(NULL,'1/1/1900')
set @enddate = DATEADD(DAY,1,ISNULL(NULL,'1/1/3000'))

SELECT ic.ListName AS CarrierName,
ic.address1 as CarrierAddress,
ic.city as CarrierCity,
ic.state as CarrierState,
ic.zip as CarrierZip,
icc.ClaimPayerId,
pv.Ticketnumber,
pv.visit as DateOfService,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
ec.Charges as VisitChargesFiled,
ec.Procedures as VisitProceduresFiled,
pv.FirstFiledDate AS FirstFiledDate,
-- DATEDIFF(day,pv.Visit,pv.firstfileddate)AS Days,
ecf.FileTransmitted,
CAST(NULL as DATETIME) as ClaimPrinted,
ecf.FiledBy,
ecf.SubmissionNumber,
ecf.name as ClaimFileName,
ch.ClearinghouseName,
fm.description as FilingMethod,
'Electronic' as FilingType
into #temp

FROM EDIClaimFile ecf

INNER JOIN EDIClaim ec ON ecf.EDIClaimFileId = ec.EDIClaimFileId
INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId
INNER JOIN InsuranceCarrierCompany icc ON ic.InsuranceCarriersId = icc.InsuranceCarriersId
INNER JOIN patientvisit pv on ec.patientvisitID = pv.patientvisitID
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on ec.filingmethodMID = fm.medlistsID
INNER JOIN clearinghouse ch on ecf.clearinghouseID = ch.clearinghouseID

WHERE ecf.FileTransmitted >= @startdate
AND ecf.FileTransmitted < @enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Carrier
(
(NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

-------------------------------------------- Paper Claims ------------------------------------------
INSERT INTO #temp (Carriername, --CarrierAddress, CarrierCity, CarrierState, CarrierZip,
ticketnumber, dateofservice, patientname, patientID, visitchargesfiled,
visitproceduresfiled, ClaimPrinted, FirstFiledDate, /*Days,*/ filedby, claimfilename, clearinghousename,
filingmethod, FilingType)

SELECT ISNULL(pvpc.Name,'No Carrier') AS CarrierName,
pv.Ticketnumber,
pv.visit as DateOfService,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
pvpc.Charges as VisitChargesFiled,
pvpc.Procedures as VisitProceduresFiled,
pv.firstFiledDate AS FirstFiledDate,
-- DATEDIFF(day,pv.Visit,pv.firstfileddate)AS Days,
pvpc.created as ClaimPrinted,
pvpc.createdby as FiledBy,
'Paper' as claimfilename,
'' as clearinghousename,
fm.description as FilingMethod,
'Paper' as FilingType

FROM PatientvisitPaperClaim pvpc

INNER JOIN patientvisit pv on pvpc.patientvisitID = pv.patientvisitID
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on pvpc.filingmethodMID = fm.medlistsID

WHERE pvpc.created >= @startdate
AND pvpc.created < @enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
-- AND --Filter on Carrier
-- (
-- (NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
-- (NULL IS NULL)
-- )
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

IF '1' = '1'
BEGIN
select *
from #temp
order by ticketnumber
END

IF '1' = '2'
BEGIN
select *
from #temp
where filingtype = 'Electronic'
order by ticketnumber
END

IF '1' = '3'
BEGIN
select *
from #temp
where filingtype = 'Paper'
order by ticketnumber
END

drop table #temp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 12:39:07

It is not DATEDIFF handling that causes your problem.
See http://www.sqlteam.com/item.asp?ItemID=26922

I think it is your NULL handling that causes problems.

Peter Larsson
Helsingborg, Sweden

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-26 : 12:53:23

The Code Works Fine with the NULL Handling --- This is the following error message I get when running in Query Analyzer.

Server: Msg 260, Level 16, State 1, Line 80
Disallowed implicit conversion from data type datetime to data type int, table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________000000002E3D', column 'DateSpan'. Use the CONVERT function to run this query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 13:10:53

can you post your #temp table DDL ?

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-26 : 13:16:31

I figured it out finally -- Stupid Human Error. I was out of sequence with my Insert Into ... DOH!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources