sql-serverjoinrow-number
Joining DB tables: repeat values in one table for each group in second table
Students are absent from classes, I have to calculate consecutive absences. 'Date' table has all dates in school year, and indicates if school is in session. Not in session for weekends or holidays. 'Absence' table lists students and the dates they were absent from specific class. I need to list all dates for each 'CLASS' group to see if a class is missed for consecutive days. I have tried CROSS JOIN, couldn't get it to work. Maybe ROW_NUMBER can be used. Any type of solution is welcome!
Date table with dates school is in session. Absence tables with days a student is absent and which class. Last picture is table I want to create. I would like to repeat the dates in the range for each Student/Class group
Solution
Try something like the following. You can run this example in SSMS.
DECLARE @Absent table ( stu_id varchar(10), class varchar(10), absent_date date );
INSERT INTO @Absent VALUES
( 'Billie', 'English', '11/03/2020' ),
( 'Billie', 'Math', '11/10/2020' ),
( 'Billie', 'Math', '11/12/2020' ),
( 'Billie', 'Science', '11/19/2020' ),
( 'Billie', 'Math', '11/19/2020' );
DECLARE @InSession table ( CalDate date, InSession bit );
INSERT INTO @InSession VALUES
( '11/02/2020', 1 ),( '11/03/2020', 1 ),( '11/04/2020', 1 ),( '11/05/2020', 1 ),( '11/06/2020', 1 ),( '11/07/2020', 0 ),( '11/08/2020', 0 ),
( '11/09/2020', 1 ),( '11/10/2020', 1 ),( '11/11/2020', 0 ),( '11/12/2020', 1 ),( '11/13/2020', 1 ),( '11/14/2020', 0 ),
( '11/15/2020', 0 ),( '11/16/2020', 1 ),( '11/17/2020', 1 ),( '11/18/2020', 1 ),( '11/19/2020', 1 ),( '11/20/2020', 0 );
SELECT DISTINCT
FORMAT ( s.CalDate, 'MM/dd/yyyy' ) AS CalDate,
s.InSession,
a.stu_id,
CASE
WHEN a.absent_date = s.CalDate THEN a.class
ELSE ''
END AS class,
CASE
WHEN a.absent_date = s.CalDate THEN FORMAT ( a.absent_date, 'MM/dd/yyyy' )
ELSE ''
END AS absent_date
FROM @InSession AS s
CROSS APPLY @Absent AS a
ORDER BY
s.CalDate;
Returns
+------------+-----------+--------+---------+-------------+
| CalDate | InSession | stu_id | class | absent_date |
+------------+-----------+--------+---------+-------------+
| 2020-11-02 | 1 | Billie | | |
| 2020-11-03 | 1 | Billie | | |
| 2020-11-03 | 1 | Billie | English | 11/03/2020 |
| 2020-11-04 | 1 | Billie | | |
| 2020-11-05 | 1 | Billie | | |
| 2020-11-06 | 1 | Billie | | |
| 2020-11-07 | 0 | Billie | | |
| 2020-11-08 | 0 | Billie | | |
| 2020-11-09 | 1 | Billie | | |
| 2020-11-10 | 1 | Billie | | |
| 2020-11-10 | 1 | Billie | Math | 11/10/2020 |
| 2020-11-11 | 0 | Billie | | |
| 2020-11-12 | 1 | Billie | | |
| 2020-11-12 | 1 | Billie | Math | 11/12/2020 |
| 2020-11-13 | 1 | Billie | | |
| 2020-11-14 | 0 | Billie | | |
| 2020-11-15 | 0 | Billie | | |
| 2020-11-16 | 1 | Billie | | |
| 2020-11-17 | 1 | Billie | | |
| 2020-11-18 | 1 | Billie | | |
| 2020-11-19 | 1 | Billie | | |
| 2020-11-19 | 1 | Billie | Math | 11/19/2020 |
| 2020-11-19 | 1 | Billie | Science | 11/19/2020 |
| 2020-11-20 | 0 | Billie | | |
+------------+-----------+--------+---------+-------------+
Note that on 11/19/2020 Billie is marked absent from two classes, creating multiple results for the day.
You will also receive a blank row in addition to an "absent" row on days an absence has been reported. I'm looking to see if I can resolve this part.
UPDATE
Removed the "empty" rows on absent days.
SELECT DISTINCT
s.CalDate,
s.InSession,
a.stu_id,
ISNULL ( x.class, '' ) AS class,
ISNULL ( x.absent_date, '' ) AS absent_date
FROM @InSession AS s
CROSS APPLY @Absent AS a
OUTER APPLY (
SELECT
ab.class, CONVERT ( varchar(10), ab.absent_date, 101 ) AS absent_date
FROM @Absent AS ab
WHERE
ab.stu_id = a.stu_id
AND ab.absent_date = s.CalDate
) AS x
ORDER BY
s.CalDate, stu_id, absent_date;
Returns
+------------+-----------+--------+---------+-------------+
| CalDate | InSession | stu_id | class | absent_date |
+------------+-----------+--------+---------+-------------+
| 2020-11-02 | 1 | Billie | | |
| 2020-11-03 | 1 | Billie | English | 11/03/2020 |
| 2020-11-04 | 1 | Billie | | |
| 2020-11-05 | 1 | Billie | | |
| 2020-11-06 | 1 | Billie | | |
| 2020-11-07 | 0 | Billie | | |
| 2020-11-08 | 0 | Billie | | |
| 2020-11-09 | 1 | Billie | | |
| 2020-11-10 | 1 | Billie | Math | 11/10/2020 |
| 2020-11-11 | 0 | Billie | | |
| 2020-11-12 | 1 | Billie | Math | 11/12/2020 |
| 2020-11-13 | 1 | Billie | | |
| 2020-11-14 | 0 | Billie | | |
| 2020-11-15 | 0 | Billie | | |
| 2020-11-16 | 1 | Billie | | |
| 2020-11-17 | 1 | Billie | | |
| 2020-11-18 | 1 | Billie | | |
| 2020-11-19 | 1 | Billie | Math | 11/19/2020 |
| 2020-11-19 | 1 | Billie | Science | 11/19/2020 |
| 2020-11-20 | 0 | Billie | | |
+------------+-----------+--------+---------+-------------+
UPDATE
So I want the date range, Nov 2 - 20, repeated for every CLASS and every STUDENT included in the raw data.
Try this:
SELECT
CalDate,
InSession,
stu_id,
class,
MAX ( absent_date ) AS absent_date
FROM (
SELECT DISTINCT
CalDate,
InSession,
stu_id,
x.class,
CASE
WHEN x.absent_date = CalDate THEN CONVERT ( varchar(10), x.absent_date, 101 )
ELSE ''
END AS absent_date
FROM @InSession AS i
CROSS APPLY @Absent AS a
OUTER APPLY (
SELECT
ab.class, CONVERT ( varchar(10), ab.absent_date, 101 ) AS absent_date
FROM @Absent AS ab
WHERE
ab.stu_id = a.stu_id
AND ab.absent_date = a.absent_date
) AS x
WHERE
i.CalDate BETWEEN '11/02/2020' AND '11/30/2020'
) AS StudentAbsent
GROUP BY
CalDate, InSession, stu_id, class
ORDER BY
stu_id, class, CalDate;
Returns
+------------+-----------+--------+---------+-------------+
| CalDate | InSession | stu_id | class | absent_date |
+------------+-----------+--------+---------+-------------+
| 2020-11-02 | 1 | Billie | English | |
| 2020-11-03 | 1 | Billie | English | 11/03/2020 |
| 2020-11-04 | 1 | Billie | English | |
| 2020-11-05 | 1 | Billie | English | |
| 2020-11-06 | 1 | Billie | English | |
| 2020-11-07 | 0 | Billie | English | |
| 2020-11-08 | 0 | Billie | English | |
| 2020-11-09 | 1 | Billie | English | |
| 2020-11-10 | 1 | Billie | English | |
| 2020-11-11 | 0 | Billie | English | |
| 2020-11-12 | 1 | Billie | English | |
| 2020-11-13 | 1 | Billie | English | |
| 2020-11-14 | 0 | Billie | English | |
| 2020-11-15 | 0 | Billie | English | |
| 2020-11-16 | 1 | Billie | English | |
| 2020-11-17 | 1 | Billie | English | |
| 2020-11-18 | 1 | Billie | English | |
| 2020-11-19 | 1 | Billie | English | |
| 2020-11-20 | 0 | Billie | English | |
| 2020-11-02 | 1 | Billie | Math | |
| 2020-11-03 | 1 | Billie | Math | |
| 2020-11-04 | 1 | Billie | Math | |
| 2020-11-05 | 1 | Billie | Math | |
| 2020-11-06 | 1 | Billie | Math | |
| 2020-11-07 | 0 | Billie | Math | |
| 2020-11-08 | 0 | Billie | Math | |
| 2020-11-09 | 1 | Billie | Math | |
| 2020-11-10 | 1 | Billie | Math | 11/10/2020 |
| 2020-11-11 | 0 | Billie | Math | |
| 2020-11-12 | 1 | Billie | Math | 11/12/2020 |
| 2020-11-13 | 1 | Billie | Math | |
| 2020-11-14 | 0 | Billie | Math | |
| 2020-11-15 | 0 | Billie | Math | |
| 2020-11-16 | 1 | Billie | Math | |
| 2020-11-17 | 1 | Billie | Math | |
| 2020-11-18 | 1 | Billie | Math | |
| 2020-11-19 | 1 | Billie | Math | 11/19/2020 |
| 2020-11-20 | 0 | Billie | Math | |
| 2020-11-02 | 1 | Billie | Science | |
| 2020-11-03 | 1 | Billie | Science | |
| 2020-11-04 | 1 | Billie | Science | |
| 2020-11-05 | 1 | Billie | Science | |
| 2020-11-06 | 1 | Billie | Science | |
| 2020-11-07 | 0 | Billie | Science | |
| 2020-11-08 | 0 | Billie | Science | |
| 2020-11-09 | 1 | Billie | Science | |
| 2020-11-10 | 1 | Billie | Science | |
| 2020-11-11 | 0 | Billie | Science | |
| 2020-11-12 | 1 | Billie | Science | |
| 2020-11-13 | 1 | Billie | Science | |
| 2020-11-14 | 0 | Billie | Science | |
| 2020-11-15 | 0 | Billie | Science | |
| 2020-11-16 | 1 | Billie | Science | |
| 2020-11-17 | 1 | Billie | Science | |
| 2020-11-18 | 1 | Billie | Science | |
| 2020-11-19 | 1 | Billie | Science | 11/19/2020 |
| 2020-11-20 | 0 | Billie | Science | |
+------------+-----------+--------+---------+-------------+
- Unable to connect to Azure Function App after integrating into VNET
- How do I remove non-breaking spaces from a column in SQL server?
- Stored procedure to insert a new employee and return the newly created record (including the Id)
- Procedure is not returning any data when run from VB6
- Create an identity column that depends on another column
- Difference between @@ and @ in SQL Server 2008 R2
- String_agg for SQL Server before 2017
- Convert file content from XML file to UTF-16
- SQL Get Numeric Value Nearest Rounded Off from Input Value
- Add new property to nested object in all objects of JSON array with SQL
- Sql Server query varchar data sort like int
- Calculate Date Difference for Non-Consecutive Months
- SQL Server reports 'Invalid column name', but the column is present and the query works through management studio
- Show only one database on SQL Server
- When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?
- How to show day name in SQL Server?
- How to get continous contract periods for customer
- Use LDAP to authenticate users of a SQL Server database?
- To change the IDENTITY property of a column error in EF Core DB
- Add leading 0 and also swap MMDDYYYY INT to YYYYDDMM INT
- Join SQL tables back together to create a report
- Create a Range From n to 1 in SQL
- How can I quickly detect and resolve SQL Server Index fragmentation for a database?
- How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
- 12 digits format - is this a datetime or?
- Split a string based on multiple variable substring
- Dockerized .NET API not responding to GET request on localhost
- ASP NET Core Microsoft.Data.SqlClient.SqlException (0x80131904)
- SQL Installation Error : directory is compressed or is in a compressed directory
- How to keep existing value (append) while using SqlParameter to update a record