Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See attached example.
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.........
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
];