Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query help 2

Status
Not open for further replies.
simian336

simian336

Programmer
Sep 16, 2009
723
US
Pulling my hair out on this query...

rpid ttid tseid info1 info2 info3
2543 5504 8562 00001 - ANCHORS-EXPANDABLE J-8135 1 NULL
2543 5504 8563 00001 - ANCHORS-EXPANDABLE J-8135 1 NULL
2543 5504 8564 00001 - ANCHORS-EXPANDABLE J-8135 1 NULL
2543 5504 8565 00001 - ANCHORS-EXPANDABLE J-8135 1 NULL
2543 5504 8566 00001 - ANCHORS-EXPANDABLE J-8135 1 USE - USED
2543 5505 8568 00008 - CABLE, AERIAL 6 X 22 CF F-8 1 NULL
2543 5505 8569 00008 - CABLE, AERIAL 6 X 22 CF F-8 1 NULL
2543 5505 8567 00008 - CABLE, AERIAL 6 X 22 CF F-8 1 NULL
2543 5506 8570 00012 - CABLE, AERIAL 12 X 22 CF F-8 NULL USE - USED
2543 5506 8571 00012 - CABLE, AERIAL 12 X 22 CF F-8 NULL USE - USED

If a single info3 code (use - used) exists for a single ttid then include the tseid that is on the same line.

If no info3 code (null) exists for a single ttid then include the lowest tseid for that ttid.

If more then one info3 code (use - used) exists for a single ttid then include the lowest tseid for that ttid.

2543 5504 8566 00001 - ANCHORS-EXPANDABLE J-8135 1 USE - USED
2543 5505 8568 00008 - CABLE, AERIAL 6 X 22 CF F-8 1 NULL
2543 5506 8570 00012 - CABLE, AERIAL 12 X 22 CF F-8 NULL USE - USED

Thanks

Simi
 
Sort by date Sort by votes
CREATE TABLE #Test(
[RpId] [int] NOT NULL,
[ttId] [int] NOT NULL,
[tseId] [int] NOT NULL,
[Info1] [nvarchar](255) NULL,
[Info2] [nvarchar](255) NULL,
[Info3] [nvarchar](255) NULL)

insert into #test values (2543,5504,8562,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into #test values (2543,5504,8563,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into #test values (2543,5504,8564,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into #test values (2543,5504,8565,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into #test values (2543,5504,8566,'00001 - ANCHORS-EXPANDABLE J-8135','1','USE - USED')
insert into #test values (2543,5505,8568,'00008 - CABLE, AERIAL 6 X 22 CF F-8','1',NULL)
insert into #test values (2543,5505,8569,'00008 - CABLE, AERIAL 6 X 22 CF F-8','1',NULL)
insert into #test values (2543,5505,8567,'00008 - CABLE, AERIAL 6 X 22 CF F-8','1',NULL)
insert into #test values (2543,5506,8570,'00012 - CABLE, AERIAL 12 X 22 CF F-8',null,'USE - USED')
insert into #test values (2543,5506,8571,'00012 - CABLE, AERIAL 12 X 22 CF F-8',null,'USE - USED')


Thanks again

Simi
 
Upvote 0 Downvote
This is getting close, but not accounting for the single yet.

Code: 
Create Table #table1
(
	rpid int,
	ttid  int,
	tseid int,
	info1 Varchar(40),
	info2 int,
	info3 Varchar(15)
)

Insert Into #table1 Values (2543,5504,8562,'00001 - ANCHORS-EXPANDABLE J-8135',1,NULL);
Insert Into #table1 Values (2543,5504,8563,'00001 - ANCHORS-EXPANDABLE J-8135',1,NULL);
Insert Into #table1 Values (2543,5504,8564,'00001 - ANCHORS-EXPANDABLE J-8135',1,NULL);
Insert Into #table1 Values (2543,5504,8565,'00001 - ANCHORS-EXPANDABLE J-8135',1,NULL);
Insert Into #table1 Values (2543,5504,8566,'00001 - ANCHORS-EXPANDABLE J-8135',1,'USE - USED');
Insert Into #table1 Values (2543,5505,8568,'00008 - CABLE, AERIAL   6 X 22 CF F-8',1,NULL);
Insert Into #table1 Values (2543,5505,8569,'00008 - CABLE, AERIAL   6 X 22 CF F-8',1,NULL);
Insert Into #table1 Values (2543,5505,8567,'00008 - CABLE, AERIAL   6 X 22 CF F-8',1,NULL);
Insert Into #table1 Values (2543,5506,8570,'00012 - CABLE, AERIAL  12 X 22 CF F-8',NULL,'USE - USED');
Insert Into #table1 Values (2543,5506,8571,'00012 - CABLE, AERIAL  12 X 22 CF F-8',NULL,'USE - USED');


With cte1(rpid,ttid,tseid,info1,info2,info3,RowNum)
As (
		Select 
			rpid,
			ttid,
			ttid,
			info1,
			info2,
			info3,
			Row_Number() Over (Partition By ttid Order By ttid) As 'RowNum'
		From #table1
		where info3 is not null
	)
	
		Select 
			rpid,
			ttid,
			ttid,
			info1,
			info2,
			info3
		from cte1
		Where RowNum = 1


Drop Table #table1
Code: 
rpid        ttid        ttid        info1                                    info2       info3
----------- ----------- ----------- ---------------------------------------- ----------- ---------------
2543        5504        5504        00001 - ANCHORS-EXPANDABLE J-8135        1           USE - USED
2543        5506        5506        00012 - CABLE, AERIAL  12 X 22 CF F-8    NULL        USE - USED

Thanks

John Fuhrman
 
Upvote 0 Downvote
Try this:

Code: 
Declare @Test TABLE (
    [RpId] [int] NOT NULL,
    [ttId] [int] NOT NULL,
    [tseId] [int] NOT NULL,
    [Info1] [nvarchar](255) NULL,
    [Info2] [nvarchar](255) NULL,
    [Info3] [nvarchar](255) NULL)

insert into @test values (2543,5504,8562,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into @test values (2543,5504,8563,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into @test values (2543,5504,8564,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into @test values (2543,5504,8565,'00001 - ANCHORS-EXPANDABLE J-8135','1',NULL)
insert into @test values (2543,5504,8566,'00001 - ANCHORS-EXPANDABLE J-8135','1','USE - USED')
insert into @test values (2543,5505,8568,'00008 - CABLE, AERIAL   6 X 22 CF F-8','1',NULL)
insert into @test values (2543,5505,8569,'00008 - CABLE, AERIAL   6 X 22 CF F-8','1',NULL)
insert into @test values (2543,5505,8567,'00008 - CABLE, AERIAL   6 X 22 CF F-8','1',NULL)
insert into @test values (2543,5506,8570,'00012 - CABLE, AERIAL  12 X 22 CF F-8',null,'USE - USED')
insert into @test values (2543,5506,8571,'00012 - CABLE, AERIAL  12 X 22 CF F-8',null,'USE - USED')

;With Temp As
(
  Select rpid, 
         Count(Case When info3='USE - USED' Then 1 End) As CountInfo3, 
         Min(Case When Info3='USE - USED' Then TSeid End) As MinTseidCount1,
         Min(tseid) As MinTSeid
  From   @test
  Group By rpid,ttid
)
Select	T.* 
From	Temp
        Inner Join @Test T
           On T.TSeid = Case When CountInfo3 = 1 Then MinTseidCount1 Else MinTSeid End

The trick here is to get the tseid based on your criteria. So... if we can get the correct tseid when the count is one, and when it's not one, then we should be able to join back to the original table and get the correct rows.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Mot excelent solution AGAIN George.

Can you explain the logic behind the case inside the aggregates?

I can see what they are doing but I would not have thought to use a CASE within an aggregate as you have done.

Very slick BTW.

Thanks

John Fuhrman
 
Upvote 0 Downvote
Hey George,

I like that approach as well unforturnatly info3='USE - USED' could contain multiple things...

I simplified my example a bit.

Thanks as always

Simi
 
Upvote 0 Downvote
Sure. I'll explain. But first, let's look at the rules again.

If a single info3 code (use - used) exists for a single ttid then include the tseid that is on the same line.

If no info3 code (null) exists for a single ttid then include the lowest tseid for that ttid.

If more then one info3 code (use - used) exists for a single ttid then include the lowest tseid for that ttid.
Click to expand...

I interpret that to mean, if one instance of "use-used", return that row, otherwise return the row with the lowest tseid. This allows for 2 conditions instead of 3.

In the CTE, I get the min tseid because I want to use that value in every case except when there is just one "USE - USED". I thought to myself, "wouldn't it be easy if I could just join the CTE back to the original table to pull out the rows I want?" In order to accomplish this, I need a consistent way of joining (or at least a relatively simple way to do it). Since some of the time I will already have a tseid (the min), I can easily join on that. But, when I have a single USE - USED row, I wanted to return the tseid for that row. I couldn't just do a simple min or max because that could return an incorrect row. So... I used a CASE/WHEN to return the tseid for the case where I have USE - USED. Note that when there are multiple USE - USED's, it's (sort of) random which tseid I will get from this query, but I don't care about that because I only use this column when I have exactly one.

Lastly, I wrap that in a MIN aggregate function so that I can easily group on it. I only care about this column when there is exactly one row with USE - USED anyway, so it will always be correct.

The next trick is the join clause between the CTE and the original table.

Code: 
On T.TSeid = Case When CountInfo3 = 1 Then MinTseidCount1 Else MinTSeid End

Basically, when there is only one "USE - USED" then return the MintseidCount1 (which has that case/when statement in the aggregate), otherwise return the MinTSeid. This become the join clause back to the original table, which should accurately return the correct rows.

Generally speaking, it's not too good to use a case/when in a join clause because it will slow down performance. In this case, it should allow you to get the correct data (which is more important than performance anyway), and... if the tables are relatively small, then performance should be good anyway.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hey George,

unless there is something wrong with my logic it appears to work with

info3 is not null

also...


With #Temp As(
Select rpid, ttid,
Count(Case When info3 is not null Then 1 End) As CountInfo3,
Min(Case When Info3 is not null Then TSeid End) As MinTseidCount1,
Min(tseid) As MinTSeid
From #test
Group By rpid, ttid
)
Select T.*
From #Temp
Inner Join #Test T
On T.TSeid = Case
When CountInfo3 = 1
Then MinTseidCount1
Else MinTSeid
End

Thanks for looking it over.

Simi
 
Upvote 0 Downvote
Simi, if the info3 field can have multiple values other than 'USE - USED' and you are test for a NOT NULL condition, do you have a rule in place that sets the field to null if it does not have a valid string value?

ie.
If rtrim(ltrim(info3)) = '' then null

(Simplified example, but you should get the idea.)

so that empty string values don't mess up your query.

Thanks

John Fuhrman
 
Upvote 0 Downvote
Hey John,

It is poplulated by a drop down so it is either null or a specified range of values.

Simi
 
Upvote 0 Downvote
Then maybe...

Count(Case When info3 Not In ('USE - USED',...) Then 1 End)

if not too many choices.

Thanks

John Fuhrman
 
Upvote 0 Downvote
Code: 
;With Temp As
(
  Select rpid, 
         Count(Case When info3 In ('USE - USED','UNUSE - UNUSED') Then 1 End) As CountInfo3, 
         Min(Case When Info3 In ('USE - USED','UNUSE - UNUSED') Then TSeid End) As MinTseidCount1,
         Min(tseid) As MinTSeid
  From   @test
  Group By rpid,ttid
)
Select    T.* 
From    Temp
        Inner Join @Test T
           On T.TSeid = Case When CountInfo3 = 1 Then MinTseidCount1 Else MinTSeid End

Thanks George!![thumbsup2]



Thanks

John Fuhrman
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
373
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
860
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
399
Andrzejek
Andrzejek
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
316
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
How to enhance query to run faster ?
Replies
0
Views
144
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top