Post reply

How to add a record when the data is not in the table

  • February 13, 2015 at 5:08 am

    #314585

    I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.

    Any thoughts?

    create table dbo.Scott_TEST

    (

    YearMonth char(9),

    Gender char(1),

    NumOfFemale int,

    NumOfMale int

    )

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2013-10','F',2,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2013-11','F',1,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2013-12','F',1,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2013-12','M',0,3)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-01','M',0,2)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-02','F',2,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-02','M',0,4)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-03','F',4,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-03','M',0,6)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-04','F',4,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-04','M',0,3)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-05','F',5,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-05','M',0,10)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-06','F',1,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-06','M',0,2)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-07','F',2,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-07','M',0,3)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-08','F',2,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-08','M',0,5)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-09','F',1,0)

    insert into dbo.Scott_TEST(YearMonth, Gender, NumOfFemale, NumOfMale) values( '2014-09','M',0,2)

  • February 13, 2015 at 5:32 am

    #1777485

    I would query a calendar table (or something similar) to fetch all the months needed, cross join this with the two genders (so that you have each month twice) and then left outer join this to your table.

    If the results is NULL, it means there was no readmission for that gender for that month.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 13, 2015 at 5:35 am

    #1777486

    Thanx.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply