Join between - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Join between

Author

Topic

DeNam
Starting Member

15 Posts

Posted - 2013-10-26 : 14:59:42

Hi,

I want to join a table with different vehicles and vehicle ages with a table containing market values.

If i have a motorcycle that is 9 months old, I would like it to choose a residual value of 72 (i.e. the middle of row 3 and 4).

How can i do this?

Brand code Brands Months Residual Value
2 Motorcycles 0 80
2 Motorcycles 1,5 77
2 Motorcycles 6 74
2 Motorcycles 12 70
2 Motorcycles 18 64
2 Motorcycles 24 58
2 Motorcycles 30 52
2 Motorcycles 36 46
2 Motorcycles 42 40
2 Motorcycles 48 33
2 Motorcycles 54 27
2 Motorcycles 60 22
2 Motorcycles 66 18
2 Motorcycles 72 14
2 Motorcycles 78 12
2 Motorcycles 84 10
2 Motorcycles 90 8
3 Cars 0 80
3 Cars 1,5 79
3 Cars 6 72
3 Cars 12 65
3 Cars 18 60
3 Cars 24 57
3 Cars 30 52
3 Cars 36 49
3 Cars 42 44
3 Cars 48 42
3 Cars 54 37
3 Cars 60 35
3 Cars 66 31
3 Cars 72 28
3 Cars 78 26
3 Cars 84 23
3 Cars 90 16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 15:31:01

why is second row alone having value in different format (1,5)? is it intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeNam
Starting Member

15 Posts

Posted - 2013-10-26 : 16:03:30

Yes, this is intentional.
Means 1.5 months old.

Anyway, how can i solve my problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-27 : 02:26:19

[code]
DECLARE @Brands varchar(100)--Pass Brand value through this
DECLARE @Age int -- Pass age value through it

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Brands ORDER BY Months) AS Seq,*
FROM Table
)
SELECT (t1.Value + COALESCE(t2.Value,0))/ (1 + CASE WHEN t2.Value IS NULL THEN 0 ELSE 1 END)
FROM CTE t1
LEFT JOIN CTE t2
ON t2.Brands = t1.Brands
AND t2.Seq = t1.Seq + 1
WHERE @Age BETWEEN t1.Months AND COALESCE(t2.Months,@Age)
AND t1.Brands = @Brands
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources