asked on

Combining data based on multiple variables

Greetings,

This is similar to another question I asked. I had hoped a more simple example would give me the information I needed to accomplish the goal I had. I was unable to get it to work though. So, here is the full problem I am having. Hopefully, I'll give enough information for you, but if you need anything further, please let me know.

We have a software package that has this as a view:
---------------------------------------------------------------------------------------------------------------------------
SELECT VE.PERSONNUM, CONVERT(char,T.STARTDTM,101) as STARTDATE, CONVERT(char,T.ENDDTM,101) as ENDDATE, LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4, PC.NAME AS PAYCODENAME, T.DURATIONSECSQTY AS TIMEINSECONDS
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
---------------------------------------------------------------------------------------------------------------------------

When this is run in QA, it returns this (this was formated in notepad, it should display correctly there):
+----------+-----------+-----------+-----+-----+-----+--------+------+
|PERSONNUM |STARTDATE |ENDDATE |LL2 |LL3 |LL4 |PCNAME |TIME |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |REG2 |21600 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |REG2 |7200 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |M2 |21600 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |M2 |7200 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |M2 |1800 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |M2 |2340 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |OVRTM2 |1800 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |OVRTM2 |2340 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |530 |80 |M2 |1800 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |530 |80 |OVRTM2 |1800 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1010 |08/31/2004 |08/31/2004 |500 |545 |80 |REG2 |2340 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1010 |08/31/2004 |08/31/2004 |500 |545 |80 |M2 |2340 |
+----------+-----------+-----------+-----+-----+-----+--------+------+
|1010 |08/31/2004 |08/31/2004 |500 |545 |80 |OVRTM2 |2340 |
+----------+-----------+-----------+-----+-----+-----+--------+------+

What I need it to do is this:
+----------+-----------+-----------+-----+-----+-----+--------+------+--------+------+--------+------+--------+------+--------+------+
|PERSONNUM |STARTDATE |ENDDATE |LL2 |LL3 |LL4 |PCNAME1 |TIME1 |PCNAME2 |TIME2 |PCNAME3 |TIME3 |PCNAME4 |TIME4 |PCNAME5 |TIME5 |
+----------+-----------+-----------+-----+-----+-----+--------+------+--------+------+--------+------+--------+------+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |518 |80 |REG2 |28800 |M2 |32940 |OVRTM2 |4140 | | | | |
+----------+-----------+-----------+-----+-----+-----+--------+------+--------+------+--------+------+--------+------+--------+------+
|1008 |08/31/2004 |08/31/2004 |500 |530 |80 |M2 |1800 |OVRTM2 |1800 | | | | | | |
+----------+-----------+-----------+-----+-----+-----+--------+------+--------+------+--------+------+--------+------+--------+------+
|1010 |08/31/2004 |08/31/2004 |500 |545 |80 |REG2 |2340 |M2 |2340 |OVRTM2 |2340 | | | | |
+----------+-----------+-----------+-----+-----+-----+--------+------+--------+------+--------+------+--------+------+--------+------+

So, this shows me total TIME for each PCNAME based on PERSONNUM and changes in LL2, LL3 or LL4. In this example, the PCNAME occurances don't exceed 3, but there can be instances where it could go up to 10 (I've only showed 5 slots in the sample).

Is this enough information? Is this possible? Or should I just give up now? :P

Thanks for your time and any ideas you might have!!
chris.

vidnan123

I have tried to understand your requirement and here is a solution.

I created a table abc that returns data as follows (similar to your first result)

num startdate enddate ll2 ll3 ll4 pcname time
----------- ------------------------------ ------------------------------ ----------- ----------- ----------- ---------- -----------
1008 31/08/2004 31/08/2004 500 518 80 REG2 21600
1008 31/08/2004 31/08/2004 500 518 80 REG2 7200
1008 31/08/2004 31/08/2004 500 518 80 M2 21600
1008 31/08/2004 31/08/2004 500 518 80 M2 7200
1008 31/08/2004 31/08/2004 500 518 80 M2 1800
1008 31/08/2004 31/08/2004 500 518 80 M2 2340

I do not know the datatypes of the fields in result set, so I have assumed in the query below.

-------------------QUERY START----------------------------
set nocount on

declare @abc table(
      num            int,
      startdate      datetime,
      enddate            datetime,
      ll2            int,
      ll3            int,
      ll4            int,
      pcname1            varchar(50),
      time1            int,
      pcname2            varchar(50),
      time2            int,
      pcname3            varchar(50),
      time3            int,
      pcname4            varchar(50),
      time4            int,
      pcname5            varchar(50),
      time5            int
)

declare @num            int,
      @startdate      datetime,
      @enddate      datetime,
      @ll2            int,
      @ll3            int,
      @ll4            int,
      @pcname            varchar(50),
      @timesum      bigint,
      @count            int

insert into @abc(num,startdate,enddate,ll2,ll3,ll4)
select distinct num,startdate,enddate,ll2,ll3,ll4
from abc

declare curA cursor for
select num,startdate,enddate,ll2,ll3,ll4
from abc

open curA
fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4

while @@fetch_status=0
begin

      SELECT @count=1

      declare curB cursor for
      select pcname,sum(time) from abc
      where num=@num
      and startdate=@startdate
      and enddate=@enddate
      and ll2=@ll2
      and ll3=@ll3
      and ll4=@ll4
      group by pcname

      open curB
      fetch next from curB into @pcname,@timesum

      while @@fetch_status=0
      begin
            if @count =1
                  update @abc
                  set pcname1 = @pcname,
                   time1 = @timesum
            else if @count =2
                  update @abc
                  set pcname2 = @pcname,
                   time2 = @timesum
            else if @count =2
                  update @abc
                  set pcname2 = @pcname,
                   time2 = @timesum
            else if @count =2
                  update @abc
                  set pcname2 = @pcname,
                   time2 = @timesum
            else if @count =2
                  update @abc
                  set pcname2 = @pcname,
                   time2 = @timesum

            select @count = @count+1


      fetch next from curB into @pcname,@timesum
      end

      close curB
      deallocate curB

fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4
end

close curA
deallocate curA

select num,convert(varchar,startdate,103)as 'startdate',convert(varchar,enddate,103) as 'enddate',
      ll2,ll3,ll4,isnull(pcname1,'') as 'pcname1',isnull(time1,'') as 'time1',
      isnull(pcname2,'')as 'pcname2',isnull(time2,'') as 'time2',
      isnull(pcname3,'')as 'pcname3',isnull(time3,'')as 'time3',
      isnull(pcname4,'')as 'pcname4',isnull(time4,'')as 'time4' ,
      isnull(pcname5,'')as 'pcname5',isnull(time5,'')as 'time5'
from @abc

set nocount off
-------------------QUERY END----------------------------

The output of this query is as follows (similar to your second/desired result)

num| startdate|enddate|ll2|ll3|ll4| pcname1|time1|pcname2|time2|pcname3|time3|pcname4|time4|pcname5| time5
----------- ------------------------------ ------------------------------ ----------- ----------- ----------- -------------------------------1008|31/08/2004|31/08/2004|500|518|80|M2|32940|REG2|28800| |0| |0| |0

Hope it helps! If you need more help do let me know.

vidnan123

Sorry there was a mistake in my query syntax. Here it is again....

-------------------QUERY START----------------------------
set nocount on

declare @abc table(
num int,
startdate datetime,
enddate datetime,
ll2 int,
ll3 int,
ll4 int,
pcname1 varchar(50),
time1 int,
pcname2 varchar(50),
time2 int,
pcname3 varchar(50),
time3 int,
pcname4 varchar(50),
time4 int,
pcname5 varchar(50),
time5 int
)

declare @num int,
@startdate datetime,
@enddate datetime,
@ll2 int,
@ll3 int,
@ll4 int,
@pcname varchar(50),
@timesum bigint,
@count int

insert into @abc(num,startdate,enddate,ll2,ll3,ll4)
select distinct num,startdate,enddate,ll2,ll3,ll4
from abc

declare curA cursor for
select num,startdate,enddate,ll2,ll3,ll4
from abc

open curA
fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4

while @@fetch_status=0
begin

SELECT @count=1

declare curB cursor for
select pcname,sum(time) from abc
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4
group by pcname

open curB
fetch next from curB into @pcname,@timesum

while @@fetch_status=0
begin
if @count =1
update @abc
set pcname1 = @pcname,
time1 = @timesum
else if @count =2
update @abc
set pcname2 = @pcname,
time2 = @timesum
else if @count =3
update @abc
set pcname3= @pcname,
time3 = @timesum
else if @count =4
update @abc
set pcname4 = @pcname,
time4 = @timesum
else if @count =5
update @abc
set pcname5 = @pcname,
time5 = @timesum

select @count = @count+1

fetch next from curB into @pcname,@timesum
end

close curB
deallocate curB

fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4
end

close curA
deallocate curA

