SQL Script for weekly reports: I know from sql experience that this question is...

[Coosa]

...just for professionals ...? Assuming we have the following attributes:1.) employee_id: int (PK)2.) i_date: datetime (PK) 2007-02-08 - 2007-02-13next row -> 2007-02-10 - 2007-02-15So it's like we have now an intersection from 2007-02-10 - 2007-02-13 which shouldn't be there. Any idea?Well, consider this snapshot of the original Data:PID DATE COMMISSION12345 2007-02-02 720.0000012345 2007-02-05 110.0000012345 2007-02-08 150.0000012345 2007-02-10 60.0000012345 2007-02-12 300.0000012345 2007-02-16 50.0000012345 2007-03-01 150.0000012345 2007-03-05 300.0000012345 2007-03-06 300.0000012345 2007-03-07 150.0000012345 2007-03-08 150.00000Desired is the following Format:PID START END TOTAL_SUM12345 2007-02-02 2007-02-04 720.0000012345 2007-02-05 2007-02-11 320.0000012345 2007-02-12 2007-02-18 350.0000012345 2007-03-01 2007-03-04 150.0000012345 2007-03-05 2007-03-08 900.00000As you can see, each row represents a week's transactional summary

[funky_dude]

I think following should solve select emp_id,emp_name,i_date as Start_date,DateAdd (''d", 5, idate) as End_date ,sum(commission) as Total_Comm from employee group by emp_id having commision in (select commision from employee where idate between '02Apr07' and '06Apr07')

[Brian G]

It was a bit difficult to follow what you really wanted. First, it started as a week being Mon-Fri, but then the example data included a sale on Sat. The following is a query that will get you a Mon start date and a Sun end date for each week:selectemployee_id,employee_name,case when datepart(dw,i_date) = 2 then i_date else dateadd(d,-1 * (datepart(dw,i_date) - 2), i_date) end startdate,case when datepart(dw,i_date) = 1 then i_date else dateadd(d,8 - datepart(dw,i_date), i_date) end enddate,sum(commission)from sales group by employee_id, employee_name,case when datepart(dw,i_date) = 2 then i_date else dateadd(d,-1 * (datepart(dw,i_date) - 2), i_date) end,case when datepart(dw,i_date) = 1 then i_date else dateadd(d,8 - datepart(dw,i_date), i_date) endHowever, if you want to modify this to Mon - Fri (which excludes the Sat 2/10/2007 sale):selectemployee_id,employee_name,case when datepart(dw,i_date) = 2 then i_date else dateadd(d,-1 * (datepart(dw,i_date) - 2), i_date) end startdate,case when datepart(dw,i_date) = 6 then i_date else dateadd(d,6 - datepart(dw,i_date), i_date) end enddate,sum(commission)from sales where datepart(dw,i_date) between 2 and 6group by employee_id, employee_name,case when datepart(dw,i_date) = 2 then i_date else dateadd(d,-1 * (datepart(dw,i_date) - 2), i_date) end,case when datepart(dw,i_date) = 6 then i_date else dateadd(d,6 - datepart(dw,i_date), i_date) end