Merging 2 queries

1

Hi All,

I have the below query which derives 'returns order' by productcode

SELECT ProductCode, producttitle, COUNT(ProductCode)as Unitsreturned
FROM [tbl_OrderItem]
where
(DateReturnAccepted between '2017-07-09 00:00:00.00' and '2017-07-15 23:59:59.99')
group by ProductCode,producttitle
order by COUNT(ProductCode) desc

I want to include corresponding sale for that product using datecreated column
Below query will retrive total sale for each product.But i want to merge these queries together?

select ProductCode, COUNT (productcode) FROM [tbl_OrderItem]
where (DateCreated between '2017-07-09 00:00:00.00' and '2017-07-15 23:59:59.99')
and NumberDespatched = '1'
group by ProductCode
order by COUNT(ProductCode) desc

Output column i need is ProductCode, producttitle, Unitsreturned, Unitssold

Any help will be greatly appreciated.

Thanks
SG

2

You can wrap each query into a cte and then join the two cte's, something like this:

;with returns as
( -- your first query here with no order by clause),
Dispached as 
( -- your second query here with no order by clause)
select
  *
from returns r full join Dispatched d on
  d.productcode = r.productcode.

Another option, is as follows, which is preferable because you are scanning the data only once

SELECT	ProductCode,
		producttitle,
		SUM( CASE WHEN 
				DateCreated >= '20170709' 
				AND DateCreated < '20170716' 
				AND NumberDespatched=1 THEN 1 
			ELSE 0 END) AS UnitsCreated,
		SUM( CASE WHEN 
			DateReturnAccepted >= '20170709' 
			AND DateReturnAccepted < '20170716'  THEN 1
			ELSE 0 END) Unitsreturned
FROM
		[tbl_OrderItem]
GROUP BY 
		ProductCode ,
        producttitle
ORDER BY 
		UnitsCreated DESC,
		Unitsreturned DESC;

In either case, use the date range as I have shown here with a >= and < rather than trying to use the between clause as you have done with time parts etc.

3

Thank You James.
I tried the 2 nd option, It's working Perfect.

4

Great!

You may want to add a WHERE clause to limit the rows that SQL server has to look through - something like this:

WHERE
  ( DateCreated >= '20170709' OR DateReturnAccepted >= '20170709' )
  AND 
  ( DateCreated < '20170716' OR DateReturnAccepted < '20170716' )

This can make it easier to retrieve the data if there are indexes on DateReturnAccepted and DateCreated columns.

5

Thank You for your guidance James. Much appreciated.

Thanks