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,
在SQL视图中,我有5个布尔变量和一个int变量。在5个布尔变量中,对于单个数据行,只有一个变量为真,
Task Type boolVerySmall Datestart TagName
Architecture Setup -- Doc Code True 1900-01-01 00:00:00.000 Design_09
架构设置 - Doc Code True 1900-01-01 00:00:00.000 Design_09
idProject boolsmall boolMedium boolLarge boolVeryLarge intHours
4 False False False False 0
4假假假假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,
以上数据是一行...在这一行中,当我将数据从数据库下载到excel表时,我只需显示值为true(boolVerySmall,boolsmall,boolMedium,boolLarge,boolVeryLarge,intHours)应该是显示在一列中,
I have written a Stored procedure for this. I am finding it difficult to get a particular row which has TRUE of intHours>0.
我为此编写了一个存储过程。我发现很难得到一个TRUE为intHours> 0的特定行。
I am adding the sql query below. Please help me
我在下面添加sql查询。请帮我
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
选择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
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,
我的存储过程是SELECT txtTask,txtProductType,boolVerySmall,txtTagName,txtTaskNotes,
dtActualCompletionDate FROM tblResourceTaskList_View WHERE (idSubProject = @idSubProjectIndex )
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.
因为db的第一行对boolVerySmall来说是TRUE,那么SP应该返回这个值,如果不是,那么它应该给出该值。
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.
我不得不在excel表中填充True,而是在excelsheet中为boolVerySmall,S - Small,M - Medium,L - Large VL- VeryLarge分配VS.
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...
所以请帮我解决这个问题。 1.分配短名称(如VS ...用于bool var)。 2.仅返回一个值(boolverysmall或...)并将VS分配给它并将其填入excel。 3.如果所有的bool都是假的,那么intHours应该是...
Kindly help me.
请帮助我。
Thanks Ramm
1 个解决方案
#1
I am able to assign values to SP using CASE statements.
我可以使用CASE语句为SP分配值。
Now, its workign fine.
现在,它的工作很好。
Thanks
Ramm
#1
I am able to assign values to SP using CASE statements.
我可以使用CASE语句为SP分配值。
Now, its workign fine.
现在,它的工作很好。
Thanks
Ramm