CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Furkat Kochkarow
Furkat Kochkarow

Reputation: 65

Creating View FROM existing View

I have to solve this problem as quickly as possible but i am a newbie to Transact-SQL.

I have a view vStockSerialsTemp:

  Serial StockCode ActionPrice  StockName      Warehouse   ActionType
    123    P-0304    350          Galaxy S4      Centeral    Income
    456    P-0305    450          Iphone 5s      Centeral    Income
    567    P-0305    450          Iphone 5s      Centeral    Income
    123    P-0304    350          Galaxy S4      Centeral    Expense
    235    P-0304    350          Galaxy S4      Centeral    Income
    567    P-0305    450          Iphone 5s      Centeral    Expense

So, based on this view, I have to create a new View that will count the balance of each stock with the same Serials. We subtract rows with ActionType Expense from ActionType Income with identical Serials. Thus the view should look like:

 Serial StockCode ActionPrice  StockName      Warehouse   ActionType
    123    P-0304    350          Galaxy S4      Centeral    Income
    456    P-0305    450          Iphone 5s      Centeral    Income
    567    P-0305    450          Iphone 5s      Centeral    Income
    123    P-0304    350          Galaxy S4      Centeral    Expense
    235    P-0304    350          Galaxy S4      Centeral    Income
    567    P-0305    450          Iphone 5s      Centeral    Expense
    -----------------------------------------------------------------
    456    P-0305    450          Iphone 5s      Centeral    Balance
    235    P-0304    350          Galaxy S4      Centeral    Balance

CREATE vStockSerials as 
SELECT * FROM [dbo].[dbo.vStockSerialsTemp]
UNION 
// Here is the problem indeed

How can I select rows from vStockSerialsTemp here?

Upvotes: 1

Views: 184

Answers (4)

Kapol
Kapol

Reputation: 6463

Based on your desired outcome, I assume you don't want to show data for serials with a balance of zero.

SELECT
    Serial,
    StockCode,
    ActionPrice,
    StockName,
    Warehouse,
    ActionType
FROM
    vStockSerialsTemp
UNION ALL
SELECT
    Serial,
    StockCode,
    sum(CASE WHEN ActionType = 'Income' THEN ActionPrice ELSE -ActionPrice END),
    StockName,
    Warehouse,
    'Balance'
FROM
    vStockSerialsTemp
GROUP BY
    Serial,
    StockCode,
    StockName,
    Warehouse
HAVING
    sum(CASE WHEN ActionType = 'Income' THEN ActionPrice ELSE -ActionPrice END) <> 0
ORDER BY
    ActionType DESC

Upvotes: 1

Knightwisp
Knightwisp

Reputation: 325

Something like this after union:

select a.Serial, a.StockCode, (a.ActionPrice - b.ActionPrice) as ActionPrice,
a.StockName, a.Warehouse, 'Balance' as ActionType
from [dbo].[dbo.vStockSerialsTemp] a
inner join [dbo].[dbo.vStockSerialsTemp] b
on a.Serial = b.Serial
and a.ActionType = 'Income'
and b.ActionType = 'Expense'

Joining same view to itself. One with all Expenses, the other with all Incomes.

Upvotes: 1

Sivaraman
Sivaraman

Reputation: 458

Like three product came in (Income), Two spent (Expense). Income - Expense is remaining row (Balance)... with that Try this:

SELECT Serial, StockCode, ActionPrice, StockName, Warehouse
FROM [dbo].[dbo.vStockSerialsTemp] Where ActionType = 'INCOME'
EXCEPT
SELECT Serial, StockCode, ActionPrice, StockName, Warehouse 
FROM [dbo].[dbo.vStockSerialsTemp] Where ActionType = 'EXPENSE'

I hope It will work. You can apply the count. To print the count side by side google for Derived table or CTE

Upvotes: 1

Arion
Arion

Reputation: 31239

I do not know if this is the best way. But you could do this:

SELECT
    vStockSerialsTemp.Serial,
    vStockSerialsTemp.StockCode,
    vStockSerialsTemp.ActionPrice,
    vStockSerialsTemp.StockName,
    vStockSerialsTemp.Warehouse,
    vStockSerialsTemp.ActionType
FROM
    vStockSerialsTemp
UNION
SELECT
    vStockSerialsTemp.Serial,
    vStockSerialsTemp.StockCode,
    vStockSerialsTemp.ActionPrice,
    vStockSerialsTemp.StockName,
    vStockSerialsTemp.Warehouse,
    (
        CASE vStockSerialsTemp.ActionType
        WHEN 'Income'
        THEN 'Expense'
        ELSE 'Income'
        END 
    )AS ActionType
FROM
    vStockSerialsTemp

Upvotes: 1

Related Questions