how to find result for this table

Question

Post reply

how to find result for this table

vanapandi

SSC Enthusiast

Points: 120
More actions
February 4, 2013 at 10:26 pm

#270281

PaymentHistoryIDpaymentIDAmountreceivedTypeReason
1115000.00DrASD
2124000.00Cr BG
3118000.00Cr BG
4113000.00Dr SD
5113000.00CrASD
61350000.00CrASD
71350000.00CrASD
8131000.00CrASD
913500.00CrASD
This is my table………..
pls help me at vanapandi@gmail.com
Payment IDAmount(dr-cr)Reason
115000-3000=2000ASD
110-8000=-8000BG
120-4000=-4000BG
I need result like below table…how to write query for getting this table.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

Please provide the ddl ..

follow this link for best practices to post :

http://www.sqlservercentral.com/articles/Best+Practices/61537/

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

Here you go:

CREATE TABLE Payment

(

PaymentHistoryID INT,

paymentID INT,

Amount DECIMAL(10, 2),

receivedType CHAR(2),

Reason VARCHAR(10)

);

INSERT INTO Payment

VALUES

(1,11, 5000.00,'Dr','ASD'),

(2,12, 4000.00,'Cr', 'BG'),

(3,11, 8000.00,'Cr', 'BG'),

(4,11, 3000.00,'Dr', 'SD'),

(5,11, 3000.00,'Cr','ASD'),

(6,13,50000.00,'Cr','ASD'),

(7,13,50000.00,'Cr','ASD'),

(8,13, 1000.00,'Cr','ASD'),

(9,13, 500.00,'Cr','ASD')

SELECT paymentID,

Sum(CASE receivedType

WHEN 'Dr' THEN Amount

ELSE -1 * Amount

END) Amount,

Reason

FROM Payment

GROUP BY paymentID,

Reason

demonfox (2/4/2013)
Please provide the ddl ..
follow this link for best practices to post :
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Always provide DDL along with test data and expected result, as mentioned by demonfox...

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 3

Lokesh Vij (2/4/2013)
Here you go:
CREATE TABLE Payment
(
PaymentHistoryID INT,
paymentID INT,
Amount DECIMAL(10, 2),
receivedType CHAR(2),
Reason VARCHAR(10)
);
INSERT INTO Payment
VALUES
(1,11, 5000.00,'Dr','ASD'),
(2,12, 4000.00,'Cr', 'BG'),
(3,11, 8000.00,'Cr', 'BG'),
(4,11, 3000.00,'Dr', 'SD'),
(5,11, 3000.00,'Cr','ASD'),
(6,13,50000.00,'Cr','ASD'),
(7,13,50000.00,'Cr','ASD'),
(8,13, 1000.00,'Cr','ASD'),
(9,13, 500.00,'Cr','ASD')
SELECT paymentID,
Sum(CASE receivedType
WHEN 'Dr' THEN Amount
ELSE -1 * Amount
END) Amount,
Reason
FROM Payment
GROUP BY paymentID,
Reason
demonfox (2/4/2013)
Please provide the ddl ..
follow this link for best practices to post :
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Always provide DDL along with test data and expected result, as mentioned by demonfox...

And the best part ; Float hasa higher precedence over int , so multiplying Amount with -1 doesn't convert the data type of amount ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

demonfox (2/4/2013)
And the best part ; Float hasa higher precedence over int , so multiplying Amount with -1 doesn't convert the data type of amount ...

Hey demonfox..spot on with regards to today's Qotd 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

vanapandi SSC Enthusiast Points: 120 More actions · Answer 5

thank you sir,,,i got result...i want to improve my knowledge in sql server..can you suggest me..

i am also finally got result by using sub query...whether it is wrong way

select distinct

cr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0'),

Dr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),

Amount=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0')-

isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),

Reason from paymentHistory ph

group by Reason

demonfox SSCertifiable Points: 6289 More actions · Answer 6

vanapandi (2/5/2013)
thank you sir,,,i got result...i want to improve my knowledge in sql server..can you suggest me..
i am also finally got result by using sub query...whether it is wrong way
select distinct
cr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0'),
Dr=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),
Amount=isnull((select sum(receivedAmout) from paymentHistory where receivedType='Cr' and Reason=ph.Reason),'0')-
isnull((select sum(receivedAmout) from paymentHistory where receivedType='Dr' and Reason=ph.Reason),'0'),
Reason from paymentHistory ph
group by Reason

I don't think that query executes , does it ?

<< For improving the knowledge , you can start with reading msdn articles and experiment a lot ; that would give you some ideas..>>

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

vanapandi SSC Enthusiast Points: 120 More actions · Answer 7

CREATE TABLE Payment

(

PaymentHistoryID INT,

paymentID INT,

Amount DECIMAL(10, 2),

receivedType CHAR(2),

Reason VARCHAR(10)

);

INSERT INTO Payment

VALUES

(1,11, 5000.00,'Dr','ASD'),

(2,12, 4000.00,'Cr', 'BG'),

