Daily Cost Accumation 1

[okibbbbb]

Hi All,
I have just 3 Field.
DateStart(date),DateFinish(date),Cost(double)

How can I make Output is Cost Accumulation per day like

Day1=Cost Which SUM all UnitCost within Day#1
Day2=Cost Which SUM all UnitCost within Day#2
Day3=Cost Which SUM all UnitCost within Day#3
..and so on until the latest DateFinish.

another word..calculate daily or weekly cost spent so i can display it as daily basis, or weekly basis. i m not able to do it becouse the data entried is not per daily..but per range of date.
Please Help

Thanks for any support.

 

[max1x]

without looking at some datasets, I would assume you can use the CURDATE function and then SUM up for CURDATE.

For intervals u can use DATE_SUB(xx, interval) and SUM it up.

 

[okibbbbb]

Dear max1x
Thanks 4 your direction. let me show the query i made. It works if datestart and finishstart in 1 month (e.g.month=6). I use UNION to combine them, it's mean i will use 11 union to combine them! it will overload MySQL. 1 month is 4 cost. cost is sum in 1 week. But this Query start to wrong result when :
1. datestart--->datefinish lenght is 2 or more month, while i want to display all accumulation cost per day or per week along 1 or more year.

==================================================

SELECT
datestart, datefinish, cost

sum(

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=1
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=1,
cost,

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=1
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )<>1,
cost*(DATEDIFF(DATE_ADD(datestart, INTERVAL(7-DAYOFWEEK(datestart)) DAY),datestart)+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )<>1
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=1,
cost*(DATEDIFF(datefinish,DATE_ADD(datefinish, INTERVAL(1-DAYOFWEEK(datefinish)) DAY))+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( 1 BETWEEN (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 ),
cost*7/(DATEDIFF(datefinish,datestart)+1),

'============'
))))) AS HASIL1

FROM
progress

where month(datestart)>=6 or month(datefinish)<=6
group by (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )

UNION

SELECT
datestart, datefinish, cost

SUM(

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=2
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=2,
cost,

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=2
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )<>2,
cost*(DATEDIFF(DATE_ADD(datestart, INTERVAL(7-DAYOFWEEK(datestart)) DAY),datestart)+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )<>2
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=2,
cost*(DATEDIFF(datefinish,DATE_ADD(datefinish, INTERVAL(1-DAYOFWEEK(datefinish)) DAY))+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( 2 BETWEEN (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 ),
cost*7/(DATEDIFF(datefinish,datestart)+1),

'============'
))))) AS HASIL1

FROM
progress

where month(datestart)>=6 or month(datefinish)<=6
group by (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )

UNION

SELECT
datestart, datefinish, cost

SUM(

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=3
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=3,
cost,

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=3
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )<>3,
cost*(DATEDIFF(DATE_ADD(datestart, INTERVAL(7-DAYOFWEEK(datestart)) DAY),datestart)+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )<>3
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=3,
cost*(DATEDIFF(datefinish,DATE_ADD(datefinish, INTERVAL(1-DAYOFWEEK(datefinish)) DAY))+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( 3 BETWEEN (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 ),
cost*7/(DATEDIFF(datefinish,datestart)+1),
'============'
))))) AS HASIL1

FROM
progress

where month(datestart)>=6 or month(datefinish)<=6
group by (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )


union


SELECT
datestart, datefinish, cost

SUM(
IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=4
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=4,
cost,

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=4
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )<>4 AND MONTH(datefinish)=6,
cost*(DATEDIFF(DATE_ADD(datestart, INTERVAL(7-DAYOFWEEK(datestart)) DAY),datestart)+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )=4
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )<>4 AND MONTH(datefinish)>6,
cost*(DATEDIFF(DATE_ADD(datestart, INTERVAL(7-DAYOFWEEK(datestart)) DAY),datestart)+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )<>4
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )=4,
cost*(DATEDIFF(datefinish,DATE_ADD(datefinish, INTERVAL(1-DAYOFWEEK(datefinish)) DAY))+1)/(DATEDIFF(datefinish,datestart)+1),

IF ( 4 BETWEEN (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 ),
cost*7/(DATEDIFF(datefinish,datestart)+1),

'============'
)))))) AS HASIL1

FROM
progress

where month(datestart)=6 or month(datefinish)=6
group by (WEEK(datestart,5) - WEEK(DATE_SUB(datestart, INTERVAL DAYOFMONTH(datestart)-1 DAY),5)+1 )
AND (WEEK(datefinish,5) - WEEK(DATE_SUB(datefinish, INTERVAL DAYOFMONTH(datefinish)-1 DAY),5)+1 )



===============================