Creating a view in SQL Server from select statement

Question

I have select statement which returns income or outcome for each day in two currencies:

SELECT SUM(CASE
            WHEN C.Name LIKE 'B%' AND T.Income = 0 THEN 0-T.Amount
            WHEN C.Name LIKE 'B%' AND T.Income = 1 THEN T.Amount
            ELSE 0
            END) AS AmountBYR
      ,SUM(CASE
            WHEN C.Name LIKE 'U%' AND T.Income = 0 THEN 0-T.Amount
            WHEN C.Name LIKE 'U%' AND T.Income = 1 THEN T.Amount
            ELSE 0
            END) AS AmountUSD
      ,CR.Date
FROM [FinanceLabkovich].[dbo].[Transactions] T
JOIN [FinanceLabkovich].[dbo].[Bills] B ON B.BillID=T.BillID
JOIN [FinanceLabkovich].[dbo].[Currencies] C ON C.CurrencyID=B.CurrencyID
RIGHT JOIN [FinanceLabkovich].[dbo].[CurrencyRates] CR ON CR.Date=T.Date
GROUP BY CR.Date
ORDER BY CR.Date

I need to create view which show how much money the person has in total for each day

possible duplicate of SYNTAX CREATE VIEW SQL SERVER

Mike D.
– Mike D.

2015-03-18 07:01:33 +00:00
Commented Mar 18, 2015 at 7:01 — Mike D.
– Mike D., Commented Mar 18, 2015 at 7:01
@MikeD. this one looks like a running totals question.

Martin Smith
– Martin Smith

2015-03-18 07:03:34 +00:00
Commented Mar 18, 2015 at 7:03 — Martin Smith
– Martin Smith, Commented Mar 18, 2015 at 7:03
I need to create view which count total money for each day

Vasily
– Vasily

2015-03-18 07:05:07 +00:00
Commented Mar 18, 2015 at 7:05 — Vasily
– Vasily, Commented Mar 18, 2015 at 7:05

Andomar · Accepted Answer · 2015-03-18 07:05:14Z

1

In SQL Server 2012 and later, you can calculate current balance using a running sum:

sum(column_name) over (order by date) as running_sum

To find only running sums at the end of the day, you could use a subquery:

select  *
from    (
        select  customer_id
        ,       sum(amount) over (
                    partition by customer_id
                    order by [date]) as balance
        ,       row_number() over (
                    partition by customer_id, cast([date] as date)
                    order by [date] desc) as rn
        from    Transactions
        ) as SubQueryAlias
 where  rn = 1 -- Only latest row per customer per day

answered Mar 18, 2015 at 7:05

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a view in SQL Server from select statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related