question

raj88 avatar image
raj88 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
sql server 2008 r2
tit.jpg (5.1 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just to clarify - the exciseInvoiceno is in format xxxx/yy, and you want to find the missing xxxx's?
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ignore the dodgy formatting there...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.