March 24, 2011 at 5:42 pm
Hi,
I wonder if anyone can help with the following issue?
I am attempting to find records with the following critria;
=====================================================================================
DROP Table #tmpDocSummary
select D1.ClientID , I1.*
INTO ##tmpDocSummary
From tDocSummary I1
INNER JOIN tIdentity D1
ON I1.IdentityNbr = D1.IdentityNbr
where I1.ApplNbr is not null
and convert(varchar(10),I1.DateExpiry,120) >= convert(varchar(10),GetDate(),120)
and I1.SingleMultiple = 'M'
and I1.LabelStatus = 'A'
and I1.Status = 'I'
and I1.Permitted = 1
=====================================================================================
However this returns several records and I require the record with the highest DocSummaryKey;
=====================================================================================
SELECT IdentityNbr FROM ##tmpDocSummary
WHERE ClientID NOT IN ( SELECT ID.ClientID FROM tImmDocSummary t1
INNER JOIN tIdentity ID
ON t1.IdentityNbr = ID.IdentityNbr
INNER JOIN ##tmpDocSummary t2
ON ID.ClientID = t2.ClientID
WHERE t1.DocSummaryKey >=t2.DocSummaryKey
and t1.SingleMultiple = 'M'
and t1.LabelStatus = 'A'
and t1.Status = 'A'
and t1.Permitted = 1)
=====================================================================================
Is there any way of combining this statement to make it cleaner, maybe a cursor?
Thanks, Phil
March 24, 2011 at 5:48 pm
Couple of things, and no, you don't want a cursor if I understand you right.
phil.layzell (3/24/2011)
and convert(varchar(10),I1.DateExpiry,120) >= convert(varchar(10),GetDate(),120)
This can't index seek, it's considered non-SARGable, because you've wrapped DateExpiry in a function. Is the purpose of this simply to trim the timestamp? There's more effective workarounds that won't kill your indexing, but want to make sure that's the purpose before we go there.
However this returns several records and I require the record with the highest DocSummaryKey;
Do you want the ClientID with the highest DocSummaryKey, or the highest DocSummaryKey per ClientID?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 24, 2011 at 5:51 pm
Thanks for responding so quick.:-)
I need the highest DocSummaryKey per ClientId.
March 24, 2011 at 5:52 pm
Phil, why this?
and convert(varchar(10),I1.DateExpiry,120) >= convert(varchar(10),GetDate(),120)
Change that to:
DECLARE @Now DATETIME
SET @Now = GETDATE()
AND l1.DateExpiry = @Now
Secondly, can you explain your situation with some nice sample data and nice expected result?
March 24, 2011 at 5:56 pm
Try this:
DECLARE @Now DATETIME
SET @Now = GETDATE()
DROP Table #tmpDocSummary
; with cte as
(
select D1.ClientID , I1.*
, rn = row_number over(partition by d1.clientid order by I1.DocSummaryKey DESC) -- added new
From tDocSummary I1
INNER JOIN tIdentity D1
ON I1.IdentityNbr = D1.IdentityNbr
where I1.ApplNbr is not null
and l1.DateExpiry = @Now -- added new
and I1.SingleMultiple = 'M'
and I1.LabelStatus = 'A'
and I1.Status = 'I'
and I1.Permitted = 1
)
select *
INTO ##tmpDocSummary
from cte where rn = 1
{Edit : Moved the INTO ##tmpDocSummary statetment out of the CTE}
March 24, 2011 at 5:57 pm
Thanks, using a Variable makes sense.
The result set I am getting is,
CLIENTID DocSummaryKey
101 10000
101 10001
105 20000
105 20001
I need to extract the highest DocSummaryKey's 10001 & 20001
March 24, 2011 at 7:06 pm
Can you supply the ddl for the tables and a little sample data?
I was in the middle of providing a pretty quick (usually) cross apply solution, and then realized I didn't know where to connect up all the dots. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 24, 2011 at 7:24 pm
CREATE TABLE [dbo].[tDocSummary](
[DocSummaryKey] [dbo].[Id_dom] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DocKey] [dbo].[Id_dom] NULL,
[IdentityNbr] [dbo].[Id_dom] NOT NULL,
[DateExpiry] [dbo].[Date_dom] NULL,
[SingleMultiple] [dbo].[Indicator_dom] NULL,
[MonthsAllowed] [dbo].[Count_dom] NULL,
[ValidTo] [dbo].[DateTime_dom] NULL,
[Type] [dbo].[Type_dom] NULL,
[Key] [dbo].[Id_dom] NULL,
[ApplNbr] [dbo].[Id_dom] NULL,
[Ind] [dbo].[Indicator_dom] NULL,
[Status] [dbo].[Status_dom] NOT NULL,
[Key] [dbo].[Id_dom] NULL,
[DocType] [dbo].[Type_dom] NULL,
[LabelStatus] [dbo].[Status_dom] NULL,
[DateTimeCreated] [dbo].[DateTime_dom] NOT NULL,
[LabelNbr] [dbo].[Id_dom] NULL
)
CREATE TABLE [dbo].[tIdentity](
[IdentityNbr] [dbo].[Id_dom] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ClientId] [dbo].[Id_dom] NOT NULL
)
The query returns ;
CLIENTID DOCSUMMARYKEY ------
10001 530000
10001 530001
I need to pull back the record with the DocSummaryKey of 530001.
Thanks 🙂
March 24, 2011 at 7:31 pm
Try this:
SELECT
drv.ClientID,
ca.*
FROM
(SELECT DISTINCT ClientID from tIdentity) AS drv
CROSS APPLY
(SELECT TOP 1
i.ClientID,
ds.DocSummaryKey,
-- picked a few random fields here, choose what you want to return...
ds.DocKey,
ds.DateExpiry
FROM
tDocSummary AS ds
JOIN
tIdentity AS i
ONds.IdentityNbr = i.IdentityNbr
WHERE
i.clientID = drv.ClientID
ORDER BY
DocSummaryKey DESC
) AS ca
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 24, 2011 at 8:21 pm
Can you give us some MOCK-UP sample data to base our queries ?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply