Get value from different table and use value in in different Query

Question

I have a query that uses a tally table to insert missing rows for a specified month. The query tests if a day is missing from the month and then gets distinct values from columns (in that month) and inserts those values and the missing date values into the table.

The table is used to monitor staff work days ,times and what section they work in. The problem is that in one of the columns Section if the staff member moves section the Distinct statement creates two sets of rows for that particular day.

This is working the query that gets undesired results, (@Zohar Peled helped me do this)

DECLARE @startdt DATETIME = '2016/6/1';
DECLARE @enddt DATETIME = '2016/7/1';
DECLARE @value nvarchar(50);


DECLARE @T as TABLE
(
    Staff_ID    int,
    [Date]      date, 
    [Year]      int,
    Mon         int,
    [Day]       int,
    First_Name  varchar(10),
    Last_Name   varchar(10),
    Section     varchar(10), 
    Time_Worked datetime
)

INSERT INTO @T VALUES
(1001, '2016-06-01', 2016, 6, 1, 'Bill', 'Price', 'Level 1', '2016-06-01 8:30:00.000'),
(1001, '2016-06-02', 2016, 6, 2, 'Bill', 'Price', 'Level 1', '2016-06-02 8:30:00.000'),
(1001, '2016-06-03', 2016, 6, 3, 'Bill', 'Price', 'Level 1', '2016-06-03 8:30:00.000'),
(1001, '2016-06-04', 2016, 6, 4, 'Bill', 'Price', 'Level 1', '2016-06-04 8:30:00.000'),
(1001, '2016-06-05', 2016, 6, 5, 'Bill', 'Price', 'Level 1', '2016-06-05 8:30:00.000'),
(1001, '2016-06-06', 2016, 6, 6, 'Bill', 'Price', 'Level 1', '2016-06-06 8:30:00.000'),
(1001, '2016-06-07', 2016, 6, 7, 'Bill', 'Price', 'Level 2', '2016-06-07 8:30:00.000'),-- Different section
(1001, '2016-07-05', 2016, 7, 5, 'Bill', 'Price', 'Level 2', '2016-07-5 8:30:00.000'), 
(1002, '2016-06-01', 2016, 6, 1, 'Mary', 'Somers', 'Level 1', '2016-06-01 8:30:00.000'),
(1002, '2016-06-05', 2016, 6, 5, 'Mary', 'Somers', 'Level 1', '2016-06-05 8:30:00.000'),
(1002, '2016-06-08', 2016, 6, 8, 'Mary', 'Somers', 'Level 1', '2016-06-08 8:30:00.000'),
(1003, '2016-06-03', 2016, 6, 3, 'Mark', 'Jones', 'Level 1', '2016-06-03 8:30:00.000'),
(1003, '2016-06-04', 2016, 6, 4, 'Mark', 'Jones', 'Level 1', '2016-06-05 8:30:00.000')

--@value = SELECT Section_Data FROM Staff_Manager.dbo.Staff_Data_TBL WHERE Staff_No = 1001


SET NOCOUNT ON;
                            IF object_id('dbo.Tally') is not null drop table dbo.tally

                            SELECT TOP 30000 IDENTITY(int,1,1) as ID
                               INTO dbo.Tally FROM master.dbo.SysColumns
                               ALTER table dbo.Tally
                               add constraint PK_ID primary key clustered(ID)
                            ; WITH Calendar AS
                             (
                                 SELECT dateadd(DD, ID-1, @startdt) as [Date]
                                   FROM dbo.Tally
                                   WHERE dateadd(DD, ID-1, @startdt) < @enddt
                            )


                            INSERT INTO @T(Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section)
                            SELECT DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, Section
                            FROM @T T
                            CROSS APPLY
                            (
                                SELECT Cal.[Date]
                                FROM Calendar Cal
                                WHERE MONTH(Cal.[Date]) = MONTH(T.[Date])
                                AND YEAR(Cal.[Date]) = YEAR(T.[Date])
                                AND NOT EXISTS
                                (
                                    SELECT 1
                                    FROM @T T2
                                    WHERE T.Staff_ID = T2.Staff_ID
                                    AND T2.[Date] = Cal.[Date]
                                )
                            ) C

                            SELECT Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section, Time_Worked 
                              FROM @T 
                              ORDER BY Staff_ID, [Date]

This is the result, and as you can see it creates two days for the staff member Bill Price,from 2016-06-08 on-wards due to him changing Section from Level 1 to Level 2. Being that the Distinct statement will get two values from the Section Column.

(Run the code and you will get all the results, this is just a snippit to show what I mean.)

