jbalbo (4/14/2014)
I think I need something like thisBut I really have my where clause screwed up !!!
sELECT distinct dbo.RECORDED_SERVICE.OID, MAX(dbo.RECORDED_SERVICE.STARTTIME) AS Expr1, dbo.SERVICE_ITEM.SERVICE_ITEM_DESCRIPTION, dbo.CostCenter.AbbrName,
dbo.Recorded_Service_Helper.Client_ID
FROM dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OID ON
dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter
where STARTTIME <
(select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)
You should get in the habit of aliasing your tables in your query. It makes life about a gazillion time easier. Also, it is rumored that referencing by full name in the select list is going to be deprecated.
Here is what your query looks like as you posted it using aliases.
SELECT distinct rs.OID,
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs --Missing one here
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID
ON dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter --??? What is this???
where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)
There are some definite issues with the sql you posted. You have a join with no ON clause. You have an extra ON clause at the end. You have an aggregate but no group by. You are using distinct and aggregate in the same query. You have a subquery that I am assuming you want to reference the OID of the inner query but you don't reference it in your subquery.
A complete shot in the dark since we have no idea what these tables are like...
SELECT rs.OID,
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs on rs.OID = cc.OID --Don't know if this is right or not????
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID
where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE rs2 where OID = rs.OID)
--using an alias here we can now reference the outer table to get the value.
group by rs.OID,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/