I'm really struggling with this and hoping someone can help me AND I need to restate that I am a SQL noob!
First attempt:
This query is returning all the data I need and then some! There are two fields I can use to try to filter on InventoryDate and RefCodeID. As you will see in the next example I've tried without success to get the info I need.
Code Snippet
SELECT B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN InventoryDate AS IStatus
ON I.InventoryID = IStatus.InventoryID
AND IStatus.RefCodeID IN (71, 73) -- Open, Spec, Sale
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale
ON I.InventoryID = BSale.InventoryID
LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN RefCode AS RC
ON IStatus.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'Copperfield'
AND P.PhaseID IN (114, 119, 120)
AND Pur.PurchaserName NOT LIKE '%HRC%'
Second attempt:
I've tried adding a select statement in the select statement but it's not working for me, if you have any questions please ask, I'm not sure what Info you will need to help me solve this puzzle.
Code Snippet
SELECT P.PhaseName,
B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
-- RC.RefCodeName AS Status,
(SELECT RC.RefCodeName AS Status
FROM InventoryDate AS IDate
JOIN RefCode AS RC
ON IDate.RefCodeID = RC.RefCodeID
WHERE IDate.InventoryID >= BSale.InventoryDate
AND IDate.InventoryDate = (SELECT MAX(InventoryDate)
FROM InventoryDate AS IDate2
WHERE IDate.InventoryID = IDate2.InventoryID)) AS 'Status',
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN InventoryDate AS BSale
ON I.InventoryID = BSale.InventoryID
AND BSale.RefCodeID IN (70, 71, 73)
WHERE PJ.ProjectName = 'Copperfield'
AND P.PhaseID IN (114, 119, 120)
AND Pur.PurchaserName NOT LIKE '%HRC%'
any and all help is appreciated
Thanks
Wade
Wade:
In addition to your queries what we also need is (1) sample source data and (2) what the target output should look like; It is rather like you have given us a gun without any munitions and without a target and said, "Shoot!"
You also need to give us the function definitions for the two table functions.
|||Hi WadeG,
When I write code, I break it down to simpler levels. As I get the data I need, I add more of the code and until I have it the way I need it. The problem with this is the level of complication. Break it down, and either eliminate or add code that will better define your query. Not a solution, but a method that will lead to the solution.
dbmsql
|||This query may fix your problem,
Code Snippet
SELECT B.BlockName AS Block,
I.Lot,
R.RefCodeName AS LotType,
I.SaleableFrontFootage As Frontage,
ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,
AI.HouseStyle AS Style,
RC.RefCodeName AS Status,
PUR.PurchaserName as Builder
FROM Inventory AS I
JOIN Block AS B
INNER JOIN Phase AS P
INNER JOIN ProjectSub AS PS
INNER JOIN Project AS PJ
ON PS.ProjectID = PJ.ProjectID
ON P.ProjectSubID = PS.ProjectSubID
INNER JOIN PhaseSetup AS PSetup
ON P.PhaseID = PSetup.PhaseID
ON B.PhaseID = P.PhaseID
ON I.BlockID = B.BlockID
LEFT JOIN InventoryDate AS IStatus
ON I.InventoryID = IStatus.InventoryID
AND IStatus.RefCodeID IN (71, 73) -- Open, Spec, Sale
LEFT JOIN RefCode AS R
ON I.LotTypeRefCodeID = R.RefCodeID
LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale
ON I.InventoryID = BSale.InventoryID
LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA
ON I.InventoryID = IA.InventoryID
LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR
ON I.InventoryID = PUR.InventoryID AND Pur.PurchaserName NOT LIKE '%HRC%'
LEFT JOIN ArchitectureInformation as AI
ON I.InventoryID = AI.InventoryID
LEFT JOIN RefCode AS RC
ON IStatus.RefCodeID = RC.RefCodeID
WHERE PJ.ProjectName = 'Copperfield' AND P.PhaseID IN (114, 119, 120)
|||The problem (I think) is with this piece of code:
Code Snippet
(SELECT RC.RefCodeName AS Status
FROM InventoryDate as IDate
JOIN RefCode AS RC
ON IDate.RefCodeID = RC.RefCodeID AND IDate.RefCodeID IN (70, 71, 73)
WHERE IDate.InventoryID = I.InventoryID
AND IDate.InventoryDate = (SELECT MAX(InventoryDate)
FROM InventoryDate AS IDate2
WHERE IDate.InventoryID = IDate2.InventoryID)) AS 'Status',
What I need to do is be able to pick the latest (MAX) date transaction, I just can't seem to get it to work. There are several tables involved, I can try to post whatever info you need.
Bascally the query with out the above code (Just using RC.RafCodeName AS Status) works it's just returning too many results.
I hope that makes sense.
, what field are you missing?