2

I pulled out some data from SQL db to Excel using the below sql query and then in excel I have added two additional columns with their respective formulas as below for analysis. Now I am trying to add these excel formulas into my sql query itself as 2 columns where I am having trouble could you please suggest me how I can add the above two formulas into my sql query.

Many thanks in advance.

  1. Older than 2 months Formula = IF(LastAccessDate>TODAY()-(365/6),"","Older than 2 months")
  2. Duration check Formula =IF(LastAccessDate-FirstAccessedDate=0,"Never Logged On",LastAccessDate-FirstAccessedDate)

Sql Query:

SELECT s.DomainName as UserId
,s.fullname as FullName
,MIN(DATEADD(HH,DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) [FirstAccessAt]
,MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())),A.CreatedOn)) [LastAccessAt]

--Tried on my own
    --,DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Month]
    --,(MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))> -6, GETDATE())  [OlderThan6Months]

    FROM archive a
    INNER JOIN Systemuser s
    ON s.systemuserid = a.objectid
    WHERE a.action = 54
    and a.CreatedOn between '2015-05-22 00:00:00.000' and '2016-11-23 00:00:00.000'
    GROUP BY s.FullName,s.DomainName --DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))
    ORDER BY [LastAccessAt] desc
Improve this question
1
  • Can someone please suggest on my above query whether is that possible to add 2 conditions into the above query or I have go for three different queries. Thanks. Commented Nov 22, 2016 at 14:15

3 Answers 3

Reset to default
2

Try this:

DECLARE @FirstAccessDate DATETIME;
DECLARE @LastAccessDate DATETIME;
DECLARE @Today DATETIME;

SET @FirstAccessDate = '20160920';
SET @LastAccessDate = '20160922';
SET @Today = '20161122';

SELECT  CASE WHEN DATEADD(MONTH, 2, @LastAccessDate) >= @Today THEN NULL
             ELSE 'Older than 2 months'
        END AS IsOlderThanTwoMonths,
        CASE WHEN @FirstAccessDate = @LastAccessDate THEN 'Never Logged On'
             ELSE CAST(DATEDIFF(DAY, @FirstAccessDate, @LastAccessDate) AS VARCHAR(20))
        END AS Duration; 

SET @LastAccessDate = '20160921';       
SET @FirstAccessDate = '20160921';

SELECT  CASE WHEN DATEADD(MONTH, 2, @LastAccessDate) >= @Today THEN NULL
             ELSE 'Older than 2 months'
        END AS IsOlderThanTwoMonths ,
        CASE WHEN @FirstAccessDate = @LastAccessDate THEN 'Never Logged On'
             ELSE CAST(DATEDIFF(DAY, @FirstAccessDate, @LastAccessDate) AS VARCHAR(20))
        END AS Duration;

So your query should look something like this:

SELECT
    UserId,
    FullName,
    FirstAccessAt,
    LastAccessAt,
    CASE WHEN DATEADD(MONTH, 2, LastAccessAt) >= @Today THEN NULL
            ELSE 'Older than 2 months'
    END AS IsOlderThanTwoMonths,
    CASE WHEN FirstAccessAt = LastAccessAt THEN 'Never Logged On'
            ELSE CAST(DATEDIFF(DAY, FirstAccessAt, LastAccessAt) AS VARCHAR(20))
    END AS Duration
FROM (
        SELECT
            s.DomainName as UserId,
            s.fullname as FullName,
            MIN(A.CreatedOn) AS FirstAccessAt,
            MAX(A.CreatedOn) AS LastAccessAt
        FROM archive a
        INNER JOIN Systemuser s
            ON s.systemuserid = a.objectid
        WHERE
            a.action = 54
        and a.CreatedOn between '2015-05-22 00:00:00.000' and '2016-11-23 00:00:00.000'
        GROUP BY
            s.FullName, s.DomainName
        ) t
ORDER BY LastAccessAt DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Hi openshac, Many thanks for your time in answering my query.I am trying to include the above two conditions statements into my sql query and run as a single query? Please suggest.
Just copy the query and replace the variables FirstAccessDate, LastAccessDate, Today with values you have in your code.
Must declare the scalar variable "@Today" error. Do you want me to insert GetDate()
I have fixed it by declaring the @Today. Thanks. Will test again now.
It's better to declare a variable to store the value of getdate() stackoverflow.com/a/12078717/283787
|
0

Yes, just add a comma between the two statements and replace 'Column Name' with unique names.

Improve this answer

2 Comments

I am getting an error saying "Invalid colun name 'LastAccessAt'."
Insert your first query into #tmpTable and then use the two case statements to query #tmpTable which will now have LastAccessAt as a valid column.
0

1. Older than 2 months Formula = IF(LastAccessDate>TODAY()-(365/6),"","Older than 2 months")

In TSQL:

CASE WHEN DateDiff(Month,LastAccessDate,GetDate()) >2 THEN 'Older Than 2 months' ELSE NULL END AS ColumnName

2. Duration check Formula =IF(LastAccessDate-FirstAccessedDate=0,"Never Logged On",LastAccessDate-FirstAccessedDate)

In TSQL:

CASE WHEN (LastAccessDate-FirstAccessDate)=0 THEN 'Never Logged On' ELSE (LastAccessDate-FirstAccessDate) END AS ColumnName

Improve this answer

1 Comment

Hi khris-kramer, Firstly many thanks for your time in answering my query. Is that possible to include the above two case statements into my above SQL query and run as a single query? Please suggest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.