Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

T-SQL Query

Hi All,

I have the following columns in my table :

LoginsLast7Days,LoginsLast30Days,LoginsLast90Days,LoginsLast180Days

I need to apply the following rules to the columns :

At Risk: LoginsLast90Days = 0
Daily: LoginsLast7Days  > 3
Weekly: LoginsLast7Days = 0
Monthly: If LoginsLast90Days = 0, then " ", if not, then it checks if it's daily or weekly, and if not, then it's monthly.
Quarterly: LoginsLast180Days = 2
Multiple Users: LoginsLast7Days > 7

So far I have written a case when statement but cannot get it right for the monthly lable...

CASE WHEN LoginsLast90Days = 0 THEN CAST('At Risk' AS VARCHAR)
                   WHEN LoginsLast7Days > 3 THEN CAST('Daily' AS VARCHAR)
             WHEN LoginsLast7Days = 0 THEN CAST('Weekly' AS VARCHAR)
             WHEN LoginsLast180Days = 2 THEN CAST('Quarterly' AS VARCHAR)
             WHEN LoginsLast7Days > 7 THEN CAST('Multiple Users' AS VARCHAR(20))
             WHEN LoginsLast7Days = 0 AND LoginsLast30Days = 0 THEN CAST('Monthly' AS VARCHAR(10))
        END AS 'UserDiagnostics'

Thanks in advance.


0
matrix_aash
Asked:
matrix_aash
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CASE WHEN LoginsLast90Days = 0 THEN 'At Risk'
                   WHEN LoginsLast7Days > 3 THEN 'Daily'
             WHEN LoginsLast7Days = 0 THEN 'Weekly'
             WHEN LoginsLast180Days = 2 THEN 'Quarterly'
             WHEN LoginsLast7Days > 7 THEN 'Multiple Users'
             WHEN LoginsLast7Days = 0 AND LoginsLast30Days = 0 THEN 'Monthly'
        END AS 'UserDiagnostics'
FROM tableName
0
 
matrix_aashAuthor Commented:
the monthly one is not using LoginsLast90Days = 0...

below is the condition for monthly:

If LoginsLast90Days = 0, then " ", if not, then it checks if it's daily or weekly, and if not, then it's monthly
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CASE WHEN LoginsLast90Days = 0 THEN 'At Risk'
                  WHEN LoginsLast7Days > 3 THEN 'Daily'
                  WHEN LoginsLast7Days = 0 THEN 'Weekly'
                  WHEN LoginsLast180Days = 2 THEN 'Quarterly'
                  WHEN LoginsLast7Days > 7 THEN 'Multiple Users'
                  WHEN LoginsLast90Days <>  0 and  LoginsLast7Days = 0 AND LoginsLast30Days = 0 THEN 'Monthly'
        END AS 'UserDiagnostics'
FROM tableName
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now