Solved

Returning summary data SQL

Posted on 2009-05-05
5
249 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
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 40

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

937 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

9 Experts available now in Live!

Get 1:1 Help Now