Write Advanced SQL Select

Write advanced SQL Select

SELECT Item, 
  [ProdSched(1)(Qnty)] = MAX(CASE WHEN ProdSched = 1 THEN Qnty END),
  [ProdSched(2)(Qnty)] = MAX(CASE WHEN ProdSched = 2 THEN Qnty END)
FROM dbo.tablename
GROUP BY Item
ORDER BY Item;

Advanced SQL SELECT

I am guessing that table3 has columns:

User, Tbl1-ID, Tbl2-ID

I am guessing that you want, for user X, the values for tbl2 based on the selecton made from tbl1 that are NOT in table 3.

SELECT ID, Value
FROM tbl2
WHERE tbl2.ID NOT IN (
   SELECT Tbl2-ID
   FROM Table3
   WHERE (User = <this user>) AND (Tbl1-ID = <selected tbl1 ID>)
   )

Advanced SQL query to select distinct data

SELECT  b.*
FROM    Table1 a
        INNER JOIN Table2 b
            ON a.rq_ID = b.rq_ID
        INNER JOIN
        (
            SELECT  rq_id, MAX(version) max_ver
            FROM    table2
            GROUP   BY rq_id
        ) c ON  b.rq_ID = c.rq_ID AND
                b.version = c.max_ver
WHERE   a.user_ID = 223 AND a.is_good = 0

SQLFiddle Demo

SELECT INTO advanced

NB: Below answer was written assuming SQL Server. I deleted it when receiving the VistaDB clarification but have undeleted it again upon reading that

VistaDB can be thought of as a subset
of Microsoft SQL Server T-SQL. All of
our syntax is supported in SQL Server,
but not the other way around

In that case I assume it is safe to say that if it is invalid in SQL Server it will be invalid in VistaDB also? This is invalid syntax in SQL Server.

SELECT (second.[cdate]=@enddate) AS 'Date'

What is the purpose of this bit of code? Is it meant to be a boolean? (i.e. return true when the column matches the variable). If so in SQL Server the closest to that would be this.

SELECT CAST((CASE WHEN second.[cdate]=@enddate THEN 1 ELSE 0 END) AS BIT) AS 'Date'

Edit From the comments I see it is intended to be

SELECT @enddate AS 'Date'

Additionally I don't see SELECT ... INTO listed as a VistaDB command here. Is it definitely supported?

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).

Advanced SQL Select Query (unique rows) (group by)

This one should do what you ask :

 SELECT 
    Person,
    SUM(case when typology = 'outbounds' then calls_number else 0 end) as outbounds,
    SUM(case when typology = 'inbounds' then calls_number else 0 end) as inbounds,
    SUM(calls_number) as calls_number,
    case when SUM(calls) = 0 then 0 else SUM(callTime) / SUM(calls) end as avgCallTime
FROM(
SELECT 
    OriginationName as Person,
    SUM(case 
        when DestinationDevice != '' and ConnectTime != '' then (EndTime - ConnectTime) 
        else 0 end
    ) as callTime,
    SUM(case when DestinationDevice != '' then 1 else 0 end) as calls_number,
    'outbounds' as typology
FROM calls 
WHERE (YEAR(EndTime) = 2018 AND MONTH(EndTime) = 12) and ConnectTime != ''
    and OriginationName != ''
GROUP BY OriginationName
    union
SELECT 
    DestinationName,
    SUM(case 
        when OriginationDevice != '' and ConnectTime != '' then (EndTime - ConnectTime) 
        else 0 end
    ),
    SUM(case when OriginationDevice  != ''  then 1 else 0 end),
    'inbounds' as typology
FROM calls 
WHERE (YEAR(EndTime) = 2018 AND MONTH(EndTime) = 12) and ConnectTime != '' 
    and DestinationName != ''
GROUP BY DestinationName) as T
GROUP BY Person;

Let me know if there is something wrong, this sample work https://sqltest.net/#386096

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

Advanced SQL Select and update items from the same table

To update the table so that the matching values have active set to 1 we can take the solution by juergen d and turn it into an update statement.

As MySQL has some issues with updating a table that it is referencing in a subquery we insert an extra level of nesting which forces the creation of a temporary result and allows the update:

update table1 t
set t.active = 1
where base_id in (
  select base_id from (
    select base_id
    from table1
    group by base_id
    having sum(step_id = 1 and active = 0) > 0
       and sum(step_id = 2 and active = 1) = 0
  ) a 
);

This would set active = 1 for item_id 3, 4 and 5

Sample SQL Fiddle

Write Advanced SQL Select