Loading

PLZ HELP - SQL QUERY

PLZ HELP - SQL QUERY

Riddhi Valecha

10y1kLearn iOS Programming

Dear all,

Again I am stuck up in an SQL Query..

Please guide for the same..

Allocation Table

ID	SerialNumber	AssignTo	Status
1	ASERTU	Shekhar	Assigned
2	ASERTU	Kiran	Assigned
3	CXZUYT	Mohan	Available

Certification Details

ID	SerialNumber	CertifiedBy
1	ASERTU	Shekhar
2	CXZUYT	Mohan

Logic–

System will pick the “SerialNumber” and “CertifiedBy” column from “Certification Details”

Eg – “ASERTU” and “Shekhar”

Get MAX ID from “Allocation Table” where “Serial Number” = “ASERTU”. The ID is = 2.

Get “AssignTo” name where ID = 2 from “Allocation Table”. The name is = Kiran

Check if “Certified By”(from “Certification Details”) and “Assign To”(From “Allocation Table”) are same or different.

If same, then gridview backcolour = red; else yellow.

Please guide.. How to do this activity in backend; hence reduce font-end code-lines.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

ketan borsdiya10y ago
Hi Riddhi,
used below query for your output,
Select A.ID, A.SerialNumber,A.AssignTo,A.[Status],B.CertifiedBy from Allocation A
inner join Certification B
on A.SerialNumber=B.SerialNumber
Where A.Id in (
Select MAX(Id) from Allocation Group by SerialNumber)

get output in your object like dt etc
then compare your column for AssignTo and CertifiedBy for gridview row color.
Bikesh Srivastava10y ago
Yo need to create a stored procedure.
Midhun Tp10y ago
Hi,
Try below query -
select t1.serialnumber,t1.certifiedby,t2.id,t2.assignto,t2.status,case when t1.certifiedby=t2.assignto then 'Red' else 'Yellow' end as Backcolor from CertificationDetails t1
inner join
(select *,RANK() over(partition by serialnumber order by id desc) as Ranking from AllocationTable)
t2 on t1.serialnumber=t2.serialnumber where t2.Ranking=1