dummy records als record niet bestaat

Status
Niet open voor verdere reacties.
M

manueld

Gebruiker
Lid geworden
12 feb 2012
Berichten
216
ik heb een ingewikkelde stored procedure in MSSQL2008 deze produceert 7 sets van 6 records via een union query tot 1 recordset. elk record heeft een id die zijn plaats bepaald.

een voorbeeld

id bestaat uit 2 karakters waarbij de eerste de groep en de tweede de stappen weergeeft dus 23 is groep 2 en stap 3

ID aantal aantal2 naam
11 34 26 bla
12 5346 677 bla2
13 34 376 bla3
14 663 734 bla4
21 346 456 oeps1
22 634 6234 oeps2
23 346 6 oeps3
24 66 234 oeps4
31 33 6 tja1
32 26 337 tja2
33 236 373 tja3
34 24 6 tja4


tot zover heb ik hetr werkend en klaar. het probleem is dat soms is de brondata niet compleet en dan valt een complete groep of stap weg. aangezien mij vervolg process daarover struikeld wil ik graag een compleet recordset hebben ook als er geen data beschikbaar is

stel stap 24 is niet beschikbaar dan wil ik dat deze stap op 0 gezet wordt zoals hier onder

ID aantal aantal2 naam
11 34 26 bla
12 5346 677 bla2
13 34 376 bla3
14 663 734 bla4
21 346 456 oeps1
22 634 6234 oeps2
23 346 6 oeps3
24 0 0 oeps4
31 33 6 tja1
32 26 337 tja2
33 236 373 tja3
34 24 6 tja4


ter ondersteuning geeft ik hierbij mijn voledige query

Code: 
declare @@startdate as date
set @@startdate = CONVERT(date, getdate())
declare @@enddate as date
set @@enddate = CONVERT(date, getdate()+1)


select avg(q.ReaQty) as Parts,avg(q.lines) as lines ,avg(q.minutes) as minutes,q.Status, p.Description as PoTypestr , q.rotype,PoType, (cast(potype as varchar(10)) + (cast(st as varchar(10))))as ID from (
-- cdate2picking

select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,cdate) as Date

,potype
,'1-cdate2picking' as Status
,'1' as st
,AVG(DATEDIFF(MINUTE, [CDate],[PickedStart] )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [CDate] = '9999-12-31 00:00:00.000' 
 and not[PickedStart]= '9999-12-31 00:00:00.000' and  cdate between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,cdate),PoType,PoRoType)z

 left join

(select convert (date,cdate)as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [CDate] = '9999-12-31 00:00:00.000' 
 and not[PickedStart]= '9999-12-31 00:00:00.000'  and  cdate between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,cdate),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype


union all

--Pickstart2PickEnd
select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,[PickedStart]) as Date

,potype
,'2-Pickstart2PickEnd' as Status
,'2' as st
,AVG(DATEDIFF(MINUTE, [PickedStart],[PickedEnd] )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [PickedStart] = '9999-12-31 00:00:00.000' 
 and not[PickedEnd]= '9999-12-31 00:00:00.000' and   [PickedStart] between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,[PickedStart]),PoType,PoRoType)z

 left join

(select convert (date,[PickedStart])as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [PickedStart] = '9999-12-31 00:00:00.000' 
 and not[PickedEnd]= '9999-12-31 00:00:00.000'  and  [PickedStart] between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,[PickedStart]),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype


 -- pickend to packstart



 union all

 select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,[Pickedend]) as Date

,potype
,'3-Pickend2Packstart' as Status
,'2' as st
,AVG(DATEDIFF(MINUTE, [Pickedend],PackedStart )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [Pickedend] = '9999-12-31 00:00:00.000' 
 and not PackedStart= '9999-12-31 00:00:00.000'  and  [Pickedend] between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,[Pickedend]),PoType,PoRoType)z

 left join

(select convert (date,[Pickedend])as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not [Pickedend] = '9999-12-31 00:00:00.000' 
 and not PackedStart= '9999-12-31 00:00:00.000'  and  [Pickedend] between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,[Pickedend]),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype


  -- packstart to packend



 union all

 select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,PackedStart) as Date

,potype
,'4-Packstart2PackEnd' as Status
,'4' as st
,AVG(DATEDIFF(MINUTE, PackedStart,PackedEnd )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not PackedStart = '9999-12-31 00:00:00.000' 
 and not PackedEnd= '9999-12-31 00:00:00.000'  and  PackedStart between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,PackedStart),PoType,PoRoType)z

 left join

(select convert (date,PackedStart)as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not PackedStart = '9999-12-31 00:00:00.000' 
 and not PackedEnd= '9999-12-31 00:00:00.000'  and  PackedStart between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,PackedStart),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype

 --PackEnd290WH

  union all

 select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,PackedEnd) as Date

,potype
,'5-PackEnd290WH' as Status
,'5' as st
,AVG(DATEDIFF(MINUTE, PackedEnd,WH90DATE )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not PackedEnd = '9999-12-31 00:00:00.000' 
 and not WH90DATE= '9999-12-31 00:00:00.000'  and  PackedEnd between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,PackedEnd),PoType,PoRoType)z

 left join

(select convert (date,PackedEnd)as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not PackedEnd = '9999-12-31 00:00:00.000' 
 and not WH90DATE= '9999-12-31 00:00:00.000'  and  PackedEnd between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,PackedEnd),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype



 --90WH2Shipped

   union all

 select z.Date,z.minutes,z.PoType,z.Status,d.ReaQty,d.lines, d.rotype,st from(SELECT 
convert (date,WH90DATE) as Date

,potype
,'6-90WH2Shipped' as Status
,'6' as st
,AVG(DATEDIFF(MINUTE, WH90DATE,ShipoutDate )) AS minutes
,PoRoType
FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not WH90DATE = '9999-12-31 00:00:00.000' 
 and not ShipoutDate= '9999-12-31 00:00:00.000'  and  WH90DATE between @@startdate and @@enddate and PoRoType = 'sop'
)x
 group by convert (date,WH90DATE),PoType,PoRoType)z

 left join

(select convert (date,WH90DATE)as date,PoType,count(line) as lines,sum(ReaQty) as ReaQty,PoRoType as rotype FROM (select* from [LMD_Process_Control].dbo.[tblPoDetails] left join [LMD_Process_Control].dbo.[tblPo] on RecordOutUid = PoRecordUid
 where    not WH90DATE = '9999-12-31 00:00:00.000' 
 and not ShipoutDate= '9999-12-31 00:00:00.000'  and  WH90DATE between @@startdate and @@enddate and PoRoType = 'sop')a 
 
 group by convert (date,WH90DATE),potype,PoRoType)D on d.date = z.date where z.PoType =d.potype and  z.PoRoType =d.rotype

 ) q left join [LMD_Process_Control].dbo.[tblPoType] P on p.[Type] = q.PoType

 group by q.Status,Description, q.rotype,type, potype,st
 
  order by  Type, Status
 
uiteindelijk zelf de oplossing gevonden. heb een tabel gemaakt met de gegevens die ik verwacht. vervolgens heb ik die via een join gekoppeld aan de resultaten van mijn oude query om vervolgens via een group by en max(veldwaarde) de reslutaten weer te geven. daarwaar waarden zijn zal hij die waarde pakken. waar geen waarden zijn vind hij alleen de tabel en geeft die als 0 weer.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan