Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to get the "Balanced" column value 'Y' if the Credit and Debit amt column matches and 'N' if they mismatch

Pymtid ID Name Type AMT
CHT 1 A CR 100
CHT1 1 A DR 100
BHT 2 B CR 100
BHT1 2 B DR 90
DHT 3 C CR 150
DHT1 3 C DR 150


Required Result :


Pymtid ID Name Type AMT Balanced
CHT 1 A CR 100 Y
CHT1 1 A DR 100 Y
BHT 2 B CR 100 N
BHT1 2 B DR 90 N
DHT 3 C CR 150 Y
DHT1 3 C DR 150 Y

What I have tried:

select 
case when 
(T.Amt = (select  P.Amt 
from BankData as p  
where P.Type = 'cr'
and p.ID = T.ID and t.Type = 'DR')) then 'Y' 
else 'N' end AS 'Balance', 
Name, *
 from BankData AS t
Posted
Updated 4-May-18 1:04am

Please try:

SQL
SELECT  b1.*, 
   CASE WHEN (b1.amt=b2.amt) THEN 'Y'
   ELSE 'N' END AS 'Balance'
FROM bankdata b1
JOIN bankdata b2
ON b1.id=b2.id AND (b1.type<>b2.type)


Explanation:
We need all the columns of bankdata (in the query alias b1) and one more column (called 'balance') with the value of 'Y' or 'N' depending that the record with the same id but distinct type has or not has the same amount.
Thus, we JOIN the bankdata table with it self (but now with alias b2). The condicion for the join is that the id in both alias are the same but that the value of the type are distinct (so, if the value of type in b1 is CR then in b2 must to be distinct of CR (that is DR), and if the value of type in b1 is DR in b2 must to be distinct of DR (that is CR)).
The value of the 'balance' column can be evaluated with a CASE depending that the amount would be equal or distinct in the two alias.
 
Share this answer
 
v2
Comments
DJPops 4-May-18 5:49am    
thanks above code works fine but having one problem bankdata has join with customer table so can i able to use "JOIN bankdata b2" after above customer join
Member 7870345 4-May-18 6:02am    
In theory there is no problem.
Can you send me the text of your query with the joins with customer?
DJPops 4-May-18 6:15am    
give 10 min , i will provide what exactly i am looking for
DJPops 4-May-18 6:29am    
Please find the bellow script for the bankdata and customer table ,

As show in bankdata there will be multiple time DR and CR type in which for same id we need
to check sum of dr = sum of CR .

Means if there is 2 records for DR and CR both against the id 2 then we need to do sum of DR amt and sum of CR amt and then balance column will have 'Y' or 'N' if the amt match or unmatch





USE [DATADB]
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')
In first place you can create a view that contains the sum of the transactions with a code like:
SQL
CREATE VIEW [dbo].[bankDataGrouped]
AS
SELECT      ID, Name, Type, CustId, SUM(Amt) AS amt
FROM         dbo.BankData
GROUP BY ID, Name, Type, CustId
ORDER BY ID, Name, Type, CustId

GO


(Then you can query 'bankDataGrouped' to obtein the sum of the records)

Then instead of use bankdata use bankDataGrouped with
SQL
SELECT  b1.*, 
   CASE WHEN (b1.amt=b2.amt) THEN 'Y'
   ELSE 'N' END AS 'Balance',
   customer.address
FROM bankdatagrouped b1
JOIN bankdatagrouped b2
	ON b1.id=b2.id AND (b1.type<>b2.type)
JOIN customer 
	ON b1.custid=customer.custid


you can JOIN both with bankdatagrouped and customer.

If this works please mark my anwser as 'anwser accepted'.
 
Share this answer
 
Comments
DJPops 4-May-18 7:08am    
let me try but i am working on finance project so creating procedures and views are not allowed , so i need single query that provides that , i am also trying to get in single query.
DJPops 4-May-18 9:45am    
Thanks For you Help , here is single query Ans .
https://stackoverflow.com/questions/50175031/get-balanced-column-value-as-y-if-both-rows-has-same-amt-and-n-if-not-in-s

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900