How to solve this SQLQuery Mentor - Bandi

Posted by Jayakumars under Sql Server on 4/24/2015 | Points: 10 | Views : 1676 | Status : [Member] [MVP] | Replies : 9

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

Bandi

I need sum amount my query i have paste sql query here then refer sample image i have attached thanks
for your mark an answer best.

I have post here sql server only. but i need sql server and mysql need this output thats helpful for me.

i have attached zip file img and script

CREATE TABLE [dbo].[ledger2](
[ledid] [int] IDENTITY(1,1) NOT NULL,
[loanno] [int] NULL,
[acctype] [varchar](10) NULL,
[ledgertype] [varchar](10) NULL,
[date] [datetime] NULL,
[Recipt] [numeric](18, 2) NULL,
[payment] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ledid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ledger1](
[ledid] [int] IDENTITY(1,1) NOT NULL,
[loanno] [int] NULL,
[acctype] [varchar](10) NULL,
[ledgertype] [varchar](10) NULL,
[date] [datetime] NULL,
[Recipt] [numeric](18, 2) NULL,
[payment] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ledid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ledger](
[ledid] [int] IDENTITY(1,1) NOT NULL,
[loanno] [int] NULL,
[acctype] [varchar](10) NULL,
[ledgertype] [varchar](10) NULL,
[date] [datetime] NULL,
[Recipt] [numeric](18, 2) NULL,
[payment] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ledid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ledger1](
[ledid] [int] IDENTITY(1,1) NOT NULL,
[loanno] [int] NULL,
[acctype] [varchar](10) NULL,
[ledgertype] [varchar](10) NULL,
[date] [datetime] NULL,
[Recipt] [numeric](18, 2) NULL,
[payment] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ledid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ledger](
[ledid] [int] IDENTITY(1,1) NOT NULL,
[loanno] [int] NULL,
[acctype] [varchar](10) NULL,
[ledgertype] [varchar](10) NULL,
[date] [datetime] NULL,
[Recipt] [numeric](18, 2) NULL,
[payment] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ledid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Attachment

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 25

0	Hi, I couldn't able find any attachment. Can you post here ony, not through images/attachments ? your .rar file is not in downloadble format in my machine/mobil.... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 4/24/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi I post above table script here data for that tables GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[ledger2] ON INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 1001, N'CCCC', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(5980.00 AS Numeric(18, 2)), CAST(6300.00 AS Numeric(18, 2))) INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 102, N'CCCC', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(9600.00 AS Numeric(18, 2)), CAST(3500.00 AS Numeric(18, 2))) INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 103, N'CCCC', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(6570.00 AS Numeric(18, 2)), CAST(4520.00 AS Numeric(18, 2))) SET IDENTITY_INSERT [dbo].[ledger2] OFF /**** Object: Table [dbo].[ledger1] Script Date: 04/24/2015 13:59:27 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[ledger1] ON INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 1001, N'BBBB', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(1000.00 AS Numeric(18, 2)), CAST(100.00 AS Numeric(18, 2))) INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 1002, N'BBBB', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(300.00 AS Numeric(18, 2)), CAST(200.00 AS Numeric(18, 2))) INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 1003, N'BBBB', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(450.00 AS Numeric(18, 2)), CAST(8500.00 AS Numeric(18, 2))) SET IDENTITY_INSERT [dbo].[ledger1] OFF /** Object: Table [dbo].[ledger] Script Date: 04/24/2015 13:59:27 ***/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[ledger] ON INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 100, N'AAA', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(1000.00 AS Numeric(18, 2)), CAST(200.00 AS Numeric(18, 2))) INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 101, N'AAA', N'CASH', CAST(0x0000A48F00000000 AS DateTime), CAST(6500.00 AS Numeric(18, 2)), CAST(7300.00 AS Numeric(18, 2))) INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 102, N'AAA', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(3500.00 AS Numeric(18, 2)), CAST(4200.00 AS Numeric(18, 2))) INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (4, 103, N'AAA', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(3200.00 AS Numeric(18, 2)), CAST(150.00 AS Numeric(18, 2))) INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (5, 104, N'AAA', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(4300.00 AS Numeric(18, 2)), CAST(7400.00 AS Numeric(18, 2))) SET IDENTITY_INSERT [dbo].[ledger] OFF Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer*. \| Alert Moderator

Hi
Bandi

I post above table script here data for that tables

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[ledger2] ON
INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 1001, N'CCCC', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(5980.00 AS Numeric(18, 2)), CAST(6300.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 102, N'CCCC', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(9600.00 AS Numeric(18, 2)), CAST(3500.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger2] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 103, N'CCCC', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(6570.00 AS Numeric(18, 2)), CAST(4520.00 AS Numeric(18, 2)))
SET IDENTITY_INSERT [dbo].[ledger2] OFF
/****** Object: Table [dbo].[ledger1] Script Date: 04/24/2015 13:59:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[ledger1] ON
INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 1001, N'BBBB', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(1000.00 AS Numeric(18, 2)), CAST(100.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 1002, N'BBBB', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(300.00 AS Numeric(18, 2)), CAST(200.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger1] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 1003, N'BBBB', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(450.00 AS Numeric(18, 2)), CAST(8500.00 AS Numeric(18, 2)))
SET IDENTITY_INSERT [dbo].[ledger1] OFF
/****** Object: Table [dbo].[ledger] Script Date: 04/24/2015 13:59:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON

SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[ledger] ON
INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (1, 100, N'AAA', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(1000.00 AS Numeric(18, 2)), CAST(200.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (2, 101, N'AAA', N'CASH', CAST(0x0000A48F00000000 AS DateTime), CAST(6500.00 AS Numeric(18, 2)), CAST(7300.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (3, 102, N'AAA', N'CASH', CAST(0x000073B100000000 AS DateTime), CAST(3500.00 AS Numeric(18, 2)), CAST(4200.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (4, 103, N'AAA', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(3200.00 AS Numeric(18, 2)), CAST(150.00 AS Numeric(18, 2)))
INSERT [dbo].[ledger] ([ledid], [loanno], [acctype], [ledgertype], [date], [Recipt], [payment]) VALUES (5, 104, N'AAA', N'ADJUST', CAST(0x000073B100000000 AS DateTime), CAST(4300.00 AS Numeric(18, 2)), CAST(7400.00 AS Numeric(18, 2)))
SET IDENTITY_INSERT [dbo].[ledger] OFF

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/24/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi But my output like this check this sample image file Download source file Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 4/24/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi check now I have attached check and let me know Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 25

0	Now, i found the attached source file. Unfortunately, I doesn't have permission to download files in my office machine.. Post/mail me back the expected result set Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 4/24/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi I already send mail to you for image file nothing just run table script with data here I need the sum separate Receipt and payment based cash and Adjust thats it Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 25

0	For single Ledger table, the below is the code for calculating SUMs based on CASH & ADJUSTment ledger types... One quick question is, How you will have THRIFT & LOAN ? Are they constant values? SELECT SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment ,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment ,SUM(recipt) TotalRecipt ,SUM(payment) TotalPayment FROM [ledger] /* ReciptAmountForCash ReciptAmountForAdjustment PaymentAmountForCash PaymentAmountForAdjustment TotalRecipt TotalPayment 11000.00 7500.00 11700.00 7550.00 18500.00 19250.00 / Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

For single Ledger table, the below is the code for calculating SUMs based on CASH & ADJUSTment ledger types...

One quick question is,
How you will have THRIFT & LOAN ? Are they constant values?

SELECT SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash

,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment

,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash

,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment

,SUM(recipt) TotalRecipt

,SUM(payment) TotalPayment

FROM [ledger]

/*
ReciptAmountForCash ReciptAmountForAdjustment PaymentAmountForCash PaymentAmountForAdjustment TotalRecipt TotalPayment
11000.00 7500.00 11700.00 7550.00 18500.00 19250.00
*/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/24/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi thanks your reply I have attached zip file here check them this question all response . I have 3 tables i need calulate 3 table like this i think we can use union all like can you update that query check my image plz. Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 4/27/2015 [Member] [MVP] Platinum | Points: 25

0	It looks like your expected output is for reporting purpose... May be this? SELECT acctype, SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment ,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment ,SUM(recipt) TotalRecipt ,SUM(payment) TotalPayment FROM [ledger] GROUP BY acctype union all SELECT acctype, SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment ,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment ,SUM(recipt) TotalRecipt ,SUM(payment) TotalPayment FROM [ledger1] GROUP BY acctype union all SELECT acctype, SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment ,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash ,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment ,SUM(recipt) TotalRecipt ,SUM(payment) TotalPayment FROM [ledger2] GROUP BY acctype Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

It looks like your expected output is for reporting purpose...

May be this?

SELECT acctype,
	SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment
	,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment
	,SUM(recipt) TotalRecipt
	,SUM(payment) TotalPayment
FROM [ledger]
GROUP BY acctype
union all
SELECT acctype,
	SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment
	,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment
	,SUM(recipt) TotalRecipt
	,SUM(payment) TotalPayment
FROM [ledger1]
GROUP BY acctype
union all
SELECT acctype,
	SUM( CASE WHEN Ledgertype='CASH' THEN Recipt END) ReciptAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Recipt END) ReciptAmountForAdjustment
	,SUM( CASE WHEN Ledgertype='CASH' THEN Payment END) PaymentAmountForCash
	,SUM( CASE WHEN Ledgertype='Adjust' THEN Payment END) PaymentAmountForAdjustment
	,SUM(recipt) TotalRecipt
	,SUM(payment) TotalPayment
FROM [ledger2]
GROUP BY acctype

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts