search missing numbers ina rnage with letters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

search missing numbers ina rnage with letters

Author

Topic

paulmc
Starting Member

1 Post

Posted - 2010-04-30 : 11:24:50

hi all just new to this site so hopefully someone can point me in the right direction ,
i have a field called docket_no that has refs like below, i need a sql query tell me what dockets are missing between say 91000 and 91010 for each of the three letters K,L,M separately,
if it has to be 3 separate queries that doesn’t matter ,any ideas
K091000
K091001
K091002
K091003
K091005
K091007
K091009
K091010
L091001
L091002
L091005
L091006
L091010
M091000
M091001
M091002
M091003
M091006
M091007
M091008
M091009
M091010

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-30 : 13:09:43

Try something like this:


DECLARE @SomeTable TABLE
(
   docket_no VARCHAR(8) NOT NULL
);

INSERT INTO @SomeTable(docket_no)
   SELECT 'K0910000' UNION ALL
   SELECT 'K0910001' UNION ALL
   SELECT 'K0910002' UNION ALL
   SELECT 'K0910003' UNION ALL
   SELECT 'K0910005' UNION ALL
   SELECT 'K0910007' UNION ALL
   SELECT 'K0910009' UNION ALL
   SELECT 'K0910010' UNION ALL
   SELECT 'L0910001' UNION ALL
   SELECT 'L0910002' UNION ALL
   SELECT 'L0910005' UNION ALL
   SELECT 'L0910006' UNION ALL
   SELECT 'L0910010' UNION ALL
   SELECT 'M0910001' UNION ALL
   SELECT 'M0910002' UNION ALL
   SELECT 'M0910003' UNION ALL
   SELECT 'M0910006' UNION ALL
   SELECT 'M0910007' UNION ALL
   SELECT 'M0910008' UNION ALL
   SELECT 'M0910009' UNION ALL
   SELECT 'M0910010';

WITH CTE1(docket_char) AS
(
   SELECT 'K' UNION ALL
   SELECT 'L' UNION ALL
   SELECT 'M'
)
, CTE2(docket_number) AS
(
   SELECT '0910000' UNION ALL
   SELECT '0910001' UNION ALL
   SELECT '0910002' UNION ALL
   SELECT '0910003' UNION ALL
   SELECT '0910004' UNION ALL
   SELECT '0910005' UNION ALL
   SELECT '0910006' UNION ALL
   SELECT '0910007' UNION ALL
   SELECT '0910008' UNION ALL
   SELECT '0910009' UNION ALL
   SELECT '0910010'
)
SELECT T2.docket_no
  FROM CTE1 AS T1
       CROSS APPLY
       (SELECT T1.docket_char + T2.docket_number AS docket_no
          FROM CTE2 AS T2) AS T2
 WHERE NOT EXISTS(SELECT *
                    FROM @SomeTable AS T3
                   WHERE T3.docket_no = T2.docket_no);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 00:41:21

[code]
SELECT m.Col
FROM
(
SELECT t.Str+t1.Val AS Col
FROM (SELECT 'K' AS Str UNION ALL
SELECT 'L' UNION ALL
SELECT 'M')t
CROSS JOIN (SELECT '091000' AS Val UNION ALL
SELECT '091001' UNION ALL
...
SELECT '091010')t1
)m
LEFT JOIN YourTable n
ON n.docket_no =m.Col
WHERE n.docket_no IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources