We help IT Professionals succeed at work.

Returning summary data SQL

278 Views
Last Modified: 2012-08-13
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?
Comment
Watch Question

Commented:
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

Data Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks, I will try it.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for all the help...

after tinkering a bit, i went with the following query.

SELECT a.column1 ,a.column3 FROM MyTable a
where a.column1 in (select max(column1)from MyTable
group by column2)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.