Skip to Content
0
Former Member
Jun 14, 2016 at 10:03 AM

Problem with query about modifcations in production orders

22 Views

Hello!

i made query which one count number of orders with modifcations in relation to the total number of orders in a given period (90 days). It works properly but i want add condition where on production order cardcode is not null. Anywhere i put for example 't1.cardcode is not null' query show incorrect vaules. Any suggestions?

select

CONVERT(float,count(b.docnum)) - convert(float,COUNT(a.docnum)) as 'Ilość zleceń zrealizowanych bez zmian',

round((CONVERT(float,count(b.docnum)) - convert(float,COUNT(a.docnum)))/CONVERT(float,count(b.docnum)),2)*100 as '% zrealizowanych w bez zmian ' ,

convert(float,COUNT(a.docnum)) as 'Ilość zleceń zrealizowanych ze zmianami',

round(convert(float,COUNT(a.docnum))/CONVERT(float,count(b.docnum)),2)*100 as '% zrealizowanych ze zmianami' ,

CONVERT(float,count(b.docnum)) as 'Ilość zamkniętych zleceń' from

(select t0.DocNum

from OWOR t0 inner join

AWOR t1 on t0.DocEntry=t1.DocEntry

where t0.DueDate>t1.DueDate and t1.LogInstanc >=2 and t0.Status in ('l') and t0.DueDate >= GETDATE()-90 --and t1.UserSign in (19,20,4)

)a

full outer join

(select t1.DocNum from OWOR t1 where t1.Status in ('l') and t1.DueDate >= GETDATE()-90

)b

on

a.DocNum=b.DocNum