Maybe Maybe - 6 months ago 9
SQL Question

How to combine two select query results into one result using sp?

I'm declaring two comparison dates, I want data from both dates. I'm using

left join
for combined propose but this is not correct way. I'm missing some data. Instead of left join which one is best for?

result

productid FirstQty SecondQty FirstProductRevenue SecondProductRevenue

COCAK117 1 2 1370.00 1440.00
COCAK632 1 2 1125.00 2250.00
COCAK656 1 NULL 795.00 NULL
COCAK657 1 2 720.00 2090.00
COCAK775 3 1 2475.00 825.00


I'm getting data from full of first table and matching productid from second table, but I want total productid's from both the tables.

CREATE PROCEDURE [dbo].[Orders]
(
@StartDate DATETIME,
@EndDate DATETIME,
@StartDate1 DATETIME,
@EndDate1 DATETIME,
@Rowname VARCHAR(100),
@AssociateName VARCHAR(50)
)
--[Orders] '05/03/2015','05/03/2015','05/05/2015','05/07/2015','Most Gifted Cakes','all'
AS BEGIN
if(@AssociateName='all')
BEGIN
----First duration for all associates-----

select t1.productid,t1.FirstQty,t2.SecondQty,t1.FirstProductRevenue,t2.SecondProductRevenue
from
(select op.Productid
, count(op.ProductId)as FirstQty
, Round(Sum(op.Price*op.Quantity),0) as FirstProductRevenue
from Orderdetails od
inner join (select Distinct Orderid,productid,Price,Quantity from Orderproducts) op on op.Orderid=od.Orderid
inner JOIN City ct ON od.RecipientCityName = ct.CityName
INNER JOIN Associates ass ON Ct.AssociateId = ass.AssociateId
Inner join HomepageRowWiseProducts hr on op.ProductId=hr.Productid
where Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate and @EndDate
and (od.TransactionId IS NOT NULL or ltrim(od.TransactionId) != '')
and @Rowname=hr.HomepageRow_name and hr.status=1
Group by op.Productid
) t1
----Second duration for all associates-----
left join
(select op.Productid
, count(op.ProductId)as SecondQty
, Round(Sum(op.Price*op.Quantity),0) as SecondProductRevenue
from Orderdetails od
inner join (select Distinct Orderid,productid,Price,Quantity from Orderproducts) op on op.Orderid=od.Orderid
inner JOIN City ct ON od.RecipientCityName = ct.CityName
INNER JOIN Associates ass ON Ct.AssociateId = ass.AssociateId
Inner join HomepageRowWiseProducts hr on op.ProductId=hr.Productid
where Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate1 and @EndDate1
and (od.TransactionId IS NOT NULL or ltrim(od.TransactionId) != '')
and @Rowname=hr.HomepageRow_name and hr.status=1
Group by op.Productid
) t2 on t1.productid=t2.productid
END

Answer

You can try to get all data at once and then sum only date ranges that you want. I could made some mistake here as I don't have your data structures. However you should get the idea how you can implement it.

select op.Productid
       , sum(  case when Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate and @EndDate then 
          1 else 0 end) FirstQty
       , sum(  case when Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate1 and @EndDate1 then 
          1 else 0 end)  SecondQty,
       , Round(Sum( case when Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate and @EndDate 
                        then op.Price*op.Quantity 
                         else 0 end),0) as FirstProductRevenue 
       , Round(Sum( case when Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate1 and @EndDate1 
                        then op.Price*op.Quantity 
                         else 0 end),0) as SecondProductRevenue 
 from Orderdetails od 
 inner join (select Distinct Orderid,productid,Price,Quantity from Orderproducts)  op on op.Orderid=od.Orderid 
 inner JOIN City ct  ON od.RecipientCityName = ct.CityName 
 INNER JOIN Associates ass ON Ct.AssociateId = ass.AssociateId
 Inner join HomepageRowWiseProducts hr on op.ProductId=hr.Productid 
 where  ( Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate and @EndDate 
       Or Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate1 and @EndDate1 )
   and (od.TransactionId IS NOT NULL or ltrim(od.TransactionId) !=  '') 
   and @Rowname=hr.HomepageRow_name and hr.status=1
   Group by op.Productid
Comments