asked on

Returning summary data SQL

My data looks like so -- column 1 is day, column 2 is week of year and column 3 is value

2009-05-05 00:00:00.000        19      8.00
2009-05-06 00:00:00.000        19      16.00
2009-05-07 00:00:00.000        19      24.00
2009-05-08 00:00:00.000        19      32.00
2009-05-09 00:00:00.000        19      32.00
2009-05-10 00:00:00.000        20      32.00
2009-05-11 00:00:00.000        20      40.00
2009-05-12 00:00:00.000        20      48.00
2009-05-13 00:00:00.000        20      56.00
2009-05-14 00:00:00.000        20      68.00
2009-05-15 00:00:00.000        20      64.00
2009-05-16 00:00:00.000        20      64.00

i want to return the following 2 rows

19 32
20 64

where the value returned corresponds to the final day of the week. MAX(value) wont work because values earlier in the week can be higher than the final day of the week.

Any suggestions?

spock787

Try this code.

You should know that 7 will be SAT. If that's not what you want use the appropriate number representation of the day of the week you want. Regardless you can use this function to select only the records from the last day of each week.

Select column2, column3
  from table1
 where datepart(weekday, column1) = 7

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BoggyBayouBoy

ASKER

Thanks, I will try it.

SOLUTION

sateeshcv