Common SQL recipes

These are read-only. Replace the @variables with literal values when you paste them into SSMS.

Inventory

-- Active inventory by community
SELECT  c.CommunityName, l.Status, COUNT(*) AS Lots
FROM    dbo.Lot l
JOIN    dbo.Project p   ON p.ProjectID   = l.ProjectID
JOIN    dbo.Community c ON c.CommunityID = p.CommunityID
WHERE   l.Status IN ('s','f','i','m','p')
GROUP BY c.CommunityName, l.Status
ORDER BY c.CommunityName, l.Status;

Open contracts

SELECT  c.ContractSysID,
        cn.LastName + ', ' + cn.FirstName AS Buyer,
        p.ProjectName,
        l.Address,
        s.ContractStatusName,
        c.AcceptedDate, c.RatifiedDate, c.EstimatedClosedDate
FROM    dbo.Contract       c
JOIN    dbo.ContractStatus s   ON s.ContractStatusID = c.ContractStatusID
JOIN    dbo.Lot            l   ON l.ProjectID  = c.ProjectID
                              AND l.BuildingID = c.BuildingID
                              AND l.UnitID     = c.UnitID
JOIN    dbo.Project        p   ON p.ProjectID = c.ProjectID
LEFT JOIN dbo.Contact      cn  ON cn.ContactSysID = c.ContactSysID
WHERE   c.ContractStatusID NOT IN ('clo','can','lc')
ORDER BY c.AcceptedDate DESC;

Critical schedule for a lot

SELECT  a.ShortName, a.ActivityName, eca.SchedDate, eca.ActualDate
FROM    dbo.EstimateCriticalActivity eca
JOIN    dbo.Activity                 a ON a.ActivityID = eca.ActivityID
WHERE   eca.ProjectID  = @ProjectID
  AND   eca.BuildingID = @BuildingID
  AND   eca.UnitID     = @UnitID
ORDER BY eca.SchedDate;

POs by trade for a project

SELECT  v.VendorName, COUNT(*) AS POs, SUM(po.Amount) AS Dollars
FROM    dbo.PurchaseOrder po
JOIN    dbo.Vendor        v  ON v.VendorID = po.VendorID
WHERE   po.ProjectID = @ProjectID
GROUP BY v.VendorName
ORDER BY Dollars DESC;

Service requests open right now

SELECT  sr.RequestDate, sr.ProjectID, sr.BuildingID, sr.UnitID,
        srl.LineDescription,
        rls.ServiceRequestLineStatusName,
        rt.RequestTypeDesc
FROM    dbo.ServiceRequest sr
JOIN    dbo.ServiceRequestLine srl  ON srl.ServiceRequestSysID = sr.ServiceRequestSysID
JOIN    dbo.ServiceRequestLineStatus rls ON rls.ServiceRequestLineStatusID = srl.ServiceRequestLineStatusID
JOIN    dbo.RequestType rt         ON rt.RequestTypeID = sr.RequestTypeID
WHERE   srl.ServiceRequestLineStatusID = 'o'
ORDER BY sr.RequestDate;

Service orders past deadline

SELECT  so.SONumber, so.AssignedVendorID, v.VendorName,
        so.DeadlineDate, sr.ProjectID, sr.BuildingID, sr.UnitID
FROM    dbo.ServiceOrder so
JOIN    dbo.ServiceRequest sr ON sr.ServiceRequestSysID = so.ServiceRequestSysID
JOIN    dbo.Vendor v          ON v.VendorID = so.AssignedVendorID
WHERE   so.CompletedDate IS NULL
  AND   so.DeadlineDate < CAST(GETDATE() AS date)
ORDER BY so.DeadlineDate;

Stuck transmissions

-- Transmission masters still queued or in error (join to a child table as needed)
SELECT TOP 200
  tm.TransmissionMasterSysID,
  tm.TransType,
  tm.Status,
  tm.ReturnMessage,
  tm.DateAdded,
  tm.DateSent
FROM dbo.TransmissionMaster tm WITH (NOLOCK)
WHERE tm.Status IN ('N', 'E')
ORDER BY tm.DateAdded DESC;

Lots whose schedule completion changed today

SELECT  l.ProjectID, l.BuildingID, l.UnitID, l.SchComplete
FROM    dbo.Lot l
WHERE   l.Updated >= DATEADD(day, -1, GETDATE())
  AND   l.SchComplete IS NOT NULL;