Dates with in a tolerance traffic light system

I have something like this in mind:

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

Select allOpen in new window

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.

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

Select allOpen in new window

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

Select allOpen in new window

I thought I did ..  Its ASP

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

Select allOpen in new window

I'm not familiar with ASP, so if this doesn't work then I'll un-monitor this question.

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

Select allOpen in new window

Do you need to keep a count? we can put a counter in the function.

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

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

Select allOpen in new window

Some sample data and expected results would surely help

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

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?

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.

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

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>

Select allOpen in new window

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

Thanks

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

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)
;

Select allOpen in new window