November 29, 2011 at 2:02 pm
Hi All
I am new to SQL programming and I am trying to amend an RDL report and I need a little help.
At the moment I have a report that runs of the following Stored Procedure, this wasnt created by me.
USE [SQLServer]
GO
/****** Object: StoredProcedure [dbo].[UKC_RSP_PUNCHDETAIL_DEPT] Script Date: 11/29/2011 17:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UKC_RSP_PUNCHDETAIL_DEPT]
@key int, @dept varchar(100), @actadj int
AS
Declare
@rownum int,
@paycodeid int,
@pcname varchar(100),
@pctotal decimal(9,2)
SET NOCOUNT ON;
CREATE TABLE #UKC_RSP_DEPT_PAYCODES (
[colpos] [int] NULL,
[paycodeid] [int] NULL)
CREATE TABLE #UKC_RSP_DEPT_TOTALS (
[colpos] [int] NULL,
[pcname] varchar(100) NULL,
[pctotal] decimal(9,2) NULL
)
Declare dept_paycodes cursor forward_only static for
-- SQL to return first 12 paycodes which contain totals for dept within period requested.
select q.rownum, q.paycodeid from
(select row_number() over (order by w.paycodeid) as rownum, w.paycodeid from wfctotal w
inner join laboracct l on l.laboracctid = w.laboracctid and l.laborlev3dsc = @dept
inner join paycode p on p.paycodeid = w.paycodeid and p.visible_to_user = 1
where w.employeeid in (select employeeid from mywtkemployee where sessionid = @key)
and w.applydtm >= (select min(startdate) from mywtkemployee where sessionid = @key)
and w.applydtm < (select max(enddate) from mywtkemployee where sessionid = @key)
and ((@actadj = 0 and w.applydtm = w.adjapplydtm) or (@actadj = 1))
and w.durationsecsqty > 0
group by w.paycodeid) q
where q.rownum <= 12
Declare paycode_totals cursor forward_only static for
select d.colpos, p.name, sum(w.durationsecsqty)/3600.00 from wfctotal w
inner join laboracct l on l.laboracctid = w.laboracctid and l.laborlev3dsc = @dept
inner join mywtkemployee m on m.employeeid = w.employeeid
and w.applydtm >= m.startdate
and w.applydtm < m.enddate
inner join #ukc_rsp_dept_paycodes d on d.paycodeid = w.paycodeid
inner join paycode p on p.paycodeid = d.paycodeid
where m.sessionid = @key
group by d.colpos, p.name
open dept_paycodes
fetch next from dept_paycodes
into @rownum, @paycodeid
while @@fetch_status = 0
begin
insert into #ukc_rsp_dept_paycodes values (@rownum, @paycodeid)
fetch next from dept_paycodes
into @rownum, @paycodeid
end
close dept_paycodes
deallocate dept_paycodes
open paycode_totals
fetch next from paycode_totals
into @rownum, @pcname, @pctotal
while @@fetch_status = 0
begin
insert into #ukc_rsp_dept_totals values (@rownum, @pcname, @pctotal)
fetch next from paycode_totals
into @rownum, @pcname, @pctotal
end
close paycode_totals
deallocate paycode_totals
select pcname as pcname1, pctotal as pctotal1,
(select pcname from #ukc_rsp_dept_totals where colpos =2) as pcname2,
(select pctotal from #ukc_rsp_dept_totals where colpos =2) as pctotal2,
(select pcname from #ukc_rsp_dept_totals where colpos =3) as pcname3,
(select pctotal from #ukc_rsp_dept_totals where colpos =3) as pctotal3,
(select pcname from #ukc_rsp_dept_totals where colpos =4) as pcname4,
(select pctotal from #ukc_rsp_dept_totals where colpos =4) as pctotal4,
(select pcname from #ukc_rsp_dept_totals where colpos =5) as pcname5,
(select pctotal from #ukc_rsp_dept_totals where colpos =5) as pctotal5,
(select pcname from #ukc_rsp_dept_totals where colpos =6) as pcname6,
(select pctotal from #ukc_rsp_dept_totals where colpos =6) as pctotal6,
(select pcname from #ukc_rsp_dept_totals where colpos =7) as pcname7,
(select pctotal from #ukc_rsp_dept_totals where colpos =7) as pctotal7,
(select pcname from #ukc_rsp_dept_totals where colpos =8) as pcname8,
(select pctotal from #ukc_rsp_dept_totals where colpos =8) as pctotal8,
(select pcname from #ukc_rsp_dept_totals where colpos =9) as pcname9,
(select pctotal from #ukc_rsp_dept_totals where colpos =9) as pctotal9,
(select pcname from #ukc_rsp_dept_totals where colpos =10) as pcname10,
(select pctotal from #ukc_rsp_dept_totals where colpos =10) as pctotal10,
(select pcname from #ukc_rsp_dept_totals where colpos =11) as pcname11,
(select pctotal from #ukc_rsp_dept_totals where colpos =11) as pctotal11,
(select pcname from #ukc_rsp_dept_totals where colpos =12) as pcname12,
(select pctotal from #ukc_rsp_dept_totals where colpos =12) as pctotal12
from #ukc_rsp_dept_totals
where colpos = 1
Unfortuantly the coding is not quite right and its missing some information. To see the other information I created a new Stored Procedure nearly exactly like above only changing the code i have highlighted in yello.
USE [SQLServer]
GO
/****** Object: StoredProcedure [dbo].[UKC_RSP_PUNCHDETAIL_DEPT] Script Date: 11/29/2011 17:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UKC_RSP_PUNCHDETAIL_DEPT]
@key int, @dept varchar(100), @actadj int
AS
Declare
@rownum int,
@paycodeid int,
@pcname varchar(100),
@pctotal decimal(9,2)
SET NOCOUNT ON;
CREATE TABLE #UKC_RSP_DEPT_PAYCODES (
[colpos] [int] NULL,
[paycodeid] [int] NULL)
CREATE TABLE #UKC_RSP_DEPT_TOTALS (
[colpos] [int] NULL,
[pcname] varchar(100) NULL,
[pctotal] decimal(9,2) NULL
)
Declare dept_paycodes cursor forward_only static for
-- SQL to return first 12 paycodes which contain totals for dept within period requested.
select q.rownum, q.paycodeid from
(select row_number() over (order by w.paycodeid) as rownum, w.paycodeid from wfctotal w
inner join laboracct l on l.laboracctid = w.laboracctid and l.laborlev3dsc = @dept
inner join paycode p on p.paycodeid = w.paycodeid and p.visible_to_user = 1
where w.employeeid in (select employeeid from mywtkemployee where sessionid = @key)
and w.applydtm >= (select min(startdate) from mywtkemployee where sessionid = @key)
and w.applydtm < (select max(enddate) from mywtkemployee where sessionid = @key)
and ((@actadj = 0 and w.applydtm = w.adjapplydtm) or (@actadj = 1))
[highlight]and w.moneyamt >0[/highlight]
group by w.paycodeid) q
where q.rownum <= 12
Declare paycode_totals cursor forward_only static for
[highlight]select d.colpos, p.name, sum(w.moneyamt) from wfctotal w[/highlight]
inner join laboracct l on l.laboracctid = w.laboracctid and l.laborlev3dsc = @dept
inner join mywtkemployee m on m.employeeid = w.employeeid
and w.applydtm >= m.startdate
and w.applydtm < m.enddate
inner join #ukc_rsp_dept_paycodes d on d.paycodeid = w.paycodeid
inner join paycode p on p.paycodeid = d.paycodeid
where m.sessionid = @key
group by d.colpos, p.name
open dept_paycodes
fetch next from dept_paycodes
into @rownum, @paycodeid
while @@fetch_status = 0
begin
insert into #ukc_rsp_dept_paycodes values (@rownum, @paycodeid)
fetch next from dept_paycodes
into @rownum, @paycodeid
end
close dept_paycodes
deallocate dept_paycodes
open paycode_totals
fetch next from paycode_totals
into @rownum, @pcname, @pctotal
while @@fetch_status = 0
begin
insert into #ukc_rsp_dept_totals values (@rownum, @pcname, @pctotal)
fetch next from paycode_totals
into @rownum, @pcname, @pctotal
end
close paycode_totals
deallocate paycode_totals
select pcname as pcname1, pctotal as pctotal1,
(select pcname from #ukc_rsp_dept_totals where colpos =2) as pcname2,
(select pctotal from #ukc_rsp_dept_totals where colpos =2) as pctotal2,
(select pcname from #ukc_rsp_dept_totals where colpos =3) as pcname3,
(select pctotal from #ukc_rsp_dept_totals where colpos =3) as pctotal3,
(select pcname from #ukc_rsp_dept_totals where colpos =4) as pcname4,
(select pctotal from #ukc_rsp_dept_totals where colpos =4) as pctotal4,
(select pcname from #ukc_rsp_dept_totals where colpos =5) as pcname5,
(select pctotal from #ukc_rsp_dept_totals where colpos =5) as pctotal5,
(select pcname from #ukc_rsp_dept_totals where colpos =6) as pcname6,
(select pctotal from #ukc_rsp_dept_totals where colpos =6) as pctotal6,
(select pcname from #ukc_rsp_dept_totals where colpos =7) as pcname7,
(select pctotal from #ukc_rsp_dept_totals where colpos =7) as pctotal7,
(select pcname from #ukc_rsp_dept_totals where colpos =8) as pcname8,
(select pctotal from #ukc_rsp_dept_totals where colpos =8) as pctotal8,
(select pcname from #ukc_rsp_dept_totals where colpos =9) as pcname9,
(select pctotal from #ukc_rsp_dept_totals where colpos =9) as pctotal9,
(select pcname from #ukc_rsp_dept_totals where colpos =10) as pcname10,
(select pctotal from #ukc_rsp_dept_totals where colpos =10) as pctotal10,
(select pcname from #ukc_rsp_dept_totals where colpos =11) as pcname11,
(select pctotal from #ukc_rsp_dept_totals where colpos =11) as pctotal11,
(select pcname from #ukc_rsp_dept_totals where colpos =12) as pcname12,
(select pctotal from #ukc_rsp_dept_totals where colpos =12) as pctotal12
from #ukc_rsp_dept_totals
where colpos = 1
What I would like to do is combine both these Stored Procedures so both information is displayed in #ukc_rsp_dept_totals table.
Its all above me so hope you can help.
Cheers
Dougie
November 29, 2011 at 2:07 pm
Hey Dougie,
Don't feel bad about this code, that's some signficantly nasty iterations they've got going there. Have you been having performance problems with this code as well?
Can you script out the schema for the tables in this query (with indexes) and allow us to see that as well? It'll help us figure out what-all is going on in here and hopefully get you a cleaner technique.
Either way, analysis and repair of this won't be something quick, and will probably take a few iterations with you to nail down.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2011 at 2:10 pm
If you are looking for a simple combine of the two (and if perf is not an issue - though I would look into changing this to a set-based routine instead anyway), then you can do one of two things.
1. Add the cursor you modified to the first stored proc and allow it to insert into that table as well.
2. keep both procs separate and have them called by a third proc. The third proc can do an Insert into #table exec proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 29, 2011 at 2:57 pm
Hi
Thanks for the replys. At the moment there doesnt seem to be any real performance issues, the reports run quite quickly.
Basically what the SP does is search a table for all instances of a paycode, sums it up and takes its name and total and row number and apply it to select statements at the bottom. So if I had 3 Paycodes with 3 totals BASIC 20, OT 10, Other 40 then
Pcname1 = BASIC Pctotal1 = 20
Pcname2 = OT Pctotal2 = 10
Pcname3 = Other Pctotal3 = 40 and so on if there were more paycodes.
The other SP does exactly the same but the totalks is money based where the above is hour based, so if I had 2 paycodes Absent £32.10, Flex £22.50 then
Pcname1 = Absent Pctotal1 32.10
Pcname2 = Flex Pctoatl2 = 22.50
What I would like by combining them is.
Pcname1 = BASIC Pctotal1 = 20
Pcname2 = OT Pctotal2 = 10
Pcname3 = Other Pctotal3 = 40
Pcname4 = Absent Pctotal4 32.10
Pcname5 = Flex Pctoatl5 = 22.50
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply