Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to find result for this table Expand / Collapse
Author
Message
Posted Monday, February 04, 2013 10:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:46 AM
Points: 22, Visits: 44
PaymentHistoryID paymentID Amount receivedType Reason
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
This is my table………..



pls help me at vanapandi@gmail.com





Payment ID Amount(dr-cr) Reason
11 5000-3000=2000 ASD
11 0-8000=-8000 BG
12 0-4000=-4000 BG
I need result like below table…how to write query for getting this table.
Post #1415570
Posted Monday, February 04, 2013 10:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 9:03 PM
Points: 1,128, Visits: 1,161
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
Post #1415573
Posted Monday, February 04, 2013 11:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415577
Posted Monday, February 04, 2013 11:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 9:03 PM
Points: 1,128, Visits: 1,161
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
Post #1415579
Posted Tuesday, February 05, 2013 12:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415604
Posted Tuesday, February 05, 2013 1:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:46 AM
Points: 22, Visits: 44
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
Post #1415626
Posted Tuesday, February 05, 2013 1:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 9:03 PM
Points: 1,128, Visits: 1,161
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
Post #1415632
Posted Tuesday, February 05, 2013 2:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:46 AM
Points: 22, Visits: 44
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 ...
Post #1415649
Posted Tuesday, February 05, 2013 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415654
Posted Tuesday, February 05, 2013 6:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415806
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse