Database

WITH cteOrderedData As
(
    SELECT
        Nr, 
        Ort,
        ROW_NUMBER() OVER (PARTITION BY Left(Nr, 8), Ort ORDER BY Nr) As RN
    FROM
        Fotos
    WHERE
        Type = 'D'
)
SELECT
    Nr,
    Ort
FROM
    cteOrderedData
WHERE
    RN = 1
;

SELECT TOP (100) PERCENT
   Nbr,
   Ort
FROM
   Fotos
WHERE
   Type = 'D'
ORDER BY
   SUBSTRING(Nbr, 1, 8) DESC,
   Ort

| Nbr          | Ort |
|--------------|-----|
| 20220717-001 | A   |
| 20220717-002 | B   |
| 20220717-003 | C   |

#realJSOP wrote:

Currently, all VehImage columns are null (intentionally).
...

SQL

FOR VehImage IN (...)

WITH cte As
(
    SELECT
        UserId,
        VehId,
        VehImage,
        ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
    FROM
        @images
)
SELECT
    UserId,
    VehId,
    [1] As Image1,
    [2] As Image2,
    [3] As Image3,
    [4] As Image4,
    [5] As Image5,
    [6] As Image6,
    [7] As Image7,
    [8] As Image8,
    [9] As Image9,
    [10] As Image10
FROM
    cte As SourceTable
    PIVOT
    (
        Max(VehImage)
        FOR RN IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
    ) As PivotTable
;

WITH cte As
(
    SELECT
        UserId,
        VehId,
        VehImage,
        ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
    FROM
        @images
)
SELECT
    UserId,
    VehId,
    MAX(CASE RN WHEN 1 THEN VehImage END) As Image1,
    MAX(CASE RN WHEN 2 THEN VehImage END) As Image2,
    MAX(CASE RN WHEN 3 THEN VehImage END) As Image3,
    MAX(CASE RN WHEN 4 THEN VehImage END) As Image4,
    MAX(CASE RN WHEN 5 THEN VehImage END) As Image5,
    MAX(CASE RN WHEN 6 THEN VehImage END) As Image6,
    MAX(CASE RN WHEN 7 THEN VehImage END) As Image7,
    MAX(CASE RN WHEN 8 THEN VehImage END) As Image8,
    MAX(CASE RN WHEN 9 THEN VehImage END) As Image9,
    MAX(CASE RN WHEN 10 THEN VehImage END) As Image10
FROM
    cte As SourceTable
GROUP BY
    UserId,
    VehId
;

Mycroft Holmes wrote:

the younger generation like to promote

declare @demo table (startdate date, enddate date)
insert into @demo (startdate, enddate) values
('2022-03-01', '2022-04-10'),
('2021-12-01', '2022-03-11'),
('2022-01-01', '2022-04-05');

declare @1stDay date = '2022-04-01';
declare @LastDay date = '2022-04-30';

select startdate, enddate

	,case	when startdate < @1stDay then @1stDay 
			when startdate > @lastDay then @LastDay
			else startdate end as AmendedStartDate
	,case	when enddate < @1stDay then @1stDay
			when enddate > @LastDay then @LastDay
			else enddate end as AmendedEndDate

from @demo;