Count of days based on month

[#1]

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

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

Ishould get the output in below format 

ID	count of days	month
1	13	jan
1	2	feb
2	4	jan
2	1	feb
2	3	mar

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

Thanks in advance

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#3]

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.........

[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   

];