For a SQL

Table a
orderid, fulfillDate, fulfillQty
1 1/1/2010 100
2 1/1/2011 200
2 1/1/2011 300
3 1/1/2010 400
Table b
orderid, shipmentDate, shipmentQty
2 1/1/2011 200
3 1/1/2012 300
4 1/1/2011 400


To write a SQL, say not clear requirements, I give you an example, given two parameter startdate=1/1/2011, enddate=1/1/2011
Table a OrderID 2 on this day of 2 records, qty sum to 200+300=500
Table B OrderID 2 on this day of 1 records, qty sum to 200, OrderID 4 also has a record of 1 qty 400
The last result table to become so:
orderid, fulfillQtyTotal, shipmentQtyTotal
2 500 200
4 0 400

Started by Bancroft at November 15, 2016 - 4:57 PM

create table #ta(orderid int, fulfillDate datetime, fulfillQty int)
insert into #ta
select 1,'1/1/2010',100
union all select 2,'1/1/2011',200
union all select 2,'1/1/2011',300
union all select 3,'1/1/2010',400

create table #tb(orderid int, shipmentDate datetime, shipmentQty int)
insert into #tb
select 2,'1/1/2011',200
union all select 3,'1/1/2012',300
union all select 4,'1/1/2011',400
go

declare @startdate datetime,@enddate datetime
select @startdate='1/1/2011',@enddate='1/1/2011'

select isnull(a.orderid,b.orderid) as orderid,a.fulfillQtyTotal,b.shipmentQtyTotal
from (select orderid,sum(fulfillQty) as fulfillQtyTotal from #ta where fulfillDate between @startdate and @enddate group by orderid)a
right join (select orderid,sum(shipmentQty) as shipmentQtyTotal from #tb where shipmentDate between @startdate and @enddate group by orderid)b
	on a.orderid=b.orderid
drop table #ta,#tb

/*
orderid  fulfillQtyTotal  shipmentQtyTotal
2	500	200
4	NULL	400
*/

Posted by Bonnie at November 16, 2016 - 5:29 PM

[code=sql][code=sql]if object_id('pro_test') is not null
drop proc pro_test
go
create proc pro_test
(
@startdate varchar(10),
@enddate varchar(10)
)

select
   isnull(a.orderid,b.orderid) as orderid,
   a.fulfillQtyTotal
   b.shipmentQtyTotal
from
   (select orderid,fulfillDate,isnull(sum(fulfillQty),0) as fulfillQtyTotal group by orderid,fulfillDate)a
full join
    (select orderid,shipmentDate,isnull(sum(shipmentQty),0) as shipmentQtyTotal group by orderid,fulfillDate)b
on
    a.orderid=b.orderid and a.fulfillDate=b.shipmentDate
where 
     isnull(a.fulfillDate,b.shipmentDate)>=@startdate and isnull(a.fulfillDate,b.shipmentDate)<=@enddate
[/code][/code]

Posted by Lionel at November 23, 2016 - 6:22 PM

Create table a
(orderid int,fulfillDate varchar(15),fulfillQty int)

Insert into a
 select 1,'1/1/2010',100 union all
 select 2,'1/1/2011',200 union all
 select 2,'1/1/2011',300 union all
 select 3,'1/1/2010',400

Create table b
(orderid int,shipmentDate varchar(15),shipmentQty int)

Insert into b
 select 2,'1/1/2011',200 union all
 select 3,'1/1/2012',300 union all
 select 4,'1/1/2011',400


declare @startdate varchar(15),@enddate varchar(15)
select @startdate='1/1/2011',@enddate='1/1/2011'

select isnull(a.orderid,b.orderid) 'orderid',
       isnull(a.fulfillQtyTotal,0) 'fulfillQtyTotal',
       isnull(b.shipmentQtyTotal,0) 'shipmentQtyTotal'
from
(select orderid,sum(fulfillQty) 'fulfillQtyTotal'
 The from table a 
 where fulfillDate between @startdate and @enddate
 group by orderid) a
full join
(select orderid,sum(shipmentQty) 'shipmentQtyTotal'
 The from table b 
 where shipmentDate between @startdate and @enddate
 group by orderid) b on a.orderid=b.orderid

/*
orderid     fulfillQtyTotal shipmentQtyTotal
----------- --------------- ----------------
2           500             200
4           0               400

(2 row(s) affected)
*/

Posted by Maxwell at November 24, 2016 - 7:15 PM

create table #ta(orderid int, fulfillDate datetime, fulfillQty int)
insert into #ta
select 1,'1/1/2010',100
union all select 2,'1/1/2011',200
union all select 2,'1/1/2011',300
union all select 3,'1/1/2010',400

create table #tb(orderid int, shipmentDate datetime, shipmentQty int)
insert into #tb
select 2,'1/1/2011',200
union all select 3,'1/1/2012',300
union all select 4,'1/1/2011',400
go

declare @startdate datetime,@enddate datetime
select @startdate='1/1/2011',@enddate='1/1/2011'

select a.orderid,isnull(SUM(fulfillQty),0) as fulfillQtyTotal,max(shipmentQty) as  shipmentQtyTotal  from #tb a
left join #ta b  
on a.orderid=b.orderid  where a.shipmentDate>=@startdate 
and a.shipmentDate<=@enddate group by a.orderid    

Posted by Ternence at November 30, 2016 - 7:19 PM

create table cstb_a(orderid int, fulfillDate datetime,fulfillQty int)
insert into cstb_a
select 1,'1/1/2010',100 union all
select 2,'1/1/2011',200 union all
select 2,'1/1/2011',300 union all
select 3,'1/1/2010',400 union all
select 5,'1/1/2011',500
go

create table cstb_b(orderid int, shipmentDate datetime,shipmentQty int)
insert into cstb_b
select 2,'1/1/2011',200 union all
select 3,'1/1/2012',300 union all
select 4,'1/1/2011',400
go

create proc TotalQty
	@startdate varchar(10), 
	@enddate varchar(10)
as
begin
	;with cte_a as
	(
		select orderid,sum(fulfillQty) fulfillQtyTotal
		from cstb_a
		where fulfillDate between @startdate and @enddate
		group by orderid,fulfillDate
	),
	cte_b as
	(
		select orderid,sum(shipmentQty) shipmentQtyTotal
		from cstb_b
		where shipmentDate between @startdate and @enddate
		group by orderid,shipmentDate
	)
	select 
		isnull(a.orderid,b.orderid) orderid,
		isnull(a.fulfillQtyTotal,0) fulfillQtyTotal,
		isnull(b.shipmentQtyTotal,0) shipmentQtyTotal
	from cte_a a full join cte_b b on a.orderid = b.orderid
end

/*
exec TotalQty '1/1/2011','1/1/2011'

orderid     fulfillQtyTotal shipmentQtyTotal
----------- --------------- ----------------
2           500             200
4           0               400
5           800             0

(3 rows affected)
*/

Posted by Eve at December 13, 2016 - 7:48 PM

The last result is 55000, the above data insertion was 800

Posted by Eve at December 20, 2016 - 8:15 PM

The reason to add their own one, because full join can not miss here

Posted by Eve at January 02, 2017 - 9:13 PM

Posted by Geoffrey at January 08, 2017 - 9:27 PM