Entering Data into a Field Based on Other Records
-
Hello:
At the end of this posting is T-SQL code. Above, I show a screenshot displaying its Results. The only issue that I'm having is on the "Year" field.
Without "hard-coding" the data into that field, I need for that field to display the same year as that of the data contained in records 14 - 18.
As you can see from records 19 - 26, there are indeed going to be times when the five fields on the far right will display 0. That's why "Year" shows NULL for records 19 - 26.
Again, when there is indeed data that is not 0 (especially in the "Ending Balance" field), I need for the same "Year" to display and to do so, for all records.
How do I modify the code to do this?
Thank you!
John
select
Accts.ACTINDX
, gl3.ACTNUMST as [Account]
, gl2.ACTDESCR as [Account Description]
, gl.YEAR1 as [Year]
, Period.PeriodNo
, case when Period.[PeriodNo] = 0 then 0 ELSE
ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0)
END as [Opening Balance]
, ISNULL(gl.DEBITAMT,0) as Debit
, ISNULL(gl.CRDTAMNT,0) as Credit
, ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change]
, ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance]
FROM (select distinct ACTINDX from SNG..GL00105) as Accts
CROSS JOIN (
select 0 as PeriodNo union all
select 1 as PeriodNo union all
select 2 as PeriodNo union all
select 3 as PeriodNo union all
select 4 as PeriodNo union all
select 5 as PeriodNo union all
select 6 as PeriodNo union all
select 7 as PeriodNo union all
select 8 as PeriodNo union all
select 9 as PeriodNo union all
select 10 as PeriodNo union all
select 11 as PeriodNo union all
select 12 as PeriodNo
) as Period
LEFT OUTER JOIN SNG..GL10110 as gl on Accts.ACTINDX = gl.ACTINDX
and Period.PeriodNo = gl.PERIODID
LEFT OUTER JOIN SNG..GL10110 as PREV
ON gl.ACTINDX = PREV.ACTINDX
AND gl.YEAR1 = PREV.YEAR1
AND gl.PERIODID >= PREV.PERIODID
INNER JOIN SNG..GL00100 AS gl2 on Accts.ACTINDX = gl2.ACTINDX
INNER JOIN SNG..GL00105 AS gl3 on gl2.ACTINDX = gl3.ACTINDX
GROUP BY gl.PERIODID, gl.ACTINDX, gl3.ACTNUMST, gl2.ACTDESCR, gl.DEBITAMT,
gl.CRDTAMNT, gl.YEAR1, gl.PERDBLNC, Accts.ACTINDX, Period.PeriodNo
order by
Accts.ACTINDX
, Period.PeriodNo -
Try the following
, COALESCE(gl.YEAR1, MAX(gl.YEAR1) OVER(PARTITION BY Accts.ACTINDX ORDER BY Period.PeriodNo ROWS UNBOUNDED PRECEDING) as [Year]Because you have a GROUP BY, you may need to place this in a CTE before the final GROUP BY. You may also need to adjust the PARTITION BY and/or ORDER BY clauses to get the correct values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thanks, for the quick response!
Unfortunately, I'm getting errors upon running this.
Is any other syntax that I can use, especially without a CTE? I don't understand why it's so hard for T-SQL to place 2017 in the fields where there are NULL values. That boggles my mind.
John - John Roy Ellis - Tuesday, February 7, 2017 2:44 PM
What was the error message?
Also, we ask that you provide sample data (no PII) and expected results (see the link in my signature) as executable code (not pictures). The allows us to TEST our code before posting it.
What do you have against CTEs? You could use other constructs, but there is no clear advantage to doing so in this particular case.
It's "hard", because SQL Server is based on set theory, which deals with UNORDERED SETS. What you want requires ORDERED SETS, so it requires special handling to transform the unordered set into an ordered set.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply