Hi,
This is my table and data
CREATE TABLE [dbo].[rpt_AR_StatementOfAcct_100](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Batch_Id] [uniqueidentifier] NULL,
[company_code] [smallint] NULL,
[ac_no] [varchar](10) NULL,
[ac_name] [varchar](200) NULL,
[address1] [varchar](200) NULL,
[address2] [varchar](200) NULL,
[postal_code] [varchar](50) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[level1_code_descrp] [varchar](100) NULL,
[AsAt] [datetime] NULL,
[Crop_Descrp] [varchar](50) NULL,
[trans_date] [datetime] NULL,
[trans_type_Descrp] [varchar](100) NULL,
[ref1] [varchar](50) NULL,
[oth_ref] [varchar](50) NULL,
[ref2] [varchar](50) NULL,
[DrAmt] [decimal](16, 2) NULL,
[CrAmt] [decimal](16, 2) NULL,
[param1] [varchar](200) NULL,
[param2] [varchar](200) NULL,
[param3] [varchar](200) NULL,
[param4] [decimal](16, 2) NULL,
[param5] [decimal](16, 2) NULL,
[param6] [decimal](16, 2) NULL,
[param7] [datetime] NULL,
[param8] [datetime] NULL,
[param9] [datetime] NULL,
[UserId] [varchar](100) NULL,
CONSTRAINT [PK_rpt_AR_StatementOfAcct_100] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[rpt_AR_StatementOfAcct_100] ON
GO
I need help to produce rows like this,
id | ref1 | oth_ref | ref2 | DrAmt | CrAmt | thisBalance
------------------------------------------------------------------------------------------------------------------
1OB-RKP-TT1OB-RKP-TT1E/T01/FFB/001/05/93 0.00 321.37 -321.37
2OB-RKP-TT7OB-RKP-TT7E/T08/FFB/001/05/93 11354.58 0.00 11033.21
3EF20930537n/aE/T01/FFB/001/05/93 21.37 0.00 11054.58
400000263PHBM231971E/T08/FFB/001/05/93 0.00 2428.91 8625.67
500000262PHBM231971E/T08/FFB/001/05/93 0.00 1820.66 6805.01
..
...
....
.....
Please assume like this,
DrAmt + CrAmt * (-1)
select 0.00 + (-321.37) = -321.37
select 11354.58 + (-321.37) = 11033.21
select 21.37 + (11033.21) = 11054.58
select -2428.91 + (11054.58) = 8625.67
select -1820.66 + (8625.67) = 6805.01
Please help. I'm stuck
October 1, 2023 at 12:53 pm
It appears the math in your example calculation is off (by 300.00) beginning on line 3: instead of 21.37 should it not be 321.37?
select 21.37 + (11033.21) = 11054.58
Should be this?
select 321.37 + (11033.21) = 11354.58
If so it appears you're looking for the "running total" or SUM OVER (in SQL Server) of the DrAmt + CrAmt * (-1) calculation. Based on your example this query uses the 'id' column to define the sort ordering of the running total. Generally it's not a good practice to order by an id column defined by an IDENTITY property because its usefulness might be compromised by row updates and/or deletes
select id, ref1, oth_ref, ref2, DrAmt, CrAmt, DrAmt+CrAmt*-1 CalcAmt,
sum(DrAmt+CrAmt*-1) over (order by id) SumOverCalcAmt
from #rpt_AR_StatementOfAcct_100
order by id;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 1, 2023 at 7:21 pm
Based on your example this query uses the 'id' column to define the sort ordering of the running total. Generally it's not a good practice to order by an id column defined by an IDENTITY property because its usefulness might be compromised by row updates and/or deletes
I agree... don't use IDENTITY for this . There IS a [trans_date] [datetime] NULL, column in the table, although why they made it nullable is beyond me and should be repaired ASAP.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2023 at 9:37 pm
Everything you posted is horrible and needs to be redone. IDENTITY is a table property and cannot be used as a key. You have more NULLs in this one table than I have ever done in entire systems for major corporations. Can you give me an example of a state abbreviation that is 50 variable characters long? Why have you failed to follow any of the ISO 11179 standards for data element names? That’s just Some examples of how badly designed this non-table is. Why are none of your variable character columns fixed length? Did you know the standard for a postal address line in the US is 35 characters? A ZIP Code is only five digits long, and nobody on earth has a postal code that is 50 characters long except you. The columns named param1 through parm9 look like a repeated group. It’s pretty obvious that this mess is not normalized. Why did you invent a numeric company code, instead of using the DUNS as is required by law in many places?
You could have saved yourself all that typing if you had bothered to correctly do an INSERT INTO statement. Your type conversions are also redundant, but they can also screw you if anything changes.
I realize it was very popular during the Renaissance, but in modern times we have access to positive and negative numbers. Keeping two separate columns for debits and credits is not the modern accounting practice in computer systems.
Based on my expertise, you start over and fire the person who gave you this mess? It’s going to be difficult to maintain, full of errors, and will never pass any kind of audit. Whoever did this obviously had never read so much as one book on RDBMS, nor did they know how to write SQL. They should not be employed because they are dangerous to their employer.
Please post DDL and follow ANSI/ISO standards when asking for help.
When posting sample data to the web, you do not need to post EVERY SINGLE FIELD IN YOUR TABLE. More importantly, you could be in trouble for posting PII (Personal Identifiable Information) on the wide open web.
Why are you overly complicating the math for your calculations? DrAmt + CrAmt * (-1) is exactly the same as DrAmt - CrAmt.
It looks like you want a running total.
SELECT *, SUM(r.DrAmt - r.CrAmt) OVER(PARTITION BY r.ac_no ORDER BY r.trans_date, r.Id ROWS UNBOUNDED PRECEDING) AS running_total
FROM #rpt_AR_StatementOfAcct_100 AS r
ORDER BY r.ac_no, r.trans_date, r.Id
While the ROWS UNBOUNDED PRECEDING isn't necessary, it's a good idea to include it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2023 at 3:11 pm
This was removed by the editor as SPAM
October 3, 2023 at 7:33 pm
Thanks for all reply. Really appreciate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply