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 44 45 46 47 48 49 50 51 52 53 54 55
| SELECT dbo.xdGetCustomerNameByCustomerID_fct(c.CustomerID) AS CustomerName ,
DATEADD(DAY, s.DureeTraitement, s.DateService) AS DtRenPrevue ,
COUNT(*) AS Nombre ,
s.DureeTraitement ,
s.DateService
FROM dbo.Ordonnance o
INNER JOIN dbo.CUSTOMERS c ON o.FKCustomerID = c.CustomerID
INNER JOIN dbo.Services s ON s.FKOrdonnanceID = o.OrdonnanceID
LEFT JOIN dbo.GROUPS g ON g.GroupID = c.fkGroupID
WHERE s.UniqueID = ( SELECT MAX(UniqueID)
FROM dbo.Services
WHERE FKOrdonnanceID = o.OrdonnanceID
)
AND o.OrdonnanceType IN ( 1, 2 )
AND o.OrdonnanceState NOT IN ( 1, 2, 3 )
AND ( ( @Renouvelable = 2
AND ( ( ( o.EndDate = '1753-01-01' )
AND ( dbo.xDRxOrdonnanceRenRestantAsOfDate_fct(o.OrdonnanceID, ISNULL(s.DateService, o.DateService)) <= 0 )
)
OR ( ( o.EndDate <> '1753-01-01' )
AND ( o.EndDate <= @DateFin )
)
)
)
OR ( @Renouvelable = 0
AND ( ( ( o.EndDate <> '1753-01-01' )
AND ( o.EndDate > @DateFin )
)
OR ( ( o.EndDate = '1753-01-01' )
AND ( dbo.xDRxOrdonnanceRenRestantAsOfDate_fct(o.OrdonnanceID, ISNULL(s.DateService, o.DateService)) > 0 )
)
)
)
OR @Renouvelable = 1
)
AND ( @FoyerID IS NULL
OR c.CustomerID IN ( SELECT ParamValues.id.value('.', 'int') AS id
FROM @FoyerID.nodes('/customers/id') AS ParamValues ( id ) )
)
GROUP BY c.CustomerID ,
c.ShipLastName ,
c.ShipFirstName ,
c.ShipNomConjoint ,
c.ShipNo ,
c.ShipStreet ,
c.ShipApp ,
c.ShipCity ,
c.PhoneNumber ,
g.GroupName ,
s.DureeTraitement ,
s.DateService /*,
DATEADD(DAY, s.DureeTraitement, s.DateService)*/
HAVING COUNT(*) >= @RxCount
ORDER BY DATEADD(DAY, s.DureeTraitement, s.DateService) ,
CustomerName |
Partager