Join Two Sql Queries [Resolved]

Posted by Sharpcnet under Sql Server on 12/11/2013 | Points: 10 | Views : 716 | Status : [Member] | Replies : 3
Query 1:
Select T.TypeId, L.Type, SUM(T.Credit)Credit,SUM(T.Debit)Debit
From tblTransactions T
Join tblType L on T.TypeId = L.TypeId
Where T.EmpCode='E1'
Group By T.TypeId, L.Type

TypeId Type Credit Debit
-------------------------
10 AL 0 2
20 CL 6 0
Query 2:
Declare @EmpGradeId uniqueidentifier = null
Select @EmpGradeId = EmpGradeId From tblEmp Where EmpCode='E1'

Select G.TypeId, L.Type, G.Leaves, G.Accruable,
'Accrued' = Case When G.Accruable = 1 Then (G.Leaves*Month(GetDate())) Else G.Leaves END
From tblGrade G
Join tblType L On G.TypeId = L.TypeId
Where G.GradeId = @EmpGradeId

TypeId Type Leaves Accruable Accrued
--------------------------------------
10 AL 5 1 5
20 CL 2 0 2
I would like to join these 2 queries and get a result like
Type Credit Debit Accrued
--------------------------
AL 0 2 5
CL 6 0 2
Tried this - but it gives only first result set.
Declare @EmpGradeId uniqueidentifier = null
Select @EmpGradeId = EmpGradeId From tblEmp Where EmpCode='E1'
Query1 union all query2





Responses

Posted by: Allemahesh on: 12/11/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
I got you problem.

Try the below one and it will solve your problem.



DECLARE @TB1 TABLE
(
TypeId int,
[Type] varchar(50),
Credit int,
Debit int
)


INSERT INTO @TB1
SELECT 1,'FD',5,5
Select T.TypeId, L.Type, SUM(T.Credit)Credit,SUM(T.Debit)Debit
From tblTransactions T
Join tblType L on T.TypeId = L.TypeId
Where T.EmpCode='E1'
Group By T.TypeId, L.Type

DECLARE @TB2 TABLE
(
TypeId int,
[Type] varchar(50),
Leaves int,
Accruable int,
Accrued int
)

Declare @EmpGradeId uniqueidentifier = null
Select @EmpGradeId = EmpGradeId From tblEmp Where EmpCode='E1'

INSERT INTO @TB2
Select G.TypeId, L.Type, G.Leaves, G.Accruable,
'Accrued' = Case When G.Accruable = 1 Then (G.Leaves*Month(GetDate())) Else G.Leaves END
From tblGrade G
Join tblType L On G.TypeId = L.TypeId
Where G.GradeId = @EmpGradeId


SELECT A.TypeId, Credit, Debit, Accrued
FROM @TB1 A inner join @TB2 B
ON A.TypeId = B.TypeId


Happy coding.
If this help to you, please click on Mark As Answer

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Declare @EmpGradeId uniqueidentifier = null 

Select @EmpGradeId = EmpGradeId From tblEmp Where EmpCode='E1'

;WITH LeaveDetails
AS (Select T.TypeId, L.Type, SUM(T.Credit)Credit,SUM(T.Debit)Debit
From tblTransactions T
Join tblType L on T.TypeId = L.TypeId
Where T.EmpCode='E1'
Group By T.TypeId, L.Type
), Accruables
(Select G.TypeId, L.Type, G.Leaves, G.Accruable,
Case When G.Accruable = 1 Then (G.Leaves*Month(GetDate())) Else G.Leaves END AS Accrued
From tblGrade G
Join tblType L On G.TypeId = L.TypeId
Where G.GradeId = @EmpGradeId)
SELECT a.Type, a.Credit, a.Debit, l.Accrued
FROM Accruables a
JOIN LeaveDetails l ON a.TypeId = l.TypeId AND a.Type = l.Type


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 12/11/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you very much.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response