-1

I want to create a procedure in which the following details should be displayed:- I have created this query but I am not getting the right results. I have attached the table schema.

TABLES WE ARE USING:- InvoiceData, CustomerDetails and InvoiceItems

Table Schema ->
1. InvoiceItems TABLE1
2. CustomerDetails Table2
3. InvoiceDetails enter image description here

There are 2 sections for invoice:-

In the first section of the Invoice, Below details should be displayed.

Invoice Information section

In the second section of the invoice, the below details should be displayed:-

Invoice Items description section

I am attaching the query below:-

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
DarkRob
  • 3,843
  • 1
  • 10
  • 27
S.Sehgal
  • 59
  • 2
  • 10
  • what is your input and expected output. Please post them in text format. – DarkRob Jul 18 '19 at 07:19
  • CustomerName, InvoiceNumber, InvoiceDate, Email, Mobile, Bill DATE, Bill Month, Due Date-- these are the fields should be populated in the Invoice with all their values. @DarkRob – S.Sehgal Jul 18 '19 at 07:57
  • check your given code again, are you sure this is all. And we don't know about your table schema for `InvoiceData` and `CustomerDetails`, also what do you have already in these table? Please see some other example to post your question. – DarkRob Jul 18 '19 at 09:05
  • @DarkRob I have attached the table structure and what I need in the invoice. Also, I have rewritten the stored procedure which I have mentioned as well. – S.Sehgal Jul 19 '19 at 05:10
  • please help me guys with this issue. – S.Sehgal Jul 22 '19 at 04:39
  • so you want all of this record in single table return or returning of multiple table is also accepted. post your sample result you are getting and expected result. Since input is not available we are not getting your issue. – DarkRob Jul 22 '19 at 05:05
  • yes, I want all of the records in a single table which is InvoiceDetails, and all the invoice number should be uniquely generated like this... Let's say for Gurgaon Region. it should be like this .. "GG_UniqueNumber" @DarkRob – S.Sehgal Jul 22 '19 at 05:11

1 Answers1

0

So I am expecting that you got all the records in your #tempInvoice from your procedure. And face problem in only generating invoice number with the format you provided.

For each region you already have a specific code generated at your end ( I am guessing).

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

If you don't have any table for region then prepare one before going forward as this needs you everywhere in your code section.

At the end pf your procedure need to update this to return your required result.

    ; 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
DarkRob
  • 3,843
  • 1
  • 10
  • 27