Order By with Sub Query in SQL Server

Last Reply 9 months ago By paulrajmca

Posted 9 months ago

Hi Friends ,

I am creating a project for ngo trust.A sponsor have to make birthday wishes for their adopted childs.its one or more than one.

my Requirement is in birthday home page i have 3 children (it can be less or more)

1.if i click 2 nd child the next page should bind 2nd child first then other two childs details . the problem is i am using slider.

2.if i click 3rd child to wish the next page should bind 3 rd child first then bind other two childs details on the slider one by one.

now i have bind 3 childrens order by birthdate . but how can i bind Clicked child firts and others next .

this is the Sql query which is am using

        SELECT q1.*, CASE WHEN MONTH(q1.Birthdate)=MONTH(GETDATE()) AND DAY(q1.Birthdate)>=DAY(GETDATE()) THEN 0 WHEN MONTH(q1.Birthdate)>MONTH(GETDATE()) THEN 1 ELSE 2 END AS ChildOrder FROM (
	SELECT PD.PledgeId, CD.ChildID, CD.GivenName as ChildName, CD.Birthdate, (CONVERT(int,DATEDIFF(d, CD.Birthdate, GETDATE()) / 365.25)+1) AS Age, DATENAME(MONTH, CD.Birthdate) AS BirthMonth, CONVERT(CHAR(3), DATENAME(MONTH, CD.Birthdate)) AS BirthMonth2, DAY(CD.Birthdate) as BirthDay, 2 AS NoDays, CD.WebPhotoPath as WebPhotoPath, PD.PaidThruDate, CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate1, CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate2
	FROM Pledge_PledgeDetails PD 
	INNER JOIN Child_ChildDetails CD ON CD.ChildID=PD.ChildID
	WHERE PD.LeadID=@LeadID AND CD.ProjectID not in (167948,197358,178124) AND PD.Status in (2) 
	and (CONVERT(DATE,GETDATE())		-- Display child's birthday with 45 days interval
	BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) 
		AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) OR 
		CONVERT(DATE,GETDATE()) 
	BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1)))) 
		AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1))))
		)
	) AS q1
	WHERE q1.ChildID NOT IN (SELECT ChildID FROM CMS_ChildBirthdayWishes WHERE CreatedDate BETWEEN q1.BtnBDayDate1 AND q1.BtnBDayDate2)
	ORDER BY ChildID,ChildOrder, MONTH(q1.Birthdate), DAY(q1.Birthdate) ASC

anyone know kindly suggest me to done this task.

 thanking you

Paul.S

Posted 9 months ago
Hi @paulrajmca,
Please try the following

Show Last 7 Records using Order By with Sub Query in SQL Server

It might help you.

Cheers Andrea.

Posted 9 months ago
  • thanking you pandeyism for your helpful reply and interest .then thanks to all 
  • i got the answer 

 this is the line i have changed 

SELECT q1.*, (case when childid = @childid then 0 else 1 end) as ClickOrder, 
SELECT q1.*, (case when childid = @childid then 0 else 1 end) as ClickOrder, 
	CASE WHEN MONTH(q1.Birthdate)=MONTH(GETDATE()) AND DAY(q1.Birthdate)>=DAY(GETDATE()) THEN 0 WHEN MONTH(q1.Birthdate)>MONTH(GETDATE()) THEN 1 ELSE 2 END AS ChildOrder FROM (
	SELECT PD.PledgeId, CD.ChildID, CD.GivenName as ChildName, CD.Birthdate, 
	(CONVERT(int,DATEDIFF(d, CD.Birthdate, GETDATE()) / 365.25)+1) AS Age, DATENAME(MONTH, CD.Birthdate) AS BirthMonth, 
	CONVERT(CHAR(3), DATENAME(MONTH, CD.Birthdate)) AS BirthMonth2, DAY(CD.Birthdate) as BirthDay, 
	2 AS NoDays, CD.WebPhotoPath as WebPhotoPath, PD.PaidThruDate, 
	CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate1, 
	CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) as BtnBDayDate2
	--ROW_NUMBER()OVER (PARTITION BY cd.childid ORDER By cd.birthdate asc) as RowNumbers
	FROM Pledge_PledgeDetails PD   
	INNER JOIN Child_ChildDetails CD ON CD.ChildID=PD.ChildID
	WHERE PD.LeadID=@LeadID AND CD.ProjectID not in (167948,197358,178124) AND PD.Status in (2) 
	and (CONVERT(DATE,GETDATE())		-- Display child's birthday with 45 days interval
	BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) 
		AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), YEAR(GETDATE())))) OR 
		CONVERT(DATE,GETDATE()) 
	BETWEEN CONVERT(DATE,DATEADD(day,-45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1)))) 
		AND CONVERT(DATE,DATEADD(day,+45, REPLACE(CD.Birthdate, YEAR(CD.Birthdate), (YEAR(GETDATE())-1))))
		)
	) AS q1
	WHERE q1.ChildID NOT IN (SELECT ChildID FROM CMS_ChildBirthdayWishes WHERE CreatedDate BETWEEN q1.BtnBDayDate1 AND q1.BtnBDayDate2)
	ORDER BY ClickOrder, ChildOrder, MONTH(q1.Birthdate), DAY(q1.Birthdate) ASC