Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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?
0
BoggyBayouBoy
Asked:
BoggyBayouBoy
2 Solutions
 
spock787Commented:
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
 
SharathData EngineerCommented:

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
 
BoggyBayouBoyAuthor Commented:
Thanks, I will try it.
0
 
sateeshcvCommented:
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
 
BoggyBayouBoyAuthor 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)
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now