3 Replies Latest reply: May 10, 2013 4:17 AM by Gysbert Wassenaar RSS

    Count of days based on month

    Swathi N

      I have a table in below format and i need to calculate count of days based on month  taking admission and discharge date into account

       

       

      IDAdmission DateDischarge date
      11-Jan-115-Jan-11
      126-Jan-112-Feb-11
      130-Jan-112-Feb-11
      27-Jan-1110-Jan-11
      228-Feb-113-Mar-11

       

      Ishould get the output in below format 

       

       

      IDcount of daysmonth
      113jan
      12feb
      24jan
      21feb
      23mar

       

      please help me out in calculating this your quick response will progress my work fast its very urgent

      Thanks in advance

        • Re: Count of days based on month
          Gysbert Wassenaar

          See attached example.

            • Re: Count of days based on month
              Swathi N

              Thanks Gysbert for your answer it worked partially ....

               

              Their is a problem in one thing if the records are duplicated i mean for same ID ,Admission date and discharge date are same ie  in two rows then it should count only once ,but it it is counting twice

               

              can you please help me out in solving this as well.........

                • Re: Count of days based on month
                  Gysbert Wassenaar

                  The best solution is to prevent duplicated records from being created in the first place. Spank the person that made that error. If that's not an option or the person persists in that perversion than you can try loading the source data with a load distinct somewhere:

                   

                  T1:

                  load distinct *, month(Date) as Month;

                  load *, [Admission Date] + IterNo()-1 as Date

                  While [Admission Date] + IterNo()-1 <= [Discharge Date];

                  LOAD

                            ID

                            , date#([Admission Date],'D-MMM-YY') as [Admission Date]

                            , date#([Discharge Date],'D-MMM-YY') as [Discharge Date]

                  INLINE [

                      ID, Admission Date, Discharge Date

                      1, 1-Jan-11, 5-Jan-11

                     1, 26-Jan-11, 2-Feb-11

                      1, 30-Jan-11, 2-Feb-11

                      2, 7-Jan-11, 10-Jan-11

                      2, 28-Feb-11, 3-Mar-11

                     1, 26-Jan-11, 2-Feb-11   

                  ];