MSSQL Queries (T-SQL)

MSSQL queries are queries written in T-SQL (Transact-SQL) in the Compass module against an MSSQL data source added in Port.

T-SQL is Microsoft SQL Server's SQL extension and contains syntactic elements that differ from standard SQL. Constructs such as TOP, GETDATE(), DATEADD(), and NOLOCK are T-SQL specific.

---

Strengths

Feature	Description
ERP / MES Integration	Systems like SAP, Oracle EBS, and Infor often use MSSQL
T-SQL Procedural Power	Stored procedure calls, temp tables, CTE support
NOLOCK Hint	Query live production tables without acquiring read locks
Comprehensive Date Functions	Advanced time arithmetic with DATEADD, DATEDIFF, DATEPART
JSON Support	JSON output and parsing with FOR JSON PATH, OPENJSON()

---

Common T-SQL Patterns in Pirivision

Demo Factory — MSSQL

The examples below use the Demo Factory MSSQL demo_fabrika.vardiya_uretim table (columns: ts, hat_id, makine_id, vardiya, urun_ailesi, uretim_adet, hedef). Port data source name: Demo Fabrika - MSSQL.

To generate the same data in your own environment → Synthetic Data Generation — MSSQL

-- Last 50 production rows — TOP N usage (instead of LIMIT)
SELECT TOP 50 ts,
       hat_id,
       makine_id,
       vardiya,
       urun_ailesi,
       uretim_adet
FROM   demo_fabrika.vardiya_uretim
WHERE  ts >= DATEADD(day, -7, GETDATE())
ORDER  BY ts DESC, hat_id;

-- Time range (parameterized)
SELECT ts, hat_id, uretim_adet
FROM   demo_fabrika.vardiya_uretim WITH (NOLOCK)
WHERE  ts >= {{start_date}}
  AND  ts <= {{end_date}}
ORDER  BY ts;

-- Shift summary
SELECT vardiya,
       SUM(uretim_adet)                AS toplam,
       AVG(CAST(uretim_adet AS FLOAT)) AS ortalama
FROM   demo_fabrika.vardiya_uretim WITH (NOLOCK)
WHERE  ts >= DATEADD(day, -7, GETDATE())
GROUP  BY vardiya;

-- DATEDIFF for day span
SELECT hat_id,
       DATEDIFF(day, MIN(ts), MAX(ts)) AS gun_araligi
FROM   demo_fabrika.vardiya_uretim
GROUP  BY hat_id;

---

Important T-SQL Constructs

Category	T-SQL	Standard SQL Equivalent
Row limit	SELECT TOP 100 *	SELECT * LIMIT 100
Current time	GETDATE()	NOW()
Date addition	DATEADD(day,-7,GETDATE())	NOW() - INTERVAL '7 days'
Date difference	DATEDIFF(s, t1, t2)	EXTRACT(EPOCH FROM t2-t1)
Null fallback	ISNULL(val, 0)	COALESCE(val, 0)
Conditional value	IIF(cond, a, b)	CASE WHEN cond THEN a ELSE b END
Lock avoidance	FROM table WITH (NOLOCK)	—
Identifier	[table name] (with spaces)	"table name"

---

Requirements

Requirement	Description
MSSQL data source in Port	Connection must be tested
SELECT permission	The DB user must have permission on the queried tables

---

Guides

Guide	Description
Create MSSQL Query	Write a new T-SQL query in Compass
Edit MSSQL Query	Update an existing query
Move Query	Move to a different folder
Duplicate Query	Copy a query
Delete Query	Remove a query

---

→ Create MSSQL Query