Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complicated Sql 2

Status
Not open for further replies.
tdong

tdong

Programmer
Mar 12, 2004
112
CA
I have two tables
Table 1

ID CompanyName
1 A
2 B

Table 2 <--this table only record the change in RatingName Value
ID RatingName Value TimeStamp
1 T1 5 10/11/2006
1 T2 5 10/11/2006
2 T1 5 10/11/2006
2 T2 5 10/11/2006
'default value above and change is below
1 T2 6 10/12/2006
2 T1 7 10/12/2006
2 T2 7 10/12/2006
1 T2 8 10/13/2006
I want to be able to return the change value if exist if not I return the old value from previous date or previous latest date

result
ID| T1| T2| T1time| T2time <--need max date the lastest
1| 5| 8| 10/11/2006| 10/13/2006<--since only T2 is updated
2| 7| 7| 10/12/2006| 10/12/2006<--since both T1 and T2 changed

Any suggestion on creating a view and select from it or an sql statement that give me the result ? thanks
 
Sort by date Sort by votes
Okay, it appears that you want the most recent "value" of each company for each rating name. I considered the possibility that a company could have more than one "value" per "rating name" per date, and in that case, the higher "value" is what you'd want.

I also assumed that the "value" would not have more than 10 digits.

I'm sure there is a more elegant solution using subqueries, but I think this gets the job done:

Code: 
CREATE TABLE rating (compid I, ratingname c(2), ratingval I, datestamp D)
INSERT INTO rating VALUES (1,"T1",5,{^2006-10-11})
INSERT INTO rating VALUES (1,"T2",5,{^2006-10-11})
INSERT INTO rating VALUES (2,"T1",5,{^2006-10-11})
INSERT INTO rating VALUES (2,"T2",5,{^2006-10-11})
INSERT INTO rating VALUES (1,"T2",6,{^2006-10-12})
INSERT INTO rating VALUES (2,"T1",7,{^2006-10-12})
INSERT INTO rating VALUES (2,"T2",7,{^2006-10-12})
INSERT INTO rating VALUES (1,"T2",8,{^2006-10-13})

SET DECIMALS TO 0

SELECT ratingname, compid, MAX(DTOS(datestamp)+"-" + PADL(ALLTRIM(STR(ratingval)),10,"0")) AS dateval;
	FROM rating;
	GROUP BY ratingname, compid;
	INTO CURSOR crsA

SELECT ratingname, compid, ;
VAL(RIGHT(dateval,10)) AS ratingval,;
DATE(VAL(LEFT(dateval,4)),VAL(SUBSTR(dateval,5,2)),VAL(SUBSTR(dateval,7,2))) AS dt;
	FROM crsA;
	INTO CURSOR crsB

SELECT compid, ratingval as T1, dt as t1time;
FROM crsB WHERE ratingname = "T1";
into CURSOR crs1

SELECT compid, ratingval as T2, dt as t2time;
FROM crsB WHERE ratingname = "T2";
into CURSOR crs2

SELECT crs1.compid, crs1.t1, crs2.t2, crs1.t1time, crs2.t2time;
	FROM crs1 INNER JOIN crs2;
	ON crs1.compid = crs2.compid
 
Upvote 0 Downvote
It would be great if you tell us what verision of VFP you use. This query is tested in VFP9 (used teresas data preparing):
Code: 
CREATE TABLE rating (compid I, ratingname c(2), ratingval I, datestamp D)
INSERT INTO rating VALUES (1,"T1",5,{^2006-10-11})
INSERT INTO rating VALUES (1,"T2",5,{^2006-10-11})
INSERT INTO rating VALUES (2,"T1",5,{^2006-10-11})
INSERT INTO rating VALUES (2,"T2",5,{^2006-10-11})
INSERT INTO rating VALUES (1,"T2",6,{^2006-10-12})
INSERT INTO rating VALUES (2,"T1",7,{^2006-10-12})
INSERT INTO rating VALUES (2,"T2",7,{^2006-10-12})
INSERT INTO rating VALUES (1,"T2",8,{^2006-10-13})


SELECT DISTINCT Rating.compid,;
       T1Tmp.RatingName AS T1,;
       T1Tmp.DateStamp  AS T1Time,;
       T2Tmp.RatingName AS T2,;
       T2Tmp.DateStamp  AS T2Time;
FROM Rating;
INNER JOIN (SELECT compid,;
                  MAX(RatingName) AS RatingName,;
                  MAX(DateStamp)  AS DateStamp;
          FROM Rating;
          GROUP BY compid;
          WHERE RatingName = 'T1') T1Tmp;
    ON Rating.compid = T1Tmp.compid;
INNER JOIN (SELECT compid,;
                  MAX(RatingName) AS RatingName,;
                  MAX(DateStamp)  AS DateStamp;
          FROM Rating;
          GROUP BY compid;
          WHERE RatingName = 'T2') T2Tmp;
    ON Rating.compid = T2Tmp.compid;
INTO CURSOR crsTest
BROW NORMAL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Hi
Thanks I will try that. I am using Sql 2000, VB6, VB.net , Crystal Report for reporting
 
Upvote 0 Downvote
I think I post to the wrong forum I don't use VFP thanks
 
Upvote 0 Downvote
[rofl]
My code will works with SQL Server also,
Just delete last two lines and remove ALL semi-coluns
Code: 
DECLARE @rating TABLE (compid Int, ratingname char(2), ratingval Int, datestamp Datetime)
INSERT INTO rating VALUES (1,"T1",5,'20061011')
INSERT INTO rating VALUES (1,"T2",5,'20061011')
INSERT INTO rating VALUES (2,"T1",5,'20061011')
INSERT INTO rating VALUES (2,"T2",5,'20061011')
INSERT INTO rating VALUES (1,"T2",6,'20061012')
INSERT INTO rating VALUES (2,"T1",7,'20061012')
INSERT INTO rating VALUES (2,"T2",7,'20061012')
INSERT INTO rating VALUES (1,"T2",8,'20061013')


SELECT DISTINCT Rating.compid,
       T1Tmp.RatingName AS T1,
       T1Tmp.DateStamp  AS T1Time,
       T2Tmp.RatingName AS T2,
       T2Tmp.DateStamp  AS T2Time
FROM Rating;
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM Rating
          GROUP BY compid
          WHERE RatingName = 'T1') T1Tmp
    ON Rating.compid = T1Tmp.compid
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM Rating
          GROUP BY compid
          WHERE RatingName = 'T2') T2Tmp
    ON Rating.compid = T2Tmp.compid

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
OOPS, cop and paste problem, it must be:
Code: 
SELECT DISTINCT Rating.compid,
       T1Tmp.RatingName AS T1,
       T1Tmp.DateStamp  AS T1Time,
       T2Tmp.RatingName AS T2,
       T2Tmp.DateStamp  AS T2Time
FROM @Rating Rating;
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM @Rating
          GROUP BY compid
          WHERE RatingName = 'T1') T1Tmp
    ON Rating.compid = T1Tmp.compid
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM @Rating
          GROUP BY compid
          WHERE RatingName = 'T2') T2Tmp
    ON Rating.compid = T2Tmp.compid

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Thanks I can't get it to run in querry analyzer yet there is an error in the where statement any idea thanks ?
 
Upvote 0 Downvote
I am spoled from VFP :)
Here the query th will run in QA or SQLMS:
Code: 
DECLARE @rating TABLE (compid Int, ratingname char(2), ratingval Int, datestamp Datetime)
INSERT INTO @rating VALUES (1,'T1',5,'20061011')
INSERT INTO @rating VALUES (1,'T2',5,'20061011')
INSERT INTO @rating VALUES (2,'T1',5,'20061011')
INSERT INTO @rating VALUES (2,'T2',5,'20061011')
INSERT INTO @rating VALUES (1,'T2',6,'20061012')
INSERT INTO @rating VALUES (2,'T1',7,'20061012')
INSERT INTO @rating VALUES (2,'T2',7,'20061012')
INSERT INTO @rating VALUES (1,'T2',8,'20061013')


SELECT DISTINCT Rating.compid,
       T1Tmp.RatingName AS T1,
       T1Tmp.DateStamp  AS T1Time,
       T2Tmp.RatingName AS T2,
       T2Tmp.DateStamp  AS T2Time
FROM @Rating Rating
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM @Rating
              WHERE ratingname = 'T1'          
              GROUP BY compid) T1Tmp
    ON Rating.compid = T1Tmp.compid
INNER JOIN (SELECT compid,
                  MAX(RatingName) AS RatingName,
                  MAX(DateStamp)  AS DateStamp
          FROM @Rating
               WHERE RatingName = 'T2'          
               GROUP BY compid) T2Tmp
    ON Rating.compid = T2Tmp.compid


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Thanks for your help it works great.
 
Upvote 0 Downvote
We are DATA guys here ;-) No data related questions can scarry us :)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
I need the rating value not name I change it. The only problem is that it select max(ratingval) for the last line in insert I change value to 1 it still return 6 since 6 is max. I need it to return rating value for max(date)

DECLARE @rating TABLE (compid Int, ratingname char(2), ratingval Int, datestamp Datetime)
INSERT INTO @rating VALUES (1,'T1',5,'20061011')
INSERT INTO @rating VALUES (1,'T2',5,'20061011')
INSERT INTO @rating VALUES (2,'T1',5,'20061011')
INSERT INTO @rating VALUES (2,'T2',5,'20061011')
INSERT INTO @rating VALUES (1,'T2',6,'20061012')
INSERT INTO @rating VALUES (2,'T1',7,'20061012')
INSERT INTO @rating VALUES (2,'T2',7,'20061012')
INSERT INTO @rating VALUES (1,'T2',1,'20061013')


SELECT DISTINCT Rating.compid,
T1Tmp.ratingval AS T1,
T1Tmp.DateStamp AS T1Time,
T2Tmp.ratingval AS T2,
T2Tmp.DateStamp AS T2Time
FROM @Rating Rating
INNER JOIN (SELECT compid,
MAX(ratingval) AS ratingval,
MAX(DateStamp) AS DateStamp
FROM @Rating
WHERE ratingname = 'T1'
GROUP BY compid) T1Tmp
ON Rating.compid = T1Tmp.compid
INNER JOIN (SELECT compid,
MAX(ratingval) AS ratingval,
MAX(DateStamp) AS DateStamp
FROM @Rating
WHERE ratingname = 'T2'
GROUP BY compid) T2Tmp
ON Rating.compid = T2Tmp.compid
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

GTGeek88
  • Question
SQL query syntax
Replies
7
Views
239
GTGeek88
GTGeek88
bharons
  • Question
SQL Server data processing with VFP 8 1
Replies
8
Views
584
bharons
bharons
bharons
  • Locked
  • Question
Update Data SQL server Using Create Cursor VFP
Replies
13
Views
1K
bharons
bharons
Steve Yu
  • Question
Westwind Web Connection server on LAN: can SPT (MS SQL) work ? 4
2 3
Replies
44
Views
2K
Steve Yu
Steve Yu
Mukesh Khandelwal
  • Locked
  • Question
Save Chinese + English text in MS Sql table,
Replies
5
Views
625
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top