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


      Ishould get the output in below format 



      IDcount of daysmonth


      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:



                  load distinct *, month(Date) as Month;

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

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



                            , 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