Hep in Query with efficient possible result

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 2008 Forums

Transact-SQL (2008)

Hep in Query with efficient possible result

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-29 : 05:25:37

I have parent table Segment
then it's child ContainerSummaryRecord
then it's child ContainerQuantityRecord

ContainerSummaryRecord it self can have self reference by the help of CSMPArentReferenceID

Now I have to get information only from the ContainerSummaryRecord and one of it's child table CQT data first matched record
that matched record can be from one ContainerSummaryRecord record child CQT or ContainerSummaryRecord childparentreferenceid child cqt

SELECT csm.fkSegmentID,
csm.csmContainerID,
csmContainerLevel,
csmContainerStatus,
Upper(csmEntryPointActualOrDeliveryLocaleKey) as csmEntryPointActualOrDeliveryLocaleKey,
csmContainerGrossWeight,
csmNumberofCopies,
cqt.cqtDestinationEntry,
csm.csmContainerDestinationZip,
csm.csmIncludedInOtherDocumentation,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM SegmentRecord seg
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
inner Join ContainerQuantityRecord cqt
on cqt.fkJobID = csm.fkJobID
and ( cqt.fkContainerID = csm.csmContainerID
or cqt.fkContainerID in (select top 1 csmContainerID
from ContainerSummaryRecord csmchild
where fkJobID = @JobID
and seg.segSegmentID = csmchild.fkSegmentID
and csmchild.csmParentContainerReferenceID = csm.csmContainerID
and csmchild.csmIsDeleted = 0) )
where seg.fkJobID = @JobID
and seg.segIsDeleted = 0
and seg.segClassDefiningPreparation = '3'
and segPrincipalProcessingCategory in ( 'FL', 'LT', 'MP', 'IR' )
and csmIsDeleted = 0
and csmContainerType = 'P'
and csmFASTContentID is null
and csmReservationNumber is null
and ( csmContainerStatus in ( 'O', 'P', 'R', 'X' )
or csmContainerStatus is null ))
SELECT @JobID as JobID,
fkSegmentID as SegmentID,
csmContainerID as ContainerID,
csmContainerLevel as ContainerLevel,
csmContainerStatus as ContainerStatus,
csmEntryPointActualOrDeliveryLocaleKey as LocaleKeyWithLOCSuffix,
csmContainerGrossWeight as ContainerGrossWeight,
csmNumberofCopies as NumberofCopies,
cqtDestinationEntry,
csmContainerDestinationZip,
csmIncludedInOtherDocumentation
FROM CTE
WHERE Rn = 1

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 10:14:40

If you want help, you will need to be a little more clear about what you need...don't post your "solution"

We most likely will need the DDL, sample data and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 12:26:13

sounds like what you need is recursive query using common table expression
Cant suggest anything until we have clear idea of data from table
Post data as Brett suggested and then we will be able to provide suggestion

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-30 : 08:35:23

I have given some detail in http://social.msdn.microsoft.com/Forums/en/transactsql/thread/23890e7b-95b2-46cd-bb1e-8dee3aea4a54

Please check it

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-30 : 12:55:54

Since you, apparently, are unfamilar with supplying DDL, DML and Expected Output I'm providing two links to help you with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-31 : 05:21:22

Here is sample Table structure and it's data

Segment
SegmentID
Class
... other non key field

CSM table
fkSegmentID referencing SegmetnTable
csmContainerID
csmParentContainerReferenceID referencing same csm table csmContainerID
csmContainerType
..other non key fields

CQT
CQTDatabaseID
fkContainerID
cqtDestinationEntry

Data Can be

SegmentID Class
0001 3
0002 3

CSM
SegmetnID CsmContainerID csmParentContainerReferenceID csmContainerType
0001 000001 NULL P
0001 000002 NULL P
0001 000003 00002 Q
0001 000004 00002 Q

CQT
fkCsmContainerID CQTDatabaseID cqtDestainationentry
000001 1 A
000001 2 A
000003 3 K
000003 4 K
000003 5 K
000004 6 K
........//thousnads of records

Resultant data should be

SegmentID csmContainerID csmContainerType cqtDestinationEntry
0001 000001 P A
0001 000002 P K

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-01 : 01:17:14

can you explain your output?

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-04-01 : 03:49:08

the output is like
foreach container who's type is P if it has any record in CQT then find firt CQT record's cqtdestinationEntry other wise found any record where csmParentreferenceID is this container's csmContainerID and found out the first record cqtDestinationEntry

SegmentID csmContainerID csmContainerType cqtDestinationEntry
0001 000001 P A
0001 000002 P K

In above output csmContainerID 000001 record found in cqt with cqtdatabase id 1 and 2 so we get first cqtdestination entry A

For csmContainerID 000002 we didn't found out any record in qt but there are records in csm file where csmparentreferenceID is 000002
so we use that record to fond out cqtdestinationentry

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 00:16:41

so what if there are more than one cqtDestinationEntry with csmparentreferenceID is 000002? do you want all of them or particular value?

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-04-02 : 02:59:48

I just wanted first matched cqt.
[Actually it can't be different.There is some sort of validation on data import and updation]

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-04-02 : 03:07:00

I have found following solution

declare @JobID as varchar(8)
Set @JobID= '00000009'
;WITH CTE
AS (SELECT csm.fkSegmentID,
csm.csmContainerID,
csm.csmContainerLevel,
csm.csmContainerStatus,
Upper(csm.csmEntryPointActualOrDeliveryLocaleKey) as csmEntryPointActualOrDeliveryLocaleKey,
csm.csmContainerGrossWeight,
csm.csmNumberofCopies,
cqt.cqtDestinationEntry,
csm.csmContainerDestinationZip,
csm.csmIncludedInOtherDocumentation,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM SegmentRecord seg
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
LEFT JOIN (Select * -- top 1 * -- Take off "top 1")
from ContainerSummaryRecord) as CSMCHILD
ON CSMCHILD.fkJobID = @JobID
and seg.segSegmentID = csmchild.fkSegmentID
and csmchild.csmParentContainerReferenceID = csm.csmContainerID
and csmchild.csmIsDeleted = 0
INNER Join ContainerQuantityRecord cqt
on cqt.fkJobID = csm.fkJobID
and ( cqt.fkContainerID = csm.csmContainerID
or ( CSMCHILD.csmContainerID is not null
and cqt.fkContainerID = CSMCHILD.csmContainerID ) )
where seg.fkJobID = @JobID
-- and CSMCHILD.csmContainerID is not null -- (remove)
and seg.segIsDeleted = 0
and seg.segClassDefiningPreparation = '3'
and segPrincipalProcessingCategory in ( 'FL', 'LT', 'MP', 'IR' )
and csm.csmIsDeleted = 0
and csm.csmContainerType = 'P'
and csm.csmFASTContentID is null
and csm.csmReservationNumber is null
and ( csm.csmContainerStatus in ( 'O', 'P', 'R', 'X' )
or csm.csmContainerStatus is null )
)
SELECT @JobID as JobID,
fkSegmentID as SegmentID,
csmContainerID as ContainerID,
csmContainerLevel as ContainerLevel,
csmContainerStatus as ContainerStatus,
csmEntryPointActualOrDeliveryLocaleKey as LocaleKeyWithLOCSuffix,
csmContainerGrossWeight as ContainerGrossWeight,
csmNumberofCopies as NumberofCopies,
cqtDestinationEntry,
csmContainerDestinationZip,
csmIncludedInOtherDocumentation
FROM CTE
WHERE Rn = 1

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-02 : 03:23:31

Yes, JohnQFlorida is good, http://social.msdn.microsoft.com/profile/johnqflorida/?ws=usercard-mini

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-04-02 : 22:23:57

I cleaned up your query a little, but I still don't like it (Your sample data and ddl was not very good, so I didn't spend much time on it)

I wrote this in notepad, so I couldn't check the syntax, but you should get a little better performance with this.


declare @JobID as varchar(8)
Set @JobID= '00000009' 
select * 
from
(
SELECT 
@jobid,
csm.fkSegmentID,
csm.csmContainerID,
csm.csmContainerLevel,
csm.csmContainerStatus,
Upper(csm.csmEntryPointActualOrDeliveryLocaleKey) as csmEntryPointActualOrDeliveryLocaleKey,
csm.csmContainerGrossWeight,
csm.csmNumberofCopies,
cqt.cqtDestinationEntry,
csm.csmContainerDestinationZip,
csm.csmIncludedInOtherDocumentation,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM 
	SegmentRecord seg
INNER JOIN 
	ContainerSummaryRecord csm
ON 		seg.fkJobID = csm.fkJobID
AND 		seg.segSegmentID = csm.fkSegmentID
LEFT JOIN 
	(Select *  
	from 
	ContainerSummaryRecord a
	where 
	a.fkJobID = @JobID
	and 
	a.csmIsDeleted = 0
	) as CSMCHILD
on
and 		seg.segSegmentID = csmchild.fkSegmentID
and 		csm.csmContainerID = csmchild.csmParentContainerReferenceID 
INNER Join 
	ContainerQuantityRecord cqt
on		 cqt.fkJobID = csm.fkJobID
and 		(cqt.fkContainerID = csm.csmContainerID
or
                 csmchild.fkcontainerid = cqt.fkContainerid )

where 
	seg.fkJobID = @JobID
and seg.segIsDeleted = 0
and seg.segClassDefiningPreparation = '3'
and segPrincipalProcessingCategory in ( 'FL', 'LT', 'MP', 'IR' )
and csm.csmIsDeleted = 0
and csm.csmContainerType = 'P'
and csm.csmFASTContentID is null
and csm.csmReservationNumber is null
and coalesce(csm.csmContainerStatus,'O')  in ( 'O', 'P', 'R', 'X' )
) a
WHERE a.Rn = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources