Hi,
I have 2 tables that contains the received transaction. the first table is a header and the other table is a details. if you see the details table there are source type named inventory and purchase. Purchase is the actual receive in Warehouse while Inventory was named after there was an adjustment. I would like to get the if there's an adjustment made or a regular receiving only. Thank you
create table #sample_header
(id nvarchar(50), [type] int , itemid nvarchar(50), createddatetime datetime, modified_date datetime, sourceid int)
insert into #sample_header
select '18987174400',2,'MAL123456RGD','2017-01-22 23:44:11.000','2017-02-15 16:06:25.000',3
union all
select '18987174401',2,'MAL223456RGD','2017-02-10 08:49:02.000','2017-02-20 21:06:28.000',3
union all
select '18987174402',2,'MAL300046GLD','2016-11-11 21:29:35.000','2017-02-17 15:34:02.000',3
create table #sample_details
(id nvarchar(50), itemid nvarchar(50), [type_source] nvarchar(50), createddatetime datetime, modified_date datetime, sourceid int)
insert into #sample_details
select '18987174400','TOC123456RGD','Purchase','2017-01-22 23:44:11.000','2017-01-22 23:44:11.000',3
union all
select '18987174400','MAL123456RGD','Inventory','2017-02-10 06:28:18.000','2017-02-10 06:28:18.000',7
union all
select '18987174401','MAL223456RGD','Purchase','2017-02-10 08:49:02.000','2017-02-20 21:06:28.000',3
union all
select '18987174402','MAL300046GLD','Purchase','2016-11-11 21:29:35.000','2016-11-11 21:29:35.000',3
union all
select '18987174402','TOC300046GLD','Purchase','2017-01-24 05:23:31.000','2017-01-24 05:23:31.000',3
union all
select '18987174402','MAL300046GLD','Inventory','2017-02-10 06:28:18.000','2017-02-10 06:28:18.000',7
select h.id, d.itemid, d.type_source, d.createddatetime, d.modified_date, d.[source]
from #sample_header h
inner join #sample_details d
on h.id = d.id
desired result:
id---------itemid--------type_source----createddatetime-----modified_date---------source
------------------------------------------------------------------------------------------------------
18987174400|MAL123456RGD|Inventory| 2017-02-10 06:28:18.000|2017-02-10 06:28:18.000 7
18987174401|MAL223456RGD|Purchase | 2017-02-10 08:49:02.000|2017-02-20 21:06:28.000 3
18987174402|MAL300046GLD|Purchase | 2016-11-11 21:29:35.000|2016-11-11 21:29:35.000 3
18987174402|MAL300046GLD|Inventory| 2017-02-10 06:28:18.000|2017-02-10 06:28:18.000 7