Count days (Date)

I would like to add a new Column to the below procedure which counts the number of days since REVIEWDATE .

The code is ordered by    

ORDER BY a.REVIEWDATE DESC




USE [IRIS]
GO
/****** Object:  StoredProcedure [dbo].[GET_TRAINEE_REVIEWSNEW]    Script Date: 07/06/2015 15:24:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GET_TRAINEE_REVIEWSNEW]
												@TRAINEEID nvarchar(30),
												@POT int
AS 
SELECT     a.REVIEWDATE, b.REVIEWRESULT, c.REVIEWTYPE, a.REVIEWID, 

CASE
WHEN dbo.vwDestinyReviews.DESTINY = 1 THEN 'Yes'
WHEN dbo.vwDestinyReviews.DESTINY IS NULL THEN 'No' 
ELSE 'FALSE'
END  as DESTINY


, dbo.vwDestinyReviews.FILEPATH, dbo.vwDestinyReviews.FILENAME
FROM         dbo.I_TRAINEE_REVIEWS AS a INNER JOIN
                      dbo.ID_REVIEWRESULTID AS b ON a.REVIEWRESULTID = b.REVIEWRESULTID INNER JOIN
                      dbo.ID_REVIEWTYPEID AS c ON a.REVIEWTYPEID = c.REVIEWTYPEID FULL OUTER JOIN
                      dbo.vwDestinyReviews ON a.REVIEWID = dbo.vwDestinyReviews.REVIEWID
WHERE	(a.POT = @POT)
AND		(a.TRAINEEID = @TRAINEEID)
ORDER BY a.REVIEWDATE DESC

Open in new window


Thanks
EdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT a.REVIEWDATE, b.REVIEWRESULT, c.REVIEWTYPE, a.REVIEWID, 
	CASE
		WHEN dbo.vwDestinyReviews.DESTINY = 1 THEN 'Yes'
		WHEN dbo.vwDestinyReviews.DESTINY IS NULL THEN 'No' 
		ELSE 'FALSE'
	END  as DESTINY
	, dbo.vwDestinyReviews.FILEPATH, dbo.vwDestinyReviews.FILENAME
	, GETDATE()-a.REVIEWDATE NumberDaysSinceReview
FROM dbo.I_TRAINEE_REVIEWS AS a 
	INNER JOIN dbo.ID_REVIEWRESULTID AS b ON a.REVIEWRESULTID = b.REVIEWRESULTID 
	INNER JOIN dbo.ID_REVIEWTYPEID AS c ON a.REVIEWTYPEID = c.REVIEWTYPEID 
	FULL OUTER JOIN dbo.vwDestinyReviews ON a.REVIEWID = dbo.vwDestinyReviews.REVIEWID
WHERE	(a.POT = @POT) AND	(a.TRAINEEID = @TRAINEEID)
ORDER BY a.REVIEWDATE DESC

Open in new window

EdAuthor Commented:
Thanks but NumberdaysSinceReview gets returned in this format.  1902-01-13 16:21:37.663  

What I'd like is to COUNT then number of days since the last REVIEWDATE

eg

NumberDaysSinceReview

                  46
EdAuthor Commented:
eg

REVIEWDATE    REVIEWRESULT    REVIEWTYPE      REVIEWID     NumberDaysSinceReview
 20/01/2015            Success              Review                    452                                 5  
15/01/2015            Success              Review                    451                                10  
05/01/2015            Success              Review                    450                                  5  
01/01/2015            Success              Review                    449                                

etc.........
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
Use DATEDIFF(), E.g.

ALTER PROCEDURE [dbo].[GET_TRAINEE_REVIEWSNEW]
    @TRAINEEID NVARCHAR(30) ,
    @POT INT
AS
    SELECT  a.REVIEWDATE ,
            b.REVIEWRESULT ,
            c.REVIEWTYPE ,
            a.REVIEWID ,
            CASE WHEN DR.DESTINY = 1 THEN 'Yes'
                 WHEN DR.DESTINY IS NULL THEN 'No'
                 ELSE 'FALSE'
            END AS DESTINY ,
            DR.FILEPATH ,
            DR.FILENAME ,
            DATEDIFF(DAY, a.REVIEWDATE, GETDATE()) AS DaysSinceReviewNumber
    FROM    dbo.I_TRAINEE_REVIEWS a
            INNER JOIN dbo.ID_REVIEWRESULTID b ON a.REVIEWRESULTID = b.REVIEWRESULTID
            INNER JOIN dbo.ID_REVIEWTYPEID c ON a.REVIEWTYPEID = c.REVIEWTYPEID
            FULL OUTER JOIN dbo.vwDestinyReviews DR ON a.REVIEWID = DR.REVIEWID
    WHERE   a.POT = @POT
            AND a.TRAINEEID = @TRAINEEID
    ORDER BY a.REVIEWDATE DESC;

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, DATEDIFF should be used for returning only the days. I'm sorry about that.
EdAuthor Commented:
ste5an

That's almost working but rather than counting the number of days since from a.REVIEWDATE,  to  GETDATE())  I want to count the number of days between  a.REVIEWDATE and the previous a.REVIEWDATE.  How do I do that.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT  a.REVIEWDATE ,
        b.REVIEWRESULT ,
        c.REVIEWTYPE ,
        a.REVIEWID ,
        CASE WHEN DR.DESTINY = 1 THEN 'Yes'
             WHEN DR.DESTINY IS NULL THEN 'No'
             ELSE 'FALSE'
        END AS DESTINY ,
        DR.FILEPATH ,
        DR.FILENAME ,
        DATEDIFF(DAY, (SELECT MAX(p.REVIEWDATE)
			FROM I_TRAINEE_REVIEWS p
			WHERE p.POT = a.POT
			 AND p.TRAINEEID = a.TRAINEEID 
			 AND p.REVIEWDATE < a.REVIEWDATE)
        , a.REVIEWDATE) AS DaysSinceReviewNumber
FROM    dbo.I_TRAINEE_REVIEWS a
        INNER JOIN dbo.ID_REVIEWRESULTID b ON a.REVIEWRESULTID = b.REVIEWRESULTID
        INNER JOIN dbo.ID_REVIEWTYPEID c ON a.REVIEWTYPEID = c.REVIEWTYPEID
        FULL OUTER JOIN dbo.vwDestinyReviews DR ON a.REVIEWID = DR.REVIEWID
WHERE   a.POT = @POT
        AND a.TRAINEEID = @TRAINEEID
ORDER BY a.REVIEWDATE DESC;

Open in new window

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
ste5anSenior DeveloperCommented:
Compare the execution plans, using ROW_NUMBER() produces often the better (faster) plan:

SELECT  O.SalesOrderID ,
        O.OrderDate ,
        O.CustomerID ,
        O.TotalDue ,
        DATEDIFF(DAY, ( SELECT  MAX(I.OrderDate)
                        FROM    Sales.SalesOrderHeader I
                        WHERE   I.CustomerID = O.CustomerID
                                AND I.OrderDate < O.OrderDate
                      ), O.OrderDate) AS DaysSinceLastOrder
FROM    Sales.SalesOrderHeader O
ORDER BY O.SalesOrderID DESC;

WITH    Ordered
          AS ( SELECT   O.SalesOrderID ,
                        O.OrderDate ,
                        O.CustomerID ,
                        O.TotalDue ,
                        ROW_NUMBER() OVER ( PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC ) AS RN
               FROM     Sales.SalesOrderHeader O
             )
    SELECT  L.* ,
            DATEDIFF(DAY, R.OrderDate, L.OrderDate) AS DaysSinceLastOrder
    FROM    Ordered L
            LEFT JOIN Ordered R ON R.CustomerID = L.CustomerID
                                   AND R.RN = L.RN - 1
    ORDER BY L.SalesOrderID DESC;

Open in new window


Using SQL Server 2012 it would be even faster with the LAG() window function:

SELECT  O.SalesOrderID ,
        O.OrderDate ,
        O.CustomerID ,
        O.TotalDue ,
        DATEDIFF(DAY, LAG(O.OrderDate, 1, NULL) OVER ( PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC ), O.OrderDate) AS DaysSinceLastOrder
FROM    Sales.SalesOrderHeader O
ORDER BY O.SalesOrderID DESC;

Open in new window

EdAuthor Commented:
Perfect Solution
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.