July 2, 2012 at 7:09 am
Hi All,
Please help us on the below scenario....
We need to find the members who has 3 or more claims in one year.
We will have to run this through all data what we have. So we will not have any input parameter ie) no year input.
Member IDClaim NumberDate of ServiceComment
1 x1 1-Mar-11 Select this member - 3 claims with in 12 consecutive
1 x2 4-Apr-11
1 x3 1-Jan-12
2 x1 1-May-11
2 x2 1-Jul-11
2 x3 30-Aug-12
2 x4 31-Aug-12
3 x3 4-Aug-12Select this member - 3 claims with in 12 consecutive months.
3 x4 1-Nov-12
3 x5 4-Jan-13
Note : It would help if we can parameterize the count. But this is nice to do. The above one with 3 occurrence is what we need.
Thanks
Pranesh P R
July 2, 2012 at 7:49 am
Try below...
Regarding the performance it depends on the indexes on the columns.Can you post which column has the clustered index and which one have nonclustered index. If you have covering index on date and customerid...It should be quite fast..
There are 4 methods...first 3 are very efficient speciallly 2 and 3...Eeven 4th is good if we do not need the distinct..if need distinct it is quite expensive..
set statistics io,time on
go
drop table #thrd
drop table #frth
go
drop table mysoh
go
select CustomerID,OrderDate,SalesOrderID into mysoh
from Sales.SalesOrderHeader
go
alter table mysoh add constraint pk_customerid primary key (customerid,salesorderid)
go
update Statistics mysoh with fullscan
go
with mycte as
(select * from mysoh
)
,finalcte as
(
select mc.CustomerID,mc.OrderDate stdate,soh.OrderDate enddt,ROW_NUMBER() over( partition by mc.customerid,mc.orderdate order by soh.orderdate) as rn from mycte mc
cross apply (
select * from mysoh soh
where soh.CustomerID= mc.CustomerID
and soh.OrderDate > mc.OrderDate and soh.OrderDate < DATEADD(yy,1,mc.OrderDate)
and soh.salesorderid <> mc.SalesOrderID) soh
)
,anotherfinalcte as
(
select * from finalcte where rn >= 2
)
select
--*
distinct CustomerID
from anotherfinalcte --run this select to check the output properly..
/* --use this to find the distinct values..
select customerid,stdate,max(rn) as maximumclaims
from anotherfinalcte
group by CustomerID,stdate
*/
--now i will add nonclusted index on orderdate
create unique nonclustered index idx_orddt_cust on mysoh(orderdate,customerid,salesorderid)
go
update Statistics mysoh with fullscan
go
with mycte as
(select * from mysoh
)
,finalcte as
(
select mc.CustomerID,mc.OrderDate stdate,soh.OrderDate enddt,ROW_NUMBER() over( partition by mc.customerid,mc.orderdate order by soh.orderdate,soh.salesorderid) as rn from mycte mc
cross apply (
select * from mysoh soh
where soh.CustomerID= mc.CustomerID
and soh.OrderDate > mc.OrderDate and soh.OrderDate < DATEADD(yy,1,mc.OrderDate)
and soh.SalesOrderID <> mc.SalesOrderID) soh
--where mc.CustomerID=11153
)
,anotherfinalcte as
(
select * from finalcte where rn >= 2
)
select
--*
distinct CustomerID
from anotherfinalcte
;with mycte as
(select * from mysoh
)
,finalcte as
(
select mc.CustomerID,mc.OrderDate stdate,soh.OrderDate enddt,ROW_NUMBER() over( partition by mc.customerid,mc.orderdate order by soh.orderdate,soh.salesorderid) as rn
from mycte mc
inner join mysoh soh
on soh.CustomerID= mc.CustomerID
and soh.OrderDate > mc.OrderDate and soh.OrderDate < DATEADD(yy,1,mc.OrderDate)
and soh.salesorderid <> mc.SalesOrderID
--and mc.CustomerID=21037
)
,anotherfinalcte as
(
select * from finalcte where rn >= 2
)
select
--*
distinct CustomerID
--into #thrd
from anotherfinalcte
order by CustomerID
;with mycte as
(
select *,ROW_NUMBER() over( partition by customerid order by orderdate,salesorderid) as rn from mysoh
)
,finalcte as
(
select mc.*,mc_3.orderdate enddt,DATEDIFF(dd,mc.orderdate,mc_3.orderdate) ddif ,mc_3.rn mcrn,
DATEPART(year,mc_3.OrderDate) - DATEPART(year,mc.OrderDate) yr,
DATEPART(month,mc_3.OrderDate) - DATEPART(month,mc.OrderDate) mnth,
DATEPART(DAY,mc_3.OrderDate) - DATEPART(DAY,mc.OrderDate) dy,
case when DATEPART(year,mc_3.OrderDate) - DATEPART(year,mc.OrderDate) = 1
then case when DATEPART(month,mc_3.OrderDate) - DATEPART(month,mc.OrderDate) < 0 then 'Y'
when DATEPART(month,mc_3.OrderDate) - DATEPART(month,mc.OrderDate) = 0 and DATEPART(DAY,mc_3.OrderDate) - DATEPART(DAY,mc.OrderDate)< 0
then 'Y'
else 'N'
end
when DATEPART(year,mc_3.OrderDate) - DATEPART(year,mc.OrderDate) = 0 and DATEPART(month,mc_3.OrderDate) - DATEPART(month,mc.OrderDate) >= 0 then 'Y'
else 'N' end selectme
from mycte mc
inner join mycte mc_3
on mc.customerid = mc_3.customerid
and mc_3.rn = mc.rn + 2-- and mc.rn + 2
and DATEDIFF(dd,mc.orderdate,mc_3.orderdate) <=366
)
select
--*
distinct CustomerID
from finalcte
where 1=1
--order by selectme
and selectme = 'Y'
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 8:03 am
SELECT *
FROM #Sample s
CROSS APPLY (
SELECT ClaimCount = COUNT(*)
FROM #Sample
WHERE [Member ID] = s.[Member ID]
AND [Date of Service] >= s.[Date of Service]
AND [Date of Service] <= DATEADD(mm,12,s.[Date of Service])
) x
WHERE x.ClaimCount > 2
ORDER BY s.[Member ID], s.[Date of Service]
Sample data:
CREATE TABLE #Sample ([Member ID] INT, [Claim Number] CHAR(2), [Date of Service] DATETIME, [Comment] VARCHAR(MAX))
INSERT INTO #Sample ([Member ID], [Claim Number], [Date of Service], [Comment])
SELECT 1, 'x1', '1-Mar-11', 'Select this member - 3 claims with in 12 consecutive' UNION ALL
SELECT 1, 'x2', '4-Apr-11', '' UNION ALL
SELECT 1, 'x3', '1-Jan-12', '' UNION ALL
SELECT 2, 'x1', '1-May-11', '' UNION ALL
SELECT 2, 'x2', '1-Jul-11', '' UNION ALL
SELECT 2, 'x3', '30-Aug-12', '' UNION ALL
SELECT 2, 'x4', '31-Aug-12', '' UNION ALL
SELECT 3, 'x3', '4-Aug-12', 'Select this member - 3 claims with in 12 consecutive months.' UNION ALL
SELECT 3, 'x4', '1-Nov-12', '' UNION ALL
SELECT 3, 'x5', '4-Jan-13', ''
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 2, 2012 at 1:34 pm
Hi all,
I'm also new to sql serer sorry if i did any thing wrong, i tried up to here can any one pls help me to complete his requirement.
Creating sample table
create table #sample(ID int, Number int,AppliedDate datetime)
insert into #sample(ID,Number,AppliedDate)
SELECT 1,1, '01-mar-2011' union all
SELECT 1,2, '04-apr-2011' union all
SELECT 1,3, '01-jan-2012' union all
SELECT 2,1, '01-may-2011' union all
SELECT 2,2, '01-jul-2011' union all
SELECT 2,3, '30-aug-2012' union all
SELECT 2,4, '31-aug-2012' union all
SELECT 3,3, '04-aug-2012' union all
SELECT 3,4, '01-nov-2012' union all
SELECT 3,5, '04-jan-2013';
Querry which i tried to solve this
SELECT ID,COUNT(AppliedDate) AS [RESULT]
from #sample
GROUP BY ID
HAVING COUNT(AppliedDate)>=3
i'm stuck here at consecutive value for date function.
@praneshram Please don't forget to post your DDL and DML for the next time.
Thanks
Ganesh
July 3, 2012 at 12:20 am
Thought I'd give the OP's problem a shot so I came up with this.
;WITH CTE AS (
SELECT [Member ID], [Claim Number], [Date of Service], [Comment]
,n=ROW_NUMBER() OVER (PARTITION BY [Member ID] ORDER BY [Date of Service])
,m=COUNT([Claim Number]) OVER (PARTITION BY [Member ID])
FROM #Sample),
CTE2 AS (
SELECT [Member ID], [Claim Number], [Date of Service], [Comment], n, m
,[Days]=(
SELECT TOP 1 CAST(b.[Date of Service]-a.[Date of Service] AS INT)
FROM CTE b
WHERE a.[Member ID] = b.[Member ID] AND a.n = b.n-2
ORDER BY b.[Date of Service] DESC)
FROM CTE a)
SELECT [Member ID], [Claim Number], [Date of Service], [Comment]
FROM CTE2
WHERE m>2 AND [Days] <= 365
It may not be the most efficient, nor does it properly handle a leap year but what the heck, a shot's a shot.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 3, 2012 at 1:36 am
You are simply counting the number of rows..He needs 3 cliams in a year.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 2:15 am
Hi All,
Thanks for all of your valuble suggestions and i will be using 'ChrisM@Work' Suggestion. Thanks for all of your valuable time.
July 3, 2012 at 2:29 am
praneshram (7/3/2012)
Hi All,Thanks for all of your valuble suggestions and i will be using 'ChrisM@Work' Suggestion. Thanks for all of your valuable time.
+1 for ChrisM!
Gullimeel: Clearly my contribution was not one of my better efforts.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 3, 2012 at 2:36 am
praneshram (7/3/2012)
Hi All,Thanks for all of your valuble suggestions and i will be using 'ChrisM@Work' Suggestion. Thanks for all of your valuable time.
:blush: thanks!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 3, 2012 at 2:39 am
Gullimeel: Clearly my contribution was not one of my better efforts.
If you have replied based on below. Then I am sorry it was not about your query.It was query posted by "ganeshnarim".
You are simply counting the number of rows..He needs 3 cliams in a year.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply