Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH P AS (
- SELECT DISTINCT
- College.Id AS CollegeId,
- Degree.Id AS DegreeId,
- CASE WHEN Master.Online IS NULL THEN 'False' ELSE 'True' END AS Online,
- Master.FacebookUrl,
- Master.InsideInformation,
- Master.DegreeUrl,
- Master.AdmissionsUrl
- FROM MasterData3 Master
- INNER JOIN (SELECT * FROM (VALUES (0,4),(1,2),(2,1)) AS Map(x,y)) AS Map -- get degree category
- ON Map.x=DegreeCategory
- INNER JOIN SearchItem -- get searchitem
- ON SearchItem.Name=Master.SubjectData
- INNER JOIN Subject -- get subject
- ON Subject.Id = SearchItem.Id
- INNER JOIN Institution -- get institution
- ON Institution.Name = Institution
- INNER JOIN College -- get college
- ON College.Name = [College]
- AND College.InstitutionId = Institution.Id
- INNER JOIN DegreeType -- get degreetype
- ON DegreeType.Abbreviation=Master.DegreeType
- AND DegreeType.DegreeCategoryId=Map.y
- INNER JOIN Degree -- get degree
- ON Degree.SubjectId=Subject.Id
- AND Degree.DegreeTypeId=DegreeType.Id
- ),
- Q AS (
- SELECT
- CollegeId,
- DegreeId,
- MIN(Online) AS Online,
- MIN(FacebookUrl) AS FacebookUrl,
- MIN(InsideInformation) AS InsideInformation,
- MIN(DegreeUrl) AS DegreeUrl,
- MIN(AdmissionsUrl) AS AdmissionsUrl,
- COUNT(*) AS COUNT
- FROM P
- GROUP BY CollegeId, DegreeId
- )
- INSERT INTO dbo.[Program]
- SELECT CollegeId, DegreeId, Online, FacebookUrl, InsideInformation, DegreeUrl, AdmissionsUrl FROM Q
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement