CODEWITHSUNDEEP
sqlsql-serversql-server-2008viewdatabase-table
user1736332
user1736332

Reputation: 723

MS SQL is this possible to do in a view?

I have 4 tables:
table A:

|LineID|

table B:

|Date|ShiftID|IsWorking|

Table C:

|LineID|Date|ShiftID|IsWorking|

Table D:

|ShiftID|HowLongWorks|

I need to create a view where I will have

|LineID|Date|HowLongWorks|

In this view I need to have:
- all lines from table A
- All dates from table B for each line
- sum(HowLongWorks) for all shifts signed to that date where IsWorking = 1

And when LineID, Date, ShiftID combination exists in table C, IsWorking must be taken from table C, if not exists from table B.

Is it possible to do in a view?

For example: table A:

|Line 1|
|Line 2|

table B:

|2013-01-01|1|1|
|2013-01-01|2|1|
|2013-01-02|1|1|
|2013-01-02|2|0|
|2013-01-03|1|0|
|2013-01-03|2|0|

table C:

|Line 1|2013-01-01|1|0|
|Line 1|2013-01-01|2|0|
|Line 1|2013-01-02|1|1|
|Line 2|2013-01-03|2|1|

table D:

|1|8|
|2|10|

the result in view should be:

|Line 1|2013-01-01|0|
|Line 1|2013-01-02|8|
|Line 1|2013-01-03|0|
|Line 2|2013-01-01|18|
|Line 2|2013-01-02|8|
|Line 2|2013-01-03|10|

Upvotes: 1

Views: 52

Answers (1)

roman
roman

Reputation: 117571

select
    a.LineID, b.Date,
    sum(case when isnull(c.IsWorking, b.IsWorking) = 1 then d.HowLongWorks else 0 end) as HowLongWorks
from TableA as a
    cross join TableB as b
    left outer join TableC as c on
        c.LineID = a.LineID and c.Date = b.Date and c.ShiftID = b.ShiftID 
    left outer join TableD as d on d.ShiftID = b.ShiftID
group by a.LineID, b.Date
order by LineID, Date

sql fiddle demo

Upvotes: 1

Related Questions