Hi ,
In a SQL View, I have 5 boolean variables and one int variable. out of 5 boolean variables only one variable will be true for a single data row,
Task Type boolVerySmall Datestart TagName
Architecture Setup -- Doc Code True 1900-01-01 00:00:00.000 Design_09
idProject boolsmall boolMedium boolLarge boolVeryLarge intHours
4 False False False False 0
The above data is for one row... in this row, when I download the data from database to excel sheet, I have to display only the value which is true (boolVerySmall ,boolsmall, boolMedium,boolLarge, boolVeryLarge, intHours) should be displayed in a single column,
I have written a Stored procedure for this. I am finding it difficult to get a particular row which has TRUE of intHours>0.
I am adding the sql query below. Please help me
SELECT dbo.tblResourceTaskList.txtTask, dbo.tblIndividualRelativeData.txtProductType, dbo.tblResourceTaskList.boolVerySmall, dbo.tblResourceTaskList.dtActualCompletionDate, dbo.tblEffortCodes.txtTagName, dbo.tblResourceTaskList.txtTaskNotes, dbo.tblResourceTaskList.idSubProject, dbo.tblResourceTaskList.idLaunch, dbo.tblResourceTaskList.idResource, dbo.tblResourceTaskList.boolSmall, dbo.tblResourceTaskList.boolMedium, dbo.tblResourceTaskList.boolLarge, dbo.tblResourceTaskList.boolVeryLarge, dbo.tblResourceTaskList.intDirectHours
FROM dbo.tblResourceTaskList INNER JOIN dbo.tblProjectUsers ON dbo.tblResourceTaskList.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblResourceTaskList.idResource = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblLaunchInfo ON dbo.tblResourceTaskList.idLaunch = dbo.tblLaunchInfo.idLaunch INNER JOIN dbo.tblIndividualRelativeData ON dbo.tblResourceTaskList.idIndividualRelativeEffort = dbo.tblIndividualRelativeData.idIndividualRelativeEffort LEFT OUTER JOINdbo.tblEffortCodes ON dbo.tblResourceTaskList.idEffortCode = dbo.tblEffortCodes.idEffortCode
My stored procedure is SELECT txtTask, txtProductType, boolVerySmall, txtTagName, txtTaskNotes,
dtActualCompletionDate FROM tblResourceTaskList_View WHERE (idSubProject = @idSubProjectIndex )
as the first row from db has TRUE for boolVerySmall, then SP should return this value and if its other than that it should give that value.
Instead of filling True in excel sheet, I , have to assign VS for boolVerySmall, S - Small, M - Medium, L - Large VL- VeryLarge in the excelsheet.
So please help me how to work on this. 1. Assigning shortnames (like VS... for the bool var's). 2. Returning only one value (boolverysmall or ... ) and assigning VS to that and fill it in excel. 3. if all the bools are false, then intHours should be assinged...
Kindly help me.
Thanks Ramm