View to show calculated column

Question

Post reply

View to show calculated column

mark 44367

SSC Rookie

Points: 30
More actions
April 1, 2012 at 6:55 am

#255865

I'm trying to create a view but I'm struggling, here is my scenario.
The first table has a list of tasks with a due date:
Task Table
Description DueDate
Task1 2012-03-30
Task2 2012-03-31
Task3 2012-04-01
Task4 2012-04-02
Task5 2012-04-03
Task6 2012-04-04
Task7 2012-04-05
I want the view to have the above data plus an extra column to show row colour based on if a task is over due by time, using rules stored in another table.
I have a RAG table, the offest column is the number of days to show the colour
RAG table
Offset Colour
2 Red
0 Amber
-2 Green
Assuming today is 2012-04-01 this is the output from the view I would like:
Description DueDate Colour
Task1 2012-03-30 Red
Task2 2012-03-31 Amber
Task3 2012-04-01 Amber
Task4 2012-04-02 Green
Task5 2012-04-03 Green
Task6 2012-04-04 null
Task7 2012-04-05 null
Can you please help?
Thanks
Mark

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why should Task2 return Amber?

Why NULL for Task 6 and 7?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mark 44367 SSC Rookie Points: 30 More actions · Answer 2

LutzM (4/1/2012)
Why should Task2 return Amber?
Why NULL for Task 6 and 7?

Task 2 is amber as it is one day old, so between red and amber.

Tasks 6 and 7 are null because there is no colour to join to.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Shouldn't be Task4 show Amber then? (It's "between" amber and green)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mark 44367 SSC Rookie Points: 30 More actions · Answer 4

LutzM (4/1/2012)
Shouldn't be Task4 show Amber then? (It's "between" amber and green)

No, it needs to take the colour above as it hasn't got to the amber colour yet.

What I want to to happen is that when a tasks due date is way in the future it has no background colour (i.e. white), as the due day approaches it turns green, on the due day it turns amber and when the task is overdue by a few days it turns red.

Thanks

Mark

LutzM SSC Guru Points: 107049 More actions · Answer 5

Something like this?

DECLARE @Task TABLE

(

DESCRIPTION CHAR(5),

DueDate DATETIME

)

INSERT INTO @Task

SELECT 'Task1','2012-03-30' UNION ALL

SELECT 'Task2','2012-03-31' UNION ALL

SELECT 'Task3','2012-04-01' UNION ALL

SELECT 'Task4','2012-04-02' UNION ALL

SELECT 'Task5','2012-04-03' UNION ALL

SELECT 'Task6','2012-04-04' UNION ALL

SELECT 'Task7','2012-04-05'

DECLARE @RAG TABLE

(

Offset INT,

Colour VARCHAR(10)

)

INSERT INTO @RAG

SELECT 2 ,'Red' UNION ALL

SELECT 0 ,'Amber' UNION ALL

SELECT -2 ,'Green'

DECLARE @dat DATETIME

SET @dat=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

SELECT t.*,r.*

FROM @Task t

OUTER APPLY

(

SELECT TOP 1 r.Colour

FROM @RAG r

WHERE r.Offset <= DATEDIFF(dd,t.DueDate, @dat)

ORDER BY r.offset DESC

)r

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

How about something like this:

SELECT X.Description,

X.DueDate,

X.Colour

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY T.Description ORDER BY R.Offset) AS Ord,

T.Description,

T.DueDate,

R.Colour

FROM dbo.Task T

LEFT JOIN dbo.RAG R ON DATEDIFF(DAY, T.DueDate, SYSDATETIME()) <= R.Offset+1) X

WHERE X.Ord = 1

ORDER BY Description, DueDate;

I hope I have understood the question correctly.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

mark 44367 SSC Rookie Points: 30 More actions · Answer 7

Many thanks Lutz, your solution works great.

Andre your solution nearly works but Tasks 6 and 7 are shown as green and not null.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 8

Also Lutz' solution is probably easier to understand and maitain with that OUTER APPLY.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

View to show calculated column

Cookies on SQLServerCentral