[Microsoft SQL] Ik loop vast bij het halen van einddatum - Softwareontwikkeling

Vraag

woensdag 6 december 2023 19:18

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

Beste Tweakers,

Helaas moet ik jullie weer een vraagstellen omdat ik vastloop om een query (VIEW) goed te krijgen. De situatie is als volgt. Ik heb 2 tabellen (persons en employments). In persons staan alle medewerkers en in employments staan de personeelsnummers met de contracten die zij hebben.

Nu ben ik bezig met het schrijven van een VIEW, deze VIEW moet de juiste data laten zien, dit omdat de tool die dit in gaat lezen niet de mogelijkheid heeft om nog te filteren/loopen etc op de data.

In de VIEW moet staan:

person_id
person_code
is_manager
manager_person_id
gender
initials
first_name
last_name
last_name_prefix
phone_business
phone_mobile
email_business
hours_per_week
company
department
job_title
StartDate
EndDate

Nu krijg ik netjes de juiste data terug op de EndDate na. Het is namelijk zo dat een medewerker meer dan 1 contract kan hebben. Maar ook kan het zo zijn dat een medewerker uit dienst gaat maar een x aantal dagen weer opnieuw in dienst komt.

Wat moet het resultaat zijn:

Als een medewerker een contract heeft die de volgende dag ingaat, dan moet de EndDatum verlengt worden met de Einddatum ( date_employed_to) van dat contract
Als er langer dan 1 dag tussen zit maar de medewerker is nog uitdienst, dan moet de toekomstige contract genegeerd worden
Als date_employed_to een null weergeeft dan heeft dat contract geen einddatum en dan moet DateEnd dus ook null zijn.

Nu een aantal screenshots:

Tabel persons:

Afbeeldingslocatie: https://tweakers.net/i/VVsJnLYdHWrWMP0iacEbJuO-fyI=/800x/filters:strip_exif()/f/image/Nppt7qwgjDYKym1UmsNyISPu.png?f=fotoalbum_large

De Contracten

Afbeeldingslocatie: https://tweakers.net/i/S0AZF3HHzNRjsjJru08IVKsYrrw=/800x/filters:strip_exif()/f/image/XahnbGVAA68SbDWM9R0UH2Tx.png?f=fotoalbum_large

Resultaat van de nu Query

Afbeeldingslocatie: https://tweakers.net/i/CEjzMsPdQu7vxnX9s8OsRNpkH8Q=/800x/filters:strip_exif()/f/image/9Z7wQefAe2osvJkj0Hz13Iu0.png?f=fotoalbum_large

Zoals je ziet bij de Query screenshot, krijg ik de einddatum mee van het contract met de einddatum die het het verst in de toekomst ligt. Dit terwijl het eigenlijk NULL moet zijn (in hoe ik het nodig heb)

Ik heb het script samen gemaakt met een collega maar ook hij komt hier niet uit.

De query:

SQL:

WITH employments_cte AS (

/* Haalt de contract data op (employments) en controleert of het contract al gestart is, in de toekomst ligt of dat er een volgend contract is (geschreven door collega).*/
    Select e.person_id, max(e.date_employed_to) as date_employed_to, min(e.date_employed_from) as date_employed_from
    From employments e
    Where (e.date_employed_from <= convert(date, getdate()) and e.date_employed_to > convert(date, getdate())) OR e.date_employed_to IS NULL OR e.date_employed_from = convert(date, DATEADD(day, 1,  getdate()))
    Group By e.person_id

    UNION ALL

    Select e.person_id, e.date_employed_to as date_employed_to, e.date_employed_from
    From employments e
        inner join employments_cte ec ON (e.person_id = ec.person_id)
    Where (e.date_employed_from <= ec.date_employed_to and e.date_employed_to > ec.date_employed_to
    )


), employments_cte_dates AS (

/* Bundelt alle contracten van een medewerker bij elkaar */
    Select c.person_id, min(c.date_employed_from) As StartDate,  max(c.date_employed_to) as EndDate
    From employments_cte AS c 
    Group by c.person_id
), 
hoofdfunctie as (
/* Haalt de functie op waarbij je de meeste uren werkt en maakt daar RowID 1 van.*/

    SELECT b.person_id AS personeelsnummer, b.employment_id, b.date_employed_from, b.date_employed_to,
    b.hours_per_week, b.working_days, b.company_id, b.company, b.department_id, b.department, b.cost_center_id, b.cost_center, b.job_title_id, b.job_title, b.manager_person_id, b.date_last_working_day,
    ROW_NUMBER() OVER (PARTITION BY b.person_id
    ORDER BY b.hours_per_week DESC) AS RowID
    FROM dbo.employments AS b 
    WHERE (b.date_employed_to IS NULL) OR (b.date_employed_to >= GETDATE())
) 


/* Toont het resultaat en pakt RowID=1 (hoofdcontract).*/

Select E.person_id,person_code,is_manager,manager_person_id,gender,initials,first_name,last_name,last_name_prefix, phone_business,phone_mobile,email_business, hours_per_week,company, department, job_title, StartDate, EndDate FROM employments_cte_dates AS E
    INNER JOIN dbo.persons AS P ON P.person_id = E.person_id 
    INNER JOIN hoofdfunctie AS HF ON HF.personeelsnummer = E.person_id 
WHERE
((e.EndDate IS NULL) OR (e.EndDate >= GETDATE())) AND hf.RowID = 1 AND e.person_id = 1

Mogelijk kan de SQL code korter en netter, maar mijn kennis van SQL is niet heel goed. Het liefst had ik hiervoor een php middleware script geschreven, aangezien ik dan de data via loops had kunnen ophalen.

SQL tabellen

code:

CREATE TABLE [dbo].[persons](
    [person_id] [int] NOT NULL,
    [person_code] [varchar](255) NULL,
    [initials] [varchar](255) NULL,
    [first_name] [varchar](255) NULL,
    [last_name] [varchar](255) NULL,
    [last_name_prefix] [varchar](255) NULL,
    [gender] [char](1) NULL,
    [is_manager] [bit] NOT NULL,
    [phone_business] [varchar](255) NULL,
    [phone_mobile] [varchar](255) NULL,
    [email_business] [varchar](255) NULL,
 CONSTRAINT [PK_persons] PRIMARY KEY CLUSTERED 
(
    [person_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

code:

CREATE TABLE [dbo].[employments](
    [person_id] [int] NOT NULL,
    [employment_id] [int] NOT NULL,
    [date_employed_from] [date] NULL,
    [date_employed_to] [date] NULL,
    [hours_per_week] [numeric](5, 2) NOT NULL,
    [working_days] [varchar](255) NULL,
    [company_id] [int] NOT NULL,
    [company] [varchar](255) NULL,
    [department_id] [int] NOT NULL,
    [department] [varchar](255) NULL,
    [cost_center_id] [varchar](7) NULL,
    [cost_center] [varchar](255) NULL,
    [job_title_id] [varchar](10) NULL,
    [job_title] [varchar](255) NULL,
    [manager_person_id] [int] NULL,
    [date_last_working_day] [date] NULL,
 CONSTRAINT [PK_employments] PRIMARY KEY CLUSTERED 
(
    [person_id] ASC,
    [employment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

code:

CREATE VIEW [dbo].[vw_medewerkers]
AS
WITH employments_cte AS (

/* Haalt de contract data op (employments) en controleert of het contract al gestart is, in de toekomst ligt of dat er een volgend contract is (geschreven door collega).*/
    Select e.person_id, max(e.date_employed_to) as date_employed_to, min(e.date_employed_from) as date_employed_from
    From employments e
    Where (e.date_employed_from <= convert(date, getdate()) and e.date_employed_to > convert(date, getdate())) OR e.date_employed_to IS NULL OR e.date_employed_from = convert(date, DATEADD(day, 1,  getdate()))
    Group By e.person_id

    UNION ALL

    Select e.person_id, e.date_employed_to as date_employed_to, e.date_employed_from
    From employments e
        inner join employments_cte ec ON (e.person_id = ec.person_id)
    Where (e.date_employed_from <= ec.date_employed_to and e.date_employed_to > ec.date_employed_to
    )


), employments_cte_dates AS (

/* Bundelt alle contracten van een medewerker bij elkaar */
    Select c.person_id, min(c.date_employed_from) As StartDate,  max(c.date_employed_to) as EndDate
    From employments_cte AS c 
    Group by c.person_id
), 
hoofdfunctie as (
/* Haalt de functie op waarbij je de meeste uren werkt en maakt daar RowID 1 van.*/

    SELECT b.person_id AS personeelsnummer, b.employment_id, b.date_employed_from, b.date_employed_to,
    b.hours_per_week, b.working_days, b.company_id, b.company, b.department_id, b.department, b.cost_center_id, b.cost_center, b.job_title_id, b.job_title, b.manager_person_id, b.date_last_working_day,
    ROW_NUMBER() OVER (PARTITION BY b.person_id
    ORDER BY b.hours_per_week DESC) AS RowID
    FROM dbo.employments AS b 
    WHERE (b.date_employed_to IS NULL) OR (b.date_employed_to >= GETDATE())
) 


/* Toont het resultaat en pakt RowID=1 (hoofdcontract).*/

Select E.person_id,person_code,is_manager,manager_person_id,gender,initials,first_name,last_name,last_name_prefix, phone_business,phone_mobile,email_business, hours_per_week,company, department, job_title, StartDate, EndDate FROM employments_cte_dates AS E
    INNER JOIN dbo.persons AS P ON P.person_id = E.person_id 
    INNER JOIN hoofdfunctie AS HF ON HF.personeelsnummer = E.person_id 
WHERE
((e.EndDate IS NULL) OR (e.EndDate >= GETDATE())) AND hf.RowID = 1 AND e.person_id = 1 
  

GO

code:

GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (1, 1, CAST(N'2023-12-01' AS Date), NULL, CAST(10.00 AS Numeric(5, 2)), N'100000', 1, N'prive', 1, N'ICT', N'123', N'test', N'2', N'TESTER', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (1, 2, CAST(N'2023-12-07' AS Date), NULL, CAST(10.00 AS Numeric(5, 2)), N'011110', 1, N'prive', 1, N'FD', N'123', N'FD', N'2', N'Huismeester', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (1, 3, CAST(N'2023-12-07' AS Date), CAST(N'2024-05-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'ICT', N'2', N'Applicatiebeheer', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (1, 4, CAST(N'2021-01-07' AS Date), CAST(N'2024-05-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'ICT', N'2', N'TESTER', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (1, 5, CAST(N'2024-01-07' AS Date), CAST(N'2024-05-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'ICT', N'2', N'TESTER', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (2, 1, CAST(N'2021-01-07' AS Date), CAST(N'2021-05-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'ICT', N'2', N'TESTER', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (2, 2, CAST(N'2024-01-01' AS Date), NULL, CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'FB', N'2', N'Receptie', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (3, 1, CAST(N'2023-01-01' AS Date), CAST(N'2023-05-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'FB', N'2', N'Receptie', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (3, 2, CAST(N'2023-05-01' AS Date), CAST(N'2023-12-06' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'FB', N'2', N'Receptie 2', 1, NULL)
GO
INSERT [dbo].[employments] ([person_id], [employment_id], [date_employed_from], [date_employed_to], [hours_per_week], [working_days], [company_id], [company], [department_id], [department], [cost_center_id], [cost_center], [job_title_id], [job_title], [manager_person_id], [date_last_working_day]) VALUES (3, 3, CAST(N'2023-12-10' AS Date), CAST(N'2024-12-01' AS Date), CAST(10.00 AS Numeric(5, 2)), N'000001', 1, N'prive', 1, N'AB', N'123', N'FB', N'2', N'Receptie 3', 1, NULL)
GO
INSERT [dbo].[persons] ([person_id], [person_code], [initials], [first_name], [last_name], [last_name_prefix], [gender], [is_manager], [phone_business], [phone_mobile], [email_business]) VALUES (1, N'ABC', N'J', N'Jo', N'VE', NULL, N'M', 0, NULL, NULL, NULL)
GO
INSERT [dbo].[persons] ([person_id], [person_code], [initials], [first_name], [last_name], [last_name_prefix], [gender], [is_manager], [phone_business], [phone_mobile], [email_business]) VALUES (2, N'ABC', N'J', N'Dum', N'MY', NULL, N'M', 0, NULL, NULL, NULL)
GO
INSERT [dbo].[persons] ([person_id], [person_code], [initials], [first_name], [last_name], [last_name_prefix], [gender], [is_manager], [phone_business], [phone_mobile], [email_business]) VALUES (3, N'ABC', N'J', N'Oud', N'MDW', NULL, N'M', 0, NULL, NULL, NULL)
GO

[ Voor 65% gewijzigd door Jboy1991 op 06-12-2023 21:52 ]

Alle reacties

woensdag 6 december 2023 20:02

Acties:

0 Henk 'm!

D-dark

Jboy1991 schreef op woensdag 6 december 2023 @ 19:18:

Wat moet het resultaat zijn:
[list]
• Als een medewerker een contract heeft die de volgende dag ingaat, dan moet de EndDatum verlengt worden met de Einddatum ( date_employed_to) van dat contract
• Als er langer dan 1 dag tussen zit maar de medewerker is nog uitdienst, dan moet de toekomstige contract genegeerd worden
• Als date_employed_to een null weergeeft dan heeft dat contract geen einddatum en dan moet DateEnd dus ook null zijn.

Volgens mij zijn dat 3 selecties en je hebt er nu maar 2

WHERE (b.date_employed_to IS NULL and b.date_employed_from <= Today+1)
or
WHERE (b.date_employed_to IS NULL and b.date_employed_from > Today+1)
or
WHERE (b.date_employed_to IS NULL and b.date_employed_from <= Today)

Uit de losse pols.
Meest simpele is eerst een grote of beperkte export te maken
Daarvoor per testgeval een medewerker te zoeken
Met dat testgeval je selectie in orde maken
Dan de selecties combineren.

woensdag 6 december 2023 20:11

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

D-dark schreef op woensdag 6 december 2023 @ 20:02:
[...]

Volgens mij zijn dat 3 selecties en je hebt er nu maar 2

WHERE (b.date_employed_to IS NULL and b.date_employed_from <= Today+1)
or
WHERE (b.date_employed_to IS NULL and b.date_employed_from > Today+1)
or
WHERE (b.date_employed_to IS NULL and b.date_employed_from <= Today)

Uit de losse pols.
Meest simpele is eerst een grote of beperkte export te maken
Daarvoor per testgeval een medewerker te zoeken
Met dat testgeval je selectie in orde maken
Dan de selecties combineren.
toon volledige bericht

Ik snap wat je zegt. Het probleem is alleen dat Null niet mee wordt geteld met MAX(). Ik heb zelf dus geen idee hoe ik ervoor kan zorgen dat wanneer er ook maar 1 contract date_employed_from op null heeft staan, hij dit ook terug weergeeft als EndDate

woensdag 6 december 2023 20:17

Acties:

0 Henk 'm!

D-dark

Jboy1991 schreef op woensdag 6 december 2023 @ 20:11:
[...]

Ik snap wat je zegt. Het probleem is alleen dat Null niet mee wordt geteld met MAX(). Ik heb zelf dus geen idee hoe ik ervoor kan zorgen dat wanneer er ook maar 1 contract date_employed_from op null heeft staan, hij dit ook terug weergeeft als EndDate

Wat je kan proberen is omgekeerd redeneren

1 een actief contract heeft een startdatum <= Today () +1
2 een actief contract heeft een lege einddatum of einddatum >= Today ()

Where (
((b.date_employed_from <= Today () +1))
and
((b.date_employed_to IS NULL or b.date_employed_to =< Today ()
)

woensdag 6 december 2023 20:18

Acties:

0 Henk 'm!

Reinier

\o/

Die NULLs kun je in je max-stukje omzetten met een fake toekomstige datum (2222-02-02 ofzo).

woensdag 6 december 2023 20:23

Acties:

0 Henk 'm!

D-dark

Reinier schreef op woensdag 6 december 2023 @ 20:18:
Die NULLs kun je in je max-stukje omzetten met een fake toekomstige datum (2222-02-02 ofzo).

ook een optie
dan krijg je zoiets als

eerste hit op google
https://www.c-sharpcorner...place-null-in-sql-server/

CASE

If the column value is null, that value will be replaced with the "replacementValue".

WHEN ColunName IS NULL THEN 'replacementValue'
ELSE

If the column value is not null, then it will print the following column value:

ColumnName

To end the case, use "end".

[ Voor 10% gewijzigd door D-dark op 06-12-2023 20:24 ]

woensdag 6 december 2023 20:32

Acties:

0 Henk 'm!

Reinier

\o/

Ik zat aan isnull te denken ja

woensdag 6 december 2023 20:39

Acties:

0 Henk 'm!

CurlyMo

@Jboy1991 Voor nu en eventuele toekomstige vragen helpt het als je voorbeeld tabellen en data beschikbaar stelt die we allemaal kunnen implementeren met een voorbeeld van de gewenste uitkomst. Nu moet ik dat of allemaal uit mijn hoofd beredeneren of de voorbeeld data overtypen. Dat nodigt niet echt uit.

[ Voor 3% gewijzigd door CurlyMo op 06-12-2023 20:41 ]

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 20:41

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

CurlyMo schreef op woensdag 6 december 2023 @ 20:39:
@Jboy1991 Voor nu en eventuele toekomstige vragen helpt het als je voorbeeld tabellen en data beschikbaar stelt die we allemaal kunnen implementeren met een voorbeeld van de gewenste uitkomst. Nu moet ik dat of allemaal uit mijn hoofd beredeneren of overtypen. Dat nodigt niet echt uit.

Dat wil ik ook erg graag doen. Maar ik zou niet weten hoe.

woensdag 6 december 2023 20:42

Acties:

0 Henk 'm!

CurlyMo

Gewoon hier create table en bijpassende insert into queries plaatsen. Net zoals je je selectie hier hebt geplaatst.

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 20:51

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

CurlyMo schreef op woensdag 6 december 2023 @ 20:42:
Gewoon hier create table en bijpassende insert into queries plaatsen. Net zoals je je selectie hier hebt geplaatst.

zie beginpost. Staat erbij. Thx

woensdag 6 december 2023 20:53

Acties:

0 Henk 'm!

CurlyMo

Jboy1991 schreef op woensdag 6 december 2023 @ 20:51:
[...]

zie beginpost. Staat erbij. Thx

Dank, nu de dummy data nog?

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 21:00

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

CurlyMo schreef op woensdag 6 december 2023 @ 20:53:
[...]

Dank, nu de dummy data nog?

Daar was ik al mee bezig, moest nog een optie aanzetten die ik niet vonden kon. Zie post.

woensdag 6 december 2023 21:18

Acties:

+1 Henk 'm!

CurlyMo

@Jboy1991 Ik kan het niet reproduceren in PostgreSQL. Mijn vertaling van je query. Kan zijn dat ik iets niet goed heb vertaald:

SQL:

WITH RECURSIVE employments_cte AS (
    SELECT
        e.person_id,
        MAX(e.date_employed_to) AS date_employed_to,
        MIN(e.date_employed_from) AS date_employed_from
    FROM
        employments e
    WHERE
        (
            e.date_employed_from <= NOW()
        AND
            e.date_employed_to > NOW()
        )
    OR
        e.date_employed_to IS NULL
    OR
        e.date_employed_from = NOW() + '1 day'
    GROUP BY
        e.person_id
    UNION ALL
    SELECT
        e.person_id,
        e.date_employed_to AS date_employed_to,
        e.date_employed_from
    FROM
        employments e
    INNER JOIN
        employments_cte ec
    ON
        e.person_id = ec.person_id
    WHERE
        (
            e.date_employed_from <= ec.date_employed_to
        AND
            e.date_employed_to > ec.date_employed_to
        )
),
employments_cte_dates AS (
    SELECT
        c.person_id,
        MIN(c.date_employed_from) AS StartDate,
        MAX(c.date_employed_to) AS EndDate
    FROM
        employments_cte AS c
    GROUP BY
        c.person_id
),
hoofdfunctie AS (
    SELECT
        b.person_id AS personeelsnummer,
        b.employment_id,
        b.date_employed_from,
        b.date_employed_to,
        b.hours_per_week,
        b.working_days,
        b.company_id,
        b.company,
        b.department_id,
        b.department,
        b.cost_center_id,
        b.cost_center,
        b.job_title_id,
        b.job_title,
        b.manager_person_id,
        b.date_last_working_day,
        ROW_NUMBER() OVER (PARTITION BY b.person_id ORDER BY b.hours_per_week DESC) AS RowID
    FROM
        employments AS b 
    WHERE
        b.date_employed_to IS NULL
    OR
        b.date_employed_to >= NOW()
) 
SELECT
    e.person_id,
    person_code,
    is_manager,
    manager_person_id,
    gender,
    initials,
    first_name,
    last_name,
    last_name_prefix,
    phone_business,
    phone_mobile,
    email_business,
    hours_per_week,
    company,
    department,
    job_title,
    StartDate,
    EndDate
FROM
    employments_cte_dates AS e
INNER JOIN
    persons AS p
ON
    p.person_id = e.person_id 
INNER JOIN
    hoofdfunctie AS hf
ON
    hf.personeelsnummer = e.person_id 
WHERE
    (
        e.enddate IS NULL
    OR
        e.enddate >= NOW()
    )
AND
    hf.rowid = 1
AND
    e.person_id = 1

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 21:40

Acties:

0 Henk 'm!

CurlyMo

Het is ook niet zo handig dat de dummy data niet volledig is voor de cases die je beschrijft. Even stoeien om daar cases voor te verzinnen.

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 21:47

Acties:

0 Henk 'm!

CurlyMo

Ik snap de logica niet van employment_id rij 2 en 3. Waarom hebben die rijen dezelfde date_employed_from?

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 21:50

Acties:

0 Henk 'm!

CurlyMo

Dit stukje levert in je huidige data namelijk geen unieke voorspelbaar resultaat op, maar is gebaseerd op toeval:

SQL:

1	ROW_NUMBER() OVER (PARTITION BY b.person_id ORDER BY b.hours_per_week DESC) AS RowID

[ Voor 21% gewijzigd door CurlyMo op 06-12-2023 21:51 ]

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 21:57

Acties:

0 Henk 'm!

Jboy1991

Topicstarter

CurlyMo schreef op woensdag 6 december 2023 @ 21:18:
@Jboy1991 Ik kan het niet reproduceren in PostgreSQL. Mijn vertaling van je query. Kan zijn dat ik iets niet goed heb vertaald:

SQL:

WITH RECURSIVE employments_cte AS (
    SELECT
        e.person_id,
        MAX(e.date_employed_to) AS date_employed_to,
        MIN(e.date_employed_from) AS date_employed_from
    FROM
        employments e
    WHERE
        (
            e.date_employed_from <= NOW()
        AND
            e.date_employed_to > NOW()
        )
    OR
        e.date_employed_to IS NULL
    OR
        e.date_employed_from = NOW() + '1 day'
    GROUP BY
        e.person_id
    UNION ALL
    SELECT
        e.person_id,
        e.date_employed_to AS date_employed_to,
        e.date_employed_from
    FROM
        employments e
    INNER JOIN
        employments_cte ec
    ON
        e.person_id = ec.person_id
    WHERE
        (
            e.date_employed_from <= ec.date_employed_to
        AND
            e.date_employed_to > ec.date_employed_to
        )
),
employments_cte_dates AS (
    SELECT
        c.person_id,
        MIN(c.date_employed_from) AS StartDate,
        MAX(c.date_employed_to) AS EndDate
    FROM
        employments_cte AS c
    GROUP BY
        c.person_id
),
hoofdfunctie AS (
    SELECT
        b.person_id AS personeelsnummer,
        b.employment_id,
        b.date_employed_from,
        b.date_employed_to,
        b.hours_per_week,
        b.working_days,
        b.company_id,
        b.company,
        b.department_id,
        b.department,
        b.cost_center_id,
        b.cost_center,
        b.job_title_id,
        b.job_title,
        b.manager_person_id,
        b.date_last_working_day,
        ROW_NUMBER() OVER (PARTITION BY b.person_id ORDER BY b.hours_per_week DESC) AS RowID
    FROM
        employments AS b 
    WHERE
        b.date_employed_to IS NULL
    OR
        b.date_employed_to >= NOW()
) 
SELECT
    e.person_id,
    person_code,
    is_manager,
    manager_person_id,
    gender,
    initials,
    first_name,
    last_name,
    last_name_prefix,
    phone_business,
    phone_mobile,
    email_business,
    hours_per_week,
    company,
    department,
    job_title,
    StartDate,
    EndDate
FROM
    employments_cte_dates AS e
INNER JOIN
    persons AS p
ON
    p.person_id = e.person_id 
INNER JOIN
    hoofdfunctie AS hf
ON
    hf.personeelsnummer = e.person_id 
WHERE
    (
        e.enddate IS NULL
    OR
        e.enddate >= NOW()
    )
AND
    hf.rowid = 1
AND
    e.person_id = 1

toon volledige bericht

Ik was even hoopvol het leek te werken. Totdat ik onder person 1 een nieuw contract hing met een einddatum. Helaas pakte hij die einddatum over in je query.

code:

WITH  employments_cte AS (
    SELECT
        e.person_id,
        MAX(e.date_employed_to) AS date_employed_to,
        MIN(e.date_employed_from) AS date_employed_from
    FROM
        employments e
    WHERE
        (
            e.date_employed_from <= GETDATE()
        AND
            e.date_employed_to > GETDATE()
        )
    OR
        e.date_employed_to IS NULL
    OR
        e.date_employed_from = GETDATE() + 1
    GROUP BY
        e.person_id
    UNION ALL
    SELECT
        e.person_id,
        e.date_employed_to AS date_employed_to,
        e.date_employed_from
    FROM
        employments e
    INNER JOIN
        employments_cte ec
    ON
        e.person_id = ec.person_id
    WHERE
        (
            e.date_employed_from <= ec.date_employed_to
        AND
            e.date_employed_to > ec.date_employed_to
        )
),
employments_cte_dates AS (
    SELECT
        c.person_id,
        MIN(c.date_employed_from) AS StartDate,
        MAX(c.date_employed_to) AS EndDate
    FROM
        employments_cte AS c
    GROUP BY
        c.person_id
),
hoofdfunctie AS (
    SELECT
        b.person_id AS personeelsnummer,
        b.employment_id,
        b.date_employed_from,
        b.date_employed_to,
        b.hours_per_week,
        b.working_days,
        b.company_id,
        b.company,
        b.department_id,
        b.department,
        b.cost_center_id,
        b.cost_center,
        b.job_title_id,
        b.job_title,
        b.manager_person_id,
        b.date_last_working_day,
        ROW_NUMBER() OVER (PARTITION BY b.person_id ORDER BY b.hours_per_week DESC) AS RowID
    FROM
        employments AS b 
    WHERE
        b.date_employed_to IS NULL
    OR
        b.date_employed_to >= GETDATE()
) 
SELECT
    e.person_id,
    person_code,
    is_manager,
    manager_person_id,
    gender,
    initials,
    first_name,
    last_name,
    last_name_prefix,
    phone_business,
    phone_mobile,
    email_business,
    hours_per_week,
    company,
    department,
    job_title,
    StartDate,
    EndDate
FROM
    employments_cte_dates AS e
INNER JOIN
    persons AS p
ON
    p.person_id = e.person_id 
INNER JOIN
    hoofdfunctie AS hf
ON
    hf.personeelsnummer = e.person_id 
WHERE
    (
        e.enddate IS NULL
    OR
        e.enddate >= GETDATE()
    )
AND
    hf.rowid = 1

CurlyMo schreef op woensdag 6 december 2023 @ 21:40:
Het is ook niet zo handig dat de dummy data niet volledig is voor de cases die je beschrijft. Even stoeien om daar cases voor te verzinnen.

Ik heb even nieuwe data toegevoegd met meerdere personen en meerdere contracten.

CurlyMo schreef op woensdag 6 december 2023 @ 21:47:
Ik snap de logica niet van employment_id rij 2 en 3. Waarom hebben die rijen dezelfde date_employed_from?

Je kunt meerdere contracten tegelijk hebben op dezelfde afdeling en functie maar er zit dan een ander rechtenprofiel achter (dit is data die dan weer bij SA bekend is). Normaal zou het zijn dat het aantal uren dan anders moet zijn.

employment_id is dan de unieke key die meegegeven wordt aan het contract.

CurlyMo schreef op woensdag 6 december 2023 @ 21:50:
Dit stukje levert in je huidige data namelijk geen unieke voorspelbaar resultaat op, maar is gebaseerd op toeval:
SQL:
1
    ROW_NUMBER() OVER (PARTITION BY b.person_id ORDER BY b.hours_per_week DESC) AS RowID

Daar was ik al bang voor. Mogelijk dat ik dan maar employment_id ga gebruiken als ORDER BY. Welk contract als eerste komt en actief is, is je hoofdcontract. Moet morgen even binnen de organisatie kijken hoe zij dit voor zich zagen. De een geeft aan dat inzet uren belangrijk is, de ander het id. Mogelijk eerst kijken naar de uren en daarna naar het ID.

woensdag 6 december 2023 22:19

Acties:

0 Henk 'm!

CurlyMo

Als ik me eerst even focus op je eerste criterium, klopt dit dan als uitkomst voor persoon 1?

SQL:

select
    *
from
    (select
        row_number() over (partition by a.person_id order by b.date_employed_from = (now() + '1 day')::date desc, employment_id) as criteriumA,
        b.employment_id,
        a.person_id,
        b.date_employed_from AS startdate,
        b.date_employed_to AS enddate
    from
        persons as a
    inner join
        employments as b
    on
        a.person_id = b.person_id
    ) as a
where
    criteriumA = 1;

code:

1 2	criteriumA employment_id person_id startdate enddate 1 2 1 2023-12-07 NULL

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 22:35

Acties:

0 Henk 'm!

CurlyMo

Jboy1991 schreef op woensdag 6 december 2023 @ 21:57:
[...]
Daar was ik al bang voor. Mogelijk dat ik dan maar employment_id ga gebruiken als ORDER BY. Welk contract als eerste komt en actief is, is je hoofdcontract. Moet morgen even binnen de organisatie kijken hoe zij dit voor zich zagen. De een geeft aan dat inzet uren belangrijk is, de ander het id. Mogelijk eerst kijken naar de uren en daarna naar het ID.

Hoe dan ook, je huidige resultaat is onbetrouwbaar en daarmee onbruikbaar. Er moet een uniek(e) (compositie van) gegeven(s) zijn om de volgordelijkheid te bepalen. Als de datums dat niet zijn, dan moet er iets anders zijn. Inzet uren lijkt me op logisch niveau daar niet de beste kandidaat voor.

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 6 december 2023 22:38

Acties:

0 Henk 'm!

sig69

Wat je volgens mij zoekt is een contractreeks. Bij ons is binnen 6 maanden opnieuw in dienst (herintreder) zelfde dienstverband, nieuwe contractreeks. Is gewoon een volgnummer. Dit zou je met jouw data structuur wel kunnen simuleren adhv de contract start- en einddatum en de LAG of LEAD functie.

Roomba E5 te koop

woensdag 6 december 2023 23:05

Acties:

0 Henk 'm!

D-dark

Jboy1991 schreef op woensdag 6 december 2023 @ 21:57:

Je kunt meerdere contracten tegelijk hebben op dezelfde afdeling en functie maar er zit dan een ander rechtenprofiel achter (dit is data die dan weer bij SA bekend is). Normaal zou het zijn dat het aantal uren dan anders moet zijn.

employment_id is dan de unieke key die meegegeven wordt aan het contract.

Daar was ik al bang voor. Mogelijk dat ik dan maar employment_id ga gebruiken als ORDER BY. Welk contract als eerste komt en actief is, is je hoofdcontract. Moet morgen even binnen de organisatie kijken hoe zij dit voor zich zagen. De een geeft aan dat inzet uren belangrijk is, de ander het id. Mogelijk eerst kijken naar de uren en daarna naar het ID.

Puur voor de zekerheid.
De contracten hebben wel allemaal verschillende begin en einddata en overlappen elkaar niet?

Anders moet er bepaald worden wat voorrang heeft.
1 Geen einddatum -> vervangen door fictieve hoogste einddatum in de database
2 einddatum in de toekomst, de hoogste daarvan
3 einddatum in het verleden -> uit de resultaten weglaten

Wat je ook kan doen is voor alle contracten met een einddatum verleden een extra berekende waarde laten toevoegen als actief / inactief
Die kan je dan gemakkelijk uitfilteren en niet in je queries meenemen.
Maakt de totale hoeveelheid data alwat kleiner

[ Voor 10% gewijzigd door D-dark op 06-12-2023 23:09 ]

Pagina: 1

Reageer