Staff_ID    Date    Year    Mon Day First_Name  Last_Name   Section Time_Worked
1001    2016-06-01  2016    6   1   Bill    Price   Level 1 2016-06-01 08:30:00.000
1001    2016-06-02  2016    6   2   Bill    Price   Level 1 2016-06-02 08:30:00.000
1001    2016-06-03  2016    6   3   Bill    Price   Level 1 2016-06-03 08:30:00.000
1001    2016-06-04  2016    6   4   Bill    Price   Level 1 2016-06-04 08:30:00.000
1001    2016-06-05  2016    6   5   Bill    Price   Level 1 2016-06-05 08:30:00.000
1001    2016-06-06  2016    6   6   Bill    Price   Level 1 2016-06-06 08:30:00.000
1001    2016-06-07  2016    6   7   Bill    Price   Level 2 2016-06-07 08:30:00.000
1001    2016-06-08  2016    6   8   Bill    Price   Level 1 NULL
1001    2016-06-08  2016    6   8   Bill    Price   Level 2 NULL
1001    2016-06-09  2016    6   9   Bill    Price   Level 1 NULL
1001    2016-06-09  2016    6   9   Bill    Price   Level 2 NULL
1001    2016-06-10  2016    6   10  Bill    Price   Level 1 NULL
1001    2016-06-10  2016    6   10  Bill    Price   Level 2 NULL
1001    2016-06-11  2016    6   11  Bill    Price   Level 1 NULL
1001    2016-06-11  2016    6   11  Bill    Price   Level 2 NULL
1001    2016-06-12  2016    6   12  Bill    Price   Level 1 NULL
1001    2016-06-12  2016    6   12  Bill    Price   Level 2 NULL
1001    2016-06-13  2016    6   13  Bill    Price   Level 1 NULL
1001    2016-06-13  2016    6   13  Bill    Price   Level 2 NULL

So I have another table that holds the staff members current section, see code below.

DECLARE @value nvarchar(50);   

DECLARE @T3 as TABLE
(
    Staff_ID    int,    
    First_Name  varchar(10),
    Last_Name   varchar(10),
    Section     varchar(10) 

)

INSERT INTO @T3 VALUES
(1001, 'Bill', 'Price', 'Level 2'),
(1002, 'Mary', 'Somers', 'Level 1'),
(1003, 'Mark', 'Jones', 'Level 1')


SELECT Section FROM @T3 WHERE Staff_ID = 1001

The results are as below, getting the Section data fro that staff member.

Level 2

Now if I could get this value and use it as the default value in the Section column that would solve my issues.

This code obviously is incorrect, but instead of getting the distinct value of the Section column, replace that value with the value from the other table @T3, Something like this,

@value = SELECT Section FROM @T3 WHERE Staff_ID = 1001

And then have that value inserted into the new rows.

DECLARE @value nvarchar(50);

@value = SELECT Section FROM @T3 WHERE Staff_ID = 1001

INSERT INTO @T(Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section)
                            SELECT DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, @value
                            FROM @T T
                            CROSS APPLY
                            (
                                SELECT Cal.[Date]
                                FROM Calendar Cal
                                WHERE MONTH(Cal.[Date]) = MONTH(T.[Date])
                                AND YEAR(Cal.[Date]) = YEAR(T.[Date])
                                AND Section = @value
                                AND NOT EXISTS
                                (
                                    SELECT 1
                                    FROM @T T2
                                    WHERE T.Staff_ID = T2.Staff_ID
                                    AND T2.[Date] = Cal.[Date]
                                )
                            ) C

Any help would be much appreciated.

is there any where that you store the date when Staff changed section ? — Squirrel
– Squirrel, Commented Aug 3, 2016 at 2:07
also for Staff 1003, there isn't any record for June 1 & 2. How would you determine the Section ? — Squirrel
– Squirrel, Commented Aug 3, 2016 at 2:13
@Squirrel, that is what the second table @T3 does. It keeps a constant Section data. That is why I want that data to be inserted to the first table. As for your second question, that is what the Distinct is for. I first need to get values from each column to insert (the distinct ensures that a value is found, which is also why I get two rows for each day when the section data has 2 unique values), which is why I could not just arbitrarily get data from the first day of the month, b/c they may not have worked on the first day of the month. — KyloRen
– KyloRen, Commented Aug 3, 2016 at 2:19

Squirrel · Accepted Answer · 2016-08-03 02:40:30Z

1

see comments within code

; WITH Calendar AS
(
        SELECT dateadd(DD, ID-1, @startdt) as [Date]
        FROM   dbo.Tally
        WHERE  dateadd(DD, ID-1, @startdt) < @enddt  -- changed for better performance
)
INSERT INTO @T(Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section)
SELECT  DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, S.Section -- changed to use S.Section
FROM    @T T
    CROSS APPLY
    (
        SELECT  Cal.[Date]
        FROM    Calendar Cal            
        WHERE   MONTH(Cal.[Date]) = MONTH(T.[Date])
        AND     YEAR(Cal.[Date]) = YEAR(T.[Date])
        AND     NOT EXISTS
                (
                    SELECT  *
                    FROM    @T T2
                    WHERE   T.Staff_ID = T2.Staff_ID
                    AND     T2.[Date] = Cal.[Date]
                )
    ) C
    OUTER APPLY   -- added to get last known section based on date
    (
        SELECT  TOP 1 Section
        FROM    @T x
        WHERE   x.Staff_ID  = T.Staff_ID
        AND x.Date      < C.Date
        ORDER BY x.Date DESC
    ) S

if there isn't any records in found in the OUTER APPLY and you want to show the currenct section, join to the @T3 and get the section from there

answered Aug 3, 2016 at 2:40

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KyloRen Over a year ago

You are a genius!!! Let me test this on the real Database, I will get back to you ASAP.

KyloRen Over a year ago

Thanks so much for helping me with this. One question, I have not used Join much, where and how would I implement that. For the record , the question is answered, I just want to know for myself, for future reference.

Squirrel Over a year ago

there several type of join like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN . Basically you will use JOIN to join more than 1 tables together. The most common will be INNER JOIN. You could just search or pickup any books on SQL to learn more about various type of joins

KyloRen Over a year ago

OK, I understand now. Thanks again and UV for the great help.

Collectives™ on Stack Overflow

Get value from different table and use value in in different Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related