Improve company productivity with a Business Account.Sign Up

x
?
Solved

calculate the normal age and corrected age in weeks and days

Posted on 2011-03-11
8
Medium Priority
?
950 Views
Last Modified: 2015-02-16
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:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26864306.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.
0
Comment
Question by:Steynsk
  • 5
  • 2
8 Comments
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35109804
can you please provide an e.g with real time data
0
 
LVL 1

Author Comment

by:Steynsk
ID: 35110040
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

0
 
LVL 1

Expert Comment

by:vandalesm
ID: 35110803
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

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 1

Author Comment

by:Steynsk
ID: 35121405
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.
0
 
LVL 1

Assisted Solution

by:vandalesm
vandalesm earned 2000 total points
ID: 35129444
There are many ways to do this. If you have SQL Server 2008 you can use the modulo (%) operator to get the remainder.

Here is the query base from your given query. This should work in any sql server version.

SELECT
  CORRECTED_AGE_IN_WEEKS =
      DATEDIFF
      (
          week,
          getdate(),
          DATEADD(DAY, (280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen)), geboortedatum )
      ),
  AND_DAYS_REMAINDER =
      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)
FROM Neonatologie_Anamnese

0
 
LVL 1

Author Comment

by:Steynsk
ID: 35139548
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


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)

Open in new window

Prenatal.csv
0
 
LVL 1

Accepted Solution

by:
Steynsk earned 0 total points
ID: 35157281
I've got it!!!


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(DAY, DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum), GETDATE()) / 7 AS Corrected_age_weeks,
        DATEDIFF(week, GETDATE(), DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum)) AS Corrected_age_weeks1,
		DATEDIFF(DAY, DATEADD(DAY, 280 - CONVERT(TINYINT, Zwangerschapsduur_weken) * 7 + CONVERT(TINYINT, Zwangerschapsduur_dagen), Geboortedatum), GETDATE()) / 7 AS Corrected_age_days,
        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_days1
FROM Neonatologie_Anamnese
WHERE (Geboortedatum IS NOT NULL) AND  (Zwangerschapsduur_weken IS NOT NULL) AND (Zwangerschapsduur_dagen IS NOT NULL)

Open in new window

0
 
LVL 1

Author Closing Comment

by:Steynsk
ID: 35187297
Thanks for your help
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question