Link to home
Start Free TrialLog in
Avatar of Ed
EdFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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

Avatar of Ed

ASKER

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
Avatar of Ed

ASKER

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

Yes, DATEDIFF should be used for returning only the days. I'm sorry about that.
Avatar of Ed

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Ed

ASKER

Perfect Solution