How to convert/simulate Excel formula into SQL Query

Question

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.

Older than 2 months Formula = IF(LastAccessDate>TODAY()-(365/6),"","Older than 2 months")
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

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. — Kittu, Nov 22 '16 at 14:15

openshac · Accepted Answer · 2016-11-22T15:14:41.950

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

edited Nov 22 '16 at 15:14

answered Nov 22 '16 at 14:47

openshac

4,966
5
46
77

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. – Kittu Nov 22 '16 at 15:00
Just copy the query and replace the variables FirstAccessDate, LastAccessDate, Today with values you have in your code. – openshac Nov 22 '16 at 15:02
Must declare the scalar variable "@Today" error. Do you want me to insert GetDate() – Kittu Nov 22 '16 at 15:24
I have fixed it by declaring the @Today. Thanks. Will test again now. – Kittu Nov 22 '16 at 15:26
It's better to declare a variable to store the value of getdate() http://stackoverflow.com/a/12078717/283787 – openshac Nov 22 '16 at 15:28
Hi openshac - Many thanks for your help. could you please tell me about the 't' in the above query..Is that for temporarily storing the query results and how best I can declare @Today. I have seen the link which you have provided but din't get anything :( – Kittu Nov 22 '16 at 15:36
All subqueries must have an alias. I used 't' for simplicity. So your query could be SELECT t.UserId, t.FullName, ... if you wanted, but it doesn't currently need it. – openshac Nov 22 '16 at 15:42
OK. I declared the today's date as' DECLARE @Today date = getDate() ' which is fine now. Could you please tell me a bit more on 't' from the above query. – Kittu Nov 22 '16 at 15:42
Hi openshac, Many thanks for your help and time. I got the result as expected. Thank you again. – Kittu Nov 22 '16 at 16:26

Khris Kramer · Answer 2 · 2016-11-22T15:03:36.393

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

edited Nov 22 '16 at 15:03

answered Nov 22 '16 at 14:43

Khris Kramer

68
6

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. – Kittu Nov 22 '16 at 14:56

score 0 · Answer 3 · answered Nov 22 '16 at 15:00

0

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

answered Nov 22 '16 at 15:00

Khris Kramer

68
6

I am getting an error saying "Invalid colun name 'LastAccessAt'." – Kittu Nov 22 '16 at 15:12
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. – Khris Kramer Nov 22 '16 at 15:18

How to convert/simulate Excel formula into SQL Query

3 Answers3