0

I was not sure about a descriptive Title of this question so I put there what i thought to be the best sentence.

I have a table like this:

StudentID, Month, Year, Present
-------------------------------
1, 1, 2016, P
1, 12, 2016, P
1, 4, 2017, P

I want to write SQL statement, without using cursor, to return a resultset like the below one:

Required output:

1, 1, 2016, P -- <-- it is 'P' in the table
1, 2, 2016, A
1, 3, 2016, A
1, 4, 2016, A
1, 5, 2016, A
1, 6, 2016, A
1, 7, 2016, A
1, 8, 2016, A
1, 9, 2016, A
1, 10, 2016, A
1, 11, 2016, A
1, 12, 2016, P -- <-- it is 'P' in the table
1, 1, 2017, A
1, 2, 2017, A
1, 3, 2017, A
1, 4, 2017, P -- <-- it is 'P' in the table
1, 5, 2017, A
1, 6, 2017, A
1, 7, 2017, A
1, 8, 2017, A
1, 9, 2017, A
1, 10, 2017, A
1, 11, 2017, A
1, 12, 2017, A

is this possible through one SQL statement, without using cursors.

I am working on SQL Server 2000 database.

4
    declare @t table(StudentID int, Month int, Year int, Present char(1));
    insert into @t 
    select 1, 1, 2016, 'P' union all
    select 1, 12, 2016, 'P' union all
    select 1, 4, 2017, 'P';

    select y.StudentID,
           y.y, m.m, 
           isnull(t.Present, 'A') as P 
    from
    (
    select StudentID, year as y       
    from @t
    group by StudentID, year
    ) y
    cross join

    (
    select number as m 
    from master..spt_values
    where type = 'p' and number between 1 and 12
    )m
    left join @t t
       on t.StudentID = y.StudentID and t.year = y.y  and t.month = m.m;

Explanation: we need as much rows as the number of students per number of years per 12 month, so I use cross join of distinct pairs student, year and 12 months.

As we have SQL Server 2000, we cannot use Row_number() to generate 12 values, niether VALUES, so we can join to a number table (I used spt_values even if I personally have a number table until 10000000) or we could generate in other ways, for example like this :

select 1 as m union all select 2...union all select 12

And finally join the presence list to a generated 'calendar' for every student

  • Some explanation of the techniques used would be beneficial. – Michael Green Jul 12 '17 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.