Avatar of Gareth_Pointon
Gareth_Pointon asked on

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
Microsoft SQL ServerMicrosoft SQL Server 2008ASP

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER
Gareth_Pointon

I have something like this in mind:

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

Open in new window

Brian Crowe

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.
Jim Horn

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Gareth_Pointon

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

ASKER
Gareth_Pointon

I thought I did ..  Its ASP
Jim Horn

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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.
Scott Fell

if you have a lot of data, instead of looping through the rs, using getrows() speeds things up a lot.
PortletPaul

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Gareth_Pointon

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
Scott Fell

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?
ASKER
Gareth_Pointon

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Gareth_Pointon

OK I have run this in SQL Query and its great. The only thing now is getting it in ASP to work with.
Scott Fell

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

ASKER
Gareth_Pointon

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

Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

>>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?
PortletPaul

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