Get 'balanced' column value as 'Y' if both rows has same amt and 'N' if not in SQL query

debugcn Published at Dev

Tiger

I have number of CR records and DR records against the same id , I need to check sum of DR amount matches with the sum of CR amount for the same id if CR matches with DR then display Balanced column as "Y" else "N"

Required Result: Balanced" column value 'Y' if the Credit and Debit amt column matches and 'N' if they mismatch

Note: single query is needed for this:

enter image description here

As shown in above screen grab red highlighted result is required .

Script is given below :

USE [data]
GO
/****** Object:  Table [dbo].[BankData]    Script Date: 04-05-2018 3.54.46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BankData](
    [ID] [int] NOT NULL,
    [Name] [nchar](10) NULL,
    [Amt] [decimal](18, 0) NULL,
    [Type] [char](10) NULL,
    [TransId] [int] IDENTITY(1,1) NOT NULL,
    [CustId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 04-05-2018 3.54.46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
    [CustId] [int] NULL,
    [Address] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[BankData] ON 

INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (1, N'YASH      ', CAST(300 AS Decimal(18, 0)), N'DR        ', 1, 1)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (1, N'YASH      ', CAST(300 AS Decimal(18, 0)), N'CR        ', 2, 1)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (2, N'FALE      ', CAST(120 AS Decimal(18, 0)), N'DR        ', 3, 2)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (2, N'FALE      ', CAST(140 AS Decimal(18, 0)), N'CR        ', 4, 2)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (3, N'RAHUL     ', CAST(100 AS Decimal(18, 0)), N'CR        ', 5, 3)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (3, N'RAHUL     ', CAST(100 AS Decimal(18, 0)), N'DR        ', 6, 3)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (4, N'DINESH    ', CAST(900 AS Decimal(18, 0)), N'CR        ', 7, 4)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (4, N'DINESH    ', CAST(900 AS Decimal(18, 0)), N'DR        ', 8, 4)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (2, N'FALE      ', CAST(30 AS Decimal(18, 0)), N'DR        ', 9, 2)
INSERT [dbo].[BankData] ([ID], [Name], [Amt], [Type], [TransId], [CustId]) VALUES (2, N'FALE      ', CAST(130 AS Decimal(18, 0)), N'DR        ', 10, 2)
SET IDENTITY_INSERT [dbo].[BankData] OFF
INSERT [dbo].[Customer] ([CustId], [Address]) VALUES (1, N'Mumbai')
INSERT [dbo].[Customer] ([CustId], [Address]) VALUES (2, N'Delhi')
INSERT [dbo].[Customer] ([CustId], [Address]) VALUES (3, N'Pune')
INSERT [dbo].[Customer] ([CustId], [Address]) VALUES (4, N'Banglore')
INSERT [dbo].[Customer] ([CustId], [Address]) VALUES (5, N'Surat')

John Cappelletti

If 2012+, this is a small matter using the window function sum() over

Example

Select *
      ,Balanced = IIF(sum(Amt * IIF([Type]='CR',-1,1)) over (Partition By ID) =0,'Y','N')
 from [BankData]

Returns

ID  Name    Amt Type    TransId CustId  Balanced
1   YASH        300 DR          1   1   Y
1   YASH        300 CR          2   1   Y
2   FALE        120 DR          3   2   N
2   FALE        140 CR          4   2   N
2   FALE        30  DR          9   2   N
2   FALE        130 DR          10  2   N
3   RAHUL       100 CR          5   3   Y
3   RAHUL       100 DR          6   3   Y
4   DINESH      900 CR          7   4   Y
4   DINESH      900 DR          8   4   Y

Collected from the Internet

Please contact [email protected] to delete if infringement.