(3,11, 8000.00,'Cr', 'BG'),

(4,11, 3000.00,'Dr', 'SD'),

(5,11, 3000.00,'Cr','ASD'),

(6,13,50000.00,'Cr','ASD'),

(7,13,50000.00,'Cr','ASD'),

(8,13, 1000.00,'Cr','ASD'),

(9,13, 500.00,'Cr','ASD')

Now I got another problem..i need result like that..

paymentID SD ASD BG

-----------------------------------

11 -3000-2000 8000

12 0 0 4000

is it possible ...

Lokesh Vij SSChampion Points: 10836 More actions · Answer 8

Try to do this yourself. Here is the link for your reference:

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lokesh Vij SSChampion Points: 10836 More actions · Answer 9

This is how it can be done, using PIVOT function

SELECT paymentID, [ASD], [BG],[SD]

FROM (SELECT paymentID,

CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END Amount, Reason

FROM Payment) AS SourceTable

PIVOT ( Sum(Amount)

FOR Reason IN ([ASD], [BG], [SD]) ) AS PivotTable;

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

vanapandi SSC Enthusiast Points: 120 More actions · Answer 10

can i create dynamic headers....:-)

SELECT paymentID, [ASD], [BG],[SD]

FROM (SELECT paymentID,

CASE receivedType WHEN 'Dr' THEN Amount ELSE -1 * Amount END Amount, Reason

FROM Payment) AS SourceTable

PIVOT ( Sum(Amount)

FOR Reason IN ([ASD], [BG], [SD]) ) AS PivotTable; //Here can i put SELECT statement for Dynamic Headers..

Lokesh Vij SSChampion Points: 10836 More actions · Answer 11

Select statement cannot be used directly to create headers. Here is how it can be done by creating a list of columns and then creating a PIVOT string which include those columns

DECLARE @ColStr VARCHAR(500),

@SqlStr VARCHAR(MAX);

-- Creating Column String

WITH col_str AS

(SELECT DISTINCT reason FROM Payment)

SELECT @ColStr=ISNULL(@ColStr+',','')+Reason FROM col_str;

-- Creating pivot query

SET @SqlStr ='SELECT paymentID, '+@ColStr+'

FROM (SELECT paymentID,

CASE receivedType WHEN ''Dr'' THEN Amount ELSE -1 * Amount END Amount, Reason

FROM Payment) AS SourceTable

PIVOT ( Sum(Amount)

FOR Reason IN ('+@ColStr+') ) AS PivotTable '

-- Execute Query

EXEC (@SqlStr);

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

vanapandi SSC Enthusiast Points: 120 More actions · Answer 12

Here i got some kind of error..all columns filled with Null...why?.I couldn't find what..please help me sir,,,

/****** Object: Table [dbo].[paymentHistory] Script Date: 02/06/2013 13:56:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[paymentHistory](

[PaymentHistoryID] [int] IDENTITY(1,1) NOT NULL,

[paymentID] [int] NULL,

[receivedAmout] [money] NULL,

[receivedDate] [datetime] NULL,

[receivedType] [varchar](30) NULL,

[BankName] [varchar](100) NULL,

[Reason] [varchar](100) NULL,

[cheque_DD_no] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[paymentHistory] ON

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (1, 11, 5000.0000, CAST(0x0000A15900000000 AS DateTime), N'Dr', N'axis', N'ASD', N'12345')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (2, 12, 4000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N' BG', N'12345')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (3, 11, 8000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N' BG', N'12345')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (4, 11, 3000.0000, CAST(0x0000A15900000000 AS DateTime), N'Dr', N'axis', N' SD', N'12345')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (5, 11, 3000.0000, CAST(0x0000A15900000000 AS DateTime), N'Cr', N'axis', N'ASD', N'12345')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (6, 13, 50000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'axis valasai', N'ASD', N'123456')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (7, 13, 50000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'axis valasai', N'ASD', N'123456')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (8, 13, 1000.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'', N'ASD', N'')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (9, 13, 500.0000, CAST(0x0000A15B00000000 AS DateTime), N'Cr', N'', N'ASD', N'')

INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (10, 14, 50000.0000, CAST(0x0000A15D00000000 AS DateTime), N'Cr', N'axis', N' FDR', N'54567')

SET IDENTITY_INSERT [dbo].[paymentHistory] OFF

---My query---------

declare @col_Str varchar(max) ,

@SQl_Query varchar(max);

WITH col as (Select Distinct Reason from paymentHistory )

select @col_Str=isnull(@col_Str+',','')+reason from col;

set @SQl_Query ='select paymentid,'+@col_Str+'

from (select paymentID, CASE receivedType when ''Dr'' then receivedAmout else -1* receivedAmout End receivedAmout ,Reason

from paymentHistory)as sourcetable

PIVOT( sum(receivedAmout) for reason IN ('+@col_Str+') ) as Pivota '

exec (@SQl_Query);

vanapandi SSC Enthusiast Points: 120 More actions · Answer 13