Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, February 11, 2015 7:51 PM
Dear Guys,
I have tried for aging report query , but still my expected results is not come. Please go through below the table and script data for your reference. Please wirte a query for aging report.
USE [SAMPLES]
GO
/****** Object: Table [dbo].[NEW_DCB_REPORT] Script Date: 02/12/2015 01:19:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[NEW_DCB_REPORT](
[MFDB_LedgerNumber] [int] IDENTITY(1,1) NOT NULL,
[MFDB_TxnDate] [datetime] NOT NULL,
[MFDB_StateId] [int] NOT NULL,
[MFDB_RegionId] [int] NOT NULL,
[MFDB_BranchId] [int] NOT NULL,
[MFDB_SHGId] [int] NOT NULL,
[MFDB_SHGName] [varchar](75) NOT NULL,
[MFDB_ShgMemberId] [int] NOT NULL,
[MFDB_MemberName] [varchar](50) NOT NULL,
[MFDB_VillageId] [int] NOT NULL,
[MFDB_LoanNumber] [int] NOT NULL,
[MFDB_LoanAmount] [int] NOT NULL,
[MFDB_DisbursedDate] [datetime] NOT NULL,
[MFDB_DemandDate] [datetime] NULL,
[MFDB_CollectionDate] [datetime] NULL,
[MFDB_InstallmentNumber] [int] NOT NULL,
[MFDB_ArrearPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_ArrearInterest] [numeric](18, 2) NOT NULL,
[MFDB_DemandPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_DemandInterest] [numeric](18, 2) NOT NULL,
[MFDB_TotDemandPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_TotDemandInterest] [numeric](18, 2) NOT NULL,
[MFDB_AdvOBPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_AdvOBInterest] [numeric](18, 2) NOT NULL,
[MFDB_CollectedPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_CollectedInterest] [numeric](18, 2) NOT NULL,
[MFDB_AdvCBPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_AdvCBInterest] [numeric](18, 2) NOT NULL,
[MFDB_BalancePrincipal] [numeric](18, 2) NOT NULL,
[MFDB_BalanceInterest] [numeric](18, 2) NOT NULL,
[MFDB_OS_Pri] [numeric](18, 2) NOT NULL,
[MFDB_OS_Int] [numeric](18, 2) NOT NULL,
[MFDB_RepaymentPercentage] [int] NOT NULL,
[MFDB_LoanSchemeId] [int] NOT NULL,
[MFDB_LActivityId] [int] NOT NULL,
[MFDB_LActSpecId] [int] NOT NULL,
[MFDB_FundAgencyId] [int] NOT NULL,
[MFDB_LSourceId] [int] NOT NULL,
[MFDB_Months] [varchar](15) NOT NULL,
[MFDB_PanchayatId] [int] NOT NULL,
[MFDB_PanchayatName] [varchar](100) NOT NULL,
[MFDB_VillageName] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [SAMPLES];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT [dbo].[NEW_DCB_REPORT] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[NEW_DCB_REPORT]([MFDB_LedgerNumber], [MFDB_TxnDate], [MFDB_StateId], [MFDB_RegionId], [MFDB_BranchId], [MFDB_SHGId], [MFDB_SHGName], [MFDB_ShgMemberId], [MFDB_MemberName], [MFDB_VillageId], [MFDB_LoanNumber], [MFDB_LoanAmount], [MFDB_DisbursedDate], [MFDB_DemandDate], [MFDB_CollectionDate], [MFDB_InstallmentNumber], [MFDB_ArrearPrincipal], [MFDB_ArrearInterest], [MFDB_DemandPrincipal], [MFDB_DemandInterest], [MFDB_TotDemandPrincipal], [MFDB_TotDemandInterest], [MFDB_AdvOBPrincipal], [MFDB_AdvOBInterest], [MFDB_CollectedPrincipal], [MFDB_CollectedInterest], [MFDB_AdvCBPrincipal], [MFDB_AdvCBInterest], [MFDB_BalancePrincipal], [MFDB_BalanceInterest], [MFDB_OS_Pri], [MFDB_OS_Int], [MFDB_RepaymentPercentage], [MFDB_LoanSchemeId], [MFDB_LActivityId], [MFDB_LActSpecId], [MFDB_FundAgencyId], [MFDB_LSourceId], [MFDB_Months], [MFDB_PanchayatId], [MFDB_PanchayatName], [MFDB_VillageName])
SELECT 18, '20140422 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', NULL, NULL, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Apr - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 162, '20140521 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140521 00:00:00.000', NULL, 1, 0.00, 0.00, 0.00, 150.00, 0.00, 150.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 150.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'May - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 13932, '20140621 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140621 00:00:00.000', NULL, 2, 0.00, 150.00, 909.00, 150.00, 909.00, 300.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 909.00, 300.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Jun - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 32505, '20140721 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140721 00:00:00.000', NULL, 3, 909.00, 300.00, 909.00, 136.00, 1818.00, 436.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 1818.00, 436.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Jul - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 62524, '20140821 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140821 00:00:00.000', NULL, 4, 1818.00, 436.00, 909.00, 123.00, 2727.00, 559.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 2727.00, 559.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Aug - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 100871, '20140921 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140921 00:00:00.000', NULL, 5, 2727.00, 559.00, 909.00, 109.00, 3636.00, 668.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 3636.00, 668.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Sep - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 153679, '20141021 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20141021 00:00:00.000', NULL, 6, 3636.00, 668.00, 909.00, 96.00, 4545.00, 764.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 4545.00, 764.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Oct - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 203840, '20141119 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', NULL, '20141119 00:00:00.000', 2, 4545.00, 764.00, 0.00, 0.00, 4545.00, 764.00, 0.00, 0.00, 10000.00, 150.00, 5455.00, 0.00, 0.00, 614.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Nov - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 291259, '20141221 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20141221 00:00:00.000', NULL, 8, 0.00, 614.00, 909.00, 68.00, 909.00, 682.00, 5455.00, 0.00, 0.00, 0.00, 4546.00, 0.00, 0.00, 682.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Dec - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 363176, '20150121 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20150121 00:00:00.000', NULL, 9, 0.00, 682.00, 909.00, 55.00, 909.00, 737.00, 4546.00, 0.00, 0.00, 0.00, 3637.00, 0.00, 0.00, 737.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Jan - 15', 2927, N'PALI', N'PALI'
COMMIT;
RAISERROR (N'[dbo].[NEW_DCB_REPORT]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[NEW_DCB_REPORT] OFF;
I expected results as below:
Report As On Date: 30-10-2014
S.No. SHG NAME MEMBER NAME <=30 31-60 61-90 91-120 91-180 181-365 365 - 689 Above 2 Yrs
1 MAHALAXMI-5514 SUNITA 0 909 1818 2727 3636 4545 0 0
Report As On Date: |
30-10-2014 |
|||||||||
S.No. |
SHG NAME |
MEMBER NAME |
<=30 |
31-60 |
61-90 |
91-120 |
91-180 |
181-365 |
365 - 689 |
Above 2 Yrs |
1 |
MAHALAXMI-5514 |
SUNITA |
0 |
909 |
1818 |
2727 |
3636 |
4545 |
0 |
0 |
Wednesday, February 11, 2015 8:55 PM ✅Answered
DECLARE @NEW_DCB_REPORT TABLE (
[MFDB_LedgerNumber] [int] NOT NULL,
[MFDB_TxnDate] [datetime] NOT NULL,
[MFDB_StateId] [int] NOT NULL,
[MFDB_RegionId] [int] NOT NULL,
[MFDB_BranchId] [int] NOT NULL,
[MFDB_SHGId] [int] NOT NULL,
[MFDB_SHGName] [varchar](75) NOT NULL,
[MFDB_ShgMemberId] [int] NOT NULL,
[MFDB_MemberName] [varchar](50) NOT NULL,
[MFDB_VillageId] [int] NOT NULL,
[MFDB_LoanNumber] [int] NOT NULL,
[MFDB_LoanAmount] [int] NOT NULL,
[MFDB_DisbursedDate] [datetime] NOT NULL,
[MFDB_DemandDate] [datetime] NULL,
[MFDB_CollectionDate] [datetime] NULL,
[MFDB_InstallmentNumber] [int] NOT NULL,
[MFDB_ArrearPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_ArrearInterest] [numeric](18, 2) NOT NULL,
[MFDB_DemandPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_DemandInterest] [numeric](18, 2) NOT NULL,
[MFDB_TotDemandPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_TotDemandInterest] [numeric](18, 2) NOT NULL,
[MFDB_AdvOBPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_AdvOBInterest] [numeric](18, 2) NOT NULL,
[MFDB_CollectedPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_CollectedInterest] [numeric](18, 2) NOT NULL,
[MFDB_AdvCBPrincipal] [numeric](18, 2) NOT NULL,
[MFDB_AdvCBInterest] [numeric](18, 2) NOT NULL,
[MFDB_BalancePrincipal] [numeric](18, 2) NOT NULL,
[MFDB_BalanceInterest] [numeric](18, 2) NOT NULL,
[MFDB_OS_Pri] [numeric](18, 2) NOT NULL,
[MFDB_OS_Int] [numeric](18, 2) NOT NULL,
[MFDB_RepaymentPercentage] [int] NOT NULL,
[MFDB_LoanSchemeId] [int] NOT NULL,
[MFDB_LActivityId] [int] NOT NULL,
[MFDB_LActSpecId] [int] NOT NULL,
[MFDB_FundAgencyId] [int] NOT NULL,
[MFDB_LSourceId] [int] NOT NULL,
[MFDB_Months] [varchar](15) NOT NULL,
[MFDB_PanchayatId] [int] NOT NULL,
[MFDB_PanchayatName] [varchar](100) NOT NULL,
[MFDB_VillageName] [varchar](100) NOT NULL
)
INSERT INTO @NEW_DCB_REPORT([MFDB_LedgerNumber], [MFDB_TxnDate], [MFDB_StateId], [MFDB_RegionId], [MFDB_BranchId], [MFDB_SHGId], [MFDB_SHGName], [MFDB_ShgMemberId], [MFDB_MemberName], [MFDB_VillageId], [MFDB_LoanNumber], [MFDB_LoanAmount], [MFDB_DisbursedDate], [MFDB_DemandDate], [MFDB_CollectionDate], [MFDB_InstallmentNumber], [MFDB_ArrearPrincipal], [MFDB_ArrearInterest], [MFDB_DemandPrincipal], [MFDB_DemandInterest], [MFDB_TotDemandPrincipal], [MFDB_TotDemandInterest], [MFDB_AdvOBPrincipal], [MFDB_AdvOBInterest], [MFDB_CollectedPrincipal], [MFDB_CollectedInterest], [MFDB_AdvCBPrincipal], [MFDB_AdvCBInterest], [MFDB_BalancePrincipal], [MFDB_BalanceInterest], [MFDB_OS_Pri], [MFDB_OS_Int], [MFDB_RepaymentPercentage], [MFDB_LoanSchemeId], [MFDB_LActivityId], [MFDB_LActSpecId], [MFDB_FundAgencyId], [MFDB_LSourceId], [MFDB_Months], [MFDB_PanchayatId], [MFDB_PanchayatName], [MFDB_VillageName])
SELECT 18, '20140422 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', NULL, NULL, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Apr - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 162, '20140521 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140521 00:00:00.000', NULL, 1, 0.00, 0.00, 0.00, 150.00, 0.00, 150.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 150.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'May - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 13932, '20140621 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140621 00:00:00.000', NULL, 2, 0.00, 150.00, 909.00, 150.00, 909.00, 300.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 909.00, 300.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Jun - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 32505, '20140721 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140721 00:00:00.000', NULL, 3, 909.00, 300.00, 909.00, 136.00, 1818.00, 436.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 1818.00, 436.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Jul - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 62524, '20140821 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140821 00:00:00.000', NULL, 4, 1818.00, 436.00, 909.00, 123.00, 2727.00, 559.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 2727.00, 559.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Aug - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 100871, '20140921 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20140921 00:00:00.000', NULL, 5, 2727.00, 559.00, 909.00, 109.00, 3636.00, 668.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 3636.00, 668.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Sep - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 153679, '20141021 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20141021 00:00:00.000', NULL, 6, 3636.00, 668.00, 909.00, 96.00, 4545.00, 764.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 4545.00, 764.00, 10000.00, 1051.00, 0, 3, 4, 77, 3, 2, N'Oct - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 203840, '20141119 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', NULL, '20141119 00:00:00.000', 2, 4545.00, 764.00, 0.00, 0.00, 4545.00, 764.00, 0.00, 0.00, 10000.00, 150.00, 5455.00, 0.00, 0.00, 614.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Nov - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 291259, '20141221 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20141221 00:00:00.000', NULL, 8, 0.00, 614.00, 909.00, 68.00, 909.00, 682.00, 5455.00, 0.00, 0.00, 0.00, 4546.00, 0.00, 0.00, 682.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Dec - 14', 2927, N'PALI', N'PALI' UNION ALL
SELECT 363176, '20150121 00:00:00.000', 6, 16, 67, 5514, N'MAHALAXMI-5514', 66735, N'SUNITA', 4624, 1, 10000, '20140422 00:00:00.000', '20150121 00:00:00.000', NULL, 9, 0.00, 682.00, 909.00, 55.00, 909.00, 737.00, 4546.00, 0.00, 0.00, 0.00, 3637.00, 0.00, 0.00, 737.00, 0.00, 901.00, 0, 3, 4, 77, 3, 2, N'Jan - 15', 2927, N'PALI', N'PALI'
DECLARE @ReportDate Date
SET @ReportDate = '2014-10-30'
SELECT
t1.[MFDB_LoanNumber]
, t1.[MFDB_SHGName]
, t1.[MFDB_MemberName]
, ISNULL(t1.[<=30], 0) AS [<=30]
, ISNULL(t2.[31-60], 0) AS [31-60]
, ISNULL(t3.[61-90], 0) AS [61-90]
, ISNULL(t4.[91-120], 0) AS [91-120]
, ISNULL(t5.[121-180], 0) AS [121-180]
, ISNULL(t6.[181-365], 0) AS [181-365]
, ISNULL(t7.[366-689], 0) AS [366-689]
, ISNULL(t8.[Above 2 Yrs], 0) AS [Above 2 Yrs]
FROM
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [<=30]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 30
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t1
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [31-60]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 31 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 60
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t2 ON t2.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t2.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t2.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [61-90]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 61 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 90
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t3 ON t3.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t3.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t3.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [91-120]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 91 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 120
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t4 ON t4.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t4.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t4.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [121-180]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 121 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 180
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t5 ON t5.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t5.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t5.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [181-365]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 181 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 365
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t6 ON t6.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t6.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t6.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [366-689]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 366 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 689
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t7 ON t7.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t7.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t7.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], COUNT(*) AS [Above 2 Yrs]
FROM @NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 690
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName]
) AS t8 ON t8.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t8.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t8.[MFDB_MemberName] = t1.[MFDB_MemberName]
A Fan of SSIS, SSRS and SSAS
Wednesday, February 11, 2015 9:24 PM
Help your reader understand your problem. First, get rid of all the columns in your sample table that have no use in your query. Next, post your existing query (even if nor "working"). Then describe the logic you use to determine "what" is aged and how you determine "age". Your table has multiple date columns, so which one is used? And it does not help to use column names or labels in your output that do not exist in your table; that only leads to more confusion. Given the desired output you posted, I guessing that you are summing some column but I have no idea which one. Below is an example that can get you started. Rather than generating a true sum, I use sum to simply count the number of rows that should be included in any particular age group.
set nocount on;
declare @asof_date date;
declare @test table (id int identity(1, 1) not null, name varchar(10) not null, tran_date date not null);
insert @test (name, tran_date) values ('xxx', '20150101'), ('xxx', '20141222'),
('yyy', '20141011'), ('xxx', '20141115'), ('yyy', '20150210')
;
set @asof_date = CURRENT_TIMESTAMP;
with cte as (select name, tran_date, datediff(day, tran_date, @asof_date) as ddif from @test)
select * from cte order by name, tran_date;
with cte as (
select name, tran_date, datediff(day, tran_date, @asof_date) as ddif from @test
where tran_date <= @asof_date
)
select name, @asof_date as [as of],
sum(case when ddif <= 30 then 1 else 0 end) as [<=30],
sum(case when ddif > 30 and ddif <= 60 then 1 else 0 end) as [31 to 60],
sum(case when ddif > 60 then 1 else 0 end) as [everything else]
from cte
group by name
order by name
;
Wednesday, February 11, 2015 9:25 PM
Hi Ravi_D,
In the below query, I have used MFDB_DisbursedDAte column to calculate the aging but i am not sure if that needs to todays date or some other column. Anway the concept is same. Just replace with required column and do the rest.
I used a Temp table. Replace it with your own table. Thanks
Select MFDB_SHGId, MFDB_SHGName, Sum([<30]) '<30', Sum([31-60]) '31-60', Sum([60-90]) '60-90' from (
select MFDB_SHGId, MFDB_SHGName, (case
when DAteDIFF(D,MFDB_DisbursedDate,MFDB_txnDate)<=30 Then
MFDB_ArrearPrincipal
ELSE
0
End) '<30',
(case
when DAteDIFF(D,MFDB_DisbursedDate,MFDB_txnDate)>30 and DAteDIFF(D,MFDB_DisbursedDate,MFDB_txnDate)<60 Then
MFDB_ArrearPrincipal
ELSE
0
End) '31-60',
(case
when DAteDIFF(D,MFDB_DisbursedDate,MFDB_txnDate)>60 and DAteDIFF(D,MFDB_DisbursedDate,MFDB_txnDate)<90 Then
MFDB_ArrearPrincipal
ELSE
0
End) '60-90'
from #NEW_DCB_REPORT
group by MFDB_SHGId, MFDB_SHGName,MFDB_txnDate,MFDB_DisbursedDate,MFDB_ArrearPrincipal) Report
Group by MFDB_SHGId, MFDB_SHGName
Wednesday, February 11, 2015 9:27 PM
Thanks for your replay, I have checked your query it seems nearly ok, Please find my script which i tried above for the same.
DECLARE @i_AsOnDate AS DATETIME = '2014-09-30'
;WITH CA1 AS
(
SELECT ROW_NUMBER() OVER(Order By DR.MFDB_SHGName,DR.MFDB_MemberName) AS SerialNumber,
DR.MFDB_SHGId AS ShgId,
DR.MFDB_SHGName AS ShgName,
DR.MFDB_ShgMemberId AS MemId,
DR.MFDB_MemberName AS MemName,
ISNULL((SELECT CAST(SUM(LD.MFDB_StdPrincipal) AS BIGINT )
FROM MFDB_LOAN_DISBURSEMENT LD
WHERE LD.MFDB_SHGId = DR.MFDB_SHGId
AND LD.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND LD.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND LD.MFDB_ApprovalStatus = 1
AND DATEDIFF(DAY,@i_AsOnDate,LD.MFDB_CollectionDate) > 0 ),0) AS YETDUE,
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) >= 0
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 30
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '0-30',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 60
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 30
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '31-60',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 90
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 60
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '61-90',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 120
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 90
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '91-120',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 180
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 120
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '121-180',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 365
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 180
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '181-365',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 689
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 365
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS '366-689',
ISNULL((SELECT TOP 1 CAST(ND.MFDB_BalancePrincipal AS BIGINT )
FROM dbo.MFDB_NEW_DCB_REPORT ND
WHERE ND.MFDB_SHGId = DR.MFDB_SHGId
AND ND.MFDB_ShgMemberId = DR.MFDB_ShgMemberId
AND ND.MFDB_LoanNumber = DR.MFDB_LoanNumber
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) <= 730
AND DATEDIFF(Day,ND.MFDB_TxnDate,@i_AsOnDate) > 689
ORDER BY ND.MFDB_LedgerNumber DESC),0) AS 'Above_2_Yrs'
FROM DBO.MFDB_NEW_DCB_REPORT DR
WHERE DR.MFDB_BranchId = 67
AND DR.MFDB_SHGId IN (5514)
AND DR.MFDB_ShgMemberId = 66735
AND DATEDIFF(DAY,MFDB_TxnDate,@i_AsOnDate) >= 0
)
SELECT * FROM CA1
results after the execution of the script:
SerialNumber |
ShgId |
ShgName |
MemId |
MemName |
YETDUE |
0-30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above_2_Yrs |
3 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
2 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
6 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
4 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
1 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
5 |
5514 |
MAHALAXMI-5514 |
#### |
SUNITA |
10000 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
0 |
But I expected results is:
s.n |
ShgId |
ShgName |
MemId |
MemName |
YETDUE |
0-30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above_2_Yrs |
1 |
5514 |
MAHALAXMI-5514 |
66735 |
SUNITA |
10000 |
|
909 |
1818 |
2727 |
3636 |
0 |
0 |
0 |
I want only one record per member with aging .. please advice me for further action.
Wednesday, February 11, 2015 9:35 PM
SELECT [MFDB_LoanNumber], [MFDB_MemberName],
MAX(CASE WHEN [MFDB_InstallmentNumber]<=2 THEN [MFDB_ArrearPrincipal] END) [<=30]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=3 THEN [MFDB_ArrearPrincipal] END) [31-60]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=4 THEN [MFDB_ArrearPrincipal] END) [61-60]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=5 THEN [MFDB_ArrearPrincipal] END) [91-120]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=6 THEN [MFDB_ArrearPrincipal] END) [121-180]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=7 THEN [MFDB_ArrearPrincipal] END) [181-365]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=8 THEN [MFDB_ArrearPrincipal] END) [366-689]
,MAX(CASE WHEN [MFDB_InstallmentNumber]=9 THEN [MFDB_ArrearPrincipal] END) [Above 2 Years]
FROM [dbo].[NEW_DCB_REPORT]
GROUP BY [MFDB_LoanNumber], [MFDB_MemberName]
Wednesday, February 11, 2015 9:43 PM
HI ,
I have modified your script it come nearly ok, but it has been return reverse aging .
Please find the script which is i modified:
DECLARE @ReportDate Date
SET @ReportDate = '2014-10-30'
SELECT
t1.[MFDB_LoanNumber]
, t1.[MFDB_SHGName]
, t1.[MFDB_MemberName]
, ISNULL(t1.[<=30], 0) AS [<=30]
, ISNULL(t2.[31-60], 0) AS [31-60]
, ISNULL(t3.[61-90], 0) AS [61-90]
, ISNULL(t4.[91-120], 0) AS [91-120]
, ISNULL(t5.[121-180], 0) AS [121-180]
, ISNULL(t6.[181-365], 0) AS [181-365]
, ISNULL(t7.[366-689], 0) AS [366-689]
, ISNULL(t8.[Above 2 Yrs], 0) AS [Above 2 Yrs]
FROM
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [<=30]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 30
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t1
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [31-60]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 31 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 60
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t2 ON t2.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t2.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t2.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [61-90]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 61 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 90
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t3 ON t3.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t3.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t3.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [91-120]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 91 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 120
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t4 ON t4.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t4.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t4.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [121-180]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 121 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 180
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t5 ON t5.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t5.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t5.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [181-365]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 181 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 365
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t6 ON t6.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t6.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t6.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [366-689]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 366 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 689
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t7 ON t7.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t7.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t7.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [Above 2 Yrs]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 690
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t8 ON t8.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t8.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t8.[MFDB_MemberName] = t1.[MFDB_MemberName]
results:
MFDB_LoanNumber |
MFDB_SHGName |
MFDB_MemberName |
<=30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above 2 Yrs |
1 |
MAHALAXMI-5514 |
SUNITA |
0 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
But I the same value must be reverse the values, I expected results is:
MFDB_LoanNumber |
MFDB_SHGName |
MFDB_MemberName |
<=30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above 2 Yrs |
1 |
MAHALAXMI-5514 |
SUNITA |
0 |
909 |
1818 |
2727 |
3636 |
0 |
0 |
0 |
pls help me almost 95% completed the script , please advice me for further steps.
Wednesday, February 11, 2015 9:45 PM
I have modified your script it come nearly ok, but it has been return reverse aging .
Please find the script which is i modified:
DECLARE @ReportDate Date
SET @ReportDate = '2014-10-30'
SELECT
t1.[MFDB_LoanNumber]
, t1.[MFDB_SHGName]
, t1.[MFDB_MemberName]
, ISNULL(t1.[<=30], 0) AS [<=30]
, ISNULL(t2.[31-60], 0) AS [31-60]
, ISNULL(t3.[61-90], 0) AS [61-90]
, ISNULL(t4.[91-120], 0) AS [91-120]
, ISNULL(t5.[121-180], 0) AS [121-180]
, ISNULL(t6.[181-365], 0) AS [181-365]
, ISNULL(t7.[366-689], 0) AS [366-689]
, ISNULL(t8.[Above 2 Yrs], 0) AS [Above 2 Yrs]
FROM
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [<=30]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 30
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t1
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [31-60]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 31 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 60
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t2 ON t2.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t2.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t2.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [61-90]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 61 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 90
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t3 ON t3.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t3.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t3.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [91-120]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 91 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 120
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t4 ON t4.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t4.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t4.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [121-180]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 121 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 180
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t5 ON t5.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t5.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t5.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [181-365]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 181 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 365
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t6 ON t6.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t6.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t6.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [366-689]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 366 AND DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 689
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t7 ON t7.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t7.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t7.[MFDB_MemberName] = t1.[MFDB_MemberName]
LEFT JOIN
(
SELECT TOP 1 [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [Above 2 Yrs]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) >= 690
AND MFDB_BranchId = 67
AND MFDB_SHGId IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
ORDER BY MFDB_LedgerNumber DESC
) AS t8 ON t8.[MFDB_LoanNumber] = t1.[MFDB_LoanNumber] AND t8.[MFDB_SHGName] = t1.[MFDB_SHGName] AND t8.[MFDB_MemberName] = t1.[MFDB_MemberName]
results:
MFDB_LoanNumber |
MFDB_SHGName |
MFDB_MemberName |
<=30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above 2 Yrs |
1 |
MAHALAXMI-5514 |
SUNITA |
0 |
3636 |
2727 |
1818 |
909 |
0 |
0 |
0 |
But I the same value must be reverse the values, I expected results is:
MFDB_LoanNumber |
MFDB_SHGName |
MFDB_MemberName |
<=30 |
31-60 |
61-90 |
91-120 |
121-180 |
181-365 |
366-689 |
Above 2 Yrs |
1 |
MAHALAXMI-5514 |
SUNITA |
0 |
909 |
1818 |
2727 |
3636 |
0 |
0 |
0 |
pls help me almost 95% completed the script , please advice me for further steps.
Wednesday, February 11, 2015 10:17 PM
The script is okay to me. But you need to make sure the conditions you added are correct, i.e., MFDB_BranchId = 67.
A Fan of SSIS, SSRS and SSAS
Wednesday, February 11, 2015 10:28 PM
Can you run the subquery for t1 and tell me what is the output (You do not need TOP 1 and ORDER BY in each subquery):
SELECT [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName], CAST(SUM(MFDB_BalancePrincipal) AS BIGINT ) AS [<=30]
FROM MFDB_NEW_DCB_REPORT
WHERE DATEDIFF(DAY, [MFDB_TxnDate], @ReportDate) <= 30
AND MFDB_BranchId
= 67
AND MFDB_SHGId
IN (5514)
AND MFDB_ShgMemberId = 66735
GROUP BY [MFDB_LoanNumber], [MFDB_SHGName], [MFDB_MemberName],MFDB_LedgerNumber
A Fan of SSIS, SSRS and SSAS