query and subquery problem - 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 2008 Forums

Transact-SQL (2008)

query and subquery problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2015-04-10 : 13:30:51

I have a simple query for a grid:

SELECT tblPosition.PositionID, tblPositionStatus.StatusCode, tblPosition.Position, tblTitleDetail.Title, tblTitleDetail.AccountCode, tblDepartment.Department, tblDepartment.DepartmentCode, tblBankCode.BankCode, tblPosition.VacantDate, tblProject.ProjectCode,
tblProject.Project, tblPosition.FTE FROM tblPositionStatus INNER JOIN
tblPosition ON tblPositionStatus.PositionStatusID = tblPosition.PositionStatusID INNER JOIN
tblTitleDetail ON tblPosition.TitleDetailID = tblTitleDetail.TitleDetailID INNER JOIN
tblDepartment ON tblPosition.DepartmentID = tblDepartment.DepartmentID INNER JOIN
tblBankCode ON tblPosition.BankCodeID = tblBankCode.BankCodeID LEFT OUTER JOIN
tblProject ON tblPosition.ProjectID = tblProject.ProjectID
WHERE (tblPositionStatus.StatusCode <> 'D')
ORDER BY tblTitleDetail.Title, tblPosition.Position

However I need 3 fields from a table that links with the positionID, tblBudgetMaster.

tblBudgetmaster records have an "effectivedate". I need fields from the tblbudgetmaster record whose effectivedate is the closest to the date the query is run each time such that:

SELECT TOP (1) dbo.tblBudgetMaster.AnnualSalary
FROM dbo.tblBudgetMaster INNER JOIN
dbo.tblPosition ON dbo.tblBudgetMaster.PositionID = dbo.tblPosition.PositionID
WHERE (dbo.tblBudgetMaster.EffectiveDate <= CONVERT(date, CURRENT_TIMESTAMP))
ORDER BY dbo.tblBudgetMaster.EffectiveDate DESC

I see there are other ways to do this (such as partition by with datediff). Anyway I had thought of using a correlated subquery :

SELECT PositionID, Position,

(SELECT TOP (1) AnnualSalary
FROM tblBudgetMaster
where (EffectiveDate <= CONVERT(date, CURRENT_TIMESTAMP)
and PositionID = BM.PositionID )

ORDER BY EffectiveDate DESC) as annualsalary
FROM tblPosition BM
ORDER BY Position

But I need 2 more fields from tblbudgetmaster in addition to the annualsalary so can't do a correlated query.

Other thoughts were a CTE (though not sure how to do this here) and as last resort, a table variable that can hold these budget master fields and the positionID to link with the query

Is there a way to do this in one query?

Thanks

smh
Yak Posting Veteran

94 Posts

Posted - 2015-04-11 : 15:14:31

I found a sample that allowed me to get the query I needed. However, I cannot insert the results of the cte chain into a table so I can link with the other tables in the final query. So the question is:
how does one insert the results of a cte chain into a table.

;with BMcte as
(
select distinct budgetmasterID, positionID, FTE
from tblbudgetmaster
)
,dteCTE as
(
select *
, ranking = row_number() over (partition by budgetmasterID order by EffectiveDate desc)
from tblbudgetmaster
where EffectiveDate <= CONVERT(date, CURRENT_TIMESTAMP)
)

select BMcte.budgetmasterID, BMcte.positionID, BMcte.FTE
, dteCTE.annualsalary
, dteCTE.BudgetedSalaryRate
, dteCTE.effectivedate
from BMcte
left join dteCTE on BMcte.budgetmasterID = dteCTE.budgetmasterID and dteCTE.ranking = 1

when I created a table variable with the fields: budgetmaster, positionID, FTE, annualsalary,budgetedsalaryrate,effective date and used an insert of the 2 cte tables using the same query as the one directly above, I received the error: invalid object name BMcte. When I have one CTE, there is no problem, but how does one insert 2?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources