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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
| SELECT
AccRight.*,
AccContr.id AS accessContractId,
AccContr.reference AS refence,
AccContrVersion.beginDate AS accContrVersBeginDate,
AccContrVersion.endDate AS accContrVersEndDate,
Comp.id AS companyId,
CompanyVersionCodeElia.codeValue AS ACHShortName,
CompVers.Id AS compVersionId,
CompVers.beginDate AS compVersionBeginDate,
CompVers.endDate AS compVersionEndDate,
Conn.Id AS connectionId,
conVers.maxPowerOfftake AS connPMax,
conVers.maxPowerOfftake AS maximumPowerOfftake,
conVers.maxPowerInjection AS maximumPowerInjection,
conVers.beginDate AS conVersBeginDate,
conVers.endDate AS conVersEndDate,
ConnPt.id AS connectionPointId,
Accpt.displayName AS accessPointDisplayName,
address.city AS city,
address.country AS country,
address.countryCodeId AS countryCode,
address.street AS street,
address.zipCode AS zipCode,
regInfo.country AS regionInfoCountryCode,
regInfo.subcityZipcode AS subcityZipcode,
regInfo.province AS province,
regInfo.subcity AS subcity,
Accpt.eanCode AS accessPointEanPoint,
AccPtVers.CVLId AS accPtVersCVLId,
AccPtVers.beginDate AS accPtVersBeginDate,
AccPtVers.EndDate AS accPtVersEndDate
FROM VCC_AccessRight AS AccRight,
TCC_Connection_ConnectionPoint AS connectionConnectionPoint
INNER JOIN TCC_AccessContract AS AccContr ON AccRight.accessContractId = AccContr.Id
INNER JOIN TCC_AccessContractVersion AS AccContrVersion ON AccContrVersion.accessContractId = AccContr.Id
INNER JOIN TCC_Company AS Comp ON AccContr.companyId = Comp.id
INNER JOIN TCC_CompanyVersion AS CompVers ON Comp.id = CompVers.id AND CompVers.state = ''Running''
LEFT OUTER JOIN TCC_CompanyVersion_Code AS CompVersCode ON CompVers.id = CompVersCode.companyVersionId
LEFT OUTER JOIN TCC_CompanyVersion_Code AS CompanyVersionCodeElia ON CompVers.id=CompanyVersionCodeElia.companyVersionId
INNER JOIN TCC_AccessPoint AS AccPt ON AccPt.id = AccRight.accessPointId
INNER JOIN TCC_AccessPointVersion AS AccPtVers ON AccPt.id = AccPtVers.accessPointId
LEFT OUTER JOIN TCC_AccessPoint_Connection AS pointCon ON pointCon.accessPointId = AccPt.id
LEFT OUTER JOIN TCC_Connection AS Conn ON pointCon.connectionId = Conn.id AND connectionConnectionPoint.connectionId = Conn.id
LEFT OUTER JOIN TCC_ConnectionVersion AS conVers ON Conn.id = conVers.connectionId
LEFT OUTER JOIN TCC_Site AS site ON AccPt.siteId = site.addressId
LEFT OUTER JOIN TCC_ContactAddress AS address ON site.addressId = address.id
LEFT OUTER JOIN TCC_RegionInfo AS regInfo ON address.regionInfoId = regInfo.id, TCC_Person_Account AS PersonAccount
LEFT OUTER JOIN TCC_ConnectionPoint AS ConnPt ON ConnPt.id = connectionConnectionPoint.connectionPointId
INNER JOIN TCC_Person AS Personxxx ON PersonAccount.personId = Personxxx.id
WHERE AccContrVersion.state = ''Running''
AND AccPtVers.state = ''Running''
AND AccContrVersion.endDate > @p0
AND conVers.endDate > @p1
AND CompVers.endDate > @p2
AND AccPtVers.endDate > @p3
AND AccRight.endDate > @p4
AND localProductionUnit = @p5
AND direction = @p6
AND AccRight.dayNight = @p7
AND PersonAccount.account = @p8
AND CompanyVersionCodeElia.codeTypeId LIKE 'C11'
ORDER BY AccRight.ID ASC, compVers.beginDate ASC, accPtVers.beginDate ASC
',N'@p0 datetime, @p1 datetime, @p2 datetime, @p3 datetime, @p4 datetime, @p5 bit, @p6 nvarchar(7), @p7 bit,@p8 nvarchar(10)',
@p0 = 'Jan 1 2004 12:00:00:000AM',
@p1 = 'Jan 1 2004 12:00:00:000AM',
@p2 = 'Jan 1 2004 12:00:00:000AM',
@p3 = 'Jan 1 2004 12:00:00:000AM',
@p4 = 'Jan 1 2004 12:00:00:000AM',
@p5 = 1,
@p6 = N'offtake',
@p7 = 0,
@p8 = N'ISOEXT\CRE' |
Partager