CODEWITHSUNDEEP
sqlsql-serversql-server-2008
sqlrookuser
sqlrookuser

Reputation: 23

Comparing rows with another rows in a single SQL Server Table

I've a table with the following rows of data.

EngID   Tower   Billing  Amt
100     ICS       Y      5000
100     EDT       Y      7777
100     ICS       N      2000

and I want the result set to be consolidated by Tower & Eng ID and the amount put into the appropriate column (Invoiced or Not Invoiced) based on Billing criteria. So, below is how the final result set should look for the above table:

EngID  Tower   Inv Amt (Amt when Billing = Y)  Non-Invoiced Amt (Billing=N)
100     ICS       5000                                     2000
100     EDT       7777

I'm able to get the 1st row of the result set by using the below query:

Select Temp1.Tower, Temp1. EngID, Temp2.InvoiceAmt as [Inv Amt], Temp1.InvoiceAmt AS [Non-Invoiced Amt] from
(
SELECT EngID, TOWER,BILLING, InvoiceAmt,RANK() OVER (PARTITION BY EngID, TOWER ORDER BY BILLING) AS RNK
  FROM [GDF].[dbo].[Sample]  ) Temp1 INNER JOIN (SELECT EngID, TOWER,Billing,InvoiceAmt, RANK() OVER (PARTITION BY EngID, TOWER ORDER BY BILLING) AS RNK
  FROM [GDF].[dbo].[Sample] ) Temp2 ON 

  Temp1.EngID = Temp2.EngID
  AND (Temp1.Tower = Temp2.Tower AND Temp1.Billing < Temp2.Billing)

However, struggling to get the second row result. My plan is to get the two rows through two separate queries and then do a union to combine the results.

Upvotes: 2

Views: 79

Answers (4)

vinay koul
vinay koul

Reputation: 346

This code will give the desired result without any complexity.Please do find the snapshot of output from below mentioned query.Hope I solved your problem. enter image description here

WITH Mycte
AS
(
Select ENGID,Tower,Case When Billing='Y' Then  ISNULL(SUM(Amt),0) END AS Inv_Amt,
Case When Billing='N' Then ISNULL(SUM(Amt),0) END AS Non_Inv_Amt from #Sample
group by ENGID,Tower,Billing
)
Select ENGID,Tower,SUM(Inv_Amt) AS Inv_Amt,SUM(Non_Inv_Amt) AS Non_Inv_Amt from mycte
group by ENGID,Tower

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can also do it using OUTER APPLY as below:

select A.EngID, 
    sum(A.Amt) as [Inv Amt (Amt when Billing = Y)], 
    sum(B.Amt) as [Non-Invoiced Amt (Billing=N)]
from #test A
outer apply(select b.Amt from #test B where A.EngID = b.EngID and b.tower = a.tower and B.Billing = 'n') B
where a.billing = 'y'
group by A.EngID, A.Tower

Simple LEFT JOIN:

select A.EngID, 
    sum(A.Amt) as [Inv Amt (Amt when Billing = Y)], 
    sum(B.Amt) as [Non-Invoiced Amt (Billing=N)]
from #test A
left join #test B on A.EngID = b.EngID 
    and b.tower = a.tower 
    and B.Billing = 'n'
where a.billing = 'y'
group by A.EngID, A.Tower

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

One method is conditional aggregation:

select s.engid, s.tower,
       sum(case when s.billing = 'Y' then amt end) as billing_y,
       sum(case when s.billing = 'N' then amt end) as billing_n
from gdf.dbo.sample s
group by s.engid, s.tower;

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select engid, tower,
    sum(case when billing = 'Y' then amt end) Inv_amt,
    sum(case when billing = 'N' then amt end) Non_Inv_amt,
from my_table
group by
    engid,
    tower;

Upvotes: 1

Related Questions