Solved

Returning summary data SQL

Posted on 2009-05-05
5
252 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 250 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 250 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 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