Post reply

Report Builder -> SQL query questions

  • February 8, 2017 at 7:13 am

    #340455

    hello together,
    i have some work to do, but i am a noob in this... and need your help.
    I need to build an report for an SQL database.
    This database contains several tables.
    Each table contains a colum called TME_STAMP and User

    If someone changed via Sharepoint a value of a table, the time_stamp and user colum will be filled.

    The report should show, which value has changed and from which user

    So i need a query (i think) which shows all rows, where time_stamp is not null.

    This would help me a lot....

    thanks and regards

  • February 8, 2017 at 7:17 am

    #1927313

    Could it be as simple as this?

    SELECT * FROM MyTable
    WHERE time_stamp IS NOT NULL

    John

  • February 8, 2017 at 7:31 am

    #1927322

    John Mitchell-245523 - Wednesday, February 8, 2017 7:17 AM

    Could it be as simple as this?

    SELECT * FROM MyTable
    WHERE time_stamp IS NOT NULL

    John

    yes, but i have multiple tables in this database which contain column time_stamp and User
    I need an output from all tables in this database where these columns are not null

    sorry  i am a little noob in this SQL thing.... iuntil today i only installed  SQL Server / Cluster / Instances and standardjobs...

  • February 8, 2017 at 7:41 am

    #1927329

    Does each table have a primary key or unique constraint that's on a column (or set of columns) with similar data types?  If so, you can do something like this:

    SELECT
         'Table1' AS 'Table'
    ,    PKCol
    ,    UserName
    ,    time_stamp
    FROM Table1
    WHERE time_stamp IS NOT NULL
    UNION ALL
    SELECT
         'Table2' AS 'Table'
    ,    PKCol
    ,    UserName
    ,    time_stamp
    FROM Table2
    WHERE time_stamp IS NOT NULL
    UNION ALL
    ...

    John

  • February 8, 2017 at 8:06 am

    #1927337

    Ok i got the correct result i test a little bit

    thanks at first

  • February 8, 2017 at 8:10 am

    #1927342

    Yes.  The "..." is an ellipsis - that means there's stuff missed out.  You need to fill that in yourself (unless you only have two tables, of course, in which case just omit the second UNION ALL).

    John

  • February 8, 2017 at 8:54 am

    #1927355

    ok
    i have 14 tables and Username and Time_Stamp exists in every table.
    if i run this query i got an correct result -> 3 rows which contain tablename / Username / TimeStamp

    Now - i thought, when i add a third parameter "name" (like PKcol in your example) i got in the results the value for the column "name" which
    , but not every table has a column "name" - sometimes its called Fullname or ID or cn etc.

    when i run the complete query ->  i got n result which is "right".
    3 rows which have a time stamp column , a Username column, a tablenamecolumn and -- a "name" column,
    which is in this case the EmployeeID ... is there a possibilty that it will show the "original" column descirption ?

    many thanks

  • February 8, 2017 at 9:06 am

    #1927361

    SELECT
      'Table1' AS 'Table'
    ,  'FullName' AS PKColName
    ,  CAST(FullName as nvarchar(128)) AS PKValue -- choose a data type that all your PK cols can be converted to
    ,  UserName
    ,  time_stamp
    FROM Table1
    WHERE time_stamp IS NOT NULL
    UNION ALL
    SELECT
      'Table2' AS 'Table'
    ,  'ID' AS PKColName
    ,  CAST(ID AS nvarchar(128)) -- choose a data type that all your PK cols can be converted to
    ,  UserName
    ,  time_stamp
    FROM Table2
    WHERE time_stamp IS NOT NULL
    UNION ALL
    ...

    John

  • February 9, 2017 at 3:23 am

    #1927469

    perfect - thanks - i hope that this will be enough for us 🙂

Viewing 9 posts - 1 through 9 (of 9 total)

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