What Is "Advanced" SQL

What is Advanced SQL?

I think it's best highlighted with an example. If you feel you could write the following SQL statement quickly with little/no reference material, then I'd guess that you probably meet their Advanced SQL requirement:

DECLARE @date DATETIME
SELECT @date = '10/31/09'

SELECT
      t1.EmpName,
      t1.Region,
      t1.TourStartDate,
      t1.TourEndDate,
      t1.FOrdDate,
      FOrdType  = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType  ELSE NULL END),
      FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),
      t1.LOrdDate,
      LOrdType  = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType  ELSE NULL END),
      LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)
  FROM 
      (--Derived table t1 returns the tourdates, and the order dates
      SELECT
            e.EmpId,
            e.EmpName,
            et.Region,
            et.TourStartDate,
            et.TourEndDate,
            FOrdDate = MIN(o.OrderDate),
            LOrdDate = MAX(o.OrderDate)
        FROM #Employees e INNER JOIN #EmpTours et
          ON e.EmpId = et.EmpId INNER JOIN #Orders o
          ON e.EmpId = o.EmpId
       WHERE et.TourStartDate <= @date
         AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
         AND o.OrderDate BETWEEN et.TourStartDate AND @date
       GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate
      ) t1 INNER JOIN #Orders o
    ON t1.EmpId = o.EmpId
   AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)
 GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

(source of query)

And to be honest, that's a relatively simple query - just some inner joins and a subquery, along with a few common keywords (max, min, case).

Outsystems Advanced SQL

You can search the table twice (one per option) and then join both searches. For example, you can do:

select distinct machineid
from machineoption x
join machineoption y on y.machineid = x.machineid and y.optionid = 'Y'
where x.optionid = 'X'

Advanced SQL Query Struggles

If I am understanding you correctly, I dont see why you even need to have the second join to severities.

Rather try something like

SELECT  DISTINCT 
    dbo.incidents.id, 
    MAX(severities1.[level]) AS severities
FROM    dbo.incidents INNER JOIN 
    dbo.incident_consequence ON dbo.incidents.id = dbo.incident_consequence.incident_id INNER JOIN 
    dbo.consequences ON dbo.incident_consequence.consequence_id = dbo.consequences.id INNER JOIN 
    dbo.severities AS severities1 ON dbo.consequences.severity = severities1.id 
GROUP BY dbo.incidents.id

Group by advanced SQL query

Assuming you mean Result.Type, you can join the tables and group by the Type and count the Magics.

SELECT r.Type, COUNT(m.Magic_ID), COUNT(r.peopleID)
FROM Result r
INNER JOIN Magic_Score ms ON r.Result_ID = ms.Result_ID
WHERE r.peopleID in groupPeopleIDs
GROUP BY r.Type

Given that the Result.Type and Magic.Type are the same, and that every Result only points to Magics with the matching type, the answer should be the same, but grouping by Magic.Type would be similar:

SELECT m.Type, COUNT(m.Magic_ID)
FROM Result r
INNER JOIN Magic_Score ms ON r.Result_ID = ms.Result_ID
INNER JOIN Magic m ON ms.Magic_ID = m.Magic_ID
WHERE r.peopleID in groupPeopleIDs
GROUP BY m.Type

How to declare advanced SQL query in C#?

Your problem is a missed quote. You didn't spot this yourself as the code is an unreadable mess.

It doesn't really make sense to use TSQL to build up the dynamic SQL query here anyway. It will be much less messy to do it in C# as below

            DateTime minDate = DateTime.Parse("2020-03-01");
            DateTime maxDate = DateTime.Parse("2020-03-07");

            //array of strings in format "[2020-03-01]" covering whole date range
            var pivotColumns =
                Enumerable.Range(0, 1 + maxDate.Subtract(minDate).Days)
                .Select(offset => minDate.AddDays(offset).ToString("[yyyy-MM-dd]"))
                .ToArray();

            //enumerable of strings in format "ISNULL([2020-03-01], 0) AS [2020-03-01]"
            var selectColumns = pivotColumns.Select(pc => "ISNULL(" + pc + ", 0) AS " + pc);

            string query = @"
SELECT FIRMA,
       " + string.Join(",", selectColumns) + @"
FROM   (SELECT Shortcut                                       AS FIRMA,
               CAST(mg.data AS DATE)                          AS DATA,
               CAST(ABS(SUM(mg.wartoscWz)) AS DECIMAL(20, 2)) AS WART
        FROM   HM.MG
               INNER JOIN SSCommon.STContractors STC
                       ON MG.khid = STC.id
        WHERE  MG.subtyp = 89
               AND MG.aktywny = 1
               AND MG.anulowany = 0
               AND MG.bufor = 0
               AND MG.kod LIKE '%PZ'
               AND MG.typ_dk <> 'SrT'
               AND MG.createdDate >= @MinDate
               AND MG.createdDate < DATEADD(day, 1, @MaxDate)
        GROUP  BY ( Shortcut ),
                  mg.data) DANE
       PIVOT ( MAX(WART)
             FOR [DATA] IN ( " + string.Join(",", pivotColumns) + @") ) p
ORDER  BY FIRMA 
";

            //TODO: Execute query and pass @MinDate/@MaxDate as parameters

---

Related Topics