CODEWITHSUNDEEP
sqlsql-serverstored-procedures
andrefadila
andrefadila

Reputation: 687

Create invoice number in sql server stored procedure

I have a data like this : 

idOrder |  transactionDate    
   31   | 04/06/2014 7:58:38  
   32   | 05/06/2014 8:00:08  
   33   | 05/06/2014 14:23:51

I want build stored procedure that produce invoice number automatically, e.g. :

In 4 June, there are 1 invoice number : 201406041 (20140604 from date and 1 at last from total order happened that day).

So, in 5 June, there are 2 invoice number : 201406051 and 201406052

I am lack on SQL scripting except this query to count transaction per day:

SELECT 
   count(idOrder) as total 
FROM 
   TR_Order 
GROUP BY 
   Convert(char(8), transactionDate, 111);

Thanks in advance.

Upvotes: 0

Views: 2610

Answers (3)

knkarthick24
knkarthick24

Reputation: 3216

Try this 

select * from test  

select 
    idorder, transactiondate,  
    REPLACE(CAST(transactionDate as DATE),'-','') + 
            CAST(ROW_NUMBER() OVER(PARTITION BY CAST(transactionDate AS DATE) 
                                   ORDER BY idorder) AS varchar(8)) AS InvoiceNumber  
from test

enter image description here

Upvotes: 1

user3709675
user3709675

Reputation: 1

Hoping this may help you to generate invoice number by using system-date.

CREATE PROCEDURE INVOICE(IN INPUT VARCHAR(255) CHARSET utf8)

RETURNS VARCHAR(255) CHARSET utf8

DETERMINISTIC

BEGIN

DECLARE TG VARCHAR(255);

DECLARE TH VARCHAR(255);

DECLARE TI VARCHAR(255);

DECLARE TJ VARCHAR(255);

SET TG = ((SELECT count(idOrder) as total FROM TR_Order GROUP BY Convert(char(8), transactionDate, 111) + 1);

SET TH = SUBSTRING(curdate(),1,4);

SET TI = SUBSTRING(curdate(),6,2);

SET TJ = SUBSTRING(curdate(),9,2);

SET INPUT = CONCAT(TH,TI,TJ,TG);

RETURN INPUT;

END;

Upvotes: 0

cameront
cameront

Reputation: 710

select convert(VARCHAR(8), transactionDate, 112) 
    + CONVERT(VARCHAR(100), RANK() 
        over (partition by convert(date, transactionDate) order by idorder))
    as 'invoice no'
    , * 
from TR_Order

Upvotes: 1

Related Questions