1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| string query =
@"SELECT
COUNT(*) AS cnt,
PhysicalBag.ID,
PhysicalBag.Number,
PhysicalBag.FillingLevel AS FLev,
PhysicalBag.OpenDate,
PhysicalBag.CloseRequestDate AS CloseNeedDt,
PhysicalBag.CloseRequiredDate AS CloseAskedDt,
PhysicalBag.CloseDate AS CloseDt,
PhysicalBag.ParcelCollectDefer AS ColectDay,
PhysicalBag.ReceivedDate AS ReceivedDt,
PhysicalBag.DispatchedDate,
Affiliate.HRK,
Affiliate.Name,
Affiliate.PhoneNumber,
Affiliate.FaxNumber,
Affiliate.GSMNumber,
Affiliate.Email
FROM
PhysicalBag
INNER JOIN Affiliate ON PhysicalBag.AffiliateID = Affiliate.ID
INNER JOIN VirtualBag ON PhysicalBag.ID = VirtualBag.PhysicalBagID
INNER JOIN TradeIn ON VirtualBag.ID = TradeIn.VirtualBagID
WHERE (PhysicalBag.OpenDate < CONVERT(DATETIME, '2010-07-30 00:00:00', 102))
AND (PhysicalBag.CloseDate IS NULL)
AND (PhysicalBag.CloseRequestDate IS NULL)
GROUP BY PhysicalBag.ID,
PhysicalBag.Number,
PhysicalBag.FillingLevel,
PhysicalBag.OpenDate,
PhysicalBag.CloseRequestDate,
PhysicalBag.CloseRequiredDate,
PhysicalBag.CloseDate,
PhysicalBag.ParcelCollectDefer,
PhysicalBag.ReceivedDate,
PhysicalBag.DispatchedDate,
Affiliate.HRK, Affiliate.Name,
Affiliate.PhoneNumber,
Affiliate.FaxNumber,
Affiliate.GSMNumber,
Affiliate.Email
HAVING (COUNT(*)>5)"; |