Convertion and concatenating (to get a field) 1

[katrina11]

1.I have AdmitDate (datetime not null) 2011-03-01 00:00:00,00

2.and I have LOS(length of stay numeric data) 1 or 2 or 3 and etc days of stay in hospital

I need to calculate Dischargedate and I do not know what what be
a syntax for convertion, calculating data type and concantinatiing.

Could you please kindly help me with it as I am certainly behind a deadline!

Thank you for the patience and support!

Katrin

 

[djj55]

Try the DATEADD function

DATEADD(day, LOS, AdmitDate)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[katrina11]

Thank you djj!


I also wonder how to create an additiona field, (that is not in any source tables) which would be a result of the calculation above.
Field "difference" should show a difference between doctor office visit (Table2) and hospital discharge (Table1):

Difference=tbl2.ServiceDate-(DATEADD(day, tbl1.LOS, tbl1.AdmitDate))

How can I incorporate field Difference expression into SELECT query?
I see it as the following query

select
field1
,field2
,(tbl2.ServiceDate-(DATEADD(day, tbl1.LOS, tbl1.AdmitDate))) as Difference
into result
FROM Table1
INNER JOIN Table2 on tbl1.claimid=tbl2.claimid...

However ...should a data type be somehow specifiled ? How can I do it in terms of SQL Server syntax?

Thank you very much for your help!

Katrin

 

[simian336]

First you will need to alter the table and add the new field.

Then run an UPDATE statement to update the new field with the aforementioned formula.

Simi

 

[katrina11]

Does it mean that I cannot do it within SELECT Query?
Actually I tried the following way:

select distinct
a1.*
a2.*
,case when a1.LOSp <>0 then (a2.ServiceDateFrom -(DATEADD(day, a1.LOSp, a1.AdmitDate))) else 0 end TimeFrame
FROM Table1 a1 INNER JOIN Table a1 on memberid=memberid where...

System generates an additional fileld "TIMEFRAME"

However I got kind of (1899-05-29 00:00:00.000) values instead of getting a number of days...(3 or 10 or 15)

 

[gmmastros]

Field "difference" should show a difference between doctor office visit (Table2) and hospital discharge (Table1):

There's a DateDiff function for that.

select field1
       ,field2
       ,DateDiff(Day, tbl.AdmitDate, tbl2.ServiceDate) - tbl1.LOS as Difference
into result
FROM Table1
INNER JOIN Table2 on tbl1.claimid=tbl2.claimid...

Look at it this way...

DateDiff will return the number of days between admit date and service date, which is the number of days that have elapsed between admission and service date. So, suppose a person is admitted on a monday, discharged on wednesday, and then visits the doc on Friday.

AdmitDate = 14-Nov-2011
LOS = 3
ServiceDate = 18-Nov-2011

You are looking for the value 2, right? The difference between wednesday (discharge) and Friday (service).

DateDiff between AdmitDate and Service date is 5.
Subtract the LOS: 5 - 3 = 2.

Make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katrina11]

Yes , I got it, thank you!