January 23, 2015 at 12:13 am
old hand thanks for the solution but this solution fails in some scenarios.
can you further help me with the same.
January 23, 2015 at 12:23 am
the above solution fails when i
update MstrMap set MapValidUpto = 40100
where MapPerson = '2001'
and
add another row
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'MGMT ', N'2001 ',N'', 40101, NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
can you please provide proper solution for this case.
January 25, 2015 at 2:18 pm
Sorry for the late answer.
If you're still struggling with the problem try this. As there may be multiple intervals for a given MapPerson/Mapgroup, we need to know next interval info. SQL 2012 has LEAD function for this. Not sure if any gaps between sequetnial intervals may exist . So the query may need some tweaking to take gaps into account.
with others as (
select top(1) mp2.MapDepartment , mp2.MapValidFrom, mp2.MapValidUpto
from [dbo].[MstrMap] mp2
where mp2.MapPerson =N'' and mp2.MapGroup = N''
), t1 as (
select
ruletype = case when (p.PrsnCode = mp.MapPerson) then 'matchig by person code'
when mp.MapDepartment is null then 'no matches found'
else 'matching by group' end
, p.PrsnCode, p.PrsnName, p.PrsnGrpCode
, mp.MapDepartment, mp.MapValidFrom, mp.MapValidUpto
, nxtFrom = lead(mp.MapValidFrom,1,NULL) over (partition by PrsnCode order by mp.MapValidFrom)
from [dbo].[MstrPerson] p
left join [dbo].[MstrMap] mp on p.PrsnCode = mp.MapPerson
or (mp.MapPerson = N'' and p.PrsnGrpCode = mp.MapGroup)
)
select t1.ruletype, t1.PrsnCode, t1.PrsnName, t1.PrsnGrpCode
, intervals.MapDepartment, intervals.MapValidFrom, intervals.MapValidUpto
from t1
outer apply (
select t1.MapDepartment, t1.MapValidFrom, t1.MapValidUpto
where t1.MapDepartment is not null
union all
-- Should it take gaps (nxtFrom - MapValidUpto) > 1 into account ?
select others.MapDepartment, t1.MapValidUpto, others.MapValidUpto
from others
where t1.MapValidUpto is not null and t1.nxtFrom is null
union all
select others.MapDepartment, others.MapValidFrom, others.MapValidUpto
from others
where t1.MapDepartment is null) intervals
January 26, 2015 at 11:15 pm
first of all serg-52 no need to be sorry.
thanks for the solution.
want a suggestion on some nice way to improve knowledge of new sql as till now i was using sql server 2000 only.
January 27, 2015 at 12:58 am
Jay Sapani (1/26/2015)
first of all serg-52 no need to be sorry.thanks for the solution.
want a suggestion on some nice way to improve knowledge of new sql as till now i was using sql server 2000 only.
Really you have to learn a lot of new features. CTE, APPLY, XML, window functions (OVER Clause ), PIVOT/UNPIVOT... to mention a few. This site has a number of helpful Stairways, see http://qa.sqlservercentral.com/articles/Stairway+Series/119933/ for example. I think you need also obtain some book by Itzik-Ben-Gan.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply