Re: Relation Query and Duplicates

From: Tokunaga T. <tonkuma_at_jp.ibm.com>
Date: 9 Jul 2002 03:49:40 -0700
Message-ID: <8156d9ae.0207090249.c313d25_at_posting.google.com>

>               
> My problem is that the number of records from the overhead table and
> job table are different for a specific Day.
> 

1. One way to resolve this problem is to use row_number() function and FULL OUTER JOIN. Following example was tested in DB2 UDB V7.2 Fixpak7.

SELECT coalesce(J.userid, O.userid) AS userid

,coalesce(J.date, O.date) AS date
,jobdescription, Jobhours, overheaddescription,
overheadhours

,coalesce(J.seq, O.seq) AS seq

          FROM (SELECT userid, date, jobdescription, Jobhours
                      ,rownumber() over(partition by userid, date
order by jobhours DESC) as seq
                  FROM Job_hours
                 WHERE year(date)  = 2002
                   AND month(date) = 7
               ) AS J
               FULL OUTER JOIN
               (SELECT userid, date, overheaddescription,
overheadhours
                      ,rownumber() over(partition by userid, date
order by overheadhours DESC) as seq
                  FROM Overhead_hours
                 WHERE year(date)  = 2002
                   AND month(date) = 7
               ) AS O
               ON  J.userid = O.userid
               AND J.date   = O.date
               AND J.seq    = O.seq

 ORDER BY userid DESC, date, seq
!

---

 

USERID DATE JOBDESCRIPTION JOBHOURS OVERHEADDESCRIPTION OVERHEADHOURS SEQ

------------ ---------- --------------- -------- -------------------
------------- --------------------
Linda        2002-07-01 Welding              3.0 Clean-up             
        0.5                    1
Linda        2002-07-01 Machining            1.5 -                    
          -                    2
Linda        2002-07-02 Welding              3.0 -                    
          -                    1
Linda        2002-07-03 -                      - Sick                 
        7.5                    1
John         2002-07-01 Sanding              2.0 Clean-up             
        0.5                    1
John         2002-07-01 Machining            1.5 -                    
          -                    2
John         2002-07-02 Welding              3.0 -                    
          -                    1
John         2002-07-03 -                      - Course               
        7.5                    1

2. It will be rather complicated to format output as you showed. This is one example and you could make simpler. But I do not know how at now.

---

SELECT char(Userid_date,30) "UserID and Date"
      ,JobDescription       "Job Description"
      ,JobHours             "Job Hours"
      ,OverheadDescription  "Overhead Description"
      ,OverheadHours        "Overhead Hours"
  FROM (SELECT CASE
               WHEN grouping(UserID) = 1 THEN
                    'Month: ' || monthname(max(Date)) || ', ' ||
char(year(max(Date)))
               WHEN grouping(Date)   = 1 THEN
                    '  UserID: ' || UserID
               WHEN grouping(Seq)    = 1 THEN
                    '    Date: ' || monthname(max(Date)) || ' ' ||
rtrim(char(day(max(Date)))) || ', ' || char(year(max(Date)))
               ELSE ''
               END Userid_date


              ,CASE

               WHEN grouping(Seq)  = 1 THEN '' 
               ELSE max(JobDescription)
               END JobDescription


              ,CASE

               WHEN grouping(Seq)  = 1 THEN '' 
               ELSE substr('  ',1,2-int(log10(max(JobHours)))) ||
substr(char(max(JobHours)),2-int(log10(max(JobHours))))
               END JobHours


              ,CASE WHEN grouping(Seq)  = 1 THEN '' ELSE


max(OverheadDescription)   END OverheadDescription


              ,CASE

               WHEN grouping(Seq)  = 1 THEN ''
               ELSE substr('  ',1,2-int(log10(max(OverheadHours)))) ||
substr(char(max(OverheadHours)),2-int(log10(max(OverheadHours))))
               END OverheadHours


              ,max(UserID) UserID

              ,min(Date)   Date


              ,Seq

          FROM (SELECT coalesce(J.UserID, O.UserID) AS UserID
                      ,coalesce(J.Date,   O.Date  ) AS Date
                      ,JobDescription, JobHours, OverheadDescription,
OverheadHours
                      ,coalesce(J.Seq,    O.Seq   ) AS Seq
                  FROM (SELECT UserID, Date, JobDescription, JobHours
                              ,rownumber() over(PARTITION BY UserID,
Date ORDER BY JobHours DESC) as Seq
                          FROM Job_hours
                         WHERE year(Date)  = 2002
                           AND month(Date) = 7
                       ) AS J
                       FULL OUTER JOIN
                       (SELECT UserID, Date, OverheadDescription,
OverheadHours
                              ,rownumber() over(PARTITION BY UserID,
Date ORDER BY OverheadHours DESC) as Seq
                          FROM Overhead_hours
                         WHERE year(Date)  = 2002
                           AND month(Date) = 7
                       ) AS O
                       ON  J.UserID = O.UserID
                       AND J.Date   = O.Date
                       AND J.Seq    = O.Seq
               ) AS Q
         GROUP BY GROUPING SETS( (UserID,Date,Seq), (UserID,Date),
(UserID), () )
       ) AS P

 ORDER BY UserID DESC, Date, Userid_date DESC, Seq !

---

 

UserID and Date                Job Description Job Hours Overhead

Description Overhead Hours

------------------------------ --------------- ---------
-------------------- --------------

Month: July, 2002
  UserID: Linda
    Date: July 1, 2002

                               Welding           3.0     Clean-up     
         0.5
                               Machining         1.5     -            
       -
    Date: July 2, 2002
                               Welding           3.0     -            
       -
    Date: July 3, 2002
                               -               -         Sick         
         7.5

  UserID: John
    Date: July 1, 2002

                               Sanding           2.0     Clean-up     
         0.5
                               Machining         1.5     -            
       -
    Date: July 2, 2002
                               Welding           3.0     -            
       -
    Date: July 3, 2002
                               -               -         Course       
         7.5

Received on Tue Jul 09 2002 - 12:49:40 CEST