Master Child - Continuity Calculation Removal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Master Child - Continuity Calculation Removal

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-06-23 : 13:53:59

Required an query to get the EMI Transaction Month wise with checking the continuation of EMI.
Normal query to get the Group wise of EMPID will get the total of Amount, Need this with checking the Continuation and calc the amount. If any discontinue The amount will be reduced.

[EMIMASTER]
[CLIENTID] [DATE] [EMPID]
101 2013-04-01 11
102 2013-04-15 11
103 2013-04-30 12
104 2013-05-10 13
105 2013-05-20 12
106 2013-06-05 13

[EMICHILD]
[CLIENTID] [DATE] [AMOUNT]
101 2013-04-01 500
102 2013-04-15 500
103 2013-04-30 500
101 2013-05-01 500
102 2013-05-15 500
103 2013-05-30 500
104 2013-05-10 500
105 2013-05-20 500
106 2013-06-05 500
101 2013-06-10 500
103 2013-06-20 500
104 2013-06-22 500
105 2013-06-30 500

Actual Output:
[EMPID] [AMOUNT]
11 2500
12 2500
13 1500

Required Output:
[EMPID] [AMOUNT]
11 1500
12 2500
13 1500

Above Actual output comes from normal query with Group wise of EMPID. Need the same with Continuity check as it on in Required Output. Please help to fix this...

Regards,
Kalai

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-23 : 22:01:02

can you explain the required logic ?

Also elaborate what is "with checking the Continuation and calc the amount" ?

KH
[spoiler]Time is always against us[/spoiler]

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-06-24 : 00:49:27

In Master u can find the Join date with Employee ID. From join date Employee have to pay the EMI monthly basis.
Here 11 Employee's particular ClientID is not payed on June month. Hence the Particular ClientID 102 should not considered on transaction. Have to remove the particular ClientID in Grouping.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 01:26:27

seeing the output, i think this is what you're after


;With CTE
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY [DATE])
FROM [EMIMASTER] 
)
SELECT m.EMPID,SUM([AMOUNT]) AS [AMOUNT]
FROM
(
SELECT c1.EMPID,
c1.[CLIENTID]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.EMPID = c1.EMPID
AND c2.Seq = c1.Seq - 1
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM CTE
             WHERE EMPID <> c1.EMPID
             AND [DATE] BETWEEN c2.[DATE] AND c1.[DATE]
             AND CLIENTID <> c1.CLIENTID 
             AND CLIENTID <> c2.CLIENTID 
            )t
WHERE t.Cnt > 0 
OR c2.CLIENTID IS NULL
)m
INNER JOIN (
             SELECT [CLIENTID],SUM([AMOUNT]) AS [AMOUNT]
             FROM [EMICHILD]
             GROUP BY [CLIENTID]
            )n
ON n.[CLIENTID] = m.[CLIENTID]
GROUP BY m.EMPID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-06-24 : 01:54:32

Hi Visakh thanks for ur Query. But the Output is not wat i expected.
You have used seq-1, It removes the Seq for both EMPID 11 and 12.
Your chk is with Month, Month which comes from APRIL is removed here for EMPID 12. And the same for EMPID 13.

Ur OUTPUT:
[EMPID] [AMT]
11 1500
12 1500
13 1000

Req Output:
[EMPID] [AMT]
11 1500
12 2500
13 1500

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 02:02:58

Sorry i missed putting the alias


;With CTE
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY [DATE]) AS Seq
FROM [EMIMASTER] 
)
SELECT m.EMPID,SUM([AMOUNT]) AS [AMOUNT]
FROM
(
SELECT c1.EMPID,
c1.[CLIENTID]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.EMPID = c1.EMPID
AND c2.Seq = c1.Seq - 1
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM CTE
             WHERE EMPID <> c1.EMPID
             AND [DATE] BETWEEN c2.[DATE] AND c1.[DATE]
             AND CLIENTID <> c1.CLIENTID 
             AND CLIENTID <> c2.CLIENTID 
            )t
WHERE t.Cnt > 0 
OR c2.CLIENTID IS NULL
)m
INNER JOIN (
             SELECT [CLIENTID],SUM([AMOUNT]) AS [AMOUNT]
             FROM [EMICHILD]
             GROUP BY [CLIENTID]
            )n
ON n.[CLIENTID] = m.[CLIENTID]
GROUP BY m.EMPID

I'm not doing any check on Month so didnt understand what you're saying

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources