Wich record is missing? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Wich record is missing?

Author

Topic

jocast
Starting Member

8 Posts

Posted - 2010-06-15 : 15:21:20

Hello group i am trying to get the "missing boxes" from a query. Here i post an example.

Table1
BoxID, noxnumber, boxqty, boxgroup
12345, 1 , 3 , abcd
12345, 3 , 3 , abcd
23456, 1 , 2 , abcd
23456, 2 , 2 , abcd
34567, 4 , 4 , abcd

My goal is to let the user know the missing boxes
BoxID, noxnumber, boxqty, boxgroup
12345, 2 , 3 , abcd
34567, 1 , 4 , abcd
34567, 2 , 4 , abcd
34567, 2 , 4 , abcd

is this possible?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-15 : 16:01:29

I'm sure there must be a better way...but try this.
Sample Data

declare @t table (
BoxID int, noxnumber int, boxqty int, boxgroup varchar(10))
insert @t
select 12345, 1 , 3 , 'abcd'
union all select 12345, 3 , 3 , 'abcd'
union all select 23456, 1 , 2 , 'abcd'
union all select 23456, 2 , 2 , 'abcd'
union all select 34567, 4 , 4 , 'abcd'

Query

select t1.*
from
(
select distinct a.BoxID,b.number,a.boxqty,a.boxgroup
from
(
select BoxID,boxqty,max(noxnumber) as noxnumber ,boxgroup from @t
group by BoxID,boxqty,boxgroup
) a
cross apply
(
select number from master..spt_values where number between 1 and a.noxnumber 
) b
) t1
left join @t t2
on t1.BoxID = t2.BoxID and t1.number = t2.noxnumber
where t2.noxnumber is null

Result

BoxID       number      boxqty      boxgroup
----------- ----------- ----------- ----------
12345       2           3           abcd
34567       1           4           abcd
34567       2           4           abcd
34567       3           4           abcd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 18:27:39

select number from master..spt_values where number between 1 and a.noxnumber

select number from master..spt_values where number between 1 and a.noxnumber and type = 'p'

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources