Order By with Sub Query in SQL Server

Last Reply 8 months ago By paulrajmca

Posted 8 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

You are viewing reply posted by: paulrajmca 8 months ago.
Posted 8 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