Solved

# T-SQL Query

Posted on 2010-01-07
176 Views
Hi All,

I have the following columns in my table :

I need to apply the following rules to the columns :

Monthly: If LoginsLast90Days = 0, then " ", if not, then it checks if it's daily or weekly, and if not, then it's monthly.

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'

0
Question by:matrix_aash

LVL 75

Expert Comment

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'
END AS 'UserDiagnostics'
FROM tableName
0

LVL 8

Author Comment

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

LVL 75

Accepted Solution

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'
END AS 'UserDiagnostics'
FROM tableName
0

## Featured Post

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…