Софтуерно Инженерство
Loading...
mitko_26 avatar mitko_26 1 Точки

Databases MSSQL Server Exam - 20 Oct 2019/ Задача10. Full Info от изпит на 20-10-2019

Здравейте,

някой успя ли да реши 10 задача:

10.Full Info

Select all info for reports along with employee first name and last name (concataned with space), department name, category name, report description, open date, status label and name of the user. Order them by first name (descending), last name (descending), department (ascending), category (ascending), description (ascending), open date (ascending), status (ascending) and user (ascending).

Date should be in format - dd.MM.yyyy

If there are empty records, replace them with 'None'.

Example:

Employee

Department

Category

Description

OpenDate

Status

User

Niki Stranaghan

Event Management

Sports Events

Sky Run competition on September 8

08.06.2015

Completed

Emlynn Alliberton

Marlo O'Malley

Infrastructure

Streetlight

Fallen streetlight columns on Str.14

12.09.2017

Blocked

Erhart Alpine

Leonardo Shopcott

Animals Care

Animal in Danger

Parked car on green area on the sidewalk of Str.74

10.11.2016

In Progress

Jocko Greggor

….

 

Моето решение не минаваа през 'judge'

SELECT CONCAT(e.FirstName, ' ', e.LastName) AS Employee,
            ISNULL(d.Name,'None') AS Department,
            ISNULL(c.Name,'None') AS Category,
            ISNULL(r.Description,'None') AS Description, CONVERT(VARCHAR,r.OpenDate,104) AS OpenDate,  
            ISNULL(s.Label,'None') AS [Status],
            ISNULL(u.Name,'None')  AS [User]

    FROM Reports AS r
    LEFT JOIN Employees AS e ON r.EmployeeId = e.Id
    LEFT JOIN Categories AS c ON r.CategoryId = c.Id
    LEFT JOIN Departments AS d ON  c.DepartmentID = d.Id
    LEFT JOIN [Status] AS s ON r.StatusId = s.Id   
    LEFT JOIN [Users] AS u ON r.UserId = u.Id


    ORDER BY Employee DESC, Department, Category, r.Description, OpenDate, [Status], [User]

 

Тагове:
0
Databases Basics 28/10/2019 00:06:19
Jordan_Dobrev12 avatar Jordan_Dobrev12 321 Точки
Best Answer

Нещо не мога да разбера какъв е този order by, не сортираш по first name, last name и т.н.

Ето как я реших аз https://pastebin.com/4LjzT3j9

0
mitko_26 avatar mitko_26 1 Точки

Благодаря за отговора.

Сбъркал съм за ORDER BY, че съм сортирал по Employee  вместо по FirstName  и LastName.

А при теб защо само e.FirstName е DESC ? По условие и LastName е descending.

0