创建存储过程中的发票创建

Question

我想创建一个过程，其中应显示以下详细信息：-我已经创建了这个查询，但我没有得到正确的结果。我已经附加了表架构。

我们使用的表格：- InvoiceData、CustomerDetails和InvoiceItems

表架构 ->
1. InvoiceItems表1
2. 表2
CustomerDetails3. InvoiceDetails在此处输入图像描述

发票有2个部分：-

在发票的第一部分中，应显示以下详细信息。

发票信息部分

在发票的第二部分，应显示以下详细信息：-

发票项目描述部分

我附上以下查询：-

alter Procedure SaveInvoiceDetails
(
@CustomerId varchar(50),
@InvoiceNumber varchar(50),
@InvoiceDate date,
@InvoiceMonth int,
@FromDate date,
@ToDate date,
@Rate int,
@Quantity int,
@ActualAmount int,
@ZoneId int
)
as
set nocount on;
begin
Declare @TotalRows int
Declare @NumPages int
set @TotalRows = 0


Select ROW_NUMBER() over (order by C.CustomerId) as InvoiceRow,
C.CustomerId, I.InvoiceNumber, I.InvoiceDate, I.FromDate, I.ToDate, 
I.InvoiceMonth, I.Rate, I.ActualAmount, I.Quantity, C.ZoneId, 
C.BillingAmount
into #tempInvoice
from ConsumerMST_LKO C
inner join InvoiceDetails I
on C.CustomerId = I.CustomerId
inner join INVOICEITEMS II
on I.InvoiceNumber = II.INVOICEID
where InvoiceNumber = @InvoiceNumber AND InvoiceDate = @InvoiceDate AND 
InvoiceMonth = @InvoiceMonth
AND FromDate =@FromDate AND ToDate = @ToDate AND ActualAmount = 
@ActualAmount
set @TotalRows = @@ROWCOUNT
If @TotalRows = 0
Begin
set @TotalRows = @TotalRows + 1
Insert #tempInvoice
(
InvoiceNumber,
InvoiceDate,
InvoiceMonth,
ZoneId,
Rate,
Quantity,
BillingAmount,
FromDate,
ToDate
)
VALUES 
(@TotalRows
,   ''
,''
,''
,0
,0
,0
,0
,''
,0)

End
End
SELECT * FROM #tempInvoice ORDER BY InvoiceRow asc
return

Answer 1

所以我期待你从你的过程中得到#tempInvoice中的所有记录。并且仅使用您提供的格式生成发票编号时会遇到问题。

对于每个区域，您已经生成了特定的代码(我猜(。

#regioncode  ---- this table contain record of zoneid and zoneocde for each area

如果您没有任何区域表，请在继续之前准备一个表，因为这需要您在代码部分的任何地方。

最后，您的过程需要更新此内容以返回所需的结果。

    ; with cte as (
    select row_number() over (partition by ZoneId order by InvoiceDate) as Slno, * from #tempInvoice as t  inner join #regioncode as r on t.zoneid=r.zoneid
    )
    select zonecode + '_' + cast(slno as varchar(10)) as Uniquecode, * from cte