help with query required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with query required

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-27 : 05:52:46

I have a table with workorders and one with the worktime these orders take in the 4 workshops. There are 5 different workshop ID's 12, 13, 22, 23, 31. (12 + 13) go together as ONE Workshop, while the other 3 go seperately[CODE]workorder time workshop
-----------------------------
WO24312 2.0 12
WO24312 17.0 31
WO24312 30.5 12
WO24312 44.5 13[/CODE]I need to sum up the times every workorder spends in the workshops. I did

Select PRODTABLE.WORKORDER, SUM(WS1x.TIME) as T1x, SUM(WS22.TIME) as T22, SUM(WS23.TIME) as T23, SUM(WS31.TIME) as T31 

from PRODTABLE left outer join WORKTASKS as WS1x on PRODTABLE.WORKORDER = WS2x.WORKORDER and (WS1x.WORKSHOP =12 OR WS1x.WORKSHOP =13)
               left outer join WORKTASKS as WS22 on PRODTABLE.WORKORDER = WS22.WORKORDER and WS22.WORKSHOP =22
               left outer join WORKTASKS as WS21 on PRODTABLE.WORKORDER = WS21.WORKORDER and WS23.WORKSHOP =23
               left outer join WORKTASKS as WS31 on PRODTABLE.WORKORDER = WS31.WORKORDER and WS31.WORKSHOP =31
GROUP BY PRODTABLE.WORKORDER

With the -wrong-result:

PRODID   T1x   T22   T23   T31
-------------------------------
WO24312  77.0  NULL  NULL  51.0

while the right result should be: T1x: 2+30.5+44.5)=77 and T31=17. But it returns 3 times (for the 3 records I guess) that value... something's wrong?!

Regards,
Martin

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 05:58:23

select p.workorder, T1x = sum(case when w.WORKSHOP in (12,13) then w.TIME else 0 end
, T22 = sum(case when w.WORKSHOP in (22) then w.TIME else 0 end
, T23 = sum(case when w.WORKSHOP in (23) then w.TIME else 0 end
, T31 = sum(case when w.WORKSHOP in (31) then w.TIME else 0 end
from PRODTABLE p
join WORKTASKS w
on p.WORKORDER = w.WORKORDER
group by p.workorder

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-27 : 06:28:34

Cool... that works - once you close the brackets ;)
Now I only have to understand it. Thanks, Nigel

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-27 : 10:42:15

I had to link 2 other tables and now I'm facing difficulties in speed.

select p.workorder, T1x = sum(case when w.WORKSHOP in (12,13) then w.TIME else 0 end
, T22 = sum(case when w.WORKSHOP in (22) then w.TIME else 0 end)
, T23 = sum(case when w.WORKSHOP in (23) then w.TIME else 0 end)
, T31 = sum(case when w.WORKSHOP in (31) then w.TIME else 0 end)
from PRODTABLE * p 
join WORKTASKS w
on p.WORKORDER = w.WORKORDER
	      INNER JOIN LP_CAPA_DAY ON p.Date = LP_CAPA_DAY.LP_DAY
	      INNER JOIN dbo.WORKCALENDARDATE ON p.Date = dbo.WORKCALENDARDATE.TRANSDATE
group by p.workorder, p.Date

I was trying to insert the inner join after the *, hoping that this for some reason would be faster, but I cannot get the syntax right... SQL always complaints about the 'p'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 11:01:50

Is there one row per day in LP_CAPA_DAY and WORKCALENDARDATE?
If so that shouldn't be an issue (get rid of the *)
Do both tables have an index on the date?

You aren't using anything from the new tables but try this
;with cte as
(
select p.workorder, p.date, T1x = sum(case when w.WORKSHOP in (12,13) then w.TIME else 0 end
, T22 = sum(case when w.WORKSHOP in (22) then w.TIME else 0 end)
, T23 = sum(case when w.WORKSHOP in (23) then w.TIME else 0 end)
, T31 = sum(case when w.WORKSHOP in (31) then w.TIME else 0 end)
from PRODTABLE * p
join WORKTASKS w
on p.WORKORDER = w.WORKORDER
group by p.workorder, p.Date
)
select *
from cte
INNER JOIN LP_CAPA_DAY ON cte.Date = LP_CAPA_DAY.LP_DAY
INNER JOIN dbo.WORKCALENDARDATE ON cte.Date = dbo.WORKCALENDARDATE.TRANSDATE

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 11:10:57

why do you need * there? can you explain what you're trying to achieve with * there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-27 : 13:01:39

The way I posted the query it takes 30 seconds, so I was trying to do the join at a different stage... just in case it would be faster.
Both tables do have exactly one result for date. I resolved by inserting a Select that was probably missing:

from (select PRODTABLE.PRODID, PRODTABLE.DATE from PRODTABLE
            INNER JOIN LP_CAPA_DAY ON PRODTABLE.DATE = LP_CAPA_DAY.LP_DAY
	    INNER JOIN dbo.WORKCALENDARDATE ON PRODTABLE.DATE = dbo.WORKCALENDARDATE.TRANSDATE) p
join PRODROUTE w
on p.PRODID = w.PRODID

... and it is really faster.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 13:04:07

quote:
Originally posted by barnabeck

The way I posted the query it takes 30 seconds, so I was trying to do the join at a different stage... just in case it would be faster.
Both tables do have exactly one result for date. I resolved by inserting a Select that was probably missing:
from (select PRODTABLE.PRODID, PRODTABLE.DATE from PRODTABLE
INNER JOIN LP_CAPA_DAY ON PRODTABLE.DATE = LP_CAPA_DAY.LP_DAY
INNER JOIN dbo.WORKCALENDARDATE ON PRODTABLE.DATE = dbo.WORKCALENDARDATE.TRANSDATE) p
join PRODROUTE w
on p.PRODID = w.PRODID
... and it is really faster.

ok makes sense

and just if you're not aware, this method is called derived table as you derive a table out of inner select query with joins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources