Steynsk
asked on
calculate the normal age and corrected age in weeks and days
Hi Experts,
For our hospital's prenatal department I need to retreive the normal age and corrected age in weeks and days of a prenatal patiënt.
Last week I've posted a similar request:
https://www.experts-exchange.com/questions/26864306/Calculate-the-age-and-corrected-age-of-prenatal-patients.html
Now we have some additional questions based on the same situation:
What do we know?
We do know the:
• birth date (stored in a nvarchar field) name “geboortedatum” format yyyy-mm-dd
• pregnancy weeks (stored in a nvarchar field) name “Zwangerschapsduur_weken” format example “27”
• pregnancy days (stored in a nvarchar field) name “Zwangerschapsduur_dagen” format example “4”
We need to know (based on the current date):
• The current uncorrected age (based on the birth date in the database) in weeks and days (each in a colum)
• The corrected age (gestational age) in weeks and days = 280 (the duration of a normal pregnancy in days) minus the current uncorrected age in days (both values presented in its own colum).
Besides the points you will receive our hospitals gratitude for your contribution to public healthcare in the Netherlands.
For our hospital's prenatal department I need to retreive the normal age and corrected age in weeks and days of a prenatal patiënt.
Last week I've posted a similar request:
https://www.experts-exchange.com/questions/26864306/Calculate-the-age-and-corrected-age-of-prenatal-patients.html
Now we have some additional questions based on the same situation:
What do we know?
We do know the:
• birth date (stored in a nvarchar field) name “geboortedatum” format yyyy-mm-dd
• pregnancy weeks (stored in a nvarchar field) name “Zwangerschapsduur_weken” format example “27”
• pregnancy days (stored in a nvarchar field) name “Zwangerschapsduur_dagen” format example “4”
We need to know (based on the current date):
• The current uncorrected age (based on the birth date in the database) in weeks and days (each in a colum)
• The corrected age (gestational age) in weeks and days = 280 (the duration of a normal pregnancy in days) minus the current uncorrected age in days (both values presented in its own colum).
Besides the points you will receive our hospitals gratitude for your contribution to public healthcare in the Netherlands.
can you please provide an e.g with real time data
ASKER
I don't know what you expect to receive. Maybe this helps:
This query works gives only totoal days old and corrected birthdate:
SELECT DATEDIFF(DAY, CONVERT(DATETIME, geboortedatum), GETDATE()) as Days_Old,
geboortedatum AS Uncorrected_Birth_Date,
DATEADD(DAY, (280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen)), geboortedatum ) Corrected_Birth_Date,
280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen) AS Days_Early
from Neonatologie_Anamnese
This query works gives only totoal days old and corrected birthdate:
SELECT DATEDIFF(DAY, CONVERT(DATETIME, geboortedatum), GETDATE()) as Days_Old,
geboortedatum AS Uncorrected_Birth_Date,
DATEADD(DAY, (280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen)),
280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen) AS Days_Early
from Neonatologie_Anamnese
If you already have the corrected_birth_date then you should be able to calculate the corrected age.
datediff(day, getdate(), corrected_birth_date) as corrected_age_in_days
datediff(week, getdate(), corrected_birth_date) as corrected_age_in_weeks
datediff(day, getdate(), corrected_birth_date) as corrected_age_in_days
datediff(week, getdate(), corrected_birth_date) as corrected_age_in_weeks
ASKER
Yes but what we need is:
The number of complete weeks and the number of days that remain.
25 days will result in 3 weeks and 4 days.
Sorry fot the misunderstanding.
The number of complete weeks and the number of days that remain.
25 days will result in 3 weeks and 4 days.
Sorry fot the misunderstanding.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi vandalesm,
No sorry both don't show the right result.
I've added the bigger picture (see SQL script) and will attach a csv with the output.
Remember that in this sheet "Weeks_of_pregnancy" means complete weeks and "Days_of_pregnancy" mains the remaining days after removal of the complete weeks. And this is the same with "Weeks_of_pregnancy" and "Days_of_pregnancy".
And that is what I need for "Corrected_age_weeks" and "Corrected_age_days".
As you can see both last columns don't show the right results
Thanks
No sorry both don't show the right result.
I've added the bigger picture (see SQL script) and will attach a csv with the output.
Remember that in this sheet "Weeks_of_pregnancy" means complete weeks and "Days_of_pregnancy" mains the remaining days after removal of the complete weeks. And this is the same with "Weeks_of_pregnancy" and "Days_of_pregnancy".
And that is what I need for "Corrected_age_weeks" and "Corrected_age_days".
As you can see both last columns don't show the right results
Thanks
SELECT geboortedatum AS Birthdate,
DATEDIFF(DAY, CONVERT(DATETIME, geboortedatum), GETDATE()) as Total_days_old,
DATEDIFF(DAY, CONVERT(DATETIME, Geboortedatum), GETDATE()) / 7 AS Weeks_old,
DATEDIFF(DAY, CONVERT(DATETIME, geboortedatum), GETDATE()) - DATEDIFF(DAY, CONVERT(DATETIME, Geboortedatum), GETDATE()) / 7*7 AS Days_old,
zwangerschapsduur_weken AS weeks_of_pregnancy,
zwangerschapsduur_dagen AS days_of_pregnancy,
CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen) AS Total_days_of_pregnancy,
280 - (CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen)) AS Days_urly,
DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum)AS Corrected_birthdate,
DATEDIFF(week, GETDATE(), DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum)) AS Corrected_age_weeks,
DATEDIFF(week, GETDATE(), DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum)) - DATEDIFF(day, GETDATE(), DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen),Geboortedatum)) / 7 * 7 AS Corrected_age_days
FROM Neonatologie_Anamnese
WHERE (Geboortedatum IS NOT NULL) AND (Zwangerschapsduur_weken IS NOT NULL) AND (Zwangerschapsduur_dagen IS NOT NULL)
Prenatal.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help