Post reply

Help needed in a query

  • March 26, 2019 at 9:21 am

    #2031639

    Hello All,
    I need help in getting the info like 

     Name
     User ID
    How many days until account expires
    Account is not set to never expire
    Last login

    from the attached SQLInstances,DB names  and Tables.

  • March 26, 2019 at 1:52 pm

    #2031820

    And what have you tried?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • March 27, 2019 at 1:34 am

    #2031858

    This is beyond help, you basically want us to do the work for you.

  • March 27, 2019 at 7:21 am

    #2033938

    This is not a homework service. We'll help. We won't do all the work. Try it. Tell us where you get stuck. Show your work. We'll help you fix it and help you learn. Promise. However, you're really not going to get free work done here.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 27, 2019 at 8:48 am

    #2033951

    DBA_007 - Tuesday, March 26, 2019 9:21 AM

    Hello All,
    I need help in getting the info like 

     Name
     User ID
    How many days until account expires
    Account is not set to never expire
    Last login

    from the attached SQLInstances,DB names  and Tables.

    Piling on to the responses, there are several things missing in your post
    😎

    1. post a sample data set as an insert statement
    2. post the expected results from the sample data
    3. don't expect anyone to use different server or database instances, just not going to happen
    4. post what you have tried so far
    5. post the details of what your current solution is missing/wrong
    6. clean up the DDL script, don't assume that anyone will be using the Primary file group for user tables
    7. add collation to the DDL script, don't take collation/location/date format for granted

    What you are asking for is absolutely trivially elementary beginners stuff so either there must be a twist to it or, you've no idea of what you are doing!

    BTW, $250/h will get me interested 😉

  • March 27, 2019 at 5:19 pm

    #2034034

    DBA_007 - Tuesday, March 26, 2019 9:21 AM

    Hello All,
    I need help in getting the info like 

     Name
     User ID
    How many days until account expires
    Account is not set to never expire
    Last login

    from the attached SQLInstances,DB names  and Tables.

    Against my better judgment, I opened your text file. Don’t like opening things from people we don’t know here on the Internet. I did a quick scan of the DDL and I think you need to start over. You’ve got assembly language style flags, incorrect datatypes (Why do you like NVARCHAR(n) so much? Your columns did not seem to need Unicode or to be of varying length), have mixed audit data in the table being audited, and absolutely no respect for any the industry standards. The same type of data element changes its name from table to table when it should be uniform throughout the entire schema. Not only do you believe in assembly language flags, but you believe in Kabbalah numbers; a key should be a subset of the columns in the table, and not a generic “id†without any qualifying attribute. Furthermore, identifiers use what is called a nominal scale, so they can never be numeric. You do not do math on identifiers. Here’s a quick rewrite the first few lines of your first table:

    CREATE TABLE Users
    (login_name VARCHAR (60) NOT NULL PRIMARY KEY,
    first_name VARCHAR (35) NOT NULL, ---same variable name everywhere
    last_name VARCHAR (35) NOT NULL,
    email_address VARCHAR (256) NOT NULL,--- get the length right
    currency_code CHAR (3) NOT NULL
    CHECK(currency_code IN (...)), ---ISO4217 Currency codes
    language_code CHAR (2) NOT NULL
    CHECK(language_code IN (..)), --- ISO3166 country codes
    timezone_name VARCHAR (5) NOT NULL
    DEFAULT(…)
    CHECK(timezone_name IN (..)),---https://www.timeanddate.com/time/zones/
    authorized_writeoff_amt DECIMAL (12,2) NOT NULL, --- amt is the attribute property
    template_id CHAR(..) NOT NULL, --- identifiers are never numeric
    team_id CHAR(..) NOT NULL --- identifiers are never numeric
     REFERENCES Teams(team_id)
     ON UPDATE CASCADE, --- use DRI actions
    logon_limit_cnt INTEGER NOT NULL --- correct the data type
    CHECK(logon_limit_cnt BETWEEN 1 AND 2),
    telephone_nbr CHAR (15) NOT NULL, --- International E164 Standard
    …);
     Please think about what happens to the audit data about login history when your user table gets screwed up. It’s destroyed too! This is the equivalent of keeping a spare set of car keys in your glove box in case you get locked out of your car. Storing the password unencrypted in the same table as the rest of the user data is a great way to go to jail.

    Again,ple ase start over. What you did post can only serve as a bad examplein one of my presentations.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • March 28, 2019 at 1:54 pm

    #2034146

    --terrible ddl but I took a stab at the first table
    SELECT [LOGINNAME]
          ,LTRIM(RTRIM([FORENAME])) + ' ' + LTRIM(RTRIM([SURNAME])) as [Name] --might want to check for nulls
          ,DATEDIFF(dd,getdate(),[EXPIRATIONDATE?] --don't have expiration dates in all tables
          ,null as [neverexpirefllag] --don't have a clue what field will hold this, may want a case statement
          ,[LASTLOGON]
    FROM [dbo].[USERS]

Viewing 7 posts - 1 through 6 (of 6 total)

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