How to solve this SQLQuery Mentor - Bandi

Posted by Jayakumars under Sql Server on 4/24/2015 | Points: 10 | Views : 670 | Status : [Member] [MVP] | Replies : 9
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]

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response