select num,convert(varchar,startdate,103)as 'startdate',convert(varchar,enddate,103) as 'enddate',
ll2,ll3,ll4,isnull(pcname1,'') as 'pcname1',isnull(time1,'') as 'time1',
isnull(pcname2,'')as 'pcname2',isnull(time2,'') as 'time2',
isnull(pcname3,'')as 'pcname3',isnull(time3,'')as 'time3',
isnull(pcname4,'')as 'pcname4',isnull(time4,'')as 'time4' ,
isnull(pcname5,'')as 'pcname5',isnull(time5,'')as 'time5'
from @abc

set nocount off
-------------------QUERY END----------------------------

vidnan123

I have modified my query to use your SELECT statement....

-----------------------QUERY START------------------------------

set nocount on

declare @abc table(
      num            int,
      startdate      datetime,
      enddate            datetime,
      ll2            int,
      ll3            int,
      ll4            int,
      pcname1            varchar(50),
      time1            int,
      pcname2            varchar(50),
      time2            int,
      pcname3            varchar(50),
      time3            int,
      pcname4            varchar(50),
      time4            int,
      pcname5            varchar(50),
      time5            int
)

declare @num            int,
      @startdate      datetime,
      @enddate      datetime,
      @ll2            int,
      @ll3            int,
      @ll4            int,
      @pcname            varchar(50),
      @timesum      bigint,
      @count            int

insert into @abc(num,startdate,enddate,ll2,ll3,ll4)
select distinct
      VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID

declare curA cursor for
SELECT PERSONNUM, STARTDTM,ENDDTM,LABORLEV2NM,LABORLEV3NM,LABORLEV4NM
from
(select      VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
)a

open curA
fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4

while @@fetch_status=0
begin

      SELECT @count=1

      declare curB cursor for
      select       NAME, sum(T.DURATIONSECSQTY)
      FROM
      (
      select      VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
       LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4,
                   PC.NAME,T.DURATIONSECSQTY
      FROM dbo.PAYCODE PC INNER JOIN
       dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
       dbo.WTKEMPLOYEE WE INNER JOIN
       dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
       M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
       dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
       dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
      )b
      where PERSONNUM=@num
      and STARTDTM=@startdate
      and ENDDTM=@enddate
      and LABORLEV2NM=@ll2
      and LABORLEV3NM=@ll3
      and LABORLEV4NM=@ll4
      group by NAME

      open curB
      fetch next from curB into @pcname,@timesum

      while @@fetch_status=0
      begin
            if @count =1
                  update @abc
                  set pcname1 = @pcname,
                   time1 = @timesum
            else if @count =2
                  update @abc
                  set pcname2 = @pcname,
                   time2 = @timesum
            else if @count =3
                  update @abc
                  set pcname3 = @pcname,
                   time3 = @timesum
            else if @count =4
                  update @abc
                  set pcname4 = @pcname,
                   time4 = @timesum
            else if @count =5
                  update @abc
                  set pcname5 = @pcname,
                   time5 = @timesum

            select @count = @count+1


      fetch next from curB into @pcname,@timesum
      end

      close curB
      deallocate curB

fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4
end

close curA
deallocate curA

select num,convert(varchar,startdate,101)as 'startdate',convert(varchar,enddate,101) as 'enddate',
      ll2,ll3,ll4,isnull(pcname1,'') as 'pcname1',isnull(time1,'') as 'time1',
      isnull(pcname2,'')as 'pcname2',isnull(time2,'') as 'time2',
      isnull(pcname3,'')as 'pcname3',isnull(time3,'')as 'time3',
      isnull(pcname4,'')as 'pcname4',isnull(time4,'')as 'time4' ,
      isnull(pcname5,'')as 'pcname5',isnull(time5,'')as 'time5'
from @abc

set nocount off

-----------------------QUERY END------------------------------

Let me know if it works..

cciesliga

ASKER

Thank you for your reply! That's kind of what I'm looking for. Only problem is when I run your query with some different data, the numbers don't add properly.

For example:
num startdate enddate ll2 ll3 ll4 pcname time
----------- ------------------------------ ------------------------------ ----------- ----------- ----------- ---------- -----------
1008 31/08/2004 31/08/2004 500 518 80 REG2 21600
1008 31/08/2004 31/08/2004 500 518 80 REG2 7200
1008 31/08/2004 31/08/2004 500 518 80 M2 21600
1008 31/08/2004 31/08/2004 500 518 80 M2 7200
1008 31/08/2004 31/08/2004 500 520 80 M2 1800
1008 31/08/2004 31/08/2004 500 520 80 M2 2340

If you change the last two rows ll3 to 520, this is what you get:
1008      31/08/2004      31/08/2004      500      518      80      M2      4140      REG2      28800      0      0      0
1008      31/08/2004      31/08/2004      500      520      80      M2      4140      REG2      28800      0      0      0

