May 8, 2008 at 11:34 pm
I've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206 | 3
201 | 5
204 | 3
203 | 1
202 | 8
205 | 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206 | 5/6/2008 | 300 | 6 | 3 | 3
201 | 5/4/2008 | 500 | 9 | 5 | 4
204 | 5/3/2008 | 800 | 4 | 3 | 1
203 | 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to show the query?
May 30, 2008 at 1:53 am
Please post proper DDL and sample data - a script that we can use to reproduce the tables and data.
A few suggestions:
1) Consider using one of the two standard and unambiguous character representations of date/time values:
yyyy-mm-ddThh:mm:ss.ttt
...or:
yyyymmdd hh:mm:ss.ttt
...instead of the ambiguous format you used in your example (for instance, does 5/9/2008 represent 5th of September of 9th of May?).
2) Avoid using reserved keywords as object names (e.g. time is a reserved keyword).
Anyway, here's a guess:
selectMaxHits.Main_ID
,tbllocation.Date_Taken
,tbllocation.[Time]
,MaxHits.Hit
,tblSetValue.Hit2
,Diff
= tblSetValue.Hit2
- MaxHits.Hit
from(
selectMain_ID
,max(Hit) as Hit
fromtbllocation
where(tbllocation.Date_Taken >= dateadd(d, datediff(d, 0, getdate()), -7))
group byMain_ID
) MaxHits
inner jointbllocation
on(tbllocation.Main_ID = MaxHits.Main_ID)
and (tbllocation.Hit = MaxHits.Hit)
inner jointblSetValue
ontblSetValue.Main_ID = MaxHits.Main_ID
You also didn't specify whether (and how) to restrict the result if the same max value appears on more than one date.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply