Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating the output based on bit values in an Stored proc


Updating the output based on bit values in an Stored proc

Author
Message
VSSGeorge
VSSGeorge
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 1406
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


Amit Raut
Amit Raut
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search