Find payment trend and payment after notice
-
Dears, I have a specific set of data which contains following information:
1. Accounts given notices (multiple time possible) about there balances
2. Payments made
Requirements:
- To find that how many customer paid (based on account_no) after or on notice date but before next notice date
- To find that after how many notices (Notice Counter) customer paid i.e. after 1st notice, 2nd notice and so on.
- How many customers didnot paid after notice
- How many customers made payments
Sample Table Creation:
CREATE TABLE [dbo].[Sample_Data](
[ACCOUNT_NO] [varchar](50) NULL,
[DATE] [varchar](50) NULL,
[FLAG] [varchar](50) NULL,
[AMOUNT] [varchar](50) NULL,
[NOTICE_COUNTER] [varchar](50) NULL
)
GO
INSERT INTO [Sample_Data] (ACCOUNT_NO, DATE, FLAG,AMOUNT,NOTICE_COUNTER)
VALUES
('304', '03/22/2019', 'Notice', '0', '1'),
('304', '03/22/2019', 'Payment', '500', '0'),
('304', '03/30/2019', 'Notice', '0', '2'),
('304', '03/30/2019', 'Notice', '0', '3'),
('304', '03/31/2019', 'Notice', '0', '4'),
('304', '03/31/2019', 'Payment', '500', '0'),
('528', '03/17/2019', 'Notice', '0', '1'),
('528', '03/17/2019', 'Payment', '240', '0'),
('528', '03/31/2019', 'Notice', '0', '2'),
('528', '03/31/2019', 'Notice', '0', '3'),
('528', '03/31/2019', 'Payment', '170', '0'),
('634', '03/09/2019', 'Notice', '0', '1'),
('634', '03/09/2019', 'Payment', '2500', '0'),
('724', '03/07/2019', 'Payment', '2500', '0'),
('724', '03/28/2019', 'Notice', '0', '1'),
('724', '03/28/2019', 'Payment', '3753.17', '0'),
('888', '03/09/2019', 'Notice', '0', '1'),
('888', '03/09/2019', 'Payment', '400', '0'),
('888', '03/19/2019', 'Notice', '0', '2'),
('888', '03/19/2019', 'Payment', '610', '0')
GO
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
Hi,
IF EXISTS(SELECT * FROM sys.tables WHERE name='Sample_Data') BEGIN
DROP TABLE Sample_Data
END
GO
CREATE TABLE [dbo].[Sample_Data](
[ACCOUNT_NO] [varchar](50) NULL,
[DATE] [varchar](50) NULL,
[FLAG] [varchar](50) NULL,
[AMOUNT] FLOAT NULL,
[NOTICE_COUNTER] [varchar](50) NULL
)
GO
INSERT INTO [Sample_Data] (ACCOUNT_NO, DATE, FLAG,AMOUNT,NOTICE_COUNTER)
VALUES
('304', '03/22/2019', 'Notice', '0', '1'),
('304', '03/22/2019', 'Payment', '500', '0'),
('304', '03/30/2019', 'Notice', '0', '2'),
('304', '03/30/2019', 'Notice', '0', '3'),
('304', '03/31/2019', 'Notice', '0', '4'),
('304', '03/31/2019', 'Payment', '500', '0'),
('528', '03/17/2019', 'Notice', '0', '1'),
('528', '03/17/2019', 'Payment', '240', '0'),
('528', '03/31/2019', 'Notice', '0', '2'),
('528', '03/31/2019', 'Notice', '0', '3'),
('528', '03/31/2019', 'Payment', '170', '0'),
('634', '03/09/2019', 'Notice', '0', '1'),
('634', '03/09/2019', 'Payment', '2500', '0'),
('724', '03/07/2019', 'Payment', '2500', '0'),
('724', '03/28/2019', 'Notice', '0', '1'),
('724', '03/28/2019', 'Payment', '3753.17', '0'),
('888', '03/09/2019', 'Notice', '0', '1'),
('888', '03/09/2019', 'Payment', '400', '0'),
('888', '03/19/2019', 'Notice', '0', '2'),
('888', '03/19/2019', 'Payment', '610', '0')
;WITH
GENEL AS(
SELECT ACCOUNT_NO,DATE,AMOUNT,NOTICE_COUNTER
FROM [Sample_Data]
WHERE ACCOUNT_NO=304
AND NOTICE_COUNTER>0),
DIGER AS (
SELECT ACCOUNT_NO,DATE,AMOUNT,NOTICE_COUNTER
FROM [Sample_Data]
WHERE ACCOUNT_NO=304
AND
NOTICE_COUNTER=0
),
SONUC AS (
SELECT GENEL.ACCOUNT_NO,GENEL.DATE,(GENEL.AMOUNT+DIGER.AMOUNT) AS TOPLAM,GENEL.NOTICE_COUNTER
FROM GENEL INNER JOIN DIGER ON
DIGER.ACCOUNT_NO=GENEL.ACCOUNT_NO
AND DIGER.DATE=GENEL.DATE)
SELECT
G.ACCOUNT_NO,
G.NOTICE_COUNTER,
CASE WHEN COALESCE(SONUC.TOPLAM,0)>0 THEN 'Yes' else 'No' end Payment_Made,
CASE WHEN COALESCE(SONUC.TOPLAM,0)>0 THEN SONUC.DATE else null end Payment_Date,
COALESCE(SONUC.TOPLAM,0) Payment
FROM GENEL AS G
OUTER APPLY
(
SELECT * FROM SONUC
WHERE ACCOUNT_NO=G.ACCOUNT_NO AND DATE=G.DATE
) AS SONUC
Allah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply