I need to get the single row for a company. Company has participated in more than one event.
I pass EventCode and Event year to this view1, has following details
CompanyName EventCd iShowYear Status
ABC SAA 2004 SOLD
ABC SAA 2006 SOLD
ABC SJJ 2007 SOLD
ABC SHJ 2008 HOLD
Let say i pass EventCd SAA and Event Year 2004, 2005, 2006, 2007, 2008 (list box selection)
Result should be
CompanyName EventCd Status2004 Status2005 Status2006 Status2007 Status2008
ABC SAA SOLD 0 SOLD 0 0
i try to use the following query but during each while loop it selects the whole set i.e 5 times selecting all the records. "Status" +@Year1 also giving me the error.
Could anyone help me?
DECLARE @year1 int, @year2 int, @statusyear char(10)
SET @YEAR1 = YEAR(GETDATE())-4
SET @YEAR2 = YEAR(GETDATE())+3
WHILE (@YEAR1 <= @YEAR2)
Select vchCompanyName, iOWNERID, vchPhonenumber, vchEmailAddress, chCountryDesc,
WHEN CHorderlinestatus='C' AND iShowYear=@YEAR1 THEN 'SOLD'
WHEN CHorderlinestatus='B' AND iShowYear=@YEAR1 THEN 'HOLD'
), 0)) AS Status_year_@YEAR1
Group BY vchCompanyName, iOWNERID, vchPhonenumber, vchEmailAddress, chCountryDesc, chShowCd, iShowYear
SET @YEAR1 = @YEAR1 +1