Dans mon code de server sql, j'ai cette instruction select
select distinct a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name from NewHire a join Position b on a.Position_ID = b.Position_ID join WorkPeriod c on a.hireID = c.HireID where a.Archived = 0 and c.InquiryID is not null order by a.HireID DESC, a.HireLastName, a.HireFirstName
Et je veux append une nouvelle colonne. Cependant, cette colonne n'est pas une colonne d'une table, elle est simplement utilisée pour stocker un float
partir d'un calcul que je fais à partir de colonnes existantes.
Le nombre que je reçois est calculé comme ceci: @acc
est le a.HireID
de l'instruction select ci-dessus.
CAST((select COUNT(*) from Hire_Response WHERE HireID = @acc AND (HireResponse = 0 OR HireResponse = 1)) as FLOAT) / CAST((select COUNT(*) from Hire_Response WHERE HireID = @acc) as FLOAT)
Comment puis-je faire ceci? Merci.
Cela devrait le faire
select distinct a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name, CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireID AND (HireResponse = 0 OR HireResponse = 1)) as FLOAT) / CAST((select case when COUNT(*) = 0 then 1 else COUNT(*) end from Hire_Response WHERE HireID = a.HireID) as FLOAT) as mySpecialColumn from NewHire a join Position b on a.Position_ID = b.Position_ID join WorkPeriod c on a.hireID = c.HireID where a.Archived = 0 and c.InquiryID is not null order by a.HireID DESC, a.HireLastName, a.HireFirstName
Vous avez juste besoin d'append le calcul à votre instruction select comme je l'ai dit ci-dessous, j'ai également alaisé le calcul avec un nom de colonne pour vous:
select distinct a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name, CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HierID AND (HireResponse = 0 OR HireResponse = 1)) as FLOAT) / CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HierID) as FLOAT) AS [Calculation] from NewHire a join Position b on a.Position_ID = b.Position_ID join WorkPeriod c on a.hireID = c.HireID where a.Archived = 0 and c.InquiryID is not null order by a.HireID DESC, a.HireLastName, a.HireFirstName
Vous devez append un alias pour la colonne. C'est en supposant que votre calcul est correct reference la colonne dont vous avez besoin.
select (Calculated Column expression) as CalculatedColumn, * from TableName select distinct a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name, (CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireIDAND (HireResponse = 0 OR HireResponse = 1)) as FLOAT) / CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireID) as FLOAT)) as CalculatedColumn from NewHire a join Position b on a.Position_ID = b.Position_ID join WorkPeriod c on a.hireID = c.HireID where a.Archived = 0 and c.InquiryID is not null order by a.HireID DESC, a.HireLastName, a.HireFirstName
Difficile d'essayer de comprendre l'objective sans un exemple de la structure des données, mais ici va. J'ai ajouté un nouveau JOIN, et la clause GROUP BY:
select distinct a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name, sum(case when hr.HireResponse in (0, 1) then 1.00 else 0.00 end) as Numerator, count(hr.*) as Denominator, sum(case when hr.HireResponse in (0, 1) then 1.00 else 0.00 end) / count(hr.*) as Fraction from NewHire a join Position b on a.Position_ID = b.Position_ID join WorkPeriod c on a.hireID = c.HireID inner join Hire_Response hr on a.HireID = hr.HireID where a.Archived = 0 and c.InquiryID is not null group by a.HireLastName, a.HireFirstName, a.HireID, a.Position_ID, a.BarNumber, a.Archived, a.DateArchived, b.Position_Name order by a.HireID DESC, a.HireLastName, a.HireFirstName