Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
 Posted - 2012-01-19 : 17:45:41
|
| Hi all I have following data in my table .[/b]ESN ManufNbr ISFound AvailbleLCN EnrollDate CancelDate repair_month350288002087259 350288002087259 Y 31100530770 2010-01-24 00:00:00.000 NULL 201112351543014566545 351543014566545 Y 30960876238 2008-05-05 00:00:00.000 2011-07-28 00:00:00.000 201112351622040012201 351622040012201 Y 31103334913 2010-05-11 00:00:00.000 NULL 201111351622040012532 351622040012532 Y 31033201002 2010-05-01 00:00:00.000 NULL 201111351622040029403 351622040029403 Y 51000064481 2010-05-24 00:00:00.000 NULL 201112351622040029965 351622040029965 Y 81060140790 2010-06-06 00:00:00.000 NULL 201111351622040034502 351622040034502 Y 51040036667 2010-04-29 00:00:00.000 NULL 201111351622040042679 351622040042679 Y 90970257157 2010-05-20 00:00:00.000 NULL 201110 [/b]What is My equirement :I want to disply one more Statu [Value should be "active' or "Inactive' column along with these colums.I want to check EnrollDate with Repair_Month colum after extracting month part and yea year Part .these part will be compared with Repair_month column value ...Repair_Month column contais value like 201212 where 2012 is year and 12 is month.I want check if Enrolldate is less than Repair_month data then will check for canceldate value if it iexist(means not not ) then again it will be compare with Reapir_month data as Enrolldate was compared.IF this Canceldt < Repair_month then status should be "INACTIVE' if not then it will be ''ACTIVE'Or Only Enrolldate is availble and which is less than Repaimonth data then it should be by default 'ACTIVE'I tried to write and implement this but unexpected resyult is coming out ..SELECT ESN,ManufNbr,ISFound,AvailbleLCN,EnrollDate,CancelDate,repair_month , CASE WHEN substring(REPAIR_Month,1,4) <= cast(DATEPART(YEAR,ENROLLDATe)as varchar(10)) and substring(REPAIR_Month,5,2) <= cast(DATEPART(MM,ENROLLDATe) as varchar(10))then Case when canceldate IS Not null then case when substring(REPAIR_Month,1,4) < cast(DATEPART(YEAR,Canceldate) as varchar(10)) and substring(REPAIR_Month,5,2) < cast(DATEPART(MM,Canceldate) as varchar(10)) then 'InActive' end else 'Active'end end as Satus FROM #temp Giveing me unexpected result ..Please advise |
|
|
|
|
|
|
|
Need update on the basis data month and year