CODEWITHSUNDEEP
sqlsql-server
user2747546
user2747546

Reputation: 33

how to join second result to first result in sql server

I have store procedure like this:

ALTER procedure [dbo].[Driverperformance]
 @Ecode nvarchar(50),
 @startdate datetime,
 @enddate datetime 
AS 
BEGIN
 declare @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120);        
 declare @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120); 

 SELECT 
   e.Ecode,
   CAST(q.dtime AS DATE) as Date, 
   e.Ename, 
   count(q.Ecode) CntEcode 
 FROM 
   EmployeeMaster_tbl e  JOIN Transaction_tbl q  ON e.Ecode = q.Ecode 
 WHERE 
   q.Ecode=@Ecode AND dtime >= '' + @date1 +'' AND dtime <= ''+@date2+''  
 GROUP BY 
   e.Ecode, e.Ename, CAST(q.dtime AS date) 
 ORDER BY CAST(q.dtime AS date)

 SELECT 
   e.Ecode,
   CAST(q.dtime AS DATE) as Date, 
   e.Ename, 
   count(q.DelEcode) DelEcode 
 FROM 
   EmployeeMaster_tbl e JOIN Transaction_tbl q  ON e.Ecode = q.DelEcode 
 WHERE 
   q.DelEcode=@Ecode AND dtime >= '' + @date1 +'' AND dtime <= ''+@date2+''  
 GROUP BY
   e.Ecode, e.Ename, CAST(q.dtime AS date) 
 ORDER BY 
   CAST(q.dtime AS date) --e.Ecode DESC
 END

I am getting out put like this first result as:

Ecode   Date         Ename    CntEcode  
E003    2013-05-05   Raheem    4                    
E003    2013-05-28   Raheem    1

Second result:

Ecode   Date         Ename    CntEcode  
E003    2013-05-05   Raheem    3         
E003    2013-05-27   Raheem    7           
E003    2013-05-28   Raheem    1

But I want to get out put in one result like this:

Ecode   Date         Ename    CntEcode   DelEcode
E003    2013-05-05   Raheem    4            3
E003    2013-05-27   Raheem    0            7
E003    2013-05-28   Raheem    1            1

How i can achieve this? if any one know please help me to find out.

Upvotes: 1

Views: 84

Answers (2)

Phill C
Phill C

Reputation: 606

Try something like this, just make sure to modify to top select statement to select the data you need 

    ALTER procedure [dbo].[Driverperformance]
    @Ecode nvarchar(50),
    @startdate datetime,
    @enddate datetime 
AS
BEGIN
DECLARE @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120); 
DECLARE @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120); 

SELECT T1.*, T2.* 
FROM
(
   SELECT e.Ecode as ECode,
   CAST(q.dtime AS DATE) as [Date], 
   e.Ename as EName, 
   COUNT(q.Ecode) CntEcode 
   FROM EmployeeMaster_tbl e  JOIN Transaction_tbl q ON e.Ecode = q.Ecode 
   WHERE q.Ecode=@Ecode and dtime >= '' + @date1 +'' and dtime <= ''+@date2+'' 
   GROUP BY e.Ecode, e.Ename, CAST(q.dtime AS date)        
) T1
FULL JOIN
(
   SELECT e.Ecode as ECode,
   CAST(q.dtime AS DATE) as [Date], 
   e.Ename as EName, 
   COUNT(q.DelEcode) DelEcode 
   FROM EmployeeMaster_tbl e JOIN Transaction_tbl q ON e.Ecode = q.DelEcode 
   WHERE q.DelEcode=@Ecode and dtime >= '' + @date1 +'' and dtime <= ''+@date2+''  
   GROUP BY e.Ecode, e.Ename, CAST(q.dtime AS date) 
) T2 
ON T1.ECode = T2.ECode
ORDER BY T1.Date     

END

Upvotes: 1

Reto
Reto

Reputation: 112

You can use the UNION or UNION ALL operator to Join identical resultsets.

declare @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120); 
declare @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120); 
SELECT e.Ecode,CAST(q.dtime AS DATE) as Date , e.Ename, count(q.Ecode) CntEcode FROM 
EmployeeMaster_tbl e JOIN Transaction_tbl q ON e.Ecode = q.Ecode where q.Ecode=@Ecode and dtime >= '' + @date1 +'' and dtime <= ''+@date2+'' 
UNION
SELECT e.Ecode,CAST(q.dtime AS DATE) as Date , e.Ename, count(q.DelEcode) DelEcode FROM 
EmployeeMaster_tbl e JOIN Transaction_tbl q ON e.Ecode = q.DelEcode where q.DelEcode=@Ecode and dtime >= '' + @date1 +'' and dtime <= ''+@date2+'' 
group by e.Ecode, e.Ename, CAST(q.dtime AS date) ORDER BY CAST(q.dtime AS date)

Upvotes: 1

Related Questions