Complex SQL statement needed here.

Dev Articles Community Forums Sponsor:

March 11th, 2004, 02:02 AM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Complex SQL statement needed here.

To all guru's need some real serious problem here.

I have 5 columns needed to be displayed the only problem is that I need.

Drawing Table
id, DrawingNo, DrawingName

Timesheet Table
id, percentage, RevisionNo, Date, location, etc...

red = primary key

and now this is the complication, I need DrawingNo, Drawing Name, percentage, RevisionNo and Date.
I can easily use a join for that but the DrawingNo have repeating numbers and I want DISTINCT DrawingNo, MAX RevisionNo and MAX percentage only (MAX Revision has higher priority then MAX percentage).

That means:

Table look like this:
DrawingNo DrawingName percentage RevisionNo Date
10, A, 20, 1, 20 March 2003
10, A, 30, 2, 13 April 2004
20, K, 15, 1,8 06 January 1974
11, B, 54, 6, 10 December 1984
13, C, 80, 7, 24 July 1977
16, D, 20, 8, 30 September 1936
16, D, 60, 9, 18 Febuary 2004
11, B, 100, 7, 12 November 2003

Will come out like this

10, A, 30, 2, 13 April 2004
11, B, 100, 7, 12 November 2003
13, C, 80, 7, 24 July 1977
16, D, 60, 9, 18 Febuary 2004
20, K, 15, 18, 06 January 1974

How can I do this? Please help!!!

I know how to get one result but don't know how to make it all join up together as one SQL statement.

March 11th, 2004, 05:16 AM

stumpy

May contain nuts.

Dev Articles Regular (2000 - 2499 posts)

Join Date: Aug 2002

Location: Sydney, AU

Posts: 2,058

Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9

Inner joins look like this:

Code:

SELECT foo FROM table1 INNER JOIN table2 ON table1.pk = table2.fk

Table2 in my example needs to have a foreign key for table1, which I'm not sure you have.

__________________
DevArticles Moderator
BlueSix - Web Development and Consulting

March 11th, 2004, 07:11 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Uhmm I know how to inner join and all that... but I don't know how to get the 2 MAX value and a Distinct Value as well, all into one big statement.

Something like :

Select Disticnt <Column name>, MAX<ColumnPiriority1>, MAX<ColumnPriority2> From <Table> Where <condition>

I need to get 2 MAX for one single Uniq Record....

The above code does not work I think.

NHK

Quote:

Originally Posted by stumpy

Inner joins look like this:

Code:

SELECT foo FROM table1 INNER JOIN table2 ON table1.pk = table2.fk

Table2 in my example needs to have a foreign key for table1, which I'm not sure you have.

March 16th, 2004, 04:50 PM

michlmann

Contributing User

Join Date: Mar 2004

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 5

If your DBS supports it, try this:

SELECT DrawingNo, DrawingName, percentage, RevisionNo, Date FROM drawing d inner join timesheet t on d.pk=t.fk where revisionNo=(select max(RevisionNo) from timesheet where fk=t.fk)

or this

SELECT DrawingNo, DrawingName, percentage, RevisionNo, Date FROM drawing d, timesheet t, (select fk, max(RevisionNo) from timesheet group by fk) m where d.pk=t.fk and d.pk=m.fk

I assume that you don't have two revision numbers for one drawing no. Therefore you don't need two MAXs since the maximum revision number is unique in that case.

March 17th, 2004, 10:23 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Uhmm I am using SQL7.0 now.

Does the code work with SQL7?
I tried several select statement... and uhmm didn't quite come out... or maybe i am doing it wrongly.

NHK

Quote:

Originally Posted by michlmann

March 19th, 2004, 03:42 PM

michlmann

Contributing User

Join Date: Mar 2004

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 5

It should.

Let the foreign key column in timesheet be named drawingId. Then, the select would be like this:

