Dates with in a tolerance traffic light system

Hi,

I'm trying to do a traffic light type report: Red, Amber and Green

I have a date that I need to monitor what reports have so many days left.

So If I take the testDate field and add
Testdat - +8 days this is Green
Testdat(+9) - Testdat+16 days this is Amber
Testdat(+17) > this is Red

The people have 20 days to do a report and this is to show them then COUNT(*) for each colour.

Oh and its based on the current date.

I keep going around in circles but I know I have done it years ago but cannot remember how.

Thanks
Gareth_PointonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gareth_PointonAuthor Commented:
I have something like this in mind:

WHERE     ({ fn NOW() } BETWEEN cal_date AND DATEADD(d, 8, cal_date))

Open in new window

0
Brian CroweDatabase AdministratorCommented:
I'm sure this made sense from your point of view but I honestly have no idea what you're asking for.  Please detail your table schema and the output you want.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Would help if you could tell us what reporting tool you are using:  SSRS, ASP, Crystal, etc.

If you just need the straight T-SQL...
Declare @cal_date date = '1-17-2013'

SELECT CASE 
	WHEN DATEDIFF(d, GETDATE(), @cal_date)  BETWEEN 0 AND 8 THEN 'Green'
	WHEN DATEDIFF(d, GETDATE(), @cal_date)  BETWEEN 9 AND 16 THEN 'Amber'
	WHEN DATEDIFF(d, GETDATE(), @cal_date)  > 16 THEN 'Red'
	ELSE 'Handle this here' END

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gareth_PointonAuthor Commented:
Does this make more sense ..:

SELECT     COUNT(*) AS COUNT
FROM         dbo.tblJob_Instruments AS INS
WHERE     (cal_date BETWEEN { fn NOW() } AND DATEADD(d, - 3, { fn NOW() }))
GROUP BY ID

Open in new window

0
Gareth_PointonAuthor Commented:
I thought I did ..  Its ASP
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you're looking for just the counts in T-SQL, copy the below code, paste in your SSMS, execute it to verify it works, then modify to meet your needs

Declare @cal_date date = '1-17-2013'

SELECT 
	SUM(CASE WHEN DATEDIFF(d, GETDATE(), @cal_date)  BETWEEN 0 AND 8 THEN 1 END) as green_count, 
	SUM(CASE WHEN DATEDIFF(d, GETDATE(), @cal_date)  BETWEEN 9 AND 16 THEN 1 END) as amber_count,
	SUM(CASE WHEN DATEDIFF(d, GETDATE(), @cal_date)  > 16 THEN 1 END) as red_count,
	SUM(CASE WHEN DATEDIFF(d, GETDATE(), @cal_date) < 0 THEN 1 END) as other_count

Open in new window


I'm not familiar with ASP, so if this doesn't work then I'll un-monitor this question.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
function lightup(iDate)
    totDays=DateDiff("d",iDate,Date)
    if totDays < 9 then
          lightup="Green"
    end if
   if totDays >= 9 and totDays < 17 then
         lightup="Amber"
   end if
   if totDays >= 17 then
        lightup="red"
    end if
end function

do until rs.eof
   response.write lightup(rs("someDate"))&"<br>"
rs.movenet
loop

Open in new window


Do you need to keep a count? we can put a counter in the function.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
if you have a lot of data, instead of looping through the rs, using getrows() speeds things up a lot.
0
PortletPaulfreelancerCommented:
not sure just a where clause is the way to handle it, here there are 3 measures of status for summary records where a report isn't done... (based on a desire for counts of)

Looks more like this to me (nb: me no translate to asp):
SELECT
          somefield -- or fields
        , COUNT(CASE WHEN DATEDIFF(day, GETDATE(), [report_by_dt])  BETWEEN 0 AND 8 
                     THEN 1 ELSE NULL END)
          AS 'Green'
        , COUNT(CASE WHEN DATEDIFF(day, GETDATE(), [report_by_dt])  BETWEEN 9 AND 16
                     THEN  1 ELSE NULL END)
          AS 'Amber'
        , COUNT(CASE WHEN DATEDIFF(day, GETDATE(), [report_by_dt])  > 16
                     THEN  1 ELSE NULL END)
          AS 'Red'
FROM sometable
WHERE << reports not done logic >>
GROUP BY
          somefield -- or fields

Open in new window

Some sample data and expected results would surely help
0
Gareth_PointonAuthor Commented:
ID      cal_Date
3      15/08/2013
4      13/08/2013
5      14/08/2013
6      27/08/2013
7      12/08/2013
8      14/08/2013
9      14/08/2013
10      14/08/2013
11      22/08/2013
12      30/08/2013
13      16/08/2013
14      14/08/2013
15      14/08/2013
16      14/08/2013
17      21/08/2013
18      21/08/2013
21      28/08/2013
24      28/08/2013
26      28/08/2013
27      03/09/2013
28      03/09/2013
29      04/09/2013

So the out put would be

                           COUNT
With in 5 day :     3
6-15 Days :          6
16-20>:                13
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You said you wanted asp, but  you probably want the sql query to spit that out.  Are there contact_id's associated with these id numbers or do you want one report that shows the overall count?  Or count by a contact?
0
Gareth_PointonAuthor Commented:
Really the ID is not important at this stage as its a count per range I need. Then when the engineers click on that range I can then display a grid view of that range with more information. This if for an overview.
0
PortletPaulfreelancerCommented:
This result:
GREEN	AMBER	RED
3	6	13

Open in new window

which I assume you can transpose using ASP, produced by the following query:
DECLARE @low int = 5, @high int = 15


SELECT
      COUNT(CASE WHEN DATEDIFF(DAY, [cal_Date], GETDATE())  BETWEEN 0 AND @low
                 THEN 1 ELSE NULL END)
      AS 'Green'
    , COUNT(CASE WHEN DATEDIFF(DAY, [cal_Date], GETDATE())  BETWEEN (@low + 1) AND @high
                 THEN  1 ELSE NULL END)
      AS 'Amber'
    , COUNT(CASE WHEN DATEDIFF(DAY, [cal_Date], GETDATE())  > @high
                 THEN  1 ELSE NULL END)
      AS 'Red'
FROM sometable
-- WHERE << reports not done logic >>
	

Open in new window

note as the threshold numbers changed I used variables, also I had to reverse the date difference logic as the sample dates are in the past (previously I had assumed them as 'in the future').
see this query working at: http://sqlfiddle.com/#!3/0395e/4
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gareth_PointonAuthor Commented:
OK I have run this in SQL Query and its great. The only thing now is getting it in ASP to work with.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
No points for this.  

If you have a recordset named rsLight

<style>
/* Play with styles or shapes font sizes */
.redbox{width:50px;height50px;background-color:red;}
.amberbox{width:50px;height50px;background-color:orange}
.greenbox{width:50px;height50px;background-color:green;}
</style>

<div class="redbox"><%=rsLight("red")%></div><div class="amberbox"><%=rsLight("amber")%></div></div class="greenbox"><%=rsLight("green")%></div>

Open in new window

0
Gareth_PointonAuthor Commented:
OK so I call a RS but can I pass over variables to it later when I want to filter by engineer ID ?

Thanks
0
PortletPaulfreelancerCommented:
>>can I pass over variables to it later when I want to filter by engineer ID ?
if you go back through the information here there's not much info about the data structure (e.g. 'engineer' in mentioned 3 times before this sentence, none in reference to a table structure).

ID: 39467709
>>Really the ID is not important at this stage as its a count per range I need.
At the moment you have a query that deliberately returns just 3 values - which you asked for.

So, I imagine you can filter by engineer: Yes - but with a lack of knowledge caveat.

I would also expect filtering by engineer means by "current login"? is that true?
If true, the query you have for the 3 values now needs expansion so the "current user" may be included into the where clause (and this probably means some joined tables too)

Can you provide that logic, or are you looking for assistance on that?
0
PortletPaulfreelancerCommented:
please try this, maybe it's some unexpected values
SELECT
        COUNT(CASE
                WHEN DATEDIFF(DAY, [cal_Date], GETDATE()) BETWEEN 0 AND 10 THEN 1
                ELSE NULL
                END) AS 'Green'
      , COUNT(CASE
                WHEN DATEDIFF(DAY, [cal_Date], GETDATE()) BETWEEN (10 + 1) AND 15 THEN 1
                ELSE NULL
                END) AS 'Amber'
      , COUNT(CASE
                WHEN DATEDIFF(DAY, [cal_Date], GETDATE()) > 15 THEN 1
                ELSE NULL
                END) AS 'Red'
      , COUNT(CASE
                WHEN DATEDIFF(DAY, [cal_Date], GETDATE()) < 0 THEN 1
                ELSE NULL
                END) AS 'BAD'
      , COUNT(*)     AS 'Count_All'
FROM dbo.qryInstrument_Job
WHERE (Enteredby = 31)
        AND (twenty_fiveday = 0)
;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.