want to check the row with previous column

Question

hi am having a table which contains nonamedobalivestatus(yes/no) that whether he s alive or not weddingdate couplename i want to display the wedding report. if the name of the member and cname is alive means i want to display their name with wedding date. how to write a query for this pls help me sir. here s my sample table No Name Dob alive status weddingdate couplename 1 sanju 2/2/1980 yes 6/23/2009 Divya 1 divya 3/4/1983 yes 2 ashok 4/4/1983 yes 3/3/2010 anju 2 anju 3/6/1986 no in this basic details of sanju and other members details are inserted. sanju's wife is divya. so divya doesnt contain the weddingdate and couplename bcse already enterd in sanju's details. so i want to check the husband and wife s alive or not if they both alive means i want to display the wedding report

Muralidharan Deenathayalan · Answer

I've changed the query please try the below one. select w1.Namew1.CoupleNameW1.WeddingDatew1.[alive status] w2.[alive status]as 'Status1' from wedding w1 inner join Wedding w2 on w1.No= w2.No and w2.[alive status]=w1.[alive status] and w2.[alive status] =1 and w1.coupleName = w2.name Hope this helps you.

raji b · Answer

hi sir thanks sir. i correct my mistake instead of putting where i put and now it corrected and runned sussefuly thanku so muchhh

Muralidharan Deenathayalan · Answer

What is the error message?

raji b · Answer

Hi sir i want to display the wedding date between 2 specified dates. for eg: no name dob alivestatus weding date couplename 1 sanju 2/21980 yes 6/23/2009 divya 1 divya 3/4/1983 yes 2 ashok 4/4/1983 yes 6/25/2010 anju 2 anju 3/6/1986 yes 3 ashok 2/5/1982 yes 5/5/2010 priya from date:6/22/2013 todate: 6/28/2009 means no name wedding date 1 sanju&divya 6/23/2009 2 ashok 6/25/2010 this is my query select Rtrim(w1.Member_Name)+'&'+ w1.cname as name CAST(DAY(w1.wdate) AS VARCHAR(2)) + ' ' + DATENAME(MM w1.wdate) AS wdate from Family_Member w1 inner join Family_Member w2 on w1.M_No= w2.M_No and w2.alivestatus =w1.alivestatus and w2.alivestatus ='yes' and w1.cname = w2.Member_Name and DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/27/2009') w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' OR DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/30/2009')w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' ORDER BY CASE WHEN DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/27/2009') w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' THEN 1 ELSE 2 END DATEPART(MONTH w1.wdate) DATEPART(DAY w1.wdate) its gets error. the error s n bold text. this bold text displays the full name. It is not checked the alive status

raji b · Answer

Hi sir i want to display the wedding date between 2 specified dates. for eg: no name dob alivestatus weding date couplename 1 sanju 2/21980 yes 6/23/2009 divya 1 divya 3/4/1983 yes 2 ashok 4/4/1983 yes 6/25/2010 anju 2 anju 3/6/1986 yes 3 ashok 2/5/1982 yes 5/5/2010 priya from date:6/22/2013 todate: 6/28/2009 means no name wedding date 1 sanju&divya 6/23/2009 2 ashok 6/25/2010

raji b · Answer

want to display the wedding date between frmdate to todate

Muralidharan Deenathayalan · Answer

Hi Can you please explain bit more? Thanks Murali

raji b · Answer

hi i want to display the name wedding date as specfied from date to date for eg wedding date fromdate 6/20/2008 todate 6/25/2009 means sanju 6/23/2009 is displayed my query is select Rtrim(w1.Member_Name)+'&'+ w1.cname as name CAST(DAY(w1.wdate) AS VARCHAR(2)) + ' ' + DATENAME(MM w1.wdate) AS wdate from Family_Member w1 inner join Family_Member w2 on w1.M_No= w2.M_No and w2.marital =w1.marital and w2.marital ='Married' and w1.cname = w2.Member_Name and DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/27/2009') w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' OR DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/30/2009')w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' ORDER BY CASE WHEN DATEADD(YEAR DATEDIFF(YEAR w1.wdate '9/27/2009') w1.wdate) BETWEEN '9/27/2009' AND '9/30/2009' THEN 1 ELSE 2 END DATEPART(MONTH w1.wdate) DATEPART(DAY w1.wdate) so pls correct my query

raji b · Answer

thank u so much

raji b · Answer

here it checks the alive status only the sanju's name not check for divya.what can i do for tht

Muralidharan Deenathayalan · Answer

Used No column in the join. select w1.Namew1.CoupleNameW1.WedddingDate from wedding w1 inner join Wedding w2 on w1.No= w2.No and w2.[alive status]= 'yes' and w2.coupleName '' and w1.WedddingDate ''

Muralidharan Deenathayalan · Answer

Hi you need to use self-join. select w1.Namew1.CoupleNameW1.WeddingDate from wedding w1 inner join Wedding w2 on w1.CoupleName = w2.CoupleName and w2.[Alive status]= 'yes' and w2.coupleName '' and w1.WeddingDate '' Please let me know if it is not solving your problem.

No	Name	Dob	alive status	weddingdate	couplename
1	sanju	2/2/1980	yes	6/23/2009	Divya
1	divya	3/4/1983	yes
2	ashok	4/4/1983	yes	3/3/2010	anju
2	anju	3/6/1986	no