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

0
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
0
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.........
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, DATEDIFF should be used for returning only the days. I'm sorry about that.
0
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.
0
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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.