June 16, 2012 at 11:41 pm
here i am writing strore proc for mLm tree proc with three
1.if a member joined means he will get salary RS.50 per day with 3 condition
of leave condition s as follows
1. Apply level 1 filter to remove Sunday and Tuesday
2. Apply level 2 filter to remove the Holiday from the holiday table
3. Apply level 3 filter to remove the Holding period of that member from Incentive Holding period table.
2. MemberId will be the input parameter.
3 Start date will be member added date which has to be taken from the tblMembersRelation table.
4. End date will be the current date of calculation.
5. Find all the dates between the start and end date
6. and once the memberadded 3 member his incentive will be increased as RS.100 DAILY WITH 3 LEAVE CONDITION
CREATE TABLE [dbo].[tblMembersRelation]
(
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
DATECREATED DATETIME
)
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID], DATECREATED1 )
VALUES
(1 ,null,6-05-2012),
(2, 1,7-05-2012),
(3, 1,7-05-2012),
(4,1,14-05-2012),
(5, 3,20-05-2012),
(6, 4,20-05-2012)
TABLE 2 GOVERMENT HOLIDAYS
Holiday
FromDate ToDate
9-May-12 13-May-12
TABLE 3 LEAVE TAKEN BY MEMBER
Member Holding Period
FromDate ToDate MemberId
16-May-12 16-May-12 2
21-May-12 23-May-12 4
THIS IS THE OUTPUT IAM JUST TRYING
FOR EXAMPLE IAM TRYING
MemberId 1
StartDate 6-May-12 Member joined on this date
EndDate 23-May-12 Assume member viewing this report on this date
Expected Proc Output
Date Weekday CompletedLevel Child Memcountonthatday Description Amount
6-May-12 Sun 0 1 Valid 50
7-May-12 Mon 0 2 Valid 50
8-May-12 Tue 0 2 Office leave 0
9-May-12 Wed 0 2 General holiday 0
10-May-12 Thu 0 2 General holiday 0
11-May-12 Fri 0 2 General holiday 0
12-May-12 Sat 0 2 General holiday 0
13-May-12 Sun 0 2 General holiday 0
14-May-12 Mon 1 3 Valid 100
15-May-12 Tue 1 3 Office leave 0
16-May-12 Wed 1 3 In hold 0
17-May-12 Thu 1 3 Valid 100
18-May-12 Fri 1 4 Valid 100
19-May-12 Sat 1 4 Office leave 0
20-May-12 Sun 1 4 Valid 100
21-May-12 Mon 1 4 Valid 100
22-May-12 Tue 1 4 Office leave 0
23-May-12 Wed 1 4 Valid 100
Total 700
i just tried like this
declare @MEMBERID int=1
declare @MEMBER int
SELECT @MEMBER= COUNT(PARENTID) FROM [tblMembersRelation] WHERE PARENTID =@MEMBERID
--select @MEMBER
IF ( @MEMBER=0)
begin
declare @amount int
declare @LEVEL int
SET @LEVEL=0
set @amount =50
SELECT
MEMBERID,
CREATEDATE,
------------GETDATE()as TODATE,
@LEVEL as LEVELNUM,
@amount as amount,
(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
(CASE WHEN DATENAME(dw, @EndDate) = 'Tuesday'
THEN 1
ELSE 0
END)
)
FROM
[tblMembersRelation]
WHERE
MEMBERID =@MEMBERID
end
June 17, 2012 at 11:00 am
Suddenly I am in panic and I can't write any SQL code. One time I lost a lot of money on this kind of "program". Since then, I get scared and panic when I see it:hehe:
June 18, 2012 at 12:14 pm
create function dbo.generateDates(@startdate date, @enddate date)
returns @dates table(dates date)
as
begin
while @startdate<@enddate
begin
insert into @dates values(@startdate)
set @startdate = dateadd(d, 1, @startdate)
end
return
end
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-06'), (2, 1,'2012-05-07'), (3, 1,'2012-05-07'), (4,1,'2012-05-14')
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 @memberid int
set @memberid = 1
;with cte as(
select [MemberID], dates
FROM @tblMembersRelation
cross apply dbo.generateDates(DATECREATED, CAST(GETDATE() as DATE))
where MemberID = @memberid
)
SELECT t.dates AS DATE
, DATENAME(DW, t.dates) as Weekday
, cnt/3 CompletedLevel
, cnt as Memcountonthatday
, case when h.FromDate is not null then 'General holiday'
when DATEPART(dw, t.dates) = 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) = 3 then 0
when d.FromDate is not null then 0
else (cnt/3+1)*50
end as amount
FROM (
select cte.MemberID, cte.dates, COUNT(ParentID) as cnt
from cte
left join @tblMembersRelation r on cte.MemberID = r.ParentID and cte.dates>=r.DATECREATED
group by cte.MemberID, cte.dates
) 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
here i found the partial answer but iam not getting
out put
Apply level 1 filter to remove Sunday and Tuesday
SOME TIME IT WAS SHOWING DATA VALUE AS 50
SOME TIME IT WAS SHOWING DATA VALUE AS 100 ALSO
DATEWeekdayCompletedLevelMemcountonthatdaydescriptionamount
2012-05-06Sunday00valid50
2012-05-07Monday02valid50
2012-05-08Tuesday02Office leave0
2012-05-09Wednesday02General holiday0
2012-05-10Thursday02General holiday0
2012-05-11Friday02General holiday0
2012-05-12Saturday02General holiday0
2012-05-13Sunday02General holiday0
2012-05-14Monday13valid100
2012-05-15Tuesday13Office leave0
2012-05-16Wednesday13In hold0
2012-05-17Thursday13valid100
2012-05-18Friday13valid100
2012-05-19Saturday13valid100
2012-05-20Sunday13valid100
2012-05-21Monday13In hold0
2012-05-22Tuesday13Office leave0
2012-05-23Wednesday13In hold0
2012-05-24Thursday13valid100
2012-05-25Friday13valid100
2012-05-26Saturday13valid100
2012-05-27Sunday13valid100
2012-05-28Monday13valid100
2012-05-29Tuesday13Office leave0
2012-05-30Wednesday13valid100
2012-05-31Thursday13valid100
2012-06-01Friday13valid100
2012-06-02Saturday13valid100
2012-06-03Sunday13valid100
2012-06-04Monday13valid100
2012-06-05Tuesday13Office leave0
2012-06-06Wednesday13valid100
2012-06-07Thursday13valid100
2012-06-08Friday13valid100
2012-06-09Saturday13valid100
2012-06-10Sunday13valid100
2012-06-11Monday13valid100
2012-06-12Tuesday13Office leave0
2012-06-13Wednesday13valid100
2012-06-14Thursday13valid100
2012-06-15Friday13valid100
2012-06-16Saturday13valid100
2012-06-17Sunday13valid100
how to avoid this error and also tell me how tosum the all amount i tried using tempeoray table the out put was not clear so plz any one try to help me
June 18, 2012 at 12:29 pm
First suggestion, replace your multi-statement table valued function with the this one:
drop function dbo.generateDates;
go
create function dbo.generateDates(@startdate date, @enddate date)
returns table
as
return(
with e1(n) as (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1), -- 10 rows
e2(n) as (select a.n from e1 a cross join e1 b), -- 100 rows
e4(n) as (select a.n from e2 a cross join e2 b), -- 10,000 rows
tally(n) as (select 0 union all select row_number() over (order by (select null)) from e4)
select top (datediff(dd,@startdate,@enddate))
dateadd(dd,n,@startdate) dates
from
tally);
go
June 18, 2012 at 1:05 pm
this is also function providing same result
June 18, 2012 at 1:13 pm
sivag (6/18/2012)
this is also function providing same result
Umm, did not say it would change anything, just said the first thing I'd do is replace the multi-statement table valued function with the inline table valued function I provided.
June 18, 2012 at 1:56 pm
plz tell me how i can change assign value o during Sunday and Tuesday with out assigning value as 50 or 100
how can i change my proc in case statement
Apply level 1 filter to remove Sunday and Tuesday IT WAS NOT WORKING PROPERLY
SOME TIME IT WAS SHOWING DATA VALUE AS 50
SOME TIME IT WAS SHOWING DATA VALUE AS 100 ALSO
June 18, 2012 at 8:30 pm
sivag (6/16/2012)
here i am writing strore proc for mLm tree proc with three
You mention "mLm" but you are only working with 2 levels so far. A lot of what has been written so far will all fall apart as soon as you add another level especially since it appears that you're only using an Adjacency List to represent your hierarchy.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2012 at 3:43 am
yes u are correct jeff now only i just watched thats
for level i just made the table like this
i am not getting the level output properly sandeep
for example
m
m1 m2 level1
m1.1 m1.2 m2.1 m2.2 level2
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-06'),
(2, 1,'2012-05-07'),
(3, 1,'2012-05-07'),
(4,2,'2012-05-14'),
(5,2,'2012-05-15'),
(6,3,'2012-05-16'),
(7,2,'2012-05-15')
here i place simple example
here member 2 & 3 are the level one member this meber id will refect as a parent id for the second level of (4,5,6,7)
plz and sorry for inconvience
June 19, 2012 at 8:17 am
jeff i tried this to divide level but i am not getting Memcountonthatday properly and if i place @memberid =8
also it was showing static with amont
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-06'), (2, 1,'2012-05-07'), (3, 1,'2012-05-07'), (4,1,'2012-05-14'),(5,2,'2012-05-18'), (6,5,'2012-05-25')
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, 50), (1,3,100), (2,5, 500), (3,25,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, @tblMembersRelation 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) 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
so plz give ur sugession
June 21, 2012 at 5:17 am
the problem was solved by myself itself
June 21, 2012 at 7:46 am
Proper forum etiquette would have you post your solution. Others may have a similar problem and seeing how you solved my help tham.
June 21, 2012 at 8:29 am
sorry lynn still now my problem was not get over i solved 75 % i unable complete level state no one is willing to help for my task
if some one asked questions i can tell the problem no one is willing to solve my problem
June 21, 2012 at 9:06 am
sivag (6/21/2012)
sorry lynn still now my problem was not get over i solved 75 % i unable complete level state no one is willing to help for my taskif some one asked questions i can tell the problem no one is willing to solve my problem
Can't answer for others, but I just haven't had the time to work on it. We are volunteers on this site and we work on those problems/questions where we feel that we can provide the most assistance. Your problem will take more effort to work on, and I have other things (read this as better things, especially on a weekend when I have my girls) to do.
If I find myself with free time, I may look at it. I did give you a new function to use, so it isn't a total loss.
Also, this:
sivag (6/21/2012)
the problem was solved by myself itself
counterdictes what you say above regarding your problem.
June 21, 2012 at 9:20 am
The problem with trying to have volunteers help with the compensation plan code for your MLM is that the code is never as simple as the few lines you have posted.
In order for somebody to spend the amount of time necessary to build this compensation plan for you, they would need the entire compensation plan written out. Also, it would be quite an undertaking for a volunteer. I know of teams that work on these plans and take months to build.
My best advice is to hire a consultant to help you with this plan.
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply