?
Solved

Combining data based on multiple variables

Posted on 2004-09-16
11
Medium Priority
?
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 2

Expert Comment

by:vidnan123
ID: 12081434
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
ID: 12081444
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
ID: 12081530
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Comment

by:cciesliga
ID: 12081573
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
ID: 12081627
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
 
LVL 1

Author Comment

by:cciesliga
ID: 12081654
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
ID: 12081680
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 2000 total points
ID: 12081685
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
ID: 12081688
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
ID: 12081698
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
ID: 12081929
It runs beatifully now!  Thank you for all your help!!  I really appreciate it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

765 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