How to solve this SQLQuery Mentor - Bandi

Posted by Jayakumars under Sql Server on 4/24/2015 | Points: 10 | Views : 440 | 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


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

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

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

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

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

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