select
DISTINCT
case
WHEN ASSIGNMENT.COURSECODE = 'E00030' THEN ASSIGNNAME
ELSE ASSIGNNAME + ' ('+C.COURSE+')'
END AS ASSIGNNAME,
assignment.assignid,
CONVERT(VARCHAR(10),assigndate, 101) as assigndate,
assigndate,
assignmenttype,
case substring (cast(assignscale as varchar(1)),1,1)
WHEN 1 THEN '1-4'
WHEN 2 THEN '1-3'
WHEN 4 THEN '1-100'
END AS ASSIGNMENTSCALE,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol1,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol2,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol3,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol4,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol5,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol6,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol7,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol8,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol9,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol10,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol11,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol12,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol13,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol14,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol15,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol16,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol17,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol18,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol19,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol20,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol21,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol22,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol23,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol24,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol25,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol26,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol27,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol28,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol29,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol30,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol31,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '20007103') as StuCol32,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol33,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol34,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol35,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol36,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol37,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol38,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol39,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol40,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol41,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol42,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol43,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol44,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol45,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol46,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol47,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol48,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol49,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol50,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol51,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol52,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol53,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol54,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol55,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol56,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol57,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol58,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol59,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol60,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol61,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol62,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol63,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol64,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol65,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol66,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol67,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol68,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol69,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol70,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol71,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol72,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol73,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol74,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol75,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol76,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol77,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol78,
(select mark from assignmarks where assignid = assignment.assignid and stunum = '') as StuCol79,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC1,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC2,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC3,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC4,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC5,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC6,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC7,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC8,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC9,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC10,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC11,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC12,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC13,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC14,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC15,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC16,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC17,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC18,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC19,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC20,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC21,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC22,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC23,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC24,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC25,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC26,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC27,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC28,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC29,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC30,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC31,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '20007103') as UC32,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC33,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC34,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC35,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC36,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC37,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC38,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC39,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC40,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC41,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC42,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC43,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC44,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC45,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC46,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC47,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC48,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC49,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC50,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC51,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC52,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC53,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC54,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC55,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC56,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC57,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC58,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC59,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC60,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC61,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC62,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC63,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC64,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC65,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC66,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC67,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC68,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC69,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC70,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC71,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC72,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC73,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC74,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC75,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC76,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC77,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC78,
(select usecode from assignmarks where assignid = assignment.assignid and stunum = '') as UC79
from assignment
join assigncat ac on assignment.assignid = ac.assignid
join courses c on c.coursecode = assignment.coursecode
where sectcode = '106'
and assigngrade = 6
and teachcode = '120003'
and assignmenttype like 'S'
and
((ASSIGNDATE >= '08/20/2011') and (convert(varchar(12), ASSIGNDATE, 101) <= '12/31/2011'))
and
(
(ac.catcode in (select catcode from cour_cat where coursecode = 'E00030'))
and
(ac.[apply] = 1)
)
order by assignment.assigndate
Title | # Comments | Views | Activity |
---|---|---|---|
ssms - object execution statistics | 12 | 37 | |
How to create a user with the same exact SA-equivalency in Microsoft SQL 2008 and 2014 | 12 | 18 | |
T-SQL Using IN with a subquery | 3 | 12 | |
SQL Server can be started but not accessed | 1 | 12 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!