Report to pull UserID field and Date field from 5 tables and display a List?

  • Hi,

    Please can anyone help, I have 5 SQL tables (T1, T2, T3, T4, T5) which contain just 2 fields: UserID and Timestamp

    I need to do a report which reads each table and pulls in the USERID and DateStamp and somehow displays the data as follows:

    UserID T1 T1 Date T2 T2 Date T3 TdDate T4 T4Date T5 T5Date

    UserA Yes 01/01/1999 Yes 02/02/1999

    The Yes just displays that there is an entry in that Table.

    So basically, the UserID may exist in one or more tables, other times the UserID might just appear once.

    Where the USERID appears there is always a Timestamp field entry, so I just need to somehow pull the data together using a SSRS report and put a YES and Date in the column.... There are literally only 300 records in total for all the 5 tables, so not much data at all.

    Can anyone please help as I'm new to reports. I've done some basic ones to read one table, but never had to do anything to go across datasets and combine in a single list.

    Thanks

  • /*****************************************

    Create some sample tables based on a guess

    ******************************************/

    CREATE TABLE #T1

    (

    UserIDVARCHAR(6)

    ,DATEDATE

    )

    CREATE TABLE #T2

    (

    UserIDVARCHAR(6)

    ,DATEDATE

    )

    CREATE TABLE #T3

    (

    UserIDVARCHAR(6)

    ,DATEDATE

    )

    CREATE TABLE #T4

    (

    UserIDVARCHAR(6)

    ,DATEDATE

    )

    CREATE TABLE #T5

    (

    UserIDVARCHAR(6)

    ,DATEDATE

    )

    /*

    Populate the tables

    */

    INSERT INTO #T1 VALUES

    ('User01',CAST(GETDATE() AS DATE))

    ,('User02',CAST(GETDATE() AS DATE))

    INSERT INTO #T2 VALUES

    ('User01',CAST(GETDATE() AS DATE))

    ,('User02',CAST(GETDATE() AS DATE))

    ,('User03',CAST(GETDATE() AS DATE))

    INSERT INTO #T3 VALUES

    ('User04',CAST(GETDATE() AS DATE))

    ,('User02',CAST(GETDATE() AS DATE))

    ,('User03',CAST(GETDATE() AS DATE))

    INSERT INTO #T4 VALUES

    ('User01',CAST(GETDATE() AS DATE))

    ,('User05',CAST(GETDATE() AS DATE))

    ,('User03',CAST(GETDATE() AS DATE))

    INSERT INTO #T5 VALUES

    ('User01',CAST(GETDATE() AS DATE))

    ,('User02',CAST(GETDATE() AS DATE))

    ,('User04',CAST(GETDATE() AS DATE));

    /***************************************************

    This part is my actual solution.

    ****************************************************/

    WITH users(UserID) AS --- First build a list of unique user id's

    (

    SELECT UserID FROM #T1 -- Substitute your table names here

    UNION

    SELECT UserID FROM #T2

    UNION

    SELECT UserID FROM #T3

    UNION

    SELECT UserID FROM #T4

    UNION

    SELECT UserID FROM #T5

    )

    SELECT

    u1.UserID

    ,CASE WHEN t1.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t1 --- If a user is on a table return 'YES'

    ,t1.DATE

    ,CASE WHEN t2.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t2

    ,t2.DATE

    ,CASE WHEN t3.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t3

    ,t3.DATE

    ,CASE WHEN t4.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t4

    ,t4.DATE

    ,CASE WHEN t5.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t5

    ,t5.DATE

    FROM

    users u1 -- Join each table to the unique list. Substitute the temporary tables for your tables

    LEFT JOIN #T1 t1 ON T1.userid = u1.UserID

    LEFT JOIN #T2 t2 ON T2.userid = u1.UserID

    LEFT JOIN #T3 t3 ON T3.userid = u1.UserID

    LEFT JOIN #T4 t4 ON T4.userid = u1.UserID

    LEFT JOIN #T5 t5 ON T5.userid = u1.UserID

    DROP TABLE --- Drop sample tables

    #T1

    ,#T2

    ,#T3

    ,#T4

    ,#T5

    Here's one possible solution but I have guessed a bit.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • BWFC....................

    You the man, your script worked great, I then (being a noob to sql), sussed out the statements and replaced the temp tables with my real-ones where you commented and it works spot on.

    Thank you so much and apologies for cross posting, wasn't sure if it was sql or ssrs.

    I presume I can just run this query in a Dataset in SRSS to display the resulting table?

  • You're welcome, I'm happy to help. Do you understand what it does?

    As a disclaimer, I've only Googled using a CTE in an SSRS dataset rather than actually doing it but it should work fine. If not, come back and we can have another crack.

    Rather than cross-posting, it's best to post in one forum and ask if it's in the right place. I think the admins can move topics if they need to.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • BWFC (10/4/2016)


    You're welcome, I'm happy to help. Do you understand what it does?

    As a disclaimer, I've only Googled using a CTE in an SSRS dataset rather than actually doing it but it should work fine. If not, come back and we can have another crack.

    Rather than cross-posting, it's best to post in one forum and ask if it's in the right place. I think the admins can move topics if they need to.

    Yes I think I have an idea how you done it.

    1) gets a unique list of UserIDs by searching through the 5 tables

    2) checks to see if that UserID exists (not null) in each table and then the date in the row belonging to that UserID

    3) Does the Join bring the list together?

  • 1) Yes; the UNION operator can combine multiple queries with the same output columns but different sources. In this case we're returning the user ids from 5 different tables. UNION returns the unique rows only. Have a look here for more info.

    2) Not quite; a CASE expression returns a result if a condition is met. In this situation, the condition happens to be that the UserID is NULL, but it could be many other things. It doesn't check whether the user id exists as such, rather it checks to see if a condition is met.

    As an example, paste the code below into the sample query

    CASE

    WHEN t1.UserID = 'User01' THEN 'Test'

    WHEN t1.UserID IS NOT NULL THEN 'YES' ELSE NULL END AS t1

    Have a look here for a more complete explanation.

    3) No; the list is built by the Users CTE. A LEFT JOIN returns all the rows on the left hand table (in this case the CTE) and only those with a matching UserID on the right hand tables. Where there isn't a matching UserID, a NULL is returned. Have a search for SQL Server Joins for (much) better explanations.

    Does that help or confuse things? 😎


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • You've been more than helpful, thanks fella!!!

  • Again, you're welcome.

    This place has helped me out no end in the past. I'm glad I can pass on something of what I've learned here. Please read the links I posted though. Compared to many on here, I'm little more than an experienced noob myself. I've just outlined what the various components did, there's a lot more detail out there.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • You don't say what you want to do when you have multiple entries for a single user in any one of the tables. The solution provided will give you a partial CROSS JOIN.

    This approach requires only one scan of each of the tables, whereas the original solution required two.

    ;

    WITH CTE(UserID, t1, t1_date, t2, t2_date, t3, t3_date, t4, t4_date, t5, t5_date) AS (

    SELECT t.UserID, 'Yes', t.DATE, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

    FROM #T1 t

    UNION ALL

    SELECT t.UserID, NULL, NULL, 'Yes', t.DATE, NULL, NULL, NULL, NULL, NULL, NULL

    FROM #T2 t

    UNION ALL

    SELECT t.UserID, NULL, NULL, NULL, NULL, 'Yes', t.DATE, NULL, NULL, NULL, NULL

    FROM #T3 t

    UNION ALL

    SELECT t.UserID, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', t.DATE, NULL, NULL

    FROM #T4 t

    UNION ALL

    SELECT t.UserID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', t.DATE

    FROM #T5 t

    )

    SELECT CTE.UserID

    , MAX(CTE.t1) AS t1

    , MAX(CTE.t1_date) AS t1_date

    , MAX(CTE.t2) AS t2

    , MAX(CTE.t2_date) AS t2_date

    , MAX(CTE.t3) AS t3

    , MAX(CTE.t3_date) AS t3_date

    , MAX(CTE.t4) AS t4

    , MAX(CTE.t4_date) AS t4_date

    , MAX(CTE.t5) AS t5

    , MAX(CTE.t5_date) AS t5_date

    FROM CTE

    GROUP BY CTE.UserID

    I've only given the last (MAX) date that each user appears in each of the tables. If you need something else, please provide more representative data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hi Drew,

    Thanks for the alternative approach, you guys are great.

    On the question of multiple entries of the same UserID in single tables, well I thought long and hard about how to avoid this and here's what I did....

    Luckily, I created the front-end forms which capture the data for these tables.

    At the moment the tables and the forms are in development, but I wanted to ask about the reporting side of things in parallel with developing the forms (and might I say you guys have helped big time with that)

    So what i'm in the process of doing is adding some validation rules to each form which will check to see if the corresponding table already contains the UserID, if it does, the user will get a "lovely & pleasant - your names already down, you're not coming in" message... So that should take care of any duplicates.

    Thanks again, learnt so much from this thread alone today!

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

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