KyloRen KyloRen - 4 months ago 7
SQL Question

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

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.

Answer

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

Comments