How to write SQL Query to get desire output-3

SQL Interview Questions 7 comments

Hello friends, i got one interesting query from some source which i thought to share with you. I want output as follows, how can you write query for that !

We have two tables as bellow:

Query output-3
Tables ACCT and TRANS

 

OUTPUT:- I want total as per credit and debit value from table.

SQL Query to get desire output-3
SQL Query to get desire output-3

Now first try yourself without looking for solution. You will learn something new if you try yourself, otherwise i will definitely help you out 🙂  Following is my query which will return above OUTPUT:

CREATE TABLE #ACCT ( userid int,username varchar(50))

INSERT #ACCT ([userid], [username]) VALUES (1, 'AMI')
INSERT #ACCT ([userid], [username]) VALUES (2, 'Aesha')
INSERT #ACCT ([userid], [username]) VALUES (3, 'Seema')


CREATE TABLE #Trans ( transactionid int,transactiondate datetime,CreditDebit varchar(50),userid int)

INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (1, '2015-12-08 18:54:11.793', '10000', 1)
INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (2, '2015-12-08 18:54:43.030', '-5000', 1)
INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (3, '2015-12-08 18:55:14.293', '2000', 2)
INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (4, '2015-12-08 18:55:49.053', '3000', 3)
INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (5, '2015-12-08 18:55:49.053', '2000', 1)

GO
with results (username, Credit, Dedit,transactionid)
AS (
select A.username,
case when sign(T.CreditDebit)=1 then CreditDebit else ''end 'Credit',
case when sign(T.CreditDebit)=-1 then CreditDebit else ''end 'Dedit',
T.transactionid
from #ACCT  A inner join #Trans T on A.userid=T.userid )
select username,credit,replace(Dedit,'-','')as Dedit,
SUM(CASE WHEN sign(credit)=1 THEN cast(credit as int)
           ELSE cast( Dedit  as int)
           END )
           OVER (PARTITION BY username ORDER BY transactionid ) As Total
   from results

You can also try your own and forward it to me. Try any built in function or anything, but try to solve it with different query. I would like to see different queries for one output.

Try your own queries in Sql Fiddle and share it as comment.

Don’t forget to share your views.

Thanks 🙂

7 comments

  1. Nice! Couple of things.. The CReditDebit is an Amount field and it should be in MONEY not VARCHAR. Replacing -1 is not a good solution.I have modified the script and logic a little bit..

    Reference http://sqlindia.com/calculate-cumulative-sum-of-previous-rows-sql-server/

    CREATE TABLE #ACCT ( userid int,username varchar(50))

    INSERT #ACCT ([userid], [username]) VALUES (1, ‘AMI’)
    INSERT #ACCT ([userid], [username]) VALUES (2, ‘Aesha’)
    INSERT #ACCT ([userid], [username]) VALUES (3, ‘Seema’)

    –Changed the CreditDebit datatype to MONEY. VARCHAR is not a real time scenario
    CREATE TABLE #Trans ( transactionid int,transactiondate datetime,CreditDebit money,userid int)

    INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (1, ‘2015-12-08 18:54:11.793’, ‘10000’, 1)
    INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (2, ‘2015-12-08 18:54:43.030’, ‘-5000’, 1)
    INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (3, ‘2015-12-08 18:55:14.293’, ‘2000’, 2)
    INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (4, ‘2015-12-08 18:55:49.053’, ‘3000’, 3)
    INSERT #Trans ([transactionid], [transactiondate], [CreditDebit], [userid]) VALUES (5, ‘2015-12-08 18:55:49.053’, ‘2000’, 1)

    SELECT
    a.username
    , t.transactiondate
    , case when sign(t.CreditDebit)=1 then CreditDebit else NULL end ‘Credit’
    , case when sign(t.CreditDebit)=-1 then CreditDebit else NULL end ‘Dedit’
    , SUM(t.CreditDebit) OVER(PARTITION BY t.userid ORDER BY t.transactiondate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OUTSTANDING_BALANCE
    FROM #Trans t
    INNER JOIN #ACCT a ON a.userid = t.userid
    ORDER BY t.userid, t.transactiondate

Leave a Reply