Convert month to date and add value in datewise from Month column

  • I am working on attendance table. We have like school,studentno,year and attendance. hard part is attendance column we have 10 attendance column from sep to Jun. September column stores students 31 days of students presents ,absent etc.. similar like other all month column of oct,nov,dec etcc.

    example

    School ID Student ID year october Nov etc

    1040 123786 2006 PPPPP---PPPP--PPCPP--PAPPP--PPP -----SP--PPPPP--PPPPP--PPAPP-

    so october is 31 days -- is holidays

    i want t0 have like

    Date school id student id year attendance

    10/1/2006 1040 123786 2006 P ( first P of october)

    10/2/2006 1040 123786 2006 P ( senond P of october)

    and so on I have to do it for all month etc..

    Please help and guide me to fix this issue

    Thanks

    Ram

  • Here's a way you can do it.

    If I understand the question correctly, you want to strip out each letter that corresponds to each day in a month (like october).

    This examples shows just the october month (column).

    The example also requires that you have a numberstable called 'nums' with a single column called 'n'

    'n' needs to have values ranging from 1-31 at the least.

    Also note that the query shown has some properties hardcoded, such as 'october' and the month '10'.

    create table #original

    ( schoolId int not null, studentId int not null, year int not null, october varchar(31) not null )

    insert #original

    select 1040, 123786, 2006, 'PPPPP---PPPP--PPCPP--PAPPP--PPP'

    -- Here's the actual query:

    select cast(o.year as char(4)) + '10' + right('0' + cast(n.n as varchar(2)), 2) as 'date',

    o.schoolId,

    o.studentId,

    o.year,

    substring(o.october, n.n, 1)

    from#original o

    joinnums n

    onn.n <= len(o.october)

    date schoolId studentId year

    -------- ----------- ----------- ----------- ----

    20061001 1040 123786 2006 P

    20061002 1040 123786 2006 P

    20061003 1040 123786 2006 P

    20061004 1040 123786 2006 P

    20061005 1040 123786 2006 P

    20061006 1040 123786 2006 -

    20061007 1040 123786 2006 -

    20061008 1040 123786 2006 -

    20061009 1040 123786 2006 P

    20061010 1040 123786 2006 P

    20061011 1040 123786 2006 P

    20061012 1040 123786 2006 P

    20061013 1040 123786 2006 -

    20061014 1040 123786 2006 -

    20061015 1040 123786 2006 P

    20061016 1040 123786 2006 P

    20061017 1040 123786 2006 C

    20061018 1040 123786 2006 P

    20061019 1040 123786 2006 P

    20061020 1040 123786 2006 -

    20061021 1040 123786 2006 -

    20061022 1040 123786 2006 P

    20061023 1040 123786 2006 A

    20061024 1040 123786 2006 P

    20061025 1040 123786 2006 P

    20061026 1040 123786 2006 P

    20061027 1040 123786 2006 -

    20061028 1040 123786 2006 -

    20061029 1040 123786 2006 P

    20061030 1040 123786 2006 P

    20061031 1040 123786 2006 P

    (31 row(s) affected)

    /Kenneth

  • I believe this is the easiest way to get the job done.

    -- create some tables to hold sample data and results

    DECLARE @att TABLE

    (SchoolID INT

    ,StudentID INT

    ,SchoolYear INT

    ,sept VARCHAR(30)

    ,oct VARCHAR(31)

    ,nov VARCHAR(30)

    ,dec VARCHAR(31)

    ,jan VARCHAR(31)

    ,feb VARCHAR(30)

    ,mar VARCHAR(31)

    ,apr VARCHAR(30)

    ,may VARCHAR(31)

    ,jun VARCHAR(30))

    DECLARE @results TABLE

    (SchoolID INT

    ,StudentID INT

    ,SchoolYear INT

    ,[date] DATETIME

    ,attendance CHAR(1))

    INSERT @att

    SELECT 1021, 321, 2007

    ,'PPPPP---PPPP--PPCPP--PAPPP--PP'

    ,'PPP--PPPPP--PPCPP--PAPPP--PPPAA'

    ,'--PPPAP--APPPP--PPCPP--PAPPP--'

    ,'PPPP--PPCPP--PAPPP--PPPAA-'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    UNION ALL

    SELECT 1021, 322, 2007

    ,'PPPPP---PPPP--PPCPP--PAPPP--PP'

    ,'PPP--PPPPP--PPCPP--PAPPP--PPPAA'

    ,'--PPPAP--APPPP--PPCPP--PAPPP--'

    ,'PPPP--PPCPP--PAPPP--PPPAA-'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    ,'PP---PPPP--PPCPP--PAPPP--PPPAA'

    -- set a variable for the school year

    DECLARE @schoolYear DATETIME

    SET @schoolYear = '9/1/2007'

    --

    INSERT @results

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,0,@schoolYear)) AS [date]

    ,SUBSTRING(sept,t.n,1) AS sept

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 30

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,1,@schoolYear)) AS [date]

    ,SUBSTRING(oct,t.n,1) AS oct

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 31

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,2,@schoolYear)) AS [date]

    ,SUBSTRING(nov,t.n,1) AS nov

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 30

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,3,@schoolYear)) AS [date]

    ,SUBSTRING(dec,t.n,1) AS dec

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 31

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,4,@schoolYear)) AS [date]

    ,SUBSTRING(jan,t.n,1) AS jan

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 31

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,5,@schoolYear)) AS [date]

    ,SUBSTRING(feb,t.n,1) AS feb

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 30

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,6,@schoolYear)) AS [date]

    ,SUBSTRING(mar,t.n,1) AS mar

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 31

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,7,@schoolYear)) AS [date]

    ,SUBSTRING(apr,t.n,1) AS apr

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 30

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,8,@schoolYear)) AS [date]

    ,SUBSTRING(may,t.n,1) AS may

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 31

    UNION

    SELECT

    SchoolID,StudentID,SchoolYear

    ,DATEADD(DAY,t.n-1,DATEADD(MONTH,9,@schoolYear)) AS [date]

    ,SUBSTRING(jun,t.n,1) AS jun

    FROM @ATT

    CROSS JOIN dbo.tally AS t

    WHERE t.n <= 30

    SELECT * FROM @results ORDER by studentid, [date]

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Of course I hit post, and see that Kenneth beat me to it ... LOL

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • are you guys (jason and Kenneth) having majic wand!. Amazing u guys replied just like a sec. I am sorry i was on vacation. I am working on this sql. I need to put in the separate table. But the data are duplicating. I just very little modified your query and tried it.

    The sql is

    I am not great sql writer. I didnot create any index or primary key like etc.. Why it is happening.? does Cross join make differences?

    Thanks

    Ram

    declare @att table

    (sch char(4),

    studentno char(6),

    -- cast([year] as int),

    year char(4),

    msep char(30),

    moct char(31),

    mnov char(30),

    mdec char(31),

    mjan char(31),

    mfeb char(30),

    mmar char(31),

    mapr char(30),

    mmay char(31),

    mjun char(30) )

    insert @att

    select sch,studentno,[year],msep,moct,mnov,mdec,mjan,mfeb,mmar,mapr,mmay,mjun from stuatndi

    DECLARE @schoolyear DATETIME

    SET @SCHOOLYEAR ='9/1/2007'

    INSERT attresults

    SELECT sch,studentno,cast([year]as int) , DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(msep,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=30

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(moct,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mnov,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE.number<=30

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mdec,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mjan,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mfeb,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=29

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mmar,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mmay,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mjun,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=30

  • ramaanujamr (6/5/2008)


    are you guys (jason and Kenneth) having majic wand!. Amazing u guys replied just like a sec. I am sorry i was on vacation. I am working on this sql. I need to put in the separate table. But the data are duplicating. I just very little modified your query and tried it.

    The sql is

    I am not great sql writer. I didnot create any index or primary key like etc.. Why it is happening.? does Cross join make differences?

    Thanks

    Ram

    declare @att table

    (sch char(4),

    studentno char(6),

    -- cast([year] as int),

    year char(4),

    msep char(30),

    moct char(31),

    mnov char(30),

    mdec char(31),

    mjan char(31),

    mfeb char(30),

    mmar char(31),

    mapr char(30),

    mmay char(31),

    mjun char(30) )

    insert @att

    select sch,studentno,[year],msep,moct,mnov,mdec,mjan,mfeb,mmar,mapr,mmay,mjun from stuatndi

    DECLARE @schoolyear DATETIME

    SET @SCHOOLYEAR ='9/1/2007'

    INSERT attresults

    SELECT sch,studentno,cast([year]as int) , DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(msep,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=30

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(moct,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mnov,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE.number<=30

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mdec,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mjan,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mfeb,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=29

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mmar,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mmay,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=31

    UNION

    SELECT sch,studentno,cast([year] as int), DATEADD(DAY,t.number-1,DATEADD(MONTH,0,@SCHOOlYEAR)) AS [DATE],SUBSTRING(mjun,t.number,1) AS September FROM @att

    CROSS JOIN dbo.tally AS t

    WHERE t.number<=30

    You're not incrementing the X in DATEADD(MONTH, X, @SchoolYear)

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Hi Jason,

    Do i need to create month table from 1 to 12 or should i add one more column in the number table ?

    I am not clear. Please let me know.

    Thanks

    Ram

  • In your case, just plug the numbers into your query.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Seems like a lot of effort for what's essentially a need to navigate a calendar, which suggests a solution that will work well provided the proper number of attendance characters fill each month's column all the time. Try this on for size, which provides some data based on 9/1/2007 thru 6/30/2008.

    -- create some tables to hold sample data and results

    DECLARE @ATTENDANCE TABLE (

    SchoolID int,

    StudentID int,

    SchoolYear int,

    sep varchar(30),

    oct varchar(31),

    nov varchar(30),

    dec varchar(31),

    jan varchar(31),

    feb varchar(29),

    mar varchar(31),

    apr varchar(30),

    may varchar(31),

    jun varchar(30)

    )

    DECLARE @RESULTS TABLE (

    SchoolID int,

    StudentID int,

    SchoolYear int,

    [DATE] DateTime,

    Attendance char(1)

    )

    INSERT @ATTENDANCE

    SELECT 1021, 321, 2007

    ,'---PPPP--PPPPP--CPPPP--APPPP--' -- SEPTEMBER (30)

    ,'PPPPP--PPPPC--PPPPA--PPPPP--PAA' -- OCTOBER (31)

    ,'PP--APAPP--PPPPC--PPP----PPPPP' -- NOVEMBER (30)

    ,'--PPPPP--PPCPP--PAPPP----PAA---' -- DECEMBER (31)

    ,'-PPP--PPCPP--PAPPP--PPPAA--PPPP' -- JANUARY (31)

    ,'P--PPPPP--PPCPP--PAPPP--PAAPP' -- FEBRUARY (29)

    ,'--PAPPP--PPCPP--PAPPP--PPPAA--P' -- MARCH (31)

    ,'PPPP--PPCPP--PAPPP--PPPAA--PPP' -- APRIL (30)

    ,'PA--PPPPP--PPCPP--PAPPP--PPPAA-' -- MAY (31)

    ,'-PPPPA--PPCPP--PAPPP--PPPAA--P' -- JUNE (30)

    UNION ALL

    SELECT 1021, 322, 2007

    ,'---PPPP--PPAPP--PPPPP--PPPPP--' -- SEPTEMBER (30)

    ,'PPPPP--PPPPC--PPPPA--PPAPP--PAA' -- OCTOBER (31)

    ,'PP--APAPP--PPPPP--PPP----PPPPP' -- NOVEMBER (30)

    ,'--PPPPP--PPCPP--PAPPP----PAA---' -- DECEMBER (31)

    ,'-PPP--PPCPP--PPPPP--PPPAA--PPPP' -- JANUARY (31)

    ,'P--PPPPP--PPCPP--PPPPP--PAAPP' -- FEBRUARY (29)

    ,'--PAPPP--PPAPP--PAPPP--PPPAA--P' -- MARCH (31)

    ,'PPPP--PPPPP--PAPPP--PPPAA--PPP' -- APRIL (30)

    ,'PA--PPPPA--PPAPP--PCPPP--PPPAA-' -- MAY (31)

    ,'-PPPPA--PPCPP--PAPPP--PPPPP--P' -- JUNE (30)

    -- set variables for the loop, with start and ending dates

    DECLARE @loop_dt datetime, @sy_end datetime, @kdt datetime, @sy_start datetime, @c_mth char(2)

    SET @loop_dt = DATEADD(m,0, CAST((SELECT MIN(SchoolYear) FROM @ATTENDANCE) AS char(4)) + '0901 00:00:00')

    SET @sy_end = DATEADD(m, 10, @loop_dt)

    SET @sy_start = @loop_dt

    -- create a table to combine the text values from all 10 months

    DECLARE @COMBINE_IT TABLE (

    SchoolID int,

    StudentID int,

    SchoolYear int,

    COMBINED_TEXT varchar(305)

    )

    -- populate the combined text values table

    INSERT INTO @COMBINE_IT

    SELECT SchoolID, StudentID, SchoolYear,

    sep + oct + nov + dec + jan + feb + mar + apr + may + jun AS COMBINED_TEXT

    FROM @ATTENDANCE

    WHERE SchoolYear = YEAR(@loop_dt)

    -- loop through the entire school year, one day at a time, and select out the one character needed

    WHILE @loop_dt < @sy_end

    BEGIN

    SELECT @c_mth = CASE WHEN MONTH(@loop_dt) < 10 THEN '0' +

    LTRIM(RTRIM(CAST(MONTH(@loop_dt) AS varchar(2))))

    ELSE RTRIM(LTRIM(CAST(MONTH(@loop_dt) AS varchar(2)))) END

    SET @kdt = DATEADD(d,-1,DATEADD(m,1,CAST(YEAR(@loop_dt) AS char(4)) + @c_mth + '01 00:00:00'))

    INSERT INTO @RESULTS

    SELECT SchoolID, StudentID, SchoolYear, @loop_dt,

    SUBSTRING(COMBINED_TEXT, DATEDIFF(d,@sy_start,@loop_dt)+1,1)

    FROM @COMBINE_IT

    SET @loop_dt = DATEADD(d,1,@loop_dt)

    END

    -- select results by Student to make it easy to see that the results are accurate

    SELECT *

    FROM @RESULTS

    ORDER BY StudentID, [DATE]

    The idea is to navigate the calendar one day at a time and just grab a substring from a combined text string value. This will work beautifully provided that the individual days data in each monthly column always has the full complement of character (1 per day in that month). My sample data is set up that way. See the attached Excel spreadsheet for the output from this query, which ran on SQL Server 2005.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Great. It works well. Thank you very much. I dont know how to appreciate your guys.

    Your guy proved yourself as 'SQL GURU'

    Thanks

    Ram

  • Hi All,

    I have another issue in this query. We dont want to show up weekends . So we want to eliminate saturday and sunday as a row in the table. Just we want to show week days. How do we do that?. Can you guys please help me?

    Thanks

    Ram

  • It seems like the scope is increasing from the original problem..?

    If you're starting to need exceptions in the dates based on 'non-calendar' attributes, such as workdays (next thing is perhaps holidays in mo-fr?), I'd suggest that you step back and create a calendar table instead. That way it'd be much easier to keep track of the odd days, and also more flexible when you find the next thing you need to take into account.

    Here's one place among many that shows how and why to use one

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    /Kenneth

  • This is actually fairly easy to do, and if we assume that the data is limited to a single school year, you won't necessarily need a calendar table. Just change the following in my query:

    SELECT *

    FROM @RESULTS

    ORDER BY StudentID, [DATE]

    to

    SELECT *

    FROM @RESULTS

    WHERE DATEPART(dw,[DATE]) BETWEEN 2 AND 6 AND -- Weekdays only

    [DATE] NOT IN ('09/xx/2007', -- Labor Day

    '10/xx/2007', -- Columbus Day

    '11/xx/2007, '11/xx/2007', -- Thanksgiving

    '12/25/2007', -- Christmas

    '01/01/2008', -- New Year's Day

    '05/xx/2008') -- Memorial Day

    -- You'll have to update the values with xx's to accurately reflect the

    -- dates to be excluded, whether they're holidays or just scheduled

    -- days where no classes are held

    ORDER BY StudentID, [DATE]

    Does that take care of it?

    Steve

    (aka smunson)

    :):):)

    ramaanujamr (6/9/2008)


    Hi All,

    I have another issue in this query. We dont want to show up weekends . So we want to eliminate saturday and sunday as a row in the table. Just we want to show week days. How do we do that?. Can you guys please help me?

    Thanks

    Ram

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hi All,

    It works weel but I have 13 years of data. Current year data is only at one table and all remaining past years is historical table and also i want to calcualte snow day or emergency day.

    And also i have another issue with sometime student left the school middle of the school year and we call it as E in the attendance column. But this sql generates blank rows after the student left for the school. Sometime student back to school after sometime in the sameyear and he will have the student number also. How do i add it this functionality.

    Thanks

    Ram

  • You probably want to be careful about DATEPART(weekday), since its return can be affected by the DATEFIRST setting.

    So - consider using:

    SELECT *

    FROM @RESULTS

    where datediff(day,'19000106',[DATE])%7>1

    ORDER BY StudentID, [DATE]

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 15 posts - 1 through 15 (of 16 total)

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