Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 how to find result for this table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 04, 2013 10:26 PM
 Grasshopper Group: General Forum Members Last Login: Saturday, August 24, 2013 1:25 AM Points: 22, Visits: 36
 PaymentHistoryID paymentID Amount receivedType Reason1 11 5000.00 Dr ASD2 12 4000.00 Cr BG3 11 8000.00 Cr BG4 11 3000.00 Dr SD5 11 3000.00 Cr ASD6 13 50000.00 Cr ASD7 13 50000.00 Cr ASD8 13 1000.00 Cr ASD9 13 500.00 Cr ASDThis is my table………..pls help me at vanapandi@gmail.comPayment ID Amount(dr-cr) Reason11 5000-3000=2000 ASD11 0-8000=-8000 BG12 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 Group: General Forum Members Last Login: Monday, October 14, 2013 7:59 AM Points: 1,119, Visits: 1,149
 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 Group: General Forum Members Last Login: Monday, December 02, 2013 4:38 AM Points: 1,321, Visits: 1,501
 Here you go:`CREATE TABLE Payment ( PaymentHistoryID INT, paymentID INT, Amount DECIMAL(10, 2), receivedType CHAR(2), Reason VARCHAR(10) ); INSERT INTO PaymentVALUES (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, ReasonFROM PaymentGROUP 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 questionGuidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.comFollow me @Twitter
Post #1415577
 Posted Monday, February 04, 2013 11:16 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 14, 2013 7:59 AM Points: 1,119, Visits: 1,149
 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 PaymentVALUES (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, ReasonFROM PaymentGROUP 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 Group: General Forum Members Last Login: Monday, December 02, 2013 4:38 AM Points: 1,321, Visits: 1,501
 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 questionGuidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.comFollow me @Twitter
Post #1415604
 Posted Tuesday, February 05, 2013 1:09 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, August 24, 2013 1:25 AM Points: 22, Visits: 36
 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 Group: General Forum Members Last Login: Monday, October 14, 2013 7:59 AM Points: 1,119, Visits: 1,149
Post #1415632
 Posted Tuesday, February 05, 2013 2:05 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, August 24, 2013 1:25 AM Points: 22, Visits: 36
 CREATE TABLE Payment ( PaymentHistoryID INT, paymentID INT, Amount DECIMAL(10, 2), receivedType CHAR(2), Reason VARCHAR(10) ); INSERT INTO PaymentVALUES (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 800012 0 0 4000is it possible ...
Post #1415649
 Posted Tuesday, February 05, 2013 2:29 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 02, 2013 4:38 AM Points: 1,321, Visits: 1,501
 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 questionGuidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.comFollow me @Twitter