Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating the output based on bit values in an Stored proc Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 6:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:40 AM
Points: 593, Visits: 1,082
Hi,

I have a situation like this:
I have four radio buttons in my SSRS report which is used for visibility (making visible/ hidden) of the respective parts in the report.
@ShowApprovedWorkload
@ShowDraftWorkload
@ShowDoneWorkload
@ShowInProgressWorkload
Presently in the stored proc, the total is hsowing, without considering the visiblity. Ie. when the approved workload needs to be shown in the report and Draft Workload not required to be shown in the report depending upon the value of parameters @ShowApprovedWorkload = 1 and @ShowDraftWorkload = 0. The approved workload is 10 and Draft Workload is 10, the total workload should be 10, as @ShowDraftWorkload = 0. But the total workload now shown is 20 itself.

In my calculation I want to use these parameters in such a way that the total workload is calculated correctly based on the visibilty required.

For example, if approved workload is 10 and Draft Workload is 10, the total workload should be 20, when the approved workload and Draft Workload needs to be shown in the report depending upon the value of parameters only if @ShowApprovedWorkload = 1, @ShowDraftWorkload = 1, @ShowDoneWorkload = 0 and @ShowInProgressWorkload = 0.

Please help in finding the solution and I attaching the existing stored proc.


ALTER PROCEDURE [dbo].[TotalWorkload_Calculation]
(@from_date datetime,
@to_date datetime,
@ShowApprovedWorkload bit,
@ShowDraftWorkload bit,
@ShowDoneWorkload bit,
@ShowInProgressWorkload bit)

AS

--DECLARING VARIABLES
declare @Minval as int
declare @Maxval as int
declare @Act_ID as int
declare @Act_MAXID as int

Begin
set @Minval = (select min(sno1) from #Activities2)
set @Maxval = (select max(sno1+1) from #Activities2 )

---------*********** This is where I need to apply the @ShowApprovedWorkload, @ShowDraftWorkload, @ShowDoneWorkload, @ShowInProgressWorkload filters ( where the total workload is calculated ) ------******************

-- total workload by day
create table #WorkloadTMP1 (Thedate datetime,workload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where sno1 = @Minval)

declare @start_date1 datetime
declare @end_date1 datetime
declare @budget1 float
declare @Datediff1 int
set @start_date1 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date1 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget1 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff1 = (select datediff(day,@start_date1,@end_date1)+1)

insert into #WorkloadTMP1
SELECT *,CAST(@budget1 as float)/cast(@datediff1 as float) ,@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date1,@end_date1)
------SELECT * FROM #WorkloadTMP1

set @Minval = @Minval+1

end
END

---------***********------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------******************


-- approved workload by day (todo)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2)


create table #WorkloadTMP2 (Thedate datetime,Approvedworkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2 and sno = @Minval) -- ToDo

declare @start_date2 datetime
declare @end_date2 datetime
declare @budget2 float
declare @Datediff2 int
set @start_date2 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date2 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget2 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff2 = (select datediff(day,@start_date2,@end_date2)+1)


insert into #WorkloadTMP2
SELECT *,cast(@budget2 as float)/cast(@datediff2 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date2,@end_date2)
------SELECT * FROM #WorkloadTMP2

set @Minval = @Minval+1

end
end




-- draft workload by day (draft)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1)


create table #WorkloadTMP3 (Thedate datetime,Draftworkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1 and sno = @Minval) -- Draft

declare @start_date3 datetime
declare @end_date3 datetime
declare @budget3 float
declare @Datediff3 int
set @start_date3 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date3 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget3 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID)
set @datediff3 = (select datediff(day,@start_date3,@end_date3)+1)


insert into #WorkloadTMP3
SELECT *,cast(@budget3 as float)/cast(@datediff3 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date3,@end_date3)
------SELECT * FROM #WorkloadTMP3

set @Minval = @Minval+1

End
end




-- done workload by day (done)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6)


create table #WorkloadTMP4 (Thedate datetime,DoneWorkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6 and sno = @Minval) -- Done

declare @start_date4 datetime
declare @end_date4 datetime
declare @budget4 float
declare @Datediff4 int
set @start_date4 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date4 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget4 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff4 = (select datediff(day,@start_date4,@end_date4)+1)


insert into #WorkloadTMP4
SELECT *,cast(@budget4 as float)/cast(@datediff4 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date4,@end_date4)
------SELECT * FROM #WorkloadTMP4

set @Minval = @Minval+1

end
end


-- In progress or On hold status workload by day (In progress or On hold status)
Begin
set @Minval = (select min(sno) from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5))
set @Maxval = (select max(sno+1) from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5))


create table #WorkloadTMP5 (Thedate datetime,ProgressHoldWorkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5) and sno = @Minval) -- In progress or On hold

declare @start_date5 datetime
declare @end_date5 datetime
declare @budget5 float
declare @Datediff5 int
set @start_date5 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date5 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget5 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @Datediff5 = (select datediff(day,@start_date5,@end_date5)+1)


insert into #WorkloadTMP5
SELECT *,cast(@budget5 as float)/cast(@Datediff5 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date5,@end_date5)
------SELECT * FROM #WorkloadTMP4

set @Minval = @Minval+1

end
end


CREATE TABLE #WorkloadTMP_new([Date] DATETIME, TotalWorkload DECIMAL(18,2), approvedworkload DECIMAL(18,2) , draftworkload DECIMAL(18,2), Doneworkload DECIMAL(18,2), ProgressHoldWorkload DECIMAL(18,2))
------INSERT into #WorkloadTMP
select T1.Thedate,T1.WORKLOAD,T2.Approvedworkload,T3.Draftworkload,T4.DoneWorkload, T5.ProgressHoldWorkload
into #WorkloadTMP
from #WorkloadTMP1 T1
Left Join #WorkloadTMP2 T2 on T2.Activityincid = T1.Activityincid and T2.thedate = T1.thedate
Left Join #WorkloadTMP3 T3 on T3.Activityincid = T1.Activityincid and T3.thedate = T1.thedate
Left Join #WorkloadTMP4 T4 on T4.Activityincid = T1.Activityincid and T4.thedate = T1.thedate
Left Join #WorkloadTMP5 T5 on T5.Activityincid = T1.Activityincid and T5.thedate = T1.thedate
------select * from #WorkloadTMP
Drop table #WorkloadTMP1
Drop table #WorkloadTMP2
Drop table #WorkloadTMP3
Drop table #WorkloadTMP4
Drop table #WorkloadTMP5

-----Final SELECT Statement
SELECT * FROM #WorkloadTMP
--Go

Post #1522647
Posted Monday, December 16, 2013 12:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
Looks like logic is coded in a complicated way, too much use ot temp tables. Sorry for not looking in depth but you can create a dynamic query to execute a particular block of SQL statements based on parameter selection.
Post #1523407
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse