Datediff in T-SQL in 'Days'

1

Hi,
I have a T-SQL script that converts rows to columns. This is put into a View.

Here is my sample code for [Created] and [Closed]:

(SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created') AS "Created",

(SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), null)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') AS "Closed",

Action: I want to calc the diff in days between [Closed] and [Created]. In other words if Closed = 19/06/2023 and Created=12/06/2023, then diff=7

Please help.

Thanks!

2

If you want the datediff in days you can use the function DATEDIFF.

SELECT DATEDIFF(DAY,[Closed], [Created] FROM YourView

1 Like
3

Hi,
thanks for this, but I need to use this in the same syntax format as quoted above, i.e. I need to promote a new field called [No_of_Days_Diff].
Also, I want the T-SQL formula to sense check the values - i.e. if [Closed] is NULL or [Created] is NULL, then the answer will be 0, otherwise perform the Diff in Days function.
Thanks.

4

Tried this and it parses:

SELECT DATEDIFF(DAY,[Closed], [Created]) FROM [dbo].[View_ABC]) AS "No_of_Days_Open"

however when I run the view, it says:

View or function ..... contains a self-reference.

Basically I am self-referencing the same View (called View_ABC) which is not liked.

I am now stuck. Also I need to build into my DateDiff query some robustness so that if Closed is NULL or Created is NULL then don't calculate, otherwise calculate.

Help! Thanks!

5

Then it would be:

SELECT
CASE WHEN (SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created') IS NOT NULL AND (SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), null)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') IS NOT NULL THEN
DATEDIFF(DAY,(SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created'),(SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), null)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') ELSE NULL END AS PerformanceKiller
1 Like
6

Thanks for this. I will try now. What does 'Performancekiller' at the end mean? Thanks.

7

When I put into a new query, I get this:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ELSE'.

Completion time: 2023-06-19T13:14:43.2384654+01:00

Doesn't like the 'ELSE' here: ELSE NULL END AS PerformanceKiller

8

You can just remove that part. As I don't have your code I cannot test it :wink:

1 Like
9

PerformanceKiller means that I think it will be slow. It's a lot of code and you are using the same subquery many times. As I don't have any clue on how your system looks like it may be sufficient for your system.

1 Like
10

Please remind me what to remove as I tried loads of variations and I think it has a ")" missing somewhere, here is the corrected version:

SELECT
CASE
WHEN (SELECT Convert(date, [Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created') IS NOT NULL
AND (SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), NULL)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') IS NOT NULL
THEN DATEDIFF(DAY, (SELECT Convert(date, [Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created'), (SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), NULL)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed'))
ELSE NULL
END AS PerformanceKiller

Thanks!

11

To do anything other than guess we need data types and sample data with results for all contingencies.

How to post a T-SQL question on a public forum | spaghettidba

1 Like
12

This is the best answer I can give. As mentioned by my and lfor we cannot help you as we don't have your code. If you provide a test case with tables and some sample data we can help you in more detail.

DECLARE @Closed datetime
SET @Closed = DATEFROMPARTS(2023,6,19)

DECLARE @Created datetime
SET  @Created = DATEFROMPARTS(2023,6,12)

SELECT DATEDIFF(day,@Created,@Closed) AS diff
13

Is that really all of your code? Your subqueries reference a table A that isn't shown here - if you post the whole query we can show you how you can rewrite the query to get rid of the separate subqueries and allow for easily performing a datediff on those 2 values.

15

Here is my recommendation:

 SELECT r.*     -- Replace this with actual column names
      , NoOfDaysOpen = datediff(DAY, r.Closed, r.Created) 
   FROM dbo.Activity                      a
  INNER JOIN dbo.ActivityEntry           ae ON ae.ActivityID = a.ID
                                           AND ae.ID = a.LastActivityEntryId
 
  CROSS APPLY (
         SELECT UniqueReference             = max(CASE WHEN aed.[Key] = 'UniqueReference' THEN aed.[Value] END)
              , VisitNumber                 = max(CASE WHEN aed.[Key] = 'Visit.Current.Number' THEN aed.[Value] END)
              , VisitReference              = max(CASE WHEN aed.[Key] = 'Visit.Current.Reference' THEN aed.[Value] END)
              , VisitDate                   = max(CASE WHEN aed.[Key] = 'Visit.Current.Report.VisitDate' THEN convert(date, nullif(aed.[Value], '')) END)
              , Created                     = max(CASE WHEN aed.[Key] = 'Created' THEN convert(date, nullif(aed.[Value], '')) END)
              , Closed                      = max(CASE WHEN aed.[Key] = 'Closed' THEN convert(date, nullif(aed.[Value], '')) END)
              , AssignmentNumber            = max(CASE WHEN aed.[Key] = 'Assignment.Number' THEN aed.[Value] END)
              , ReportTemplateName          = max(CASE WHEN aed.[Key] = 'ActivityType' THEN aed.[Value] END)
              , CustomerName                = max(CASE WHEN aed.[Key] = 'Customer.Address' THEN aed.[Value] END)
              , ProjectAssignment           = max(CASE WHEN aed.[Key] = 'EVONumber' THEN aed.[Value] END)
              , OperatingProjectCoordinator = max(CASE WHEN aed.[Key] = 'Intertek.Contract.Project.Coordinator' THEN aed.[Value] END)
              , IntertekProjectNumber       = max(CASE WHEN aed.[Key] = 'Intertek.ProjectNumber' THEN aed.[Value] END)
              , SupplierName                = max(CASE WHEN aed.[Key] = 'Supplier.Location.SiteName' THEN aed.[Value] END)
              , SubSupplierName             = max(CASE WHEN aed.[Key] = 'SubSupplier.Location.SiteName' THEN aed.[Value] END)
              , TSName                      = max(CASE WHEN aed.[Key] = 'TSName' THEN aed.[Value] END)
              , TSPin                       = max(CASE WHEN aed.[Key] = 'TechnicalSpecialist.PIN' THEN aed.[Value] END)
              , TSEntity                    = max(CASE WHEN aed.[Key] = 'TSEntity' THEN aed.[Value] END)
              , TSEntityName                = max(CASE WHEN aed.[Key] = 'TSEntityName' THEN aed.[Value] END)
              , QualityCategory             = max(CASE WHEN aed.[Key] = 'QualityCategory' THEN aed.[Value] END)
              , QualityComments             = max(CASE WHEN aed.[Key] = 'QualityComments' THEN aed.[Value] END)
              , QualityCriticality          = max(CASE WHEN aed.[Key] = 'QualityCriticality' THEN aed.[Value] END)
              , QualityEquipment            = max(CASE WHEN aed.[Key] = 'QualityEquipment' THEN aed.[Value] END)
              , QualityItemNumber           = max(CASE WHEN aed.[Key] = 'QualityItem.Number' THEN aed.[Value] END)
              , QualityObsType              = max(CASE WHEN aed.[Key] = 'QualityObsType' THEN aed.[Value] END)
              , QualitySubCategory          = max(CASE WHEN aed.[Key] = 'QualitySubCategory' THEN aed.[Value] END)
           FROM dbo.ActivityEntryData   aed
          WHERE aed.ActivityEntryId = a.LastActivityEntryId
              )                           r

  WHERE a.ActivityType = 'QualityObservationReport'

You shouldn't need DISTINCT at all - if you have duplicates then there must be something else you need to filter on to eliminate the duplicates.

What this does is uses CROSS APPLY to query the ActivityEntryData table for the associated LastActivityEntryID and CROSS-TABs the results. That gives us all of the columns you want in a single query - that can then be used in the SELECT with a simple DATEDIFF.

This should at least give you a good start...

1 Like
17

Yes - you should not use '*' in the outer part of a query/view as that can and does cause binding issues. For example, if you are setting up a view - and later add a new column after CustomerName - then anything that was using that view would get incorrect data because the columns have changed.

It is also generally seen as a bad practice.

18

Ok thank you. Much appreciated.