counting each day for a datespan

[Jumroo]

i have atbale called BATCH which has fields Batch_id, Startdate and end date. i have a start date of 11/2/08 and end date of 11/6/08....i want to create a logic for a monthly report where i need to count each of the day once for

11/1 11/2, 11/3, 11/4, 11/5, 11/6
0 1 1 1 1 1

how can i do it in crystal or SQL?

 

[lbass]

To get a count, you can just use:

datediff("d",{table.startdate},{table.enddate})+1

-LB

 

[Jumroo]

lbass,

i dpn't want the difference....i want the days to be couted for each day of the month.

Input
BATCH_ID START_DATE END_DATE
99999 11/2/2008 11/06/08
88888 11/1/2008 11/03/08

Output 11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/30
99999 0 1 1 1 1 1 0 0
88888 1 1 1 0 0 0 0 0


how to achieve this?

 

[Jumroo]

found a SQL solution...just wanted to keep the post updated


Select batch_id,
SUM( CASE WHEN Day_of_month = 1 THEN 1 ELSE 0 END) AS DAY1,
SUM( CASE WHEN Day_of_month = 2 THEN 1 ELSE 0 END) AS DAY2,
SUM( CASE WHEN Day_of_month = 3 THEN 1 ELSE 0 END) AS DAY3,
SUM( CASE WHEN Day_of_month = 4 THEN 1 ELSE 0 END) AS DAY4,
SUM( CASE WHEN Day_of_month = 5 THEN 1 ELSE 0 END) AS DAY5,
SUM( CASE WHEN Day_of_month = 6 THEN 1 ELSE 0 END) AS DAY6,
SUM( CASE WHEN Day_of_month = 7 THEN 1 ELSE 0 END) AS DAY7,
SUM( CASE WHEN Day_of_month = 8 THEN 1 ELSE 0 END) AS DAY8,
SUM( CASE WHEN Day_of_month = 9 THEN 1 ELSE 0 END) AS DAY9,
SUM( CASE WHEN Day_of_month = 10 THEN 1 ELSE 0 END) AS DAY10,
SUM( CASE WHEN Day_of_month = 11 THEN 1 ELSE 0 END) AS DAY11,
SUM( CASE WHEN Day_of_month = 12 THEN 1 ELSE 0 END) AS DAY12,
SUM( CASE WHEN Day_of_month = 13 THEN 1 ELSE 0 END) AS DAY13,
SUM( CASE WHEN Day_of_month = 14 THEN 1 ELSE 0 END) AS DAY14,
SUM( CASE WHEN Day_of_month = 15 THEN 1 ELSE 0 END) AS DAY15,
SUM( CASE WHEN Day_of_month = 16 THEN 1 ELSE 0 END) AS DAY16,
SUM( CASE WHEN Day_of_month = 17 THEN 1 ELSE 0 END) AS DAY17,
SUM( CASE WHEN Day_of_month = 18 THEN 1 ELSE 0 END) AS DAY18,
SUM( CASE WHEN Day_of_month = 19 THEN 1 ELSE 0 END) AS DAY19,
SUM( CASE WHEN Day_of_month = 20 THEN 1 ELSE 0 END) AS DAY20,
SUM( CASE WHEN Day_of_month = 21 THEN 1 ELSE 0 END) AS DAY21,
SUM( CASE WHEN Day_of_month = 22 THEN 1 ELSE 0 END) AS DAY22,
SUM( CASE WHEN Day_of_month = 23 THEN 1 ELSE 0 END) AS DAY23,
SUM( CASE WHEN Day_of_month = 24 THEN 1 ELSE 0 END) AS DAY24,
SUM( CASE WHEN Day_of_month = 25 THEN 1 ELSE 0 END) AS DAY25,
SUM( CASE WHEN Day_of_month = 26 THEN 1 ELSE 0 END) AS DAY26,
SUM( CASE WHEN Day_of_month = 27 THEN 1 ELSE 0 END) AS DAY27,
SUM( CASE WHEN Day_of_month = 28 THEN 1 ELSE 0 END) AS DAY28,
SUM( CASE WHEN Day_of_month = 29 THEN 1 ELSE 0 END) AS DAY29,
SUM( CASE WHEN Day_of_month = 30 THEN 1 ELSE 0 END) AS DAY30,
SUM( CASE WHEN Day_of_month = 31 THEN 1 ELSE 0 END) AS DAY31
from
(
Select batch_id,start_date,end_date,CAL.DAY_OF_MONTH from batch bat

left outer JOIN
(
Select CALENDAR_DATE,DAY_OF_MONTH from SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE >= ADD_MONTHS(CURRENT_DATE,-1) - DAYOFMONTH(ADD_MONTHS(CURRENT_DATE, -1))+1
AND CALENDAR_DATE < ADD_MONTHS(CURRENT_DATE,0) - DAYOFMONTH(ADD_MONTHS(CURRENT_DATE, 0))+1
) CAL
ON CAL.CALENDAR_DATE >= bat.start_date
and CAL.CALENDAR_DATE <= bat.end_date

) BATCH_DISTRIBUTION_monthly