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

Question

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 1

try this

you create a function as follows

CREATE FUNCTION OrderDetails (
@StartDate     DATETIME,
@EndDate       DATETIME
)
RETURNS  @tblList TABLE  
(  
orderId  VARCHAR(1000) 

)   
AS 
BEGIN       
 select orderid
 insert into @tblList
  from Orderdetails od 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 

   and (od.TransactionId IS NOT NULL or ltrim(od.TransactionId) !=  '') 
   and @Rowname=hr.HomepageRow_name and hr.status=1 
  where  Convert(datetime,Convert(Varchar(50),od.DeliveryDate,101)) between @StartDate and @EndDate 
return
end

then call the function in your stored procedure according to the date you are passing you don't miss any records

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 op1.Productid
      , count(op.ProductId)as FirstQty
      ,count(op1.ProductId)as SecondQty
      , Round(Sum(op.Price*op.Quantity),0) as FirstProductRevenue 
          , Round(Sum(op1.Price*op1.Quantity),0) as FirstProductRevenue 
 from (select Distinct Orderid,productid,Price,Quantity from Orderproducts opp  inner join [Your_ScemaName].[OrderDetails](@StartDate,@EndDate) od on opp.Orderid=od.Orderid  ) op 
 inner join  (select Distinct Orderid,productid,Price,Quantity from Orderproducts  opp inner join [Your_ScemaName].[OrderDetails](@StartDate1,@EndDate1) od on opp.Orderid=od.Orderid  ) op1 
-- since 1=1 is always true you will get all data
 on 1=1
   Group by op.Productid
end
end

Answer 2

Maybe this (simplified):

select coalesce(t1.productid, t2.productid) as productid, t1.FirstQty, t2.SecondQty, t1.FirstProductRevenue, t2.SecondProductRevenue 
from 
(select ...) t1
   ----Second duration for all associates-----
full join 
(select ...) t2 on t1.productid = t2.productid

Answer 3

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