All we need is an easy explanation of the problem, so here it is.
I have these tables in which I need to use Row_number()
in where
clause like this
where spectrumcms.staffchilddiscountpolicy.childno =
(select ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC))
I have tables like these
Now i have this query
select
std.ID as ID,
std.FirstName,
std.LastName,
emp.ID as EmpID,
emp.FirstName + '' + emp.LastName as EmpName,
emp.CNIC,
(
select count(*) as NoOfChilds
from StudentManagement.SpectrumCMS.Student stdd
join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
where empp.ID = emp.ID and stdd.CompanyID = std.CompanyID and empp.CompanyID = emp.CompanyID
) as NoOfchilds,
ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo
from StudentManagement.SpectrumCMS.Student std
join HRManagement.SpectrumCMS.Employee emp on std.GuardianCNIC = emp.cnic
where std.CompanyID = 20145
and emp.CompanyID = 20145
and std.IsActive = 1
and emp.IsActive = 1
order by emp.CNIC
Now coming to main question why I need where=row_number()over(partition by col order by col)
:
Because i need to get percentage in last column of above mentioned query from the table of which i have posted the picture of.
This is what currently i am getting
Now in last percentage
column, I need percentage
from the staffchilddiscountpolicy
table of which I have added picture
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
As mentioned in the comments, you can’t use a window function in that capacity. Rather you should take your first query and put it in a subquery or CTE such that you can then join on ChildNo
between it and the SpectrumCMS.StaffChildsDiscountPolicy
table like so:
WITH CTE_StudentChildNumbers AS
(
select
std.ID as ID,
std.FirstName,
std.LastName,
emp.ID as EmpID,
emp.FirstName + '' + emp.LastName as EmpName,
emp.CNIC,
(
select count(*) as NoOfChilds
from StudentManagement.SpectrumCMS.Student stdd
join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
where empp.ID = emp.ID
and stdd.CompanyID = std.CompanyID
and empp.CompanyID = emp.CompanyID
) as NoOfchilds,
ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo
from StudentManagement.SpectrumCMS.Student std
join HRManagement.SpectrumCMS.Employee emp on std.GuardianCNIC = emp.cnic
where std.CompanyID = 20145
and emp.CompanyID = 20145
and std.IsActive = 1
and emp.IsActive = 1
)
SELECT
SCN.ID,
SCN.FirstName,
SCN.LastName,
SCN.EmpID,
SCN.EmpName,
SCN.CNIC,
SCN.NoOfchilds,
SCN.ChildNo,
SCDP.Percentage
FROM CTE_StudentChildNumbers AS SCN
INNER JOIN SpectrumCMS.StaffChildsDiscountPolicy SCDP
ON SCN.ChildNo = SCDP.ChildNo
WHERE SCDP.CompanyID = 20145 -- Not sure if you wanted the same filters from your screenshot, feel free to remove if not needed
AND SCDP.IsActive = 1
ORDER BY SCN.CNIC
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0