how to find result for this table

  • 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.

  • 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:

  • 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

  • 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:

  • 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

  • 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

  • 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:

  • 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 ...

  • 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

  • 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

  • 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..

  • 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

  • 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);

  • 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);

  • sir , i want to use '0' instead of null.here i couldn't use isNull().why?

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

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