Deleted

Question

Deleted

chocthree

Ten Centuries

Points: 1276
More actions
July 10, 2017 at 9:59 am

#324391

Deleted.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

chocthree - Monday, July 10, 2017 9:59 AM
Hi,
I have the following SQL query:
SELECT
    u.id AS u_id
    , u.user_type AS u_user_type
    , u.username AS u_username
    , u.password AS u_password
    , u.centre_id AS u_centre_id
    , u.enabled AS u_enabled
    , u.secret_pin AS u_secret_pin
    , u.last_logged_in AS u_last_logged_in
    , c.id AS c_id
    , c.number AS c_number
    , c.name AS c_name
    , c.place_id AS c_place_id
    , c.admin_zone AS c_admin_zone
    , c.city AS c_city
    , c.county AS c_country
    , c.country AS c_country
    , c.region AS c_region
    , DATEDIFF( day, u.last_logged_in, GETDATE() ) AS Days
    , CASE
        WHEN DATEDIFF( day, GETDATE(), u.last_logged_in ) BETWEEN 0 AND 5 THEN '0-5'
        WHEN DATEDIFF( day, GETDATE(), u.last_logged_in ) BETWEEN 6 AND 15 THEN '0-15'
        WHEN DATEDIFF( day, GETDATE(), u.last_logged_in ) BETWEEN 16 AND 30 THEN '16-30'
        WHEN DATEDIFF( day, GETDATE(), u.last_logged_in ) BETWEEN 31 AND 50 THEN '31-50'
        WHEN DATEDIFF( day, GETDATE(), u.last_logged_in ) >= 51 THEN 'Greater than 50 days'
        ELSE 'Never logged on'
    END AS DaysGroup
FROM users u FULL OUTER JOIN centres c ON u.centre_id = c.id
The returning dataset is shown below. You will see it correctly shows the correct 'DaysGroup' for the first value in the 'Days' column (i.e. 0) but it then seems to populated the CASE default of 'Never logged on' for the remaining values (i.e. in the example the 12 and 190 values in the 'Days' column. It should show 6-15 and Greater than 50 days, respectively.)
Thanks in advance.

Run this:

SELECT

DATEDIFF( day, GETDATE()-10, GETDATE() ),

DATEDIFF( day, GETDATE(), GETDATE()-10 )

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The u.last_logged_in is NULL for all those cases.

NULL never returns true in any comparison (other than IS (NOT) NULL), and so the ELSE branch of the case statement is the only one that can apply.

Edit: Sorry, you meant the non-null cases? Yeah, the parameters are the wrong way around in the DATEDIFF that's within the CASE. It's DATEDIFF(<interval>, <start>, <end> ) . You've got it correct in the Days column, then reversed inside the CASE.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 3

It's the nulls for the majority. In addition, you get the incorrect values as you aren't calculating your days column the same as the case statement.
These are the two you are using - those in the case statement will give you a negative value:

DATEDIFF( day, u.last_logged_in, GETDATE() ) AS Days DATEDIFF( day, GETDATE(), u.last_logged_in ) BETWEEN 0 AND 5 THEN '0-5

Sue

chocthree Ten Centuries Points: 1276 More actions · Answer 4

Of course. Thanks for pointing it out. I guess it was a case of not seeing the wood for the trees.