Complex VIEW - 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 2005 Forums

Transact-SQL (2005)

Complex VIEW

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-29 : 10:51:37

I have this data in table 1


ID      VRef    Type    CAPID   Make    Model                   Derivative
110	12594	Car	37863	TOYOTA	VERSO DIESEL ESTATE	2.2 D-4D T Spirit 5dr
111	12595	Car	37860	TOYOTA	VERSO DIESEL ESTATE	2.2 D-4D T2 5dr
112	12596	Car	37862	TOYOTA	VERSO DIESEL ESTATE	2.2 D-4D T3 5dr

Table 2 contains a collection of figures associated with each VRef


ID      VRef    Term    Miles   Maint   Price
114851	12594	2	10000	False	248.9900
114852	12594	2	10000	True	275.9900
114853	12594	2	20000	False	280.9900
114854	12594	2	20000	True	324.9900
114855	12594	2	30000	False	312.9900
114856	12594	2	30000	True	373.9900
114857	12594	3	10000	False	229.9900
114858	12594	3	10000	True	256.9900
114859	12594	3	20000	False	254.9900
114860	12594	3	20000	True	305.9900
114861	12594	3	30000	False	278.9900
114862	12594	3	30000	True	354.9900
114863	12595	2	10000	False	193.9900
114864	12595	2	10000	True	220.9900
114865	12595	2	20000	False	221.9900
114866	12595	2	20000	True	265.9900
114867	12595	2	30000	False	248.9900
114868	12595	2	30000	True	310.9900
114869	12595	3	10000	False	183.9900
114870	12595	3	10000	True	210.9900
114871	12595	3	20000	False	204.9900
114872	12595	3	20000	True	256.9900
114873	12595	3	30000	False	225.9900
114874	12595	3	30000	True	301.9900
114875	12596	2	10000	False	222.9900
114876	12596	2	10000	True	249.9900
114877	12596	2	20000	False	252.9900
114878	12596	2	20000	True	296.9900
114879	12596	2	30000	False	281.9900
114880	12596	2	30000	True	342.9900
114881	12596	3	10000	False	207.9900
114882	12596	3	10000	True	234.9900
114883	12596	3	20000	False	230.9900
114884	12596	3	20000	True	282.9900
114885	12596	3	30000	False	252.9900
114886	12596	3	30000	True	329.9900

My view needs to group the vehicles by derivative add in in the cheapest 2 year and three year prices.

I have this view, which I thought was working until today (theres columns in the view omitted from the above tables for clarity.


SELECT     TOP (100) PERCENT Manufacturer, Model, MIN(Derivative) AS Expr1, MIN(LOCatCode) AS Expr2, MIN(TwoYearPrice) AS TwoYearPrice, 
                      MIN(ThreeYearPrice) AS ThreeYearPrice
FROM         (SELECT     TOP (100) PERCENT A.Manufacturer, A.Model, A.Derivative, A.LOCatCode, B.CH AS TwoYearPrice, C.CH AS ThreeYearPrice
                       FROM          dbo.tblNewMatrixLtd AS A INNER JOIN
                                              dbo.tblNewMatrixFigures AS B ON A.VehicleRef = B.VehicleRef AND B.Term = 2 AND B.MilesPA = 10000 AND B.Maintained = 0 INNER JOIN
                                              dbo.tblNewMatrixFigures AS C ON A.VehicleRef = C.VehicleRef AND C.Term = 3 AND C.MilesPA = 10000 AND C.Maintained = 0
                       WHERE      (A.eMatrixLive = 1) AND (A.Authorized = 1)
                       ORDER BY A.Manufacturer, A.Model) AS derivedtbl_1
GROUP BY Manufacturer, Model
ORDER BY Manufacturer, Model

In the above example the following data is returned.

TOYOTA VERSO DIESEL ESTATE 2.2 D-4D T Spirit 5dr 3 193.9900 183.9900

BUT

The cheapest VERSO DIESEL ESTATE should be the D-4D T2 5dr - the prices it has returned are correct, just not the derivative.

Thanks

klo
Starting Member

7 Posts

Posted - 2007-10-29 : 16:24:11

Your outer query is retrieving the MIN( Derivative ) - regardless of what model has the MIN PRICE e.g.


DECLARE @tv_table TABLE (
    derivative varchar(100)
)

INSERT INTO @tv_table
       SELECT '2.2 D-4D T Spirit 5dr'
UNION
   SELECT '2.2 D-4D T2 5dr'
UNION
   SELECT '2.2 D-4D T3 5dr'

SELECT MIN(derivative) FROM @tv_table

Extending your current logic you can retrieve the MIN( Price ) inside in your derived table ( just GROUP BY manufacturer and model ) you'll then have to join back to the dbo.tblNewMatrixFigures table (twice in fact) once where price = twoyearprice and AGAIN where price = threeyearpice then you'll have to join back to the tblNewMatrixLtd table on VehicleRef to get the exact derivative. Something like the following (beware I haven't run this or anything you'll also notice that I've taken out your ORDER BY's etc)...


SELECT *
  FROM 
 (SELECT     MIN(b.ch ) as TwoYearPrice 
            ,MIN(c.ch) as ThreeYearPrice 
    FROM    dbo.tblNewMatrixLtd AS A 
      INNER JOIN
           dbo.tblNewMatrixFigures AS B 
        ON A.VehicleRef = B.VehicleRef
       AND B.Term = 2 AND B.MilesPA = 10000 
       AND B.Maintained = 0 
      INNER JOIN
           dbo.tblNewMatrixFigures AS C 
        ON A.VehicleRef = C.VehicleRef 
       AND C.Term = 3 
       AND C.MilesPA = 10000 
       AND C.Maintained = 0
     WHERE      (A.eMatrixLive = 1) AND (A.Authorized = 1)
  GROUP BY A.Manufacturer, A.Model) AS derivedtbl_1
INNER JOIN 
     dbo.tblNewMatrixFigures AS T2 
   ON TwoYearPrice     = T2.price
INNER JOIN 
     dbo.tblNewMatrixFigures AS T3 
   ON ThreeYearPrice     = T3.price
INNER JOIN 
     dbo.tblNewMatrixLtd AS T4
   ON T3.VehicleRef      = T4.VehicleRef
WHERE T2.term            = 2 
  AND T3.term            = 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources