February 22, 2019 at 2:13 am
Hi all,
I'm have a SQL agent job (running in live business situation) and it's doing the following:
-read data from source DB (and some additional lookup) and keep it in a view (see code)
This has all been working perfectly for years and here the code:
Code : (view)
ALTER VIEW [dbo].[StagingForCardManagement] AS
select SFCM.*,dbo.CostPL.Description,dbo.CostPL.Template
FROM(
SELECT
3 as Actie,
CAST(t1.pers_nr as VARCHAR(32)) + CAST(t2.dv_vlgnr as VARCHAR(32)) COLLATE Latin1_General_CI_AS as persoonsnummer,
CAST(t1.e_roepnaam as VARCHAR(40)) COLLATE Latin1_General_CI_AS as roepnaam,
CAST(
CASE WHEN t1.vrvg_samen is not null THEN t1.vrvg_samen + ' ' +
(
CASE WHEN (CHARINDEX('-', t1.naam_samen) > 0 ) THEN LEFT(naam_samen, CHARINDEX('-', naam_samen) - 1)
ELSE t1.naam_samen END)
ELSE
(
CASE WHEN (CHARINDEX('-', t1.naam_samen) > 0 ) THEN LEFT(naam_samen, CHARINDEX('-', naam_samen) - 1)
ELSE t1.naam_samen END
)
END
as VARCHAR(40)) COLLATE Latin1_General_CI_AS AS achternaam,
CAST(t3.func_oms as VARCHAR(50)) COLLATE Latin1_General_CI_AS as functieomschrijving,
CAST(t4.oe_vol_nm as VARCHAR(40)) COLLATE Latin1_General_CI_AS as afdeling,
t5.opdrgvr_oms COLLATE Latin1_General_CI_AS as opdrachtgever,
CAST(t4.kstpl_kd as INT) as kostenplaats,
CAST(t4.oe_kort_nm as varchar(18)) COLLAte Latin1_General_CI_AS as OE_naam,
CAST(
CASE WHEN LEN(t4.kstpl_kd) = 6 THEN t4.kstpl_kd
ELSE(
CASE WHEN LEN(t4.kstpl_kd) = 5 AND LEFT(t4.oe_kort_nm,2) = 'SO' THEN convert(int,'9'+ CONVERT(VARCHAR(10),t4.kstpl_kd))
ELSE convert(int,'8'+ CONVERT(VARCHAR(10),t4.kstpl_kd))
END)
END
as VARCHAR(10)) COLLATE Latin1_General_CI_AS AS nw_kostplaats,
CAST(t2.indnst_dt as datetime) as indienst,
CAST(t2.uitdnst_dt as datetime) as uitdienst,
CAST(t2.arelsrt_kd as VARCHAR(10)) COLLATE Latin1_General_CI_AS as ARELSRT_OMS
From Beaufort.dbo.DPIB010 t1
left outer join Beaufort.dbo.DPIC300 t2 on t1.pers_nr = t2.pers_nr
join Beaufort.dbo.DPIC351 t3 on t2.primfunc_kd = t3.func_kd
join Beaufort.dbo.DPIB015 t4 on t2.oe_hier_sl = t4.dpib015_sl
join beaufort.dbo.DPIC200 t5 on t2.opdrgvr_nr = t5.OPDRGVR_NR
WHERE
t2.uitdnst_dt is null AND t5.OPDRGVR_NR <> '23929' AND t5.OPDRGVR_NR <> '23843'
OR
t2.uitdnst_dt >= DATEADD(month, -1, GETDATE()) AND t5.OPDRGVR_NR <> '23929' AND t5.OPDRGVR_NR <> '23843'
) SFCM
LEFT JOIN dbo.CostPL on SFCM.nw_kostplaats between CostPL.Val_lo AND costpl.Val_hi
Now here's what I need to accomplish:
There's another table in the source DB (Beaufort.dbo.DPIC310) which holds all alteration per pers_nr. So if pers_nr 999999 has been altered 5 times it holds 5 recs with data including datetime of alteration.
Now I need to add a column to the view which contains for every row (= pers_nr) the latest datetime from that DPIC310 table. That's DPIC310.ingang_dt.
Here's how this table (DPIC310) could look like:
| pers_nr | dv_vlgnr | oe_oper_sl | operfunc_kd | ingang_dt | u_version | pslot_sel |
| 999999 | 1 | 1052 | 80206 | 2017-04-01 00:00:00.000 | ; | NULL |
| 999999 | 1 | 1052 | 80206 | 2016-12-01 00:00:00.000 | q | NULL |
| 999999 | 1 | 1052 | 80206 | 2016-05-01 00:00:00.000 | 3 | NULL |
| 999999 | 1 | 1052 | 10001 | 2019-03-01 00:00:00.000 | # | NULL |
| 999999 | 1 | 1052 | 10001 | 2019-02-01 00:00:00.000 | ' | NULL |
| 999999 | 1 | 510 | 20001 | 2019-03-15 00:00:00.000 | ! | NULL |
So in short: I need to add a column to the view which contains at pers_nr 999999 the latest datetime from this table (= 2019-03-15 00:00:00.000)
I did try myself but I'm afraid this is a bit beyond me. When I add a join on this table it add all the recs from this table to the view (which I can understand). But I tried to narrow down the select with MAX(ingang_dt) but it throws me red lines.
Anyone willing to help me with this? Can this be done? Or maybe I need to create another view with just the data I need from this last table and then later on join them?
Thanks in advance!
February 22, 2019 at 6:16 am
You might wish to disambiguate your WHERE clause before pursuing this further:
WHERE
t2.uitdnst_dt is null
AND t5.OPDRGVR_NR <> '23929'
AND t5.OPDRGVR_NR <> '23843'
OR t2.uitdnst_dt >= DATEADD(month, -1, GETDATE())
AND t5.OPDRGVR_NR <> '23929'
AND t5.OPDRGVR_NR <> '23843'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 22, 2019 at 9:17 am
You just need to add the table in a CROSS APPLY or OUTER APPLY and select TOP(1) with an ORDER BY date DESC...
From Beaufort.dbo.DPIB010 t1
CROSS APPLY(SELECT TOP(1) * FROM Beaufort.dbo.DPIC310 x WHERE x.pers_nr = t1.pers_nr ORDER BY x.Date DESC) x
left outer join Beaufort.dbo.DPIC300 t2 on t1.pers_nr = t2.pers_nr
...
February 25, 2019 at 12:19 pm
Thank you very much Jonathan!
I added the line and fieldname and no error whatsoever. So view shows data, but no additional column is showing. The query result (view) is the same as before. Since there a 'SELECT' it should, shouldn't it?
February 25, 2019 at 1:49 pm
Super_Grover - Monday, February 25, 2019 12:19 PMThank you very much Jonathan!
I added the line and fieldname and no error whatsoever. So view shows data, but no additional column is showing. The query result (view) is the same as before. Since there a 'SELECT' it should, shouldn't it?
I'm not sure what you mean.
The columns in the cross apply have an alias of x. So in the sub-query you need to select a column from x CAST(t2.arelsrt_kd AS VARCHAR(10)) COLLATE Latin1_General_CI_AS AS ARELSRT_OMS,
x.col1
FROM Beaufort.dbo.DPIB010 t1
CROSS APPLY(SELECT TOP(1) * FROM Beaufort.dbo.DPIC310 x WHERE x.pers_nr = t1.pers_nr ORDER BY x.Date DESC) x
left outer join Beaufort.dbo.DPIC300 t2 on t1.pers_nr = t2.pers_nr
...
February 27, 2019 at 7:06 am
Jonathan AC Roberts - Monday, February 25, 2019 1:49 PMI'm not sure what you mean.
The columns in the cross apply have an alias of x. So in the sub-query you need to select a column from xCAST(t2.arelsrt_kd AS VARCHAR(10)) COLLATE Latin1_General_CI_AS AS ARELSRT_OMS,
x.col1
FROM Beaufort.dbo.DPIB010 t1
CROSS APPLY(SELECT TOP(1) * FROM Beaufort.dbo.DPIC310 x WHERE x.pers_nr = t1.pers_nr ORDER BY x.Date DESC) x
left outer join Beaufort.dbo.DPIC300 t2 on t1.pers_nr = t2.pers_nr
...
Sorry mate, you're right! I made mistake. I now have it implemented as follows and works great:
CAST(t2.arelsrt_kd as VARCHAR(10)) COLLATE Latin1_General_CI_AS as ARELSRT_OMS,
x.ingang_dt as MutDat
From Beaufort.dbo.DPIB010 t1
CROSS APPLY(SELECT TOP(1) * FROM Beaufort.dbo.DPIC310 x WHERE x.pers_nr = t1.pers_nr ORDER BY x.ingang_dt DESC) x
left outer join Beaufort.dbo.DPIC300 t2 on t1.pers_nr = t2.pers_nr
join Beaufort.dbo.DPIC351 t3 on t2.primfunc_kd = t3.func_kd
Thanks very much for your help! i honestly hadn't heard of the CROSS APPLY command before.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply