Rearrange Query - 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)

Rearrange Query

Author

Topic

Kwozzie
Starting Member

4 Posts

Posted - 2012-06-28 : 01:33:02

I have read only access of this database and would like to change this query around so that I can compare the tblBranchTransfers.BranchId in the sub-query to a number, say '1' and if it doesn't exist, check the tblItems.BranchId for 1.

I can't seem to find a way to do a sub-query or join or case statement to make this work.


SELECT 
   Findex,
   BranchID, 
   DivisionID, 
   CategoryId, 
   SerialNumber,
   (SELECT 
      TOP 1 BranchID 
    FROM 
       tblBranchTransfers 
    WHERE 
       Findex = tblItems.Findex 
    ORDER BY 
       DateApplied DESC,AID DESC
    ) as current_BranchID 
FROM 
    tblItems

I thought a join on a virtual table may be the way to go but can't think how to get only the 'top' result (so only the most recent result for Findex of 5040 for the Findex of this query:


SELECT * FROM tblBranchTransfers WHERE Quantity = 1 ORDER BY DateApplied DESC, AID DESC

AID         BranchId Findex SerialNo                         Quantity DateApplied             Version
----------- -------- ------ -------------------------------- -------- ----------------------- -----------
24986       8        5040                                    1        2012-06-28 13:15:00.000 8230
24984       8        2618                                    1        2012-06-28 13:15:00.000 8230
24982       8        5034                                    1        2012-06-28 13:15:00.000 8230
24980       8        1969                                    1        2012-06-28 13:15:00.000 8230
24978       8        6904                                    1        2012-06-28 13:15:00.000 8230
24970       1        2635                                    1        2012-06-28 08:45:00.000 8228
24976       1        5040                                    1        2012-06-27 17:00:00.000 8229
24974       1        5034                                    1        2012-06-27 17:00:00.000 8229
24972       1        6904                                    1        2012-06-27 17:00:00.000 8229

Any idea's or suggestions?

Kwozzie
Starting Member

4 Posts

Posted - 2012-07-03 : 00:19:25

Solved. Case statement, subquery and join.


$sql = "SELECT 
	tblItems.Findex,  
	tblItems.SerialNumber,
	tblItems.Description,
	tblDivisions.Division, 
	tblCategories.Category,
	tblItems.BranchId,
	tblBranchTransfers.BranchId as TransferBranchId,
	(SELECT ShortName FROM tblBranches WHERE tblBranches.BranchId = (CASE WHEN tblBranchTransfers.BranchId >0 THEN tblBranchTransfers.BranchId ELSE tblItems.BranchId END)) as site_name
FROM 
	tblItems 
JOIN 
	tblDivisions ON tblItems.DivisionId = tblDivisions.DivisionId 
JOIN 
	tblCategories ON tblItems.DivisionId = tblCategories.DivisionId AND tblItems.CategoryId = tblCategories.CategoryId 
LEFT JOIN 
	tblBranchTransfers ON tblBranchTransfers.AID = (SELECT max(AID) FROM tblBranchTransfers WHERE tblItems.Findex = tblBranchTransfers.Findex AND Quantity = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources