SQL Server Hoe te invoegen wanneer niet bestaat?

Ik heb twee tabellen, de ene wordt Facturen genoemd en de andere wordt Records genoemd.

CREATE TABLE Invoices
(
    InvoiceNum INT NOT NULL,
    Amount DECIMAL,
    RecordPK UNIQUEIDENTIFIER NOT NULL
)

CREATE TABLE Records(
    RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    StartNum INT NOT NULL,
    NextNum INT NOT NULL,
    MaxNum INT NOT NULL,
    InvPrefix VARCHAR(2) NOT NULL
)

In de tabel met records worden het startnummer van de factuur vastgelegd, het aantal facturen dat we hebben aangemaakt (NextNum) en het aantal facturen dat we kunnen maken (MaxNum).

Neem bijvoorbeeld aan dat we verschillende records in twee tabellen hebben.

Factuurtabel:

InvoiceNum    Amount    RecordPk
1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9

Records tabel:

RecordPk                                StartNum    NextNum    MaxNum    Prefix
EDFA0541-5583-4CDD-BDFF-21D6F6504522    1           4          10        AA
D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9    10          13         14        AA

Mijn vraag is wanneer ik de factuurtabel doorzoek met Prefix AA, hoe kan ik het resultaat krijgen zoals hieronder, het InvoiceNum zou het MaxNum moeten bereiken, het Bedrag en RecordPK van niet bestaande rijen zouden leeg moeten blijven, de Opmerking kolom zou moeten vullen met Blank.

InvoiceNum    Amount    RecordPk                                Remark
1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
4                                                               Blank
5                                                               Blank
6                                                               Blank
7                                                               Blank
8                                                               Blank
9                                                               Blank
10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
13                                                              Blank
14                                                              Blank
0

5 antwoord

Je hebt een LEFT JOIN nodig

SELECT I.*,
       CASE WHEN I.InvoiceNum IS NULL THEN 'Blank' END Remark
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) RC (InvoiceNum)
LEFT JOIN Invoices I
     ON RC.InvoiceNum = I.InvoiceNum;

The value 1 is the MIN InvoiceNum and 12 is the MAX InvoiceNum

Demo

0
toegevoegd

U moet een tabel met getallen genereren om het bereik van nummers te dekken dat u nodig hebt (voor elke rij in de tabel Records , van StartNum tot MaxNum ). U kunt dit bijvoorbeeld doen door een bestaande tabel met voldoende rijen te selecteren en ROW_NUMBER vensterfunctie. Filter vervolgens deze reeks om alleen de nummers op te nemen die u nodig hebt. Sluit u aan bij de tabel Facturen om de gegevens voor de bijbehorende factuur weer te geven en gebruik IIF functie om te controleren is er een factuur met dit nummer of niet.

declare @Invoices table(InvoiceNum INT NOT NULL, Amount DECIMAL, RecordPK UNIQUEIDENTIFIER NOT NULL)
declare @Records table(RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, StartNum INT NOT NULL, NextNum INT NOT NULL, MaxNum INT NOT NULL, InvPrefix VARCHAR(2) NOT NULL)

insert into @Invoices(InvoiceNum, Amount, RecordPk) values
(1 ,            19.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(2 ,            50.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(3 ,            3.00 ,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(10,            1.00 ,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(11,            99.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(12,            13.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9')

insert into @Records(RecordPk, StartNum, NextNum, MaxNum, InvPrefix) values
('EDFA0541-5583-4CDD-BDFF-21D6F6504522',    1 ,          4 ,         10,        'AA'),
('D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9',    10,          13,         14,        'AA')

;with numbers as (select ROW_NUMBER() over(order by object_id) as No from sys.objects)
select
    n.No as InvoiceNum
    , inv.Amount
    , inv.RecordPK
    , IIF(inv.InvoiceNum is null, 'Blank', null) as Remark
from numbers n
left join @Invoices inv on n.No = inv.InvoiceNum
where exists(select * from @Records r where r.StartNum <= n.No and n.No <= r.MaxNum)
0
toegevoegd

U moet een tabel met getallen genereren om het bereik van nummers te dekken dat u nodig hebt (voor elke rij in de tabel Records , van StartNum tot MaxNum ). U kunt dit bijvoorbeeld doen door een bestaande tabel met voldoende rijen te selecteren en ROW_NUMBER vensterfunctie. Filter vervolgens deze reeks om alleen de nummers op te nemen die u nodig hebt. Sluit u aan bij de tabel Facturen om de gegevens voor de bijbehorende factuur weer te geven en gebruik IIF functie om te controleren is er een factuur met dit nummer of niet.

declare @Invoices table(InvoiceNum INT NOT NULL, Amount DECIMAL, RecordPK UNIQUEIDENTIFIER NOT NULL)
declare @Records table(RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, StartNum INT NOT NULL, NextNum INT NOT NULL, MaxNum INT NOT NULL, InvPrefix VARCHAR(2) NOT NULL)

insert into @Invoices(InvoiceNum, Amount, RecordPk) values
(1 ,            19.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(2 ,            50.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(3 ,            3.00 ,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(10,            1.00 ,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(11,            99.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(12,            13.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9')

insert into @Records(RecordPk, StartNum, NextNum, MaxNum, InvPrefix) values
('EDFA0541-5583-4CDD-BDFF-21D6F6504522',    1 ,          4 ,         10,        'AA'),
('D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9',    10,          13,         14,        'AA')

;with numbers as (select ROW_NUMBER() over(order by object_id) as No from sys.objects)
select
    n.No as InvoiceNum
    , inv.Amount
    , inv.RecordPK
    , IIF(inv.InvoiceNum is null, 'Blank', null) as Remark
from numbers n
left join @Invoices inv on n.No = inv.InvoiceNum
where exists(select * from @Records r where r.StartNum <= n.No and n.No <= r.MaxNum)
0
toegevoegd

@Andrey Nikolov heeft het geregeld, maar ik heb hier de afgelopen 15 minuten aan gewerkt, dus ik dacht dat ik het toch zou plaatsen.

In essentie moet een intermediaire tabel worden gebruikt om de waarden die u niet hebt op te tellen. In mijn versie van dit antwoord heb ik een uniequery gebruikt om de waarde 'Leeg' te genereren. Ik heb de unieke ID niet voor beknoptheid opgenomen, maar de toepassing is hetzelfde.

if OBJECT_ID('tempdb..#invoice') is not null drop table #invoice;
if OBJECT_ID('tempdb..#rowcount') is not null drop table #rowcount;

create table #invoice
    (
        invoicenum int,
        amount decimal
    );


insert into #invoice (invoicenum, amount)
values
(1, 19.00),
(2, 50.00),
(3, 3.00),
(10, 1.00),
(11, 99.00),
(12, 13.00);


create table #rowcount

    (
        rownumber int 
    );

declare @max int = 1;

select @max=count(*) from #invoice;

declare @runs int = 1;

while @runs<[email protected]
begin
insert into #rowcount (rownumber)
values (@runs);
select @[email protected]+1;
end


select invoicenum, cast(amount as nvarchar(25)) as amount from #invoice
union
select rownumber, 'BLANK' from #rowcount r left join #invoice i on 
r.rownumber=i.invoicenum where i.invoicenum is null
order by invoicenum;

drop table #invoice, #rowcount;
0
toegevoegd

@Andrey Nikolov heeft het geregeld, maar ik heb hier de afgelopen 15 minuten aan gewerkt, dus ik dacht dat ik het toch zou plaatsen.

In essentie moet een intermediaire tabel worden gebruikt om de waarden die u niet hebt op te tellen. In mijn versie van dit antwoord heb ik een uniequery gebruikt om de waarde 'Leeg' te genereren. Ik heb de unieke ID niet voor beknoptheid opgenomen, maar de toepassing is hetzelfde.

if OBJECT_ID('tempdb..#invoice') is not null drop table #invoice;
if OBJECT_ID('tempdb..#rowcount') is not null drop table #rowcount;

create table #invoice
    (
        invoicenum int,
        amount decimal
    );


insert into #invoice (invoicenum, amount)
values
(1, 19.00),
(2, 50.00),
(3, 3.00),
(10, 1.00),
(11, 99.00),
(12, 13.00);


create table #rowcount

    (
        rownumber int 
    );

declare @max int = 1;

select @max=count(*) from #invoice;

declare @runs int = 1;

while @runs<[email protected]
begin
insert into #rowcount (rownumber)
values (@runs);
select @[email protected]+1;
end


select invoicenum, cast(amount as nvarchar(25)) as amount from #invoice
union
select rownumber, 'BLANK' from #rowcount r left join #invoice i on 
r.rownumber=i.invoicenum where i.invoicenum is null
order by invoicenum;

drop table #invoice, #rowcount;
0
toegevoegd