Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get first of month from end of month 1

Status
Not open for further replies.

azoe

Technical User
Feb 21, 2005
175
US
When I execute the stored procedure below I get the following error:

Server: Msg 241, Level 16, State 1, Procedure aa_dch_ChgDOS_To_ClmCreate_LagTime, Line 19
Syntax error converting datetime from character string.

I'm sure it has to do with this bit of code that I used from another stored procedure we already had - and I don't understand it all.


DECLARE @StartDate DATETIME
DECLARE @strStartDate varchar(28)
DECLARE @EndDate DATETIME

select @EndDate = max(create_timestamp),
@strStartDate = substring(convert(varchar(28), max(create_timestamp), 102),1,6) + '01'
from claims

SET @StartDate = CONVERT(datetime, @strStartDate)

What I want to do is get the most recent - max - date and use that to get the first of that month. The other procedure it is used in gets the max date from a varchar date. This is a timestamp and I don't know how to change it so it will work. I thought maybe it was the Set statement but if I delete that then I end up with an empty table.

Also, I want to put the date (create_timestamp) in the table but I think I've read on this site that I can't create a timestamp field for it - but is datetime ok?

Thank you -


Code:
ALTER     proc dbo.aa_dch_ChgDOS_To_ClmCreate_LagTime
as
--For chgDOS to ClmCreate Lag Time  srb  3-14-06

/* Convert date strings to dates */
DECLARE @StartDate DATETIME
DECLARE @strStartDate  varchar(28)
DECLARE @EndDate DATETIME

DROP TABLE CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime

select @EndDate = max(create_timestamp), 
       @strStartDate = substring(convert(varchar(28), max(create_timestamp), 102),1,6) + '01'
from claims

SET @StartDate = CONVERT(datetime, @strStartDate) 

create table CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime 
	(enc_nbr numeric(9) not null,
	transmitter_id varchar(80) null,
	payer_name varchar(40) not null,
	service_from_date varchar(8) not null,
	create_timestamp datetime null,
	ChgDosClmCreate numeric(10)
)

Insert into CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime 
	(enc_nbr,
	transmitter_id,
	payer_name,
	service_from_date,
	create_timestamp,
	ChgDosClmCreate
)

SELECT 
distinct 
pe.enc_nbr,
pp.transmitter_id,
pm.payer_name, 
cd.service_from_date, 
c.create_timestamp as ClaimCreate,
datediff(dd, cd.service_from_date, c.create_timestamp)-- as [ChgDOS-ClmProc]

FROM
claim_requests cr
inner join claims c on c.claim_id = cr.claim_id
inner join claim_detail cd on c.claim_id = cd.claim_id
inner join claim_charges cc on (cc.claim_id = c.claim_id and cc.enc_id = c.enc_id)
--inner join charges chg on (chg.source_id = c.enc_id  and cc.charge_id = chg.charge_id and cd.line_charges = chg.amt)
inner join patient_encounter pe on c.enc_id = pe.enc_id
left outer join practice_payers pp on pp.payer_id = c.payer_id
left outer join payer_mstr pm on c.payer_id = pm.payer_id

WHERE 
pp.transmitter_id in ('00900', '00156', '87726', '62308', '60054')
and c.cob = 1
and c.create_timestamp between @StartDate and @EndDate
and datediff(dd, cd.service_from_date, c.create_timestamp)<> 0

order by pp.transmitter_id, pe.enc_nbr


Select Count(*)
From CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime

SELECT 
distinct 
enc_nbr,
transmitter_id,
payer_name, 
service_from_date, 
create_timestamp as ClaimCreate,
ChgDosClmCreate

From 
CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top