SQL Server 2005 : Query problem

wkm1925 · May 9th, 2008, 08:00 AM

I'm a sql server beginner.

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?

ciderpunx · May 11th, 2008, 08:30 AM

well, I'm a mysql-ist so may not work in sql-server but you should be able to google equivalents for functions like CURDATE() and so on. Also I changed the main_id fields in my test database so the numbers don't correspond.

Code:

mysql> SELECT * FROM location;
+---------+------------+------+------+
| main_id | dateTaken  | time | hit  |
+---------+------------+------+------+
|       1 | 2008-05-09 |  100 |    2 | 
|       1 | 2008-05-09 |  200 |    3 | 
|       1 | 2008-05-06 |  300 |    6 | 
|       2 | 2008-05-01 |  400 |    5 | 
|       2 | 2008-05-04 |  500 |    9 | 
|       2 | 2008-05-07 |  600 |    2 | 
|       3 | 2008-05-02 |  700 |    2 | 
|       3 | 2008-05-03 |  800 |    4 | 
|       3 | 2008-05-06 |  900 |    2 | 
|       4 | 2008-05-07 |  100 |    2 | 
|       4 | 2008-05-08 |  200 |    3 | 
|       4 | 2008-05-09 |  300 |    6 | 
|       5 | 2008-05-04 |  400 |    5 | 
|       5 | 2008-05-03 |  500 |    9 | 
|       5 | 2008-05-08 |  200 |    3 | 
|       6 | 2008-05-02 |  300 |    6 | 
|       6 | 2008-05-01 |  400 |    5 | 
|       6 | 2008-05-09 |  500 |    9 | 
+---------+------------+------+------+
18 rows in set (0.00 sec)

mysql> SELECT * FROM setValue;
+---------+------+
| main_id | hit2 |
+---------+------+
|       1 |    3 | 
|       2 |    5 | 
|       3 |    3 | 
|       4 |    1 | 
|       5 |    8 | 
|       6 |    7 | 
+---------+------+
6 rows in set (0.00 sec)

mysql> SELECT location.main_id, dateTaken, time, MAX(hit) AS maxHit, hit2, (MAX(hit)-hit2) as diff 
         FROM location,setValue 
        WHERE dateTaken BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() 
          AND location.main_id=setValue.main_id 
     GROUP BY main_id 
     ORDER BY main_id;
+---------+------------+------+--------+------+------+
| main_id | dateTaken  | time | maxHit | hit2 | diff |
+---------+------------+------+--------+------+------+
|       1 | 2008-05-09 |  100 |      6 |    3 |    3 | 
|       2 | 2008-05-04 |  500 |      9 |    5 |    4 | 
|       3 | 2008-05-06 |  900 |      2 |    3 |   -1 | 
|       4 | 2008-05-07 |  100 |      6 |    1 |    5 | 
|       5 | 2008-05-04 |  400 |      5 |    8 |   -3 | 
|       6 | 2008-05-09 |  500 |      9 |    7 |    2 | 
+---------+------------+------+--------+------+------+
6 rows in set (0.00 sec)

mysql>

Can't remember off the top of my head how to get rid of the - sign off your diff column. There will be a function though.

HTH,
Charlie

--
Charlie Harvey's website - linux, perl, java, anarchism and punk rock: http://charlieharvey.org.uk