R/parse_sql_report_query.R
parse_sql_report_query.Rd
Parse the query based on the template information and the chosen projects
parse_sql_report_query(template, project)
SQL server query
{
temp <- get_report_config_info(template = "default")
query <- parse_sql_report_query(temp, "I-19W001-01")
query
}
#> Rows: 66 Columns: 9
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: "\t"
#> chr (6): Type, Veldnaam, Tabel, Afkorting, Kolom, Beschrijving
#> dbl (3): template:all, template:default, template:minimal
#>
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
#> [1] "select Sleutel = concat(r.LimsAnalysisName, '__', r.Component, '__', r.TestReplicateCount, '__', r.ResultReplicate),\nLimsStaalNummer = s.[LIMSSampleNumber],\nOrigineelStaal = s.[LIMSOriginalSampleNumber],\nProject = r.[Project],\nLaboCode = s.[LabSampleID],\nContractID = c.[Contract],\nKlant = r.[Customer],\nVerantwoordelijkLabo = p.[ResponsibleLab],\nExternSampleID = s.[FieldSampleID],\nSampleProduct = s.[Product],\nProductGrade = s.[MatrixDetail],\nSamplingPoint = s.[SamplingPoint],\nMatrix = s.[Matrix],\nMonsternamedatum = s.[FieldSamplingDate],\nMonsternemer = s.[FieldObserver],\nToestand = s.[SampleCondition],\nVoorbehandelingExtern = s.[SamplePreparation],\nOpmerking = s.[FieldSampleRemark],\nLimsAnalyseNaam = r.[LimsAnalysisName],\nLimsAnalyseVersie = a.[AnalysisVersion],\nSapCode = a.[SAPcode],\nAnalyseNaam = a.[AnalysisLabName],\nComponent = r.[Component],\nGerapporteerd = r.[IsReportable],\nTestReplicaat = r.[TestReplicateCount],\nResultaatReplicaat = r.[ResultReplicate],\nResultaatInstrument = r.[Instrument],\nBatch = r.[Batch],\nResultaatType = r.[ResultType],\nEenheid = u.[Unit],\nWaardeRuw = r.[Result],\nWaardeGeformatteerd = r.[ResultFormatted],\nWaardeNumeriek = r.[ResultNumeric],\nWaardeNumeriekGeformatteerd = r.[ResultFormattedNumeric],\nBenedenLOQ = r.[isBelowLOQ],\nVisueleMatrix = s.[VisualMatrix],\nPlaat = s.[samplePlate],\nPlaatPositie = s.[SamplePlatePosition],\nArchiefStaal = s.[IsArchiveSample],\nXcoord = s.[SampleLambertX],\nYcoord = s.[SampleLambertY],\nDiepte = s.[SampleDepth],\nToponiem = s.[SampleToponym],\nStaalStatus = s.[SampleStatus],\nResultaatStatus = r.[ResultStatus],\nBatchInstrument = b.[BatchInstrument] from dimSample s \nleft join factResult r on r.SampleKey = s.SampleKey \ninner join dimProject p on r.ProjectKey = p.ProjectKey \ninner join dimContract c on r.ContractKey = c.ContractKey \ninner join dimAnalysis a on r.AnalysisKey = a.AnalysisKey \nleft join dimUnit u on r.UnitKey = u.UnitKey \nleft join dimBatch b on r.BatchKey = b.BatchKey where r.Project in ('I-19W001-01') AND \ns.SampleStatus = 'A' AND \nr.ResultStatus= 'A' AND \np.ProjectStatus = 'V' AND \nr.IsReportable=1"