Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning summary data SQL

Posted on 2009-05-05
5
Medium Priority
?
254 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?
0
Comment
Question by:BoggyBayouBoy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 1

Expert Comment

by:spock787
ID: 24310823
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

0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 24311050

What if you don't have 7 records for all the weekdays.
for example if you have only one record for a week or less than 7 records for a week in your table, then you cannot apply the filter datepart(weekday,column1)  = 7.
You need to try like this.

select column2,MAX(column3)
  from YourTable t1
  where DATEPART(weekday,column1) = (select MAX(datepart(weekday,column1)) from YourTable t2 where t1.column2 = t2.column2)
  group by column2
 
-- or like this
 
select t1.column2,MAX(t1.column3)
  from YourTable t1
  join (select column2,max(datepart(weekday,column1)) as dp from YourTable group by column2) t2
    on t1.column2 = t2.column2 and datepart(weekday,t1.column1) = t2.dp
 group by t1.column2
 

Open in new window

0
 
LVL 1

Author Comment

by:BoggyBayouBoy
ID: 24311070
Thanks, I will try it.
0
 
LVL 3

Assisted Solution

by:sateeshcv
sateeshcv earned 1000 total points
ID: 24311521
HI,
TRY THE BELOW CODE
--COLUMN1 IS DAY, COLUMN2 IS WEEKDAY, COLUMN3 IS VALUE
SELECT COLUMN2,COLUMN3 FROM TABLE
WHERE CAST(COLUMN1 AS VARCHAR(20)+CAST(COLUMN2 AS VARCHAR(2))
IN
( SELECT CAST(MAX(COLUMN1) AS VARCHAR(20))+CAST(COLUMN2 AS VARCHAR(2))
FROM TABLE GROUP BY COLUMN2)
ORDER BY COLUMN2
0
 
LVL 1

Author Comment

by:BoggyBayouBoy
ID: 24325140
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)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question