Solved

Combining data based on multiple variables

Posted on 2004-09-16
11
184 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:cciesliga
  • 7
  • 4
11 Comments
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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----------------------------
0
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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..
0
 
LVL 1

Author Comment

by:cciesliga
Comment Utility
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?
0
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:cciesliga
Comment Utility
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!
0
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
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....
0
 
LVL 2

Accepted Solution

by:
vidnan123 earned 500 total points
Comment Utility
One more error in my query :-) Sorry about that...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,
                      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(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------------------------------


0
 
LVL 1

Author Comment

by:cciesliga
Comment Utility
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!
0
 
LVL 2

Expert Comment

by:vidnan123
Comment Utility
Please refer my latest query (that I modified for this error)

Let me know if the query still gives you the error....
0
 
LVL 1

Author Comment

by:cciesliga
Comment Utility
It runs beatifully now!  Thank you for all your help!!  I really appreciate it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now