For help if the date is XXXX. Xx. Xx 'ten characters varchar format, the following figure, now hope to initial date for the starting point, every other month divided into a group with statistical GMV in each group, could you tell me how to group?
If the substr (dt, 1, 7) points is natural, not the desired results, use dt + interval '1' if the month can only find out in the first group, don't know how to cycle

CodePudding user response:

Since not natural, that the so-called interval for a month, refers to how many days does, unity is 30 days, if the interval number is fixed, then in each record date date minus the minimum number of days, divided by a fixed the quotient of the number of days between to grouping,

CodePudding user response:

A good way, thank you ~

CodePudding user response:

reference 1st floor RINK_1 response:

now that is not natural, the so-called interval for a month, refers to how many days does, unity is 30 days, if the interval number is fixed, then in each record date date minus the minimum number of days, divided by a fixed the quotient of the number of days between to grouping,

thank you

CodePudding user response:

Suggest a time range table, put all of the starting time and end time in the list, then both sides a association came out,

CodePudding user response:

The create table # t (
Dt smalldatetime, GMV int
)
Insert into # t
Select '2018/05/10', '10' union all
Select '2018/05/10', '20' union all
Select '2018/05/11', '10' union all
Select '2018/05/16', '5' union all
Select '2018/05/23', '60' union all
Select '2018/05/23', '10' union all
Select '2018/06/05', '20' union all
Select '2018/06/07', '60' union all
Select '2018/06/17', '30' union all
Select '2018/06/29', '10' union all
Select '2018/07/03', '10' union all
Select '2018/08/24', '5' union all
Select '2018/08/31', '70' union all
Select '2018/10/27', '20' union all
Select '2018/11/13', '10' union all
Select '2018/12/08', '80'
- enter a date in accordance with the input date every 30 days to group summary GMV field
Declare @ dt as smalldatetime
The set @ dt='2018/05/10'
Select SUM (GMV) as gmv_count from (
The select Datediff (dd, @ dt, dt)/30 as px, * from # t) ta group by px