Dim cmdText As String = "Select EDescription, SUM(EQuantity) AS EQuantity, " & _ "SUM(EAmount) AS EAmount, TDescription,SUM(TQuantity) AS TQuantity, SUM(TAmount) AS TAmount " & _ "From " & _ "(" & _ "SELECT de.Description AS EDescription, de.Quantity AS EQuantity, de.Amount AS EAmount," & _ "0 AS TDescription, 0 AS TQuantity,0 AS TAmount FROM DailyExpenses de " & _ "WHERE de.EDate Between @START and @END " & _ "UNION ALL " & _ "SELECT 0 AS EDescription, 0 AS EQuantity, 0 AS EAmount, " & _ "dt.Description AS TDescription,dt.Quantity AS TQuantity,dt.Amount AS TAmount FROM DailyTransaction dt " & _ "WHERE dt.DTDate Between @START and @END " & _ ") a " & _ "GROUP BY EDescription,TDescription"
EDescription EQuantity EAmount TDescription TQuantity TAmount
0 0 0 cocacola 2 6
0 0 0 Sprite 3 12
0 0 0 Pepsi 4 15
Rent 1 1500 0 0 0
Beaf 10 50 0 0 0
Conveyance 1 10 0 0 0
My question is, How I can amend this query to avoid these zeros and to get the result as under:
EDescription EQuantity EAmount TDescription TQuantity TAmount
Rent 1 1500 cocacola 2 6
Beaf 10 50 Sprite 3 12
Conveyance 1 10 Pepsi 4 15
Thanks
monty edwardsPosted Jan 21, 2010, 3:39 PM