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
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