Hello,
Been a long time and hope all are well in these trying times.
I have a query i have been working on and an issue i have tried to resolve for some time:
Context - I have rather long winded query to extract relevant records from a "program" of assorted works - Whether the works have been carried out or are outstanding. And whether they have gone through there relevant stage.
For most this is purely "Whether it was due" and has it been done in terms of work carried out and the job/inventory (the database) updated.
But for other regimes it require obtaining whether a "certificate" document (out of a huge quantity of documents was uploaded) - and for certain "type" the result.
Based on the DateFrom and DateTo (which is normally a reporting month, but could be more extensive - introducing "more potential of the issue below"
My issue is getting that closest record - for the most part my results are "almost correct" - but presently if "multiple documents have been uploaded" it doesnt always select the correct one
as i have tried using the "minimum" and the minimum on some rare occasions (and more potential anomalies the wider the date range entered) is retrieving the a record BEFORE the works was done (and discarding as these shouldn't be the one retrieved. Anyway sorry to be longwinded - but when you see the current code you may see why i tried to provide all the additional context above - I am hoping some of you folks with more experience and intelligence may be able to enlighten me of HOW i can introduce the greater "work date" as a pararameter/critiera before or during the selection of the minimum (and what would then result in right "certificate" being selected =/
Any assistance would be a life (and career) saver =/ I feel there must be something I should be doing that i am coming at this from the wrong angle. And it shouldn't be so complicated =/
PS/Additional edit: Domain relates to "contract" of which there are 4 (only 2 with the convention for the documents established) i have declared "Contract1" as the parameter in the above but query will be run on basis of end user credential (set else where and controlled by access right so can only select the one or ones in which they are involved) - I do not know how to set a variable parameter for multiple contracts here (unless i omit the @domain and list them in an in criteria instead (if anyone could advise how i introduce a variable at runtime instead that would also be great) =/ Apologies. And my appreciation in advance to any who might respond pointing at a solution to my conundrum (and anything which might simplify and tidy what i acknowledge has become very unwieldy coding =/).
Been a long time and hope all are well in these trying times.
I have a query i have been working on and an issue i have tried to resolve for some time:
Context - I have rather long winded query to extract relevant records from a "program" of assorted works - Whether the works have been carried out or are outstanding. And whether they have gone through there relevant stage.
For most this is purely "Whether it was due" and has it been done in terms of work carried out and the job/inventory (the database) updated.
But for other regimes it require obtaining whether a "certificate" document (out of a huge quantity of documents was uploaded) - and for certain "type" the result.
Based on the DateFrom and DateTo (which is normally a reporting month, but could be more extensive - introducing "more potential of the issue below"
My issue is getting that closest record - for the most part my results are "almost correct" - but presently if "multiple documents have been uploaded" it doesnt always select the correct one
as i have tried using the "minimum" and the minimum on some rare occasions (and more potential anomalies the wider the date range entered) is retrieving the a record BEFORE the works was done (and discarding as these shouldn't be the one retrieved. Anyway sorry to be longwinded - but when you see the current code you may see why i tried to provide all the additional context above - I am hoping some of you folks with more experience and intelligence may be able to enlighten me of HOW i can introduce the greater "work date" as a pararameter/critiera before or during the selection of the minimum (and what would then result in right "certificate" being selected =/
Code:
Declare
@Domain AS VARCHAR(50)
, @DateFrom AS DATETIME2
, @DateTo AS DATETIME2
;
SET @Domain = 'Contract1'
SET @DateFrom = '01/01/2021'
SET @DateTo = '04/11/2021'
;
WITH Certs AS (
SELECT
doc.DOCNOM AS 'Filename'
,doc.ID_DOCUMENT AS 'Doc_ID'
,doc.DATESAISIE AS 'Upload Date'
,edoc.ID_ELEMENT 'Asset_ID'
,Case
WHEN doc.DOCNOM like '%[_]AMBER%'
THEN 'AMBER'
WHEN doc.DOCNOM like '%[_]RED%'
THEN 'RED'
WHEN doc.DOCNOM like '%[_]GREEN%'
THEN 'GREEN'
ELSE
NULL
END AS Result
,'TestType2' AS CertType
FROM
[DBO].DOCUMENT AS doc
JOIN [DBO].ELEMENTDOCUMENT AS edoc on doc.ID_DOCUMENT = edoc.ID_DOCUMENT
JOIN [DBO].ELEMENT AS e on edoc.ID_ELEMENT = e.ID_ELEMENT
JOIN [DBO].DOMAINE AS dom on e.ID_DOMAINE = dom.ID_DOMAINE AND dom.DOMLIBELLE IN (@Domain)
WHERE
doc.DOCNOM like 'ST[_]%'
AND doc.DATESAISIE >= @DateFrom
UNION ALL
SELECT
doc.DOCNOM AS 'Filename'
,doc.ID_DOCUMENT AS 'Doc_ID'
,doc.DATESAISIE AS 'Upload Date'
,edoc.ID_ELEMENT 'Asset_ID'
,NULL AS Result
,'TestType1' AS CertType
FROM
[DBO].DOCUMENT AS doc
JOIN [DBO].ELEMENTDOCUMENT AS edoc on doc.ID_DOCUMENT = edoc.ID_DOCUMENT
JOIN [DBO].ELEMENT AS e on edoc.ID_ELEMENT = e.ID_ELEMENT
JOIN [DBO].DOMAINE AS dom on e.ID_DOMAINE = dom.ID_DOMAINE AND dom.DOMLIBELLE IN (@Domain)
WHERE
doc.DOCNOM like 'ET[_]%'
AND doc.DATESAISIE >= @DateFrom
)
Select
Q.[Asset ID]
, convert(VARCHAR(50),Q.[Asset Code]) AS 'Asset Code'
, Q.[MTG ID] AS 'Component ID'
, Q.[MTG Asset Code] AS 'Component Code'
, Q.[Asset Type]
, Q.[Contract Status]
, Q.[Road Name]
, Q.[Ward]
, Q.[Regime Category]
, Q.[Regime]
, Q.[Frequency]
, Q.[Project] AS 'Works Project'
, Q.[Next Due Date]
, Q.[Last Due Date]
, Q.[Works Date]
, Q.[Inventory Updated Date]
, Q.[Upload Date] 'Certificate Upload Date'
, Q.[Filename] 'Certificate Name'
, Q.[Result] AS 'TestType2 Status'
, CASE
WHEN
Q.[Regime Category] NOT IN ('TestType2','TestType1')
AND Q.[Next Due Date] >= @DateTo AND Q.[Inventory Updated Date] < @DateTo
THEN 'Yes'
WHEN
Q.DOMAIN IN ('Contract1', 'Contract2') and Q.[Regime Category] IN ('TestType2','TestType1')
AND Q.[Next Due Date] >= @DateTo AND Q.[Inventory Updated Date] < @DateTo AND Q.[Inventory Updated Date] >='01/09/2021' AND Q.[Upload Date] < @DateTo
THEN 'Yes'
WHEN
Q.DOMAIN IN ('Contract1', 'Contract2') and Q.[Regime Category] IN ('TestType2','TestType1')
AND Q.[Next Due Date] >= @DateTo AND Q.[Inventory Updated Date] < @DateTo AND Q.[Inventory Updated Date] <'01/09/2021'
THEN 'Yes'
WHEN
Q.DOMAIN NOT IN ('Contract1', 'Contract2') and Q.[Regime Category] IN ('TestType2','TestType1')
AND Q.[Next Due Date] >= @DateTo AND Q.[Inventory Updated Date] < @DateTo
THEN 'Yes'
ELSE 'No'
END
AS 'Inventory Updated at time of criteria'
, Q.[Technicians on Report] AS 'Name of Employee/s Attending'
, Q.[Vehicle]
, Q.[Domain]
FROM
(
Select
el.id_element AS 'Asset ID'
, el.ELECLE AS 'Asset Code'
, NULL AS 'MTG ID'
, NULL AS 'MTG Asset Code'
, reg.ETRTYPE AS 'Regime Source'
, eltype.ETYLIBELLE AS 'Asset Type'
, casv1.cavlibelle AS 'Contract Status'
, v.VOILIBELLE AS 'Road Name'
, ve.VENCLE AS 'Road'
, v.VOICLE AS 'Section'
, dom.domlibelle AS 'Domain'
, sec.SECLIBELLE AS 'Ward'
, regcat.ENCLIBELLE AS 'Regime Category'
, reg.ETRLIBELLE AS 'Regime'
, reg.ETRFREQUENCE AS 'Frequency'
,CASE
WHEN Pres.[Inventory Updated Date] IS NOT NULL AND Pres.[Works Undertaken] IS NOT NULL
THEN Pres.[Works Undertaken]
ELSE regele.EENDATEREALISATION
END AS 'Works Date'
, regele.EENDATEPREVUEINITIALE AS 'Last Due Date'
, regele.EENDATEPROCHAINENTRETIEN AS 'Next Due Date'
, Pres.[ID_PROJET] AS 'Project'
, Pres.[Technicians on Report]
, Pres.[Vehicle]
, Pres.[Works Undertaken]
, Pres.[Inventory Updated Date]
, Pres.[Excluded at Selection]
, Pres.[Refused]
, Pres.[Validated]
, Pres.[Updated]
, Pres.[Project Status]
, Pres.[Filename]
, Pres.[Doc_ID]
, Pres.[Upload Date]
, Pres.[Asset_ID]
, Pres.[Result]
FROM
[DBO].[ELEMENTENTRETIEN] AS regele
JOIN [DBO].ENTRETIEN AS reg on regele.ID_ENTRETIEN = reg.ID_ENTRETIEN AND reg.ETRTYPE = 'ELE'
LEFT OUTER JOIN [DBO].[ELEMENT] AS el on regele.EENID = el.ID_ELEMENT
LEFT OUTER JOIN [DBO].[ELEMENTCONTENEUR] AS elcon on el.ID_ELEMENT = elcon.ID_ELEMENT
LEFT OUTER JOIN [DBO].[ELEMENTTYPE] AS eltype on eltype.ID_ELEMENTTYPE = elcon.ID_ELEMENTTYPE
LEFT OUTER JOIN [DBO].[VOIE] AS v on elcon.ID_VOIE = v.ID_VOIE
LEFT OUTER JOIN [DBO].[VOIEENTIERE] AS ve on v.ID_VOIEENTIERE = ve.ID_VOIEENTIERE
LEFT OUTER JOIN [DBO].[DOMAINE] AS dom on el.ID_DOMAINE = dom.ID_DOMAINE
AND dom.DOMLIBELLE IN (@Domain)
LEFT OUTER JOIN [DBO].[SECTEUR] AS sec on v.ID_SECTEUR = sec.ID_SECTEUR
LEFT OUTER JOIN [DBO].[ENTRETIENCATEGORIE] AS regcat on reg.ID_ENTRETIENCATEGORIE = regcat.ID_ENTRETIENCATEGORIE
left JOIN [DBO].catattributsupp cas1 on cas1.id_element = el.id_element AND cas1.id_catattributsuppmeta IN (3,35,63,90,122,150,213,239,291,322,349)
left JOIN [DBO].catattributsuppmetavaleur AS casv1 ON cas1.id_catattributsuppmeta = casv1.id_catattributsuppmeta AND cas1.ctxentier = casv1.ID_CATATTRIBUTSUPPMETAVALEUR
LEFT OUTER JOIN
(Select
p.ID_PROJET
-- Needed for link Pres -Project Results- to previous
, pde.PDEELEMENT
, pde.PDETYPE
, ent.ETRLIBELLE AS 'Regime'
, entc.ENCLIBELLE AS 'Regime Category'
--Need all 4 of above
,( SELECT CONCAT (USER.MUSNOM, ' ',USER.MUSPRENOM,' // '
) AS [text()]
FROM
[DBO].[PROJETDETAIL_CLOS] AS pdet
LEFT OUTER JOIN [DBO].[PROJETDETAILUSER_CLOS] AS pdetec ON pdet.ID_PROJETDETAIL =pdetec.ID_PROJETDETAIL
LEFT OUTER JOIN [DBO].[USER] ON pdetec.ID_USER = USER.ID_USER
WHERE pdet.ID_PROJETDETAIL = pde.ID_PROJETDETAIL
FOR XML PATH('')
) AS 'Technicians on Report'
, veh.VEHMODELE AS 'Vehicle'
, pde.PDEDATETRAVAUX 'Works Undertaken'
, pde.PDEDATEMAJPAT 'Inventory Updated Date'
, pdee.PENEXCLU AS 'Excluded at Selection'
, pde.PDEREFUSE AS 'Refused'
, pde.PDEVALIDE AS 'Validated'
, pde.PDEMAJPAT 'Updated'
, 'Closed' AS 'Project Status'
,Certs2.[Filename]
,Certs2.[Doc_ID]
,Certs2.[Upload Date]
,Certs2.[Asset_ID]
,Certs2.[Result]
FROM [DBO].[PROJET_CLOS] AS p
JOIN [DBO].[PROJETFAMILLE] AS PF ON p.ID_PROJETFAMILLE = pf.ID_PROJETFAMILLE AND pf.ID_PROJETCATEGORIE = 2
JOIN [DBO].[PROJETDETAIL_CLOS] AS pde ON p.ID_PROJET = pde.ID_PROJET
LEFT JOIN [DBO].[VEHICULE] AS veh ON veh.ID_VEHICULE = pde.ID_VEHICULE
LEFT JOIN [DBO].[PROJETDETAILENTRETIEN_CLOS] AS pdee ON pde.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
LEFT JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
LEFT JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
LEFT JOIN (Select
Certs1.Asset_ID
,Certs1.Doc_ID
,Certs1.Result
,Certs1.[Filename]
,Certs1.[Upload Date]
,Certs1.[CertType]
FROM
CERTS AS Certs1
JOIN Certs on Certs1.DOC_ID IN (Select min(Certs.DOC_ID)
FROM
[DBO].[PROJETDETAILENTRETIEN_CLOS] AS pdee
JOIN [DBO].[PROJETDETAIL_CLOS] AS pde2 on pde2.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
JOIN Certs on Certs.Asset_ID = pde2.PDEELEMENT
WHERE
pde2.PDETYPE = 'ELE'
AND pde2.PDEREFUSE = 0
AND Certs.[Upload Date] >= pde2.PDEDATETRAVAUX
--AND Certs.[Upload Date] >= @DateFrom
Group By
Certs.Asset_ID
,Certs.CertType
)
) AS Certs2 on pde.PDEELEMENT = Certs2.Asset_ID
AND Certs2.[Upload Date] >= pde.PDEDATETRAVAUX
AND (entc.ENCLIBELLE = 'TestType1' AND Certs2.CertType = 'TestType1' OR entc.ENCLIBELLE = 'TestType2' AND Certs2.CertType = 'TestType2')
WHERE
ent.ID_ENTRETIENCATEGORIE <> 21
AND pf.ID_PROJETFAMILLE <> 18
AND pde.PDETYPE = 'ELE'
AND pde.PDEREFUSE = 0
AND
--Any not "TestType1/TestType2"
((entc.ENCLIBELLE Not IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 But not Contract1 and Contract2 No Date/Document check required currently and if does get introduced will probably be on different basis or after a specific date per contract based on intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE NOT in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 No Date/Document check required current as before intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT <'01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 Document check required where document present and after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo AND certs2.[Upload Date] >= pde.PDEDATETRAVAUX)
--TestType1 For Contract1 and Contract2 Document include where NO Electrical Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType1' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
--TestType2 For Contract1 and Contract2 Document include where NO TestType2 Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType2' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
)
UNION ALL
SELECT
p.ID_PROJET
-- Needed for link Pres -Project Results- to previous
, pde.PDEELEMENT
, pde.PDETYPE
, ent.ETRLIBELLE AS 'Regime'
, entc.ENCLIBELLE AS 'Regime Category'
--Need all 4 of above
,( SELECT CONCAT (USER.MUSNOM, ' ',USER.MUSPRENOM,' // '
) AS [text()]
FROM
[DBO].[PROJETDETAIL] AS pdet
LEFT OUTER JOIN [DBO].[PROJETDETAILUSER] AS pdetec ON pdet.ID_PROJETDETAIL =pdetec.ID_PROJETDETAIL
LEFT OUTER JOIN [DBO].[USER] ON pdetec.ID_USER = USER.ID_USER
WHERE pdet.ID_PROJETDETAIL = pde.ID_PROJETDETAIL
FOR XML PATH('')
) AS 'Technicians on Report'
, veh.VEHMODELE AS 'Vehicle'
, pde.PDEDATETRAVAUX 'Works Undertaken'
, pde.PDEDATEMAJPAT 'Inventory Updated Date'
, pdee.PENEXCLU AS 'Excluded at Selection'
, pde.PDEREFUSE AS 'Refused'
, pde.PDEVALIDE AS 'Validated'
, pde.PDEMAJPAT 'Updated'
, 'Open' AS 'Project Status'
,Certs2.[Filename]
,Certs2.[Doc_ID]
,Certs2.[Upload Date]
,Certs2.[Asset_ID]
,Certs2.[Result]
FROM [DBO].[PROJET] AS p
JOIN [DBO].[PROJETFAMILLE] AS PF ON p.ID_PROJETFAMILLE = pf.ID_PROJETFAMILLE AND pf.ID_PROJETCATEGORIE = 2
JOIN [DBO].[PROJETDETAIL] AS pde ON p.ID_PROJET = pde.ID_PROJET
LEFT JOIN [DBO].[VEHICULE] AS veh ON veh.ID_VEHICULE = pde.ID_VEHICULE
LEFT JOIN [DBO].[PROJETDETAILENTRETIEN] AS pdee ON pde.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
LEFT JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
LEFT JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
LEFT JOIN (Select
Certs1.Asset_ID
,Certs1.Doc_ID
,Certs1.Result
,Certs1.[Filename]
,Certs1.[Upload Date]
,Certs1.[CertType]
FROM
CERTS AS Certs1
JOIN Certs on Certs1.DOC_ID IN (Select min(Certs.DOC_ID)
FROM
[DBO].[PROJETDETAILENTRETIEN] AS pdee
JOIN [DBO].[PROJETDETAIL] AS pde2 on pde2.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
JOIN Certs on Certs.Asset_ID = pde2.PDEELEMENT
WHERE
pde2.PDETYPE = 'ELE'
AND pde2.PDEREFUSE = 0
AND Certs.[Upload Date] >= pde2.PDEDATETRAVAUX
--AND Certs.[Upload Date] >= @DateFrom
Group By
Certs.Asset_ID
,Certs.CertType
)
) AS Certs2 on pde.PDEELEMENT = Certs2.Asset_ID
AND Certs2.[Upload Date] >= pde.PDEDATETRAVAUX
AND (entc.ENCLIBELLE = 'TestType1' AND Certs2.CertType = 'TestType1' OR entc.ENCLIBELLE = 'TestType2' AND Certs2.CertType = 'TestType2')
WHERE
ent.ID_ENTRETIENCATEGORIE <> 21
AND pf.ID_PROJETFAMILLE <> 18
AND pde.PDETYPE = 'ELE'
AND pde.PDEREFUSE = 0
AND
--Any not "TestType1/TestType2"
((entc.ENCLIBELLE Not IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 But not Contract1 and Contract2 No Date/Document check required currently and if does get introduced will probably be on different basis or after a specific date per contract based on intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE NOT in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 No Date/Document check required current as before intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT <'01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 Document check required where document present and after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo AND certs2.[Upload Date] >= pde.PDEDATETRAVAUX)
--TestType1 For Contract1 and Contract2 Document include where NO Electrical Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType1' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
--TestType2 For Contract1 and Contract2 Document include where NO TestType2 Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType2' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
)
) AS Pres on Pres.PDEELEMENT = el.id_element AND Pres.PDETYPE = reg.ETRTYPE AND Pres.Regime = reg.ETRLIBELLE
---------------------Material Level Regimes
UNION ALL
SELECT el.id_element AS 'Asset ID'
, el.ELECLE AS 'Asset Code'
, regele.EENID AS 'MTG ID'
, el2.ELECLE AS 'MTG Asset Code'
, reg.ETRTYPE AS 'Regime Source'
, eltype.ETYLIBELLE AS 'Asset Type'
, casv1.cavlibelle AS 'Contract Status'
, v.VOILIBELLE AS 'Road Name'
, ve.VENCLE AS 'Road'
, v.VOICLE AS 'Section'
, dom.domlibelle AS 'Domain'
, sec.SECLIBELLE AS 'Ward'
, regcat.ENCLIBELLE AS 'Regime Category'
, reg.ETRLIBELLE AS 'Regime'
, reg.ETRFREQUENCE AS 'Frequency'
, CASE
WHEN Pres.[Inventory Updated Date] IS NOT NULL AND Pres.[Works Undertaken] IS NOT NULL
THEN Pres.[Works Undertaken]
ELSE regele.EENDATEREALISATION
END AS 'Works Date'
, regele.EENDATEPREVUEINITIALE AS 'Last Due Date'
, regele.EENDATEPROCHAINENTRETIEN AS 'Next Due Date'
, Pres.[ID_PROJET] AS 'Project'
, Pres.[Technicians on Report]
, Pres.[Vehicle]
, Pres.[Works Undertaken]
, Pres.[Inventory Updated Date]
, Pres.[Excluded at Selection]
, Pres.[Refused]
, Pres.[Validated]
, Pres.[Updated]
, Pres.[Project Status]
, Pres.[Filename]
, Pres.[Doc_ID]
, Pres.[Upload Date]
, Pres.[Asset_ID]
, Pres.[Result]
FROM
[DBO].[ELEMENTENTRETIEN] AS regele
JOIN [DBO].ENTRETIEN AS reg on reg.ID_ENTRETIEN = regele.ID_ENTRETIEN AND reg.ETRTYPE = 'MTG'
LEFT OUTER JOIN [DBO].ELEMENTMATGERE AS elmat on regele.EENID = elmat.ID_ELEMENT
LEFT OUTER JOIN [DBO].ELEMENTCOMPO AS elcomp on elmat.ID_ELEMENTCOMPO = elcomp.ID_ELEMENTCOMPO
LEFT OUTER JOIN [DBO].[ELEMENT] AS el on elcomp.ID_ELEMENT = el.id_element
LEFT OUTER JOIN [DBO].[ELEMENT] AS el2 on regele.EENID = el2.id_element
LEFT OUTER JOIN [DBO].[ELEMENTCONTENEUR] AS elcon on el.ID_ELEMENT = elcon.ID_ELEMENT
LEFT OUTER JOIN [DBO].[ELEMENTTYPE] AS eltype on eltype.ID_ELEMENTTYPE = elcon.ID_ELEMENTTYPE
LEFT OUTER JOIN [DBO].[VOIE] AS v on elcon.ID_VOIE = v.ID_VOIE
LEFT OUTER JOIN [DBO].[VOIEENTIERE] AS ve on v.ID_VOIEENTIERE = ve.ID_VOIEENTIERE
LEFT OUTER JOIN [DBO].[DOMAINE] AS dom on el.ID_DOMAINE = dom.ID_DOMAINE
AND dom.DOMLIBELLE IN (@Domain)
LEFT OUTER JOIN [DBO].[SECTEUR] AS sec on v.ID_SECTEUR = sec.ID_SECTEUR
LEFT OUTER JOIN [DBO].[ENTRETIENCATEGORIE] AS regcat on reg.ID_ENTRETIENCATEGORIE = regcat.ID_ENTRETIENCATEGORIE
left JOIN [DBO].catattributsupp cas1 on cas1.id_element = el.id_element AND cas1.id_catattributsuppmeta IN (3,35,63,90,122,150,213,239,291,322,349)
left JOIN [DBO].catattributsuppmetavaleur AS casv1 ON cas1.id_catattributsuppmeta = casv1.id_catattributsuppmeta AND cas1.ctxentier = casv1.ID_CATATTRIBUTSUPPMETAVALEUR
LEFT OUTER JOIN
(Select
p.ID_PROJET
-- Needed for link Pres -Project Results- to previous
, pde.PDEELEMENT
, pde.PDETYPE
, ent.ETRLIBELLE AS 'Regime'
, entc.ENCLIBELLE AS 'Regime Category'
--Need all 4 of above
,( SELECT CONCAT (USER.MUSNOM, ' ',USER.MUSPRENOM,' // '
) AS [text()]
FROM
[DBO].[PROJETDETAIL_CLOS] AS pdet
LEFT OUTER JOIN [DBO].[PROJETDETAILUSER_CLOS] AS pdetec ON pdet.ID_PROJETDETAIL =pdetec.ID_PROJETDETAIL
LEFT OUTER JOIN [DBO].[USER] ON pdetec.ID_USER = USER.ID_USER
WHERE pdet.ID_PROJETDETAIL = pde.ID_PROJETDETAIL
FOR XML PATH('')
) AS 'Technicians on Report'
, veh.VEHMODELE AS 'Vehicle'
, pde.PDEDATETRAVAUX 'Works Undertaken'
, pde.PDEDATEMAJPAT 'Inventory Updated Date'
, pdee.PENEXCLU AS 'Excluded at Selection'
, pde.PDEREFUSE AS 'Refused'
, pde.PDEVALIDE AS 'Validated'
, pde.PDEMAJPAT 'Updated'
, 'Closed' AS 'Project Status'
,Certs2.[Filename]
,Certs2.[Doc_ID]
,Certs2.[Upload Date]
,Certs2.[Asset_ID]
,Certs2.[Result]
FROM [DBO].[PROJET_CLOS] AS p
JOIN [DBO].[PROJETFAMILLE] AS PF ON p.ID_PROJETFAMILLE = pf.ID_PROJETFAMILLE AND pf.ID_PROJETCATEGORIE = 2
JOIN [DBO].[PROJETDETAIL_CLOS] AS pde ON p.ID_PROJET = pde.ID_PROJET
LEFT JOIN [DBO].[VEHICULE] AS veh ON veh.ID_VEHICULE = pde.ID_VEHICULE
LEFT JOIN [DBO].[PROJETDETAILENTRETIEN_CLOS] AS pdee ON pde.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
LEFT JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
LEFT JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
LEFT JOIN (Select
Certs1.Asset_ID
,Certs1.Doc_ID
,Certs1.Result
,Certs1.[Filename]
,Certs1.[Upload Date]
,Certs1.[CertType]
FROM
CERTS AS Certs1
JOIN Certs on Certs1.DOC_ID IN (Select min(Certs.DOC_ID)
FROM
[DBO].[PROJETDETAILENTRETIEN_CLOS] AS pdee
JOIN [DBO].[PROJETDETAIL_CLOS] AS pde2 on pde2.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
---------------Need a JOIN to Material Regimes
JOIN Certs on Certs.Asset_ID = pde2.PDEELEMENT
WHERE
pde2.PDETYPE = 'MTG'
AND pde2.PDEREFUSE = 0
AND Certs.[Upload Date] >= pde2.PDEDATETRAVAUX
--AND Certs.[Upload Date] >= @DateFrom
Group By
Certs.Asset_ID
,Certs.CertType
)
) AS Certs2 on pde.PDEELEMENT = Certs2.Asset_ID
AND Certs2.[Upload Date] >= pde.PDEDATETRAVAUX
AND (entc.ENCLIBELLE = 'TestType1' AND Certs2.CertType = 'TestType1' OR entc.ENCLIBELLE = 'TestType2' AND Certs2.CertType = 'TestType2')
WHERE
ent.ID_ENTRETIENCATEGORIE <> 21
AND pf.ID_PROJETFAMILLE <> 18
AND pde.PDETYPE = 'MTG'
AND pde.PDEREFUSE = 0
AND
--Any not "TestType1/TestType2"
((entc.ENCLIBELLE Not IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 But not Contract1 and Contract2 No Date/Document check required currently and if does get introduced will probably be on different basis or after a specific date per contract based on intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE NOT in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 No Date/Document check required current as before intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT <'01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 Document check required where document present and after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo AND certs2.[Upload Date] >= pde.PDEDATETRAVAUX)
--TestType1 For Contract1 and Contract2 Document include where NO Electrical Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType1' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
--TestType2 For Contract1 and Contract2 Document include where NO TestType2 Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType2' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
)
UNION ALL
SELECT
p.ID_PROJET
-- Needed for link Pres -Project Results- to previous
, pde.PDEELEMENT
, pde.PDETYPE
, ent.ETRLIBELLE AS 'Regime'
, entc.ENCLIBELLE AS 'Regime Category'
--Need all 4 of above
,( SELECT CONCAT (USER.MUSNOM, ' ',USER.MUSPRENOM,' // '
) AS [text()]
FROM
[DBO].[PROJETDETAIL] AS pdet
LEFT OUTER JOIN [DBO].[PROJETDETAILUSER] AS pdetec ON pdet.ID_PROJETDETAIL =pdetec.ID_PROJETDETAIL
LEFT OUTER JOIN [DBO].[USER] ON pdetec.ID_USER = USER.ID_USER
WHERE pdet.ID_PROJETDETAIL = pde.ID_PROJETDETAIL
FOR XML PATH('')
) AS 'Technicians on Report'
, veh.VEHMODELE AS 'Vehicle'
, pde.PDEDATETRAVAUX 'Works Undertaken'
, pde.PDEDATEMAJPAT 'Inventory Updated Date'
, pdee.PENEXCLU AS 'Excluded at Selection'
, pde.PDEREFUSE AS 'Refused'
, pde.PDEVALIDE AS 'Validated'
, pde.PDEMAJPAT 'Updated'
, 'Open' AS 'Project Status'
,Certs2.[Filename]
,Certs2.[Doc_ID]
,Certs2.[Upload Date]
,Certs2.[Asset_ID]
,Certs2.[Result]
FROM [DBO].[PROJET] AS p
JOIN [DBO].[PROJETFAMILLE] AS PF ON p.ID_PROJETFAMILLE = pf.ID_PROJETFAMILLE AND pf.ID_PROJETCATEGORIE = 2
JOIN [DBO].[PROJETDETAIL] AS pde ON p.ID_PROJET = pde.ID_PROJET
LEFT JOIN [DBO].[VEHICULE] AS veh ON veh.ID_VEHICULE = pde.ID_VEHICULE
LEFT JOIN [DBO].[PROJETDETAILENTRETIEN] AS pdee ON pde.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
LEFT JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
LEFT JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
LEFT JOIN (Select
Certs1.Asset_ID
,Certs1.Doc_ID
,Certs1.Result
,Certs1.[Filename]
,Certs1.[Upload Date]
,Certs1.[CertType]
FROM
CERTS AS Certs1
JOIN Certs on Certs1.DOC_ID IN (Select min(Certs.DOC_ID)
FROM
[DBO].[PROJETDETAILENTRETIEN] AS pdee
JOIN [DBO].[PROJETDETAIL] AS pde2 on pde2.ID_PROJETDETAIL = pdee.ID_PROJETDETAIL
JOIN [DBO].[ENTRETIEN] AS ent ON pdee.ID_ENTRETIEN = ent.ID_ENTRETIEN
JOIN [DBO].[ENTRETIENCATEGORIE] AS entc ON ent.ID_ENTRETIENCATEGORIE = entc.ID_ENTRETIENCATEGORIE
----------- Check JOIN to Material Regimes - Need some instances of Material level being done
JOIN Certs on Certs.Asset_ID = pde2.PDEELEMENT
WHERE
pde2.PDETYPE = 'MTG'
AND pde2.PDEREFUSE = 0
AND Certs.[Upload Date] >= pde2.PDEDATETRAVAUX
--AND Certs.[Upload Date] >= @DateFrom
Group By
Certs.Asset_ID
,Certs.CertType
)
) AS Certs2 on pde.PDEELEMENT = Certs2.Asset_ID
AND Certs2.[Upload Date] >= pde.PDEDATETRAVAUX
AND (entc.ENCLIBELLE = 'TestType1' AND Certs2.CertType = 'TestType1' OR entc.ENCLIBELLE = 'TestType2' AND Certs2.CertType = 'TestType2')
WHERE
ent.ID_ENTRETIENCATEGORIE <> 21
AND pf.ID_PROJETFAMILLE <> 18
AND pde.PDETYPE = 'MTG'
AND pde.PDEREFUSE = 0
AND
--Any not "TestType1/TestType2"
((entc.ENCLIBELLE Not IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 But not Contract1 and Contract2 No Date/Document check required currently and if does get introduced will probably be on different basis or after a specific date per contract based on intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE NOT in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 No Date/Document check required current as before intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT <'01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo)
--TestType2/TestType1 For Contract1 and Contract2 Document check required where document present and after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE IN ('TestType1', 'TestType2') AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATEMAJPAT >= @DateFrom AND pde.PDEDATETRAVAUX <@DateTo AND certs2.[Upload Date] >= pde.PDEDATETRAVAUX)
--TestType1 For Contract1 and Contract2 Document include where NO Electrical Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType1' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
--TestType2 For Contract1 and Contract2 Document include where NO TestType2 Test document present even after intro of procedure and naming convention etc
OR
(ent.ID_DOMAINE in (1, 2) AND entc.ENCLIBELLE = 'TestType2' AND pde.PDEDATEMAJPAT >='01/09/2021' AND pde.PDEDATETRAVAUX <@DateTo AND Certs2.[Upload Date] IS NULL)
)
) AS Pres on Pres.PDEELEMENT = el2.ID_ELEMENT AND Pres.PDETYPE = reg.ETRTYPE AND Pres.Regime = reg.ETRLIBELLE
) AS Q
--/*
WHERE
Q.DOMAIN
in (@Domain)
-- Specifics Start
AND (
-- Not Electrical/TestType2
Q.[Regime Category] Not IN ('TestType1', 'TestType2')
AND
-- Works done IN period (where updates might occur subsequent)
(Q.[Inventory Updated Date] >= @DateFrom AND Q.[Works Date] <@DateTo)
OR
--(
-- Electrical/TestType2 Test - But not Contract1/Contract2 (unless changes when will need contract specific criteria added for when process and convention added).
Q.[Regime Category] IN ('TestType1', 'TestType2')
AND Q.DOMAIN NOT IN ('Contract1', 'Contract2')
AND
-- Works done IN period (where updates might occur subsequent)
(Q.[Inventory Updated Date] >= @DateFrom AND Q.[Works Date] <@DateTo)
-- )
OR
--(
--Electrical/TestType2 Test - Contract1/Contract2 - after process/convention - document uploaded
Q.[Regime Category] IN ('TestType1', 'TestType2')
AND Q.DOMAIN IN ('Contract1', 'Contract2')
AND Q.[Inventory Updated Date] >='01/09/2021'
AND
-- Works done IN period (where updates might occur subsequent) WITH Document
(Q.[Inventory Updated Date] >= @DateFrom AND Q.[Upload Date] >= @DateFrom AND Q.[Works Date] <@DateTo)
-- )
OR
--(
--Electrical/TestType2 Test - Contract1/Contract2 - after process/convention - no document uploaded
Q.[Regime Category] IN ('TestType1', 'TestType2')
AND Q.DOMAIN IN ('Contract1', 'Contract2')
AND Q.[Inventory Updated Date] >='01/09/2021'
AND
-- Works done IN period (where updates might occur subsequent) WITHOUT Document
(Q.[Upload Date] IS NULL AND Q.[Works Date] <@DateTo)
-- )
OR
--(
--Electrical/TestType2 Test - Contract1/Contract2 - before introduction of process - NO criteria check for document upload
Q.[Regime Category] IN ('TestType1', 'TestType2')
AND Q.DOMAIN IN ('Contract1', 'Contract2')
AND Q.[Inventory Updated Date] <'01/09/2021'
AND
-- Works done IN period (where updates might occur subsequent) - NO criteria check for document upload
(Q.[Inventory Updated Date] >= @DateFrom AND Q.[Works Date] <@DateTo)
--)
OR
--(
-- If "overdue" Next Due Date still less than date to.
(Q.[Next Due Date] <@DateTo)
-- Works Done AFTER report period - But Last due still in past! (Review)
OR (Q.[Works Date] >= @DateTo AND Q.[Last Due Date] <@DateTo)
-- Works done BEFORE end of report period but Last Due projected into future - Some with merit/review
OR (Q.[Works Date] < @DateTo AND Q.[Last Due Date] >= @DateFrom)
-- )
)
GROUP BY
Q.[Asset ID]
, Q.[Works Undertaken]
, Q.[Asset Code]
, Q.[MTG ID]
, Q.[MTG Asset Code]
, Q.[Asset Type]
, Q.[Contract Status]
, Q.[Road Name]
, Q.[Ward]
, Q.[Regime Category]
, Q.[Regime]
, Q.[Frequency]
, Q.[Project]
, Q.[Next Due Date]
, Q.[Last Due Date]
, Q.[Works Date]
, Q.[Inventory Updated Date]
, Q.[Upload Date]
, Q.[Filename]
, Q.[Result]
, Q.[Technicians on Report]
, Q.[Vehicle]
, Q.[Domain]
ORDER BY
Q.[Next Due Date]
,Q.[Asset Code]Any assistance would be a life (and career) saver =/ I feel there must be something I should be doing that i am coming at this from the wrong angle. And it shouldn't be so complicated =/
PS/Additional edit: Domain relates to "contract" of which there are 4 (only 2 with the convention for the documents established) i have declared "Contract1" as the parameter in the above but query will be run on basis of end user credential (set else where and controlled by access right so can only select the one or ones in which they are involved) - I do not know how to set a variable parameter for multiple contracts here (unless i omit the @domain and list them in an in criteria instead (if anyone could advise how i introduce a variable at runtime instead that would also be great) =/ Apologies. And my appreciation in advance to any who might respond pointing at a solution to my conundrum (and anything which might simplify and tidy what i acknowledge has become very unwieldy coding =/).
