how to make a tendays database from daily data

[abrew]

hi

 

i have daily data table

it's contain

 

ID, Date, and Rain

 

i need to make a new table but tendays period ,,,

 

any clues ,,, will be very greatfull

 

thanks a lot ,,,

[johnny86]

Why don't you just query your current table with SELECT * FROM daily_data WHERE date_field BETWEEN [some_date] AND [some_date+10days];

[abrew]

thx John ,,,

but u'r query result only one output

 

i mean i need it  Total Rain every (10-day) for all long data such from year 2000 to 2012

so the output will be like this

 

TIME                Rain

tenday-1          10

tenday-2          5

tenday-3          32

,

,

,

 

any more idea ???

[Barand]

Are the 10-day periods like those in "A" below or like "B" ?

January

1        10        20        30

---------|---------|---------|-

 

**********

          **********              (A)

                    **********

                   

**********

**********                        (B)

  **********

  **********

[abrew]

almost like that Barrand

10-day ecaxtly like this

 

January ;

1 ------- 10

11 -------20

21 -------31 (end of month)

 

February ;

1--------10

11------20

21------28 or 29 (the end of the month)

 

the last 10-days is not always 10 day ,,, depend on the month

 

thx ,

any idea ??

 

[DavidAM]

I wrote this query last night, but my grandson flipped off the power strip and it was lost. I think it went something like this:

 

SELECT FLOOR(DATE_DIFF(RainDate, '2011-01-01') / 10) AS RainSet,
	SUM(RainQty) AS TotalRain
FROM dayRain
WHERE RainDate BETWEEN '2011-01-01' AND '2011-01-31'
GROUP BY 1

 

• DATE_DIFF(RainDate, '2011-01-01') calculates the number of days between the date in the table and the starting date of your range. For the first date in your range, this is zero
  
• FLOOR( <DATE_DIFF> / 10) - Divides that number of days by 10 and returns an integer result. So the first ten days will have a RainSet value of zero, the next 10 days will have a RainSet value of 1, etc.
  
• GROUP BY 1 - groups by the first expression in the select list (this is a shortcut so I don't have to type the whole FLOOR(DATE_DIFF ...) expression again
  

 

The Date in the DATE_DIFF expression needs to be the start date of your range. Your range is also specified in the WHERE clause --- remember BETWEEN is inclusive.

 

This will return a result set like:

RainSet  TotalRain
-------  -------
0        20
1        5
2        15

 

 

i need to make a new table but tendays period ,,,

 

The beauty of relational databases and SQL, is that you almost never "need" to make a new table for calculated data. Unless you have a compelling reason to create the table, you should use queries to summarize your data.

[Barand]

This should do the whole year in one go

SELECT YEAR(raindate)as year, MONTH(raindate)as Month, 0 as dayset, SUM(rainqty) as rainfall
FROM dayrain
WHERE DAYOFMONTH(raindate) BETWEEN 1 AND 10
GROUP BY year, Month
UNION
SELECT YEAR(raindate)as year, MONTH(raindate)as Month, 1 as dayset, SUM(rainqty) as rainfall
FROM dayrain
WHERE DAYOFMONTH(raindate) BETWEEN 11 AND 20
GROUP BY year, month
UNION
SELECT YEAR(raindate)as year, MONTH(raindate)as Month, 2 as dayset, SUM(rainqty) as rainfall
FROM dayrain
WHERE DAYOFMONTH(raindate) BETWEEN 21 AND 31
GROUP BY year, month
ORDER BY year, month, dayset

[abrew]

Thank you barrand ,,, u'r the man ,,, that's all i need !!!

david it's good 2 ,,, but i use barrand clue ,,, thank all