asked
how to find missing values from table
i have a table like![alt text][1] in this N number of values is there from that i have to find missing 1 so how i can find that particular missing values.One more thing i have to find that missing in same formate that my exciseInvoiceNo is printing. [1]: /storage/temp/3302-tit.jpg
Just to clarify - the exciseInvoiceno is in format xxxx/yy, and you want to find the missing xxxx's?
Assuming that you want to find the missing first part of the invoice number, something like this: DECLARE @Invoices TABLE (ExciseInvoiceNo VARCHAR(20)) INSERT INTO @invoices VALUES ('1574/15'), ('1576/15'), ('1577/15'), ('1578/15'), ('1579/15'), ('1580/15'); --I've added 1574/15 to create a gap WITH InvoiceIDs AS (SELECT ExciseInvoiceNo, CONVERT(INTEGER, LEFT(ExciseInvoiceNo,CHARINDEX('/',ExciseInvoiceNo)-1 )) AS InvoiceID FROM @Invoices), t1 AS (SELECT 1 AS N UNION ALL SELECT 1), t2 AS (SELECT 1 N FROM t1 x,t1 y), t3 AS (SELECT 1 N FROM t2 x,t2 y), t4 AS (SELECT 1 N FROM t3 x,t3 y), TallyCTE AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x,t4 y) SELECT *, CONVERT(VARCHAR(20), N) + '/15' AS MissingExciseInvoiceNo FROM TallyCTE LEFT OUTER JOIN InvoiceIDs ON TallyCTE.N = InvoiceIDs.InvoiceID WHERE N >= ( SELECT MIN(InvoiceIDs.InvoiceID) FROM InvoiceIDs ) AND N
18 People are following this question.
