June 21, 2012 at 9:27 am
Going to agree with Jason, hire a consultant. If I were being paid, I'd make time during the evenings and weekends (as long as I was also able to spend time with my girls when I have them) to work on this, but then it would be a job, not just something I was doing to help someone with a problem.
June 21, 2012 at 9:51 am
this is code which was made
DECLARE @tblMembersRelation TABLE (
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
DATECREATED DATETIME
)
INSERT INTO @tblMembersRelation ([MemberID],[ParentID], DATECREATED )
VALUES (1 ,null,'2012-05-03'),
(2, 1,'2012-05-04'),
(3, 1,'2012-05-07'),
(4,2,'2012-05-14'),
(5,2,'2012-05-15'),
(6,3,'2012-05-16'),
(7,2,'2012-05-17'),
(8,3,'2012-05-15'),
(9,4,'2012-06-18')
select * from @tblMembersRelation
DECLARE @tblGovtHolidays TABLE (FromDate DATE, ToDate DATE)
insert into @tblGovtHolidays values ('9-May-12','13-May-12')
DECLARE @holdiing TABLE (
FromDate date, ToDate date, MemberId int)
insert into @holdiing values('16-May-12','16-May-12',1), ('21-May-12','23-May-12',1)
declare @level table(
level int, members int, amount int
)
insert into @level (level, members, amount)values
(0, 0, 75), (1,2,100), (2,3, 500), (3,4,2500)
declare @memberid int
set @memberid =1
;WITH cte
AS (SELECT [MemberID], ParentID, DATECREATED
FROM @tblMembersRelation
WHERE [MemberID] = @memberid
UNION ALL
SELECT t1.MemberID, t1.ParentID, t1.DATECREATED
FROM @tblMembersRelation t1, cte t2
WHERE t1.ParentID = t2.MemberID
), cte1 as(
select [MemberID], dates
FROM cte
cross apply dbo.generateDates(DATECREATED, CAST(GETDATE() as DATE))
), cte2 as (
select @memberid as MemberID, dates, COUNT(1)-1 as cnt from cte1 group by dates
)
SELECT t.dates AS DATE
, DATENAME(DW, t.dates) as Weekday
, (select top 1 level from @level where members <= cnt order by level desc ) as CompletedLevel
, cnt as Memcountonthatday
, case when h.FromDate is not null then 'General holiday'
when DATEPART(dw, t.dates) in (1,3) then 'Office leave'
when d.FromDate is not null then 'In hold'
else 'valid'
end as description
, case when h.FromDate is not null then 0
when DATEPART(dw, t.dates) in (1,3) then 0
when d.FromDate is not null then 0
else (select top 1 amount from @level where members <= cnt order by level desc )
end as amount
FROM cte2 t
left join @tblGovtHolidays h on t.dates between h.FromDate and h.ToDate
left join @holdiing d on t.dates between d.FromDate and d.ToDate and t.MemberID = d.MemberId
the code was not checking level properly
level 1 member count should be m1.1,m1.2
leve 2 member count should be m2.1,m2.2
only count place the role of level
June 25, 2012 at 11:02 am
hi Lynn Pettis did u get any idea about the mlm tree formation if u get any idea means just forward to me
thanks
June 25, 2012 at 11:07 am
Nope, I have better things to do with my unpaid time at the moment, like working on some possible articles that will get me paid some money.
June 25, 2012 at 11:11 am
ok Lynn Pettis no problem i will try complete my task of my own
June 25, 2012 at 11:14 am
Saw Brave this weekend with my daughter, really good movie. We enjoyed very much.
June 25, 2012 at 11:19 am
Lynn Pettis (6/25/2012)
Saw Brave this weekend with my daughter, really good movie. We enjoyed very much.
Random thoughts out of the blue? ;-);-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply