totle_count	dt	name	program_id
1	1/3/2015	Tea	14
1	1/5/2015	Tea	14
1	1/6/2015	Lunch	13
17	1/6/2015	Tea	14
2	1/9/2015	Breakfast	1008
39	1/10/2015	Breakfast	1008
4	1/19/2015	Breakfast	1008
1	1/19/2015	Dinner	1009
3	1/21/2015	Tea	14

totle_count	dt	name	program_id
1	1/6/2015 and 1/8/2015	Lunch	13
2	1/3/2015 and 1/5/2015	Tea	14
17	1/6/2015 and 1/9/2015	Tea	14
3	1/21/2015 and 1/23/2015	Tea	14
41	1/9/2015 and 1/11/2015	Breakfast	1008
4	1/18/2015 and 1/20/2015	Breakfast	1008
1	1/18/2015 and1/20/2015	Dinner	1009

Here is 2 table, 1st is my row table, and I want to group with 2 date.
Here is 3 days difference. and sum of value is coming.

2nt table is my expectation table.

Plz help me for this problem.

Thanks
Samir

hmm, it's not clear how you want to group and how you get those [dt] values. E.g. where does 1/18/2015 and1/20/2015 for Dinner come from?

Whats the rule based on which you want to merge rows?

I am doing 3 days grouping here

"dt" is name of date column

if "dt" column has '1/19/2015' then next in group it will come in "1/18/2015 and1/20/2015"

any date of 18 or 19 0r 20 it will come in "1/18/2015 and1/20/2015"

and counting will add between 3 date

• Marked as answer by ShankarSamir Saturday, January 31, 2015 4:35 AM
• Unmarked as answer by ShankarSamir 22 hours 52 minutes ago

I am doing 3 days grouping here

"dt" is name of date column

if "dt" column has '1/19/2015' then next in group it will come in "1/18/2015 and1/20/2015"

any date of 18 or 19 0r 20 it will come in "1/18/2015 and1/20/2015"

and counting will add between 3 date

Sorry your posted output is not as per above rule

How did row for  1/6/2015 and 1/9/2015 got included then. If you group by 3 days you wont get this row as 6-9 is 4 days not 3 days

thanks for conversion,

may be my counting is wrong, it was not matter. it was just clubbing 3 or 4 days.

Now it solved

I added for loop of "date" from start date to end date

range of 3 days, I updated table (column - dt) in start date

now I got 1 days unique

and then "group by"

my problem have been solved

Hope understood my word

• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator Saturday, January 31, 2015 7:35 AM