You could use Table-Valued Parameters to pass a set of InvoiceItems to a procedure that will insert a new Invoice and also insert the InvoiceItems to the InvoiceItems table.
Without knowing your table schema, I am just going to throw in a few columns.
Example user-defined table type for use as a table-valued parameter:
create type dbo.InvoiceItems_udt as table (
ProductId int
, Quantity decimal(9,2)
, LineTotal decimal(19,2)
);
go
Example procedure using scope_identity() to get the InvoiceId after inserting a row into dbo.Invoice:
create procedure dbo.Invoice_Insert_WithItems (
@InvoiceId int output
, @CustomerId int not null
, @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
set nocount on, xact_abort on;
begin tran;
insert into dbo.Invoice (CustomerId, InvoiceTotal)
select @CustomerId, InvoiceTotal = sum(LineTotal)
from @InvoiceItems i;
select @InvoiceId = convert(int,scope_identity());
insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
select @InvoiceId, ProductId, Quantity, LineTotal
from @InvoiceItems i;
commit tran;
end;
go
Table-valued parameter reference:
In SQL Server 2012+, an alternative to using an auto-numbered identity() for InvoiceId, you could use a sequence.
Example of how to create and use a sequence as the primary key on dbo.Invoice:
create sequence dbo.InvoiceIdSequence as int
start with 1
increment by 1;
create table dbo.Invoice (
InvoiceId int not null default next value for dbo.InvoiceIdSequence
, CustomerId int not null
, InvoiceTotal decimal(19,2)
, constraint pk_Invoice primary key clustered (InvoiceId)
, constraint fk_Invoice_Customer foreign key (CustomerId)
references dbo.Customer(CustomerId)
);
In this example, instead of using scope_identity() after inserting a row into dbo.Invoice,
we would use next value for dbo.InvoiceIdSequence before inserting a row into dbo.Invoice.
create procedure dbo.Invoice_Insert_WithItems (
@InvoiceId int output
, @CustomerId int not null
, @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
set nocount on, xact_abort on;
begin tran;
set @InvoiceId = next value for dbo.InvoiceIdSequence;
insert into dbo.Invoice (CustomerId, InvoiceId, InvoiceTotal)
select @CustomerId, @InvoiceId, InvoiceTotal = sum(LineTotal)
from @InvoiceItems i;
insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
select @InvoiceId, ProductId, Quantity, LineTotal
from @InvoiceItems i;
commit tran;
end;
go
Sequence reference:
Best Answer
You can use a window function such as
ROW_NUMBER()inside of a CTE to generate a unique integer series partitioned by eachParent_entryand ordered by thetimedescending and then use that dataset to filter out the latest records where thenotes = 'check'like so:Note if your ParentTable has rows that don't exist in the ChildTable, and you want to keep those rows in the final results, then you need to use an outer join instead of an inner, and will need to do an
ISNULL()onS.[time]and replace withPT.[time]. If all ParentTable rows have at least one correlating ChildTable row, then the above example is fine.