When it needs to look like:
1008      31/08/2004      31/08/2004      500      518      80      M2      28800 REG2      28800      0      0      0
1008      31/08/2004      31/08/2004      500      520      80      M2      4140      0      0      0      0

Similar problem if you change the num from 1008 to 1010 on the last two rows.

Does that make sense?

vidnan123

Yup! Sorry about that mistake in my query...Here it is again

-----------------------QUERY START------------------------------

set nocount on

declare @abc table(
num int,
startdate datetime,
enddate datetime,
ll2 int,
ll3 int,
ll4 int,
pcname1 varchar(50),
time1 int,
pcname2 varchar(50),
time2 int,
pcname3 varchar(50),
time3 int,
pcname4 varchar(50),
time4 int,
pcname5 varchar(50),
time5 int
)

declare @num int,
@startdate datetime,
@enddate datetime,
@ll2 int,
@ll3 int,
@ll4 int,
@pcname varchar(50),
@timesum bigint,
@count int

insert into @abc(num,startdate,enddate,ll2,ll3,ll4)
select distinct
VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID

declare curA cursor for
SELECT DISTINCT PERSONNUM, STARTDTM,ENDDTM,LABORLEV2NM,LABORLEV3NM,LABORLEV4NM
from
(select VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
)a

open curA
fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4

while @@fetch_status=0
begin

SELECT @count=1

declare curB cursor for
select NAME, sum(T.DURATIONSECSQTY)
FROM
(
select VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM AS LABORLEVELNAME2,
LA1.LABORLEV3NM AS LABORLEVELNAME3, LA1.LABORLEV4NM AS LABORLEVELNAME4,
PC.NAME,T.DURATIONSECSQTY
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
)b
where PERSONNUM=@num
and STARTDTM=@startdate
and ENDDTM=@enddate
and LABORLEV2NM=@ll2
and LABORLEV3NM=@ll3
and LABORLEV4NM=@ll4
group by NAME

open curB
fetch next from curB into @pcname,@timesum

while @@fetch_status=0
begin
if @count =1
update @abc
set pcname1 = @pcname,
time1 = @timesum
       where num=@num
       and startdate=@startdate
       and enddate=@enddate
       and ll2=@ll2
       and ll3=@ll3
       and ll4=@ll4

else if @count =2
update @abc
set pcname2 = @pcname,
time2 = @timesum
       where num=@num
       and startdate=@startdate
       and enddate=@enddate
       and ll2=@ll2
       and ll3=@ll3
       and ll4=@ll4

else if @count =3
update @abc
set pcname3 = @pcname,
time3 = @timesum
       where num=@num
       and startdate=@startdate
       and enddate=@enddate
       and ll2=@ll2
       and ll3=@ll3
       and ll4=@ll4

else if @count =4
update @abc
set pcname4 = @pcname,
time4 = @timesum
       where num=@num
       and startdate=@startdate
       and enddate=@enddate
       and ll2=@ll2
       and ll3=@ll3
       and ll4=@ll4

else if @count =5
update @abc
set pcname5 = @pcname,
time5 = @timesum
       where num=@num
       and startdate=@startdate
       and enddate=@enddate
       and ll2=@ll2
       and ll3=@ll3
       and ll4=@ll4

select @count = @count+1

fetch next from curB into @pcname,@timesum
end

close curB
deallocate curB

fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4
end

close curA
deallocate curA

select num,convert(varchar,startdate,101)as 'startdate',convert(varchar,enddate,101) as 'enddate',
ll2,ll3,ll4,isnull(pcname1,'') as 'pcname1',isnull(time1,'') as 'time1',
isnull(pcname2,'')as 'pcname2',isnull(time2,'') as 'time2',
isnull(pcname3,'')as 'pcname3',isnull(time3,'')as 'time3',
isnull(pcname4,'')as 'pcname4',isnull(time4,'')as 'time4' ,
isnull(pcname5,'')as 'pcname5',isnull(time5,'')as 'time5'
from @abc

set nocount off

-----------------------QUERY END------------------------------
Hope this helps!

cciesliga

ASKER

Tried the modified query and it returns these errors:

Server: Msg 207, Level 16, State 3, Line 46
Invalid column name 'LABORLEV2NM'.
Server: Msg 207, Level 16, State 1, Line 46
Invalid column name 'LABORLEV3NM'.
Server: Msg 207, Level 16, State 1, Line 46
Invalid column name 'LABORLEV4NM'.
Server: Msg 107, Level 16, State 1, Line 68
The column prefix 'T' does not match with a table name or alias name used in the query.

I tried adding LA1. before the LABORLEV... but then it returns:
Server: Msg 107, Level 16, State 2, Line 46
The column prefix 'LA1' does not match with a table name or alias name used in the query.

Thanks!

vidnan123

Oops...Sorry about that again....Here is the query again...

-----------------------QUERY START------------------------------

set nocount on

declare @abc table(
num int,
startdate datetime,
enddate datetime,
ll2 int,
ll3 int,
ll4 int,
pcname1 varchar(50),
time1 int,
pcname2 varchar(50),
time2 int,
pcname3 varchar(50),
time3 int,
pcname4 varchar(50),
time4 int,
pcname5 varchar(50),
time5 int
)

declare @num int,
@startdate datetime,
@enddate datetime,
@ll2 int,
@ll3 int,
@ll4 int,
@pcname varchar(50),
@timesum bigint,
@count int

insert into @abc(num,startdate,enddate,ll2,ll3,ll4)
select distinct
VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM,
LA1.LABORLEV3NM, LA1.LABORLEV4NM
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID

declare curA cursor for
SELECT DISTINCT PERSONNUM, STARTDTM,ENDDTM,LABORLEV2NM,LABORLEV3NM,LABORLEV4NM
from
(select VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM,
LA1.LABORLEV3NM, LA1.LABORLEV4NM
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
)a

open curA
fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4

while @@fetch_status=0
begin

SELECT @count=1

declare curB cursor for
select NAME, sum(T.DURATIONSECSQTY)
FROM
(
select VE.PERSONNUM, T.STARTDTM, T.ENDDTM , LA1.LABORLEV2NM,
LA1.LABORLEV3NM, LA1.LABORLEV4NM,
PC.NAME,T.DURATIONSECSQTY
FROM dbo.PAYCODE PC INNER JOIN
dbo.PAYCODE1MMFLAT M ON PC.PAYCODEID = M.GRANDPAYCODEID INNER JOIN
dbo.WTKEMPLOYEE WE INNER JOIN
dbo.WFCTOTAL T ON WE.EMPLOYEEID = T.EMPLOYEEID ON M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND
M.EXPIRATIONDTM > T.APPLYDTM INNER JOIN
dbo.PERSON VE ON WE.PERSONID = VE.PERSONID INNER JOIN
dbo.LABORACCT LA1 ON T.LABORACCTID = LA1.LABORACCTID
)b
where PERSONNUM=@num
and STARTDTM=@startdate
and ENDDTM=@enddate
and LABORLEV2NM=@ll2
and LABORLEV3NM=@ll3
and LABORLEV4NM=@ll4
group by NAME

open curB
fetch next from curB into @pcname,@timesum

while @@fetch_status=0
begin
if @count =1
update @abc
set pcname1 = @pcname,
time1 = @timesum
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4

else if @count =2
update @abc
set pcname2 = @pcname,
time2 = @timesum
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4

else if @count =3
update @abc
set pcname3 = @pcname,
time3 = @timesum
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4

else if @count =4
update @abc
set pcname4 = @pcname,
time4 = @timesum
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4

else if @count =5
update @abc
set pcname5 = @pcname,
time5 = @timesum
where num=@num
and startdate=@startdate
and enddate=@enddate
and ll2=@ll2
and ll3=@ll3
and ll4=@ll4

select @count = @count+1

fetch next from curB into @pcname,@timesum
end

close curB
deallocate curB

fetch next from curA into @num,@startdate,@enddate,@ll2,@ll3,@ll4
end

close curA
deallocate curA

select num,convert(varchar,startdate,101)as 'startdate',convert(varchar,enddate,101) as 'enddate',
ll2,ll3,ll4,isnull(pcname1,'') as 'pcname1',isnull(time1,'') as 'time1',
isnull(pcname2,'')as 'pcname2',isnull(time2,'') as 'time2',
isnull(pcname3,'')as 'pcname3',isnull(time3,'')as 'time3',
isnull(pcname4,'')as 'pcname4',isnull(time4,'')as 'time4' ,
isnull(pcname5,'')as 'pcname5',isnull(time5,'')as 'time5'
from @abc

set nocount off

-----------------------QUERY END------------------------------

Any more issues let me know....

ASKER CERTIFIED SOLUTION

vidnan123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cciesliga

ASKER

Am I wearing you out yet?

This is the error now:
Server: Msg 107, Level 16, State 2, Line 68
The column prefix 'T' does not match with a table name or alias name used in the query.

Thanks for your help and patience!

vidnan123

Please refer my latest query (that I modified for this error)

Let me know if the query still gives you the error....

cciesliga

ASKER

It runs beatifully now! Thank you for all your help!! I really appreciate it.