May 29, 2008 at 8:22 am
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
May 29, 2008 at 8:51 am
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
join nums n
on n.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
May 29, 2008 at 9:17 am
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. SelburgMay 29, 2008 at 9:19 am
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. SelburgJune 5, 2008 at 1:08 pm
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
June 5, 2008 at 1:16 pm
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. SelburgJune 5, 2008 at 1:39 pm
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
June 5, 2008 at 2:04 pm
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. SelburgJune 6, 2008 at 11:05 am
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 9, 2008 at 8:41 am
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
June 9, 2008 at 10:54 am
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
June 10, 2008 at 2:56 am
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
/Kenneth
June 10, 2008 at 10:19 am
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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 10, 2008 at 1:18 pm
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
June 10, 2008 at 1:23 pm
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