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:

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

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 🙂