jedgard
Reputation: 868
Getting aggregated data in SQL
I have a StatementsEntries table as follows
ID | Name | CaseId | StatementID | Amount | Balance | Paid
--------------------------------------------------------------------------
1 | Statement 1 | 1 | 1 | 1000 | 1000 | 1
--------------------------------------------------------------------------
2 | Payment | 1 | 0 | 1000 | 0 | 0
--------------------------------------------------------------------------
3 | Statement 2 | 3 | 2 | 500 | 500 | 0
--------------------------------------------------------------------------
4 | Statement 3 | 4 | 3 | 50 | 50 | 0
--------------------------------------------------------------------------
5 | Payment | 3 | 0 | 25 | 475 | 0
This is an existing table with hundreds of records and basically when there is a Name Statement * and a StatementId it means it was processed "invoiced", then any payments to the same case Id always goes to the older invoice. In this case Statement 1 is fully paid because the statement was generated and then a payment was received for the balance amount.
In the case of Statement 2, it was generated and it received a payment after statement 3 was generated, the payment was for 25 dollars so the payment was applied to Statement 2 with the same caseId, only payments to the specific case id are assigned, so if I had made a payment for CaseId 4, i would have applied it to Statement 3.
I would like to obtain a query with the following results:
StatementId | Name | Amount | Balance | IsPaid
----------------------------------------------------------
1 | Statement 1 | 1000 | 1000 | 1
---------------------------------------------------------
1 | Statement 1 | 1000 | 0 | 0
----------------------------------------------------------
2 | Statement 2 | 500 | 500 | 0
----------------------------------------------------------
2 | Statement 2 | 25 | 475 | 0
----------------------------------------------------------
3 | Statement 3 | 50 | 50 | 0
I have tried using UNION ALL, and joining to the same table but I am struggling setting the StatementId for the record that has the payment. Will appreciate some direction for this.
UPDATE I tried the following script
SELECT
[Statement ID] = CASE WHEN s.StatementId> 0 THEN s.StatementId ELSE x.StatementId END,
Name = CASE WHEN s.StatementId> 0 THEN s.Description ELSE x.Description END,
s.Amount,
s.Balance,
s.IsPaid
FROM StatementEntries s
OUTER APPLY(
SELECT *
FROM StatementEntries
WHERE
CaseId = s.CaseId
AND StatementId > s.StatementId AND StatementId > 0
ORDER BY ID
)x
WHERE s.CaseId = 1
ORDER BY s.ID
That yields
StatementId | Name | Amount | Balance | IsPaid
----------------------------------------------------------
1 | Statement 1 | 1000 | 1000 | 1
---------------------------------------------------------
1 | Statement 1 | 1000 | 0 | 0
----------------------------------------------------------
2 | Statement 2 | 500 | 500 | 0
----------------------------------------------------------
1 | Statement 1 | 25 | 475 | 0
----------------------------------------------------------
3 | Statement 3 | 50 | 50 | 0
See record #4, it should show Statement ID 2 and Statement 2. I am working on it trying to make it work, thank you so much for the answer though i think it's setting me in the right path.
UPDATE 2 (Adding schema and sample data)
CREATE TABLE StatementsEntries (
Id INT,
Name VARCHAR(200),
Date DATE,
IsCredit BIT,
Amount DECIMAL,
CaseId INT,
IsAdjustment BIT,
StatementId INT,
Balance DECIMAL,
IsPaid BIT,
CreateDate DATE
)
INSERT INTO StatementsEntries VALUES
(1, 'Statement 4444','04/01/2007',0, 850,1,0,4444,850,1,'04/01/2007'),
(2, 'Payment made', '04/02/2007', 1, 850,1,0,0,0,0,'04/02/2007'),
(3, 'Statement 5555','08/01/2008', 0,1003, 1,0,5555,1003, 0,'08/01/2008'),
(4, 'Statement 7777','09/01/2008',0, 3000,2,0,7777,3000,0,'09/01/2008'),
(5, 'Statement 8883','10/01/2008', 0,1500, 5,0,8883,1500, 1,'10/01/2008'),
(6, 'Payment made', '10/02/2008', 1, 1500,5,0,0,0,0,'10/02/2008'),
(7, 'Statement 9992','11/01/2008',0, 1750,7,0,9992,1750,0,'11/01/2008'),
(8, 'Payment made', '04/02/2009', 1, 1002,1,0,0,648,0,'04/02/2009'),
(9, 'Statement 9994','08/01/2010', 0,1650, 1,0,9994,1650, 1,'08/01/2010'),
(10, 'Payment made', '09/02/2011', 1, 10,1,0,0,638,0,'09/02/2011');
Upvotes: 1
Views: 176
Answers (5)
MWillemse
Reputation: 980
This should do the trick
SELECT id,
ISNULL([Statement].StatementId, SE.StatementId) AS StatementId
, ISNULL([Statement].Name, SE.Name) AS Name
, Date
, IsCredit
, Amount
, CaseId
, IsAdjustment
, Balance
, IsPaid
, CreateDate
FROM StatementsEntries AS SE
OUTER APPLY(
SELECT Name, StatementId
FROM StatementsEntries AS SE_name
WHERE SE_name.Id = SE.CaseId
AND SE.IsCredit = 1
) AS [Statement]
ORDER BY StatementId
Upvotes: 0
Jesús López
Reputation: 9221
Using the sample data, the following query:
SELECT S.Id, S.Name, S.Date, S.IsCredit, S.Amount, S.CaseId, S.IsAdjustment, S.StatementId, S.Balance, S.IsPaid, S.CreateDate
FROM
StatementsEntries S
WHERE
S.IsCredit = 0
UNION ALL
SELECT P.Id, S.Name, P.Date, P.IsCredit, P.Amount, P.CaseId, P.IsAdjustment, S.StatementId, P.Balance, P.IsPaid, P.CreateDate
FROM
StatementsEntries P
CROSS APPLY (
SELECT TOP (1) S.StatementId, S.Name
FROM StatementsEntries S
WHERE
S.CaseId = P.CaseId
AND S.IsCredit = 0
AND S.Id < P.Id
ORDER
BY S.Id DESC
) S
WHERE
IsCredit = 1
ORDER BY StatementId, Id
Produces the following result:
╔════╦════════════════╦════════════╦══════════╦════════╦════════╦══════════════╦═════════════╦═════════╦════════╦════════════╗
║ Id ║ Name ║ Date ║ IsCredit ║ Amount ║ CaseId ║ IsAdjustment ║ StatementId ║ Balance ║ IsPaid ║ CreateDate ║
╠════╬════════════════╬════════════╬══════════╬════════╬════════╬══════════════╬═════════════╬═════════╬════════╬════════════╣
║ 1 ║ Statement 4444 ║ 2007-04-01 ║ 0 ║ 850 ║ 1 ║ 0 ║ 4444 ║ 850 ║ 1 ║ 2007-04-01 ║
║ 2 ║ Statement 4444 ║ 2007-04-02 ║ 1 ║ 850 ║ 1 ║ 0 ║ 4444 ║ 0 ║ 0 ║ 2007-04-02 ║
║ 3 ║ Statement 5555 ║ 2008-08-01 ║ 0 ║ 1003 ║ 1 ║ 0 ║ 5555 ║ 1003 ║ 0 ║ 2008-08-01 ║
║ 8 ║ Statement 5555 ║ 2009-04-02 ║ 1 ║ 1002 ║ 1 ║ 0 ║ 5555 ║ 648 ║ 0 ║ 2009-04-02 ║
║ 4 ║ Statement 7777 ║ 2008-09-01 ║ 0 ║ 3000 ║ 2 ║ 0 ║ 7777 ║ 3000 ║ 0 ║ 2008-09-01 ║
║ 5 ║ Statement 8883 ║ 2008-10-01 ║ 0 ║ 1500 ║ 5 ║ 0 ║ 8883 ║ 1500 ║ 1 ║ 2008-10-01 ║
║ 6 ║ Statement 8883 ║ 2008-10-02 ║ 1 ║ 1500 ║ 5 ║ 0 ║ 8883 ║ 0 ║ 0 ║ 2008-10-02 ║
║ 7 ║ Statement 9992 ║ 2008-11-01 ║ 0 ║ 1750 ║ 7 ║ 0 ║ 9992 ║ 1750 ║ 0 ║ 2008-11-01 ║
║ 9 ║ Statement 9994 ║ 2010-08-01 ║ 0 ║ 1650 ║ 1 ║ 0 ║ 9994 ║ 1650 ║ 1 ║ 2010-08-01 ║
║ 10 ║ Statement 9994 ║ 2011-09-02 ║ 1 ║ 10 ║ 1 ║ 0 ║ 9994 ║ 638 ║ 0 ║ 2011-09-02 ║
╚════╩════════════════╩════════════╩══════════╩════════╩════════╩══════════════╩═════════════╩═════════╩════════╩════════════╝
Upvotes: 0
Felix Pamittan
Reputation: 31879
Sample Data:
Id Name Date IsCredit Amount CaseId IsAdjustment StatementId Balance IsPaid CreateDate
----------- --------------- ---------- -------- ----------- ----------- ------------ ----------- ----------- ------ ----------
1 Statement 4444 2007-04-01 0 850 1 0 4444 850 1 2007-04-01
2 Payment made 2007-04-02 1 850 1 0 0 0 0 2007-04-02
3 Statement 5555 2008-08-01 0 1003 1 0 5555 1003 0 2008-08-01
4 Statement 7777 2008-09-01 0 3000 2 0 7777 3000 0 2008-09-01
5 Statement 8883 2008-10-01 0 1500 5 0 8883 1500 1 2008-10-01
6 Payment made 2008-10-02 1 1500 5 0 0 0 0 2008-10-02
7 Statement 9992 2008-11-01 0 1750 7 0 9992 1750 0 2008-11-01
8 Payment made 2009-04-02 1 1002 1 0 0 648 0 2009-04-02
9 Statement 9994 2010-08-01 0 1650 1 0 9994 1650 1 2010-08-01
10 Payment made 2011-09-02 1 10 1 0 0 638 0 2011-09-02
SOLUTION
SELECT
s.id,
[Statement ID] = CASE WHEN s.StatementID > 0 THEN s.StatementID ELSE x.StatementID END,
Name = CASE WHEN s.StatementID > 0 THEN s.Name ELSE x.Name END,
s.Amount,
s.Balance,
s.IsPaid
FROM StatementsEntries s
OUTER APPLY(
SELECT TOP 1 *
FROM StatementsEntries
WHERE
CaseID = s.CaseID
AND StatementID > 0
AND ID < s.Id
ORDER BY ID Desc
)x
ORDER BY [Statement ID], s.Id
RESULT
id Statement ID Name Amount Balance IsPaid
----------- ------------ --------------- ----------- ----------- ------
1 4444 Statement 4444 850 850 1
2 4444 Statement 4444 850 0 0
3 5555 Statement 5555 1003 1003 0
8 5555 Statement 5555 1002 648 0
4 7777 Statement 7777 3000 3000 0
5 8883 Statement 8883 1500 1500 1
6 8883 Statement 8883 1500 0 0
7 9992 Statement 9992 1750 1750 0
9 9994 Statement 9994 1650 1650 1
10 9994 Statement 9994 10 638 0
Upvotes: 2
shree.pat18
Reputation: 21757
Try this:
select distinct src.caseid,
src.name,
max(src.statementid) over (partition by src.caseid order by src.id) statementid,
src.amount,
src.balance,
src.paid ispaid
from tbl src
left join tbl tgt on tgt.caseid = src.caseid and tgt.statementid = 0
order by caseid, src.balance desc
Note: This will not work with SQL Server version below SQL Server 2005.
Upvotes: 1
Pரதீப்
Reputation: 93704
Use Cross Apply
SELECT Dense_rank()OVER(ORDER BY cs.name) StatementID,
cs.name,
amount,
Balance,
paid
FROM Yourtable a
CROSS apply (SELECT TOP 1 name
FROM Yourtable b
WHERE a.CaseId = b.CaseId
ORDER BY id) cs
Upvotes: 1
Related Questions
- Returning total rows for one column
- SQL query to Group By and Aggregate results
- TSQL query with aggregated data
- T-SQL query - aggregation
- Combined aggregates into a single SQL table
- Aggregation in same row
- aggregate values in SQL columns
- Summarize aggregated data
- Aggregation over multiple rows SQL Server 2008
- a SQL aggregation query