PHP Code:


		
			
 SELECT DrawingNo, DrawingName, percentage, RevisionNo, Date 
  FROM drawing d inner join timesheet t ON t.drawingId = d.id
 WHERE revisionNo = 
        (SELECT max(revisionNo) FROM timesheet WHERE drawingId = t.drawingId)

Perhaps I misunderstood you and you want to have the maximum revision number AND the maximum percentage of one drawing. This would result in something like

PHP Code:


		
			
 SELECT DrawingNo, DrawingName, maxPercentage, maxRevision, Date
  FROM drawing d, 
         (SELECT drawingId, max(percentage) as maxPercentage
          FROM timesheet GROUP BY drawingId) p, 
         (SELECT drawingId, max(revisionNo) as maxRevision
          FROM timesheet group by drawingId) r, 
         timesheet t
 WHERE d.id=p.drawingId 
  AND d.id=r.drawingId
  AND d.id=t.drawingId 
  AND t.revisionNo = r.maxRevision

In this case, the date is the one from the timesheet entry with the maximum revision number.

Quote:

Originally Posted by Brick1235

Uhmm I am using SQL7.0 now.

Does the code work with SQL7?
I tried several select statement... and uhmm didn't quite come out... or maybe i am doing it wrongly.

NHK

March 21st, 2004, 07:22 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

WOW!!! thanks alot I didn't know u can do Select in a FROM <Statement> Area.

This is my first time seeing it.

Thanks a bunch.

NHK

Quote:

Originally Posted by michlmann

It should.

Let the foreign key column in timesheet be named drawingId. Then, the select would be like this:

PHP Code:


		
			
 SELECT DrawingNo, DrawingName, percentage, RevisionNo, Date 
FROM drawing d inner join timesheet t ON t.drawingId = d.id
WHERE revisionNo = 
     (SELECT max(revisionNo) FROM timesheet WHERE drawingId = t.drawingId)

Perhaps I misunderstood you and you want to have the maximum revision number AND the maximum percentage of one drawing. This would result in something like

PHP Code:


		
			
 SELECT DrawingNo, DrawingName, maxPercentage, maxRevision, Date
FROM drawing d, 
        (SELECT drawingId, max(percentage) as maxPercentage
         FROM timesheet GROUP BY drawingId) p, 
        (SELECT drawingId, max(revisionNo) as maxRevision
         FROM timesheet group by drawingId) r, 
        timesheet t
WHERE d.id=p.drawingId 
AND d.id=r.drawingId
AND d.id=t.drawingId 
AND t.revisionNo = r.maxRevision

In this case, the date is the one from the timesheet entry with the maximum revision number.

March 22nd, 2004, 02:01 AM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Oh... i face another problem now.

I made the following SQL statement:

Code:

  
SELECT 
 e.S_code, [Name], a.Nwork, b.OT
FROM 
 Employee e, 
 
 (Select S_code, SUM(Totalhr) as Nwork 
 from [TimeSheetDetail] group by S_code) a,
  
 (Select S_Code, SUM(Totalhr) as OT 
 from [TimeSheetDetail] 
 where (timecode = 2) group by S_code) b
order by Name

The results are empty, I figure that the highlighted in red is at fault. Is there any solution to this problem i have?
I want to show many conditions similar to the above. All with different TimeCodes.

Pls help

NHK

March 22nd, 2004, 01:16 PM

michlmann

Contributing User

Join Date: Mar 2004

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 5

Hi again,

now it's time to introduce some math ;-)

You have three sets A,B and C. Your SELECT doesn't have a WHERE-clause. Therefore the cross-product AxBxC is your result. If one set is empty (e.g C=empty set), then your cross-product AxBxC is also empty.

If you have some records in table employee and some records in timesheetdetail, then the last (red) subselect returns no results. In this select you have a WHERE-clause. I guess, that you don't have a timecode 2 in your timesheetdetail table.

Why don't you select the timecode in subquery b?

I'd suggest something like

PHP Code:


		
			
 SELECT 
   e.S_code, [Name], a.Nwork, b.timecode, b.OT
  FROM 
   Employee e, 
   
   (Select S_code, SUM(Totalhr) as Nwork 
   from [TimeSheetDetail] group by S_code) a,
    
   (Select S_Code, timecode, SUM(Totalhr) as OT 
   from [TimeSheetDetail] 
   group by S_code, timecode) b
 where e.s_code=a.s_code and e.s_code=b.s_code
  order by Name

Quote:

Originally Posted by Brick1235

Oh... i face another problem now.

I made the following SQL statement:

Code:

  
 SELECT 
  e.S_code, [Name], a.Nwork, b.OT
 FROM 
  Employee e, 
  
  (Select S_code, SUM(Totalhr) as Nwork 
  from [TimeSheetDetail] group by S_code) a,
   
  (Select S_Code, SUM(Totalhr) as OT 
  from [TimeSheetDetail] 
  where (timecode = 2) group by S_code) b
 order by Name

NHK

#10

March 22nd, 2004, 11:43 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Yeah i get what you mean...abou the A*B*C thing...

If I put the what have been suggested that means my result will come out the same for column Nwork and OT.
That means I have no conditions build into the FROM <statement>.
So does anyone know how can i do this kinda of similar statement with conditions and also show records that are empty.
The TimeCode have 1 = working hours, 2 = OT, 3 = OTonSite, 4 = Holiday, 5 = Weekends, 6 = MC, 7 = On leave.
I need a report in SQL come out S_code, Name, Total hours work, Total OT work, Total MC taken, total Leave taken.
As you can see above and below... how do I do it? Statements like this?
My Table looks like this:
TimeSheetDetail
S_code, DWGID, TimeCode, Revno, TotalHr, TSpercent
Drawing Detail
DWGID, JobNo, Dwgno, service,
Employee
S_code, TS_code, Name, Initial, Status

Help help pls
NHK

#11

March 23rd, 2004, 02:20 AM

michlmann

Contributing User

Join Date: Mar 2004

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 5

Ok, I think I understand now.

In that case, your posted query with timecode=2 was nearly correct.

But: use an outer (left/right) join.

I don't know SQL server syntax well, but I'd try it with

SELECT ... FROM (employee e inner join (select ... ) a) left join (select ... where timecode=2 group by s_code) b

Quote:

Originally Posted by Brick1235

So does anyone know how can i do this kinda of similar statement with conditions and also show records that are empty.

#12

March 23rd, 2004, 08:03 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Thanks for all the replies...

I got these results on testing a small SELECT

Code:

SELECT S_code, a.nHours, b.OT
FROM 
employee e 
inner join (select S_code, Sum(Totalhr)as nHours) a
left join (select S_code, Sum(Totalhr) as OT where timecode = 1 group by s_code) b

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'b'.

And if without the b behind...

Code:

 
SELECT S_code, a.nHours, OT
FROM 
employee e 
inner join (select S_code, Sum(Totalhr)as nHours) a
left join (select S_code, Sum(Totalhr) as OT where timecode = 1 group by s_code)
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.

I can't see what are the errors... can someone correct me?...

#13

March 24th, 2004, 11:00 AM

michlmann

Contributing User

Join Date: Mar 2004

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 5

The ON keyword is missing. Try:

FROM (employee e inner join (select ...) a on e.s_code=a.s_code) left join (select ...) b on e.s_code=b.s_code

#14

March 24th, 2004, 07:44 PM

Brick1235

Contributing User

Join Date: Feb 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 5

Thanks alot... the results came out very well.

This is the code incase anyone have the same prob.

Thanks to all that help and contributed.

Code:

 
SELECT e.S_code, a.nHours, b.OT
FROM 
(employee e 
inner join (select S_code, Sum(Totalhr)as nHours 
 from Timesheetdetail
 group by s_code) a
 on e.S_code = a.S_code)
left join (select S_code, Sum(Totalhr) as OT 
 from Timesheetdetail
 where timecode = 2 
 group by s_code) b 
 on e.S_code = b.S_code