Solved

Returning summary data SQL

Posted on 2009-05-05
5
248 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
5 Comments
 
LVL 1

Expert Comment

by:spock787
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility

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
Comment Utility
Thanks, I will try it.
0
 
LVL 3

Assisted Solution

by:sateeshcv
sateeshcv earned 250 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now