Date difference help required

Question

Hi,

I have a query which returns some basic Risk information from a table (see bottom of post).

The RAG column contains RED, AMBER, or GREEN.
Status contains Open or Closed.
Modified is a date time field.

I'd like a three row table returned similar to the following:

RAG      Closed last week     New (0-1 weeks)     New (1-2 weeks)    In Progress (2-3 weeks)    >3 weeks
GREEN    5                           4                          6                           4                                   2
AMBER   5                            6                           3                          5                                   2
RED       5                            2                          3                           3                                   3

I am using SSRS to export the data into Excel - the results are then Pivoted in Excel to help give us something which resembles the above.

So the help is really to give me the required valuss based on my SQL statement.

Any help appreciated :)

SELECT 
COUNT([ItemId]) AS Risks, 
Project, 
datepart(year, DueDate) AS [DueDateYear], 
LEFT(datename(month, DueDate),3) as [DueDateMonthName], 
Owner, 
Status,
RAG,
Modified
FROM Risks 
GROUP BY [Project], 
datepart(year, DueDate),
LEFT(datename(month, DueDate),3),
[owner],
[Status],
[RAG]

Answer 1

So if Modified is 2012-05-06 12:23 and status is Closed, we can assume that the item was closed at this time?

Here is an outline of how run a pivot query (without using the PIVOT operator, which only makes things more difficult).

SELECT RAG,
       SUM(CASE WHEN Status = 'Closed' AND
                     datediff(DAY, Modified, getdate()) <= 7
                THEN 1
                ELSE 0
           THEN) AS [Closed last week],
       SUM(CASE WHEN Status = 'New' AND
                     datediff(DAY, Modified, getdate()) <= 7
                THEN 1
                ELSE 0
           THEN) AS [New (0-1) weeks],
       SUM(CASE WHEN Status = 'New' AND
                     datediff(DAY, Modified, getdate()) BETWEEN 8 AND 14
                THEN 1
                ELSE 0
           THEN) AS [New (1-2 weeks],
       -- etc
FROM   Risks
GROUP  BY RAG

When it comes to the use datediff, bear in mind that datediff counts boundary passages, which can give results that are not always intuitive. For this reason it is often a good idea to use a datepart with higher resolution. For instance, day instead of week. But of course, it all depends on the business rules. If you run the report on Friday, and an issue was closed Monday 11 days ago, should it appear in the first column or not?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thanks Erland - I'll try this now. The week starts on a Monday, so if an issue is over 7 days old I wouldn't expect it to appear in the first column - if that makes sense?

Answer 3

So if Modified is 2012-05-06 12:23 and status is Closed, we can assume that the item was closed at this time?

Here is an outline of how run a pivot query (without using the PIVOT operator, which only makes things more difficult).

SELECT RAG,
       SUM(CASE WHEN Status = 'Closed' AND
                     datediff(DAY, Modified, getdate()) <= 7
                THEN 1
                ELSE 0
           THEN) AS [Closed last week],
       SUM(CASE WHEN Status = 'New' AND
                     datediff(DAY, Modified, getdate()) <= 7
                THEN 1
                ELSE 0
           THEN) AS [New (0-1) weeks],
       SUM(CASE WHEN Status = 'New' AND
                     datediff(DAY, Modified, getdate()) BETWEEN 8 AND 14
                THEN 1
                ELSE 0
           THEN) AS [New (1-2 weeks],
       -- etc
FROM   Risks
GROUP  BY RAG

When it comes to the use datediff, bear in mind that datediff counts boundary passages, which can give results that are not always intuitive. For this reason it is often a good idea to use a datepart with higher resolution. For instance, day instead of week. But of course, it all depends on the business rules. If you run the report on Friday, and an issue was closed Monday 11 days ago, should it appear in the first column or not?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Ok - I have the following. It looks good initially. I have used the created datetime field (which I also capture) for items which are still OPEN.

Look ok?

SELECT RAG, 
       SUM(CASE WHEN [Status] = 'Closed' AND
                     datediff(DAY, Modified, getdate()) <= 7
                THEN 1
                ELSE 0
           end) AS [Closed last week],
       SUM(CASE WHEN Status = 'Open' AND
                     datediff(DAY, Created, getdate()) <= 7
                THEN 1
                ELSE 0
           end) AS [New (0-1) weeks],
       SUM(CASE WHEN Status = 'Open' AND
                     datediff(DAY, Created, getdate()) BETWEEN 8 AND 14
                THEN 1
                ELSE 0
           end) AS [New (1-2 weeks)],
          
       SUM(CASE WHEN Status = 'Open' AND
                     datediff(DAY, Created, getdate()) BETWEEN 15 AND 22
                THEN 1
                ELSE 0
           end) AS [New (2-3 weeks)],
       
        SUM(CASE WHEN Status = 'Open' AND
                     datediff(DAY, Created, getdate()) > 22
                THEN 1
                ELSE 0
           end) AS [> 3 weeks]
FROM Risks
GROUP BY RAG

Answer 4

Lastly, I have about 15 reports in Excel which feed from the original SQL:

SELECT 
COUNT([ItemId]) AS Risks, 
Project, 
datepart(year, DueDate) AS [DueDateYear], 
LEFT(datename(month, DueDate),3) as [DueDateMonthName], 
Owner, 
Status,
RAG,
Modified
FROM Risks 
GROUP BY [Project], 
datepart(year, DueDate),
LEFT(datename(month, DueDate),3),
[owner],
[Status],
[RAG]

Based on the new SQL I wondering if there is a where to combine both SQL statements so that I don't have to use two different datasets?

I guess not as my SQL gives me a row per Risk.

Answer 5

Thanks Erland - I'll try this now. The week starts on a Monday, so if an issue is over 7 days old I wouldn't expect it to appear in the first column - if that makes sense?

Again, I don't know your business rules. If it's Monday today, and an item was open Friday, how many weeks to you consider it to be? If the report is produced on Friday and an item was opened on Monday 11 days earlier, is that the same age?

Can issues be opened on weekends, or is this mainly a Mon-Fri business?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Based on the new SQL I wondering if there is a where to combine both SQL statements so that I don't have to use two different datasets?

The shape of the result set of this query is completely different from the other result set. But then again, a dataset (in ADO .Net) can include more than one DataTable, so you can produce to result sets. But I don't know if that applies to SSRS.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Thanks Erland - I'll try this now. The week starts on a Monday, so if an issue is over 7 days old I wouldn't expect it to appear in the first column - if that makes sense?

Again, I don't know your business rules. If it's Monday today, and an item was open Friday, how many weeks to you consider it to be? If the report is produced on Friday and an item was opened on Monday 11 days earlier, is that the same age?

Can issues be opened on weekends, or is this mainly a Mon-Fri business?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Monday to Sunday - something raised on Friday (if it's now Monday) would mean last week. Is my current statement therefore correct?

Answer 8

Monday to Sunday - something raised on Friday (if it's now Monday) would mean last week. Is my current statement therefore correct?

The only way to find out whether your code is correct, is to test different cases, but you don't expect

  datediff(DAY, '20120604', '20120601')

to return a value > 7, do you?

You see, while you can get useful answers in these forums, you are not relieved from the duty of thinking that comes with the programmer's trade. You should not have to ask me to learn that the code you have is not what you want.

It may seem that you could do:

 datediff(WEEK, Modified, getdate())

But there is a catch. SQL Server permits you to set the week to begin on any day, but being a product from the US, by default the week in SQL Server starts on Sunday.

The variable (or function) @@datefirst returns the current setting. 1 means that the week starts on Monday(!) and 7 is sunday. We can use this in this way:

   datediff(WEEK, dateadd(DAY, -@@datefirst, Modified),
                  dateadd(DAY, -@@datefirst, getdate())

This will work the way you want, since, as I explained earlier, datediff returns the number of boundary passages. If Modified is Sunday 23:59, and it is now a few minutes past midnight that's a d difference of one week.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

Thanks Erland - all very useful.