SQL vraagstuk data

Status
Niet open voor verdere reacties.
B

barendrecht82

Gebruiker
Lid geworden
24 mrt 2013
Berichten
230
hallo

Ik heb deze SQL gemaakt, maar ik kan de datum niet aanpassen naar Jaar en daarna filteren op jaar. De uitkomst geeft alle jaren. Ik wil mijn jaar filter zien.. Tevens bij Condition wil ik alleen alle A zien.. werkt ook niet..

Code: 
SELECT        TOP (100) 
                         PERCENT (CASE WHEN ep.PropName = 'Class_01' THEN 'Assortiment 1 (Selectiecode)' WHEN ep.PropName = 'Class_02' THEN 'Assortiment 2 (KAM Artikel)' WHEN ep.PropName = 'Class_03' THEN 'Assortiment 3 (Productieprint)'
                          WHEN ep.PropName = 'Class_04' THEN 'Assortiment 4 (Voorraadgestuurd)' WHEN ep.PropName = 'Class_05' THEN 'Assortiment 5 (Balans)' WHEN ep.PropName = 'Class_06' THEN 'Assortiment 6 (Produktieorders)'
                          WHEN ep.PropName = 'Class_07' THEN 'Assortiment 7 (Assortment 7)' WHEN ep.PropName = 'Class_08' THEN 'Assortiment 8 (Assortment 8)' WHEN ep.PropName = 'Class_09' THEN 'Assortiment 9 (Prijslijst Assortiment)'
                          WHEN ep.PropName = 'Class_10' THEN 'Assortiment 10 (MRP)' WHEN ep.PropName = 'ConfigurationClass' THEN 'Configuratieklasse' WHEN ep.PropName = 'NetWeight' THEN '(nto: niet van toepassing)' ELSE ep.PropName
                          END) AS FieldName, (CASE WHEN ep.CaptionTermID = 4138 THEN 1102 WHEN ep.CaptionTermID = 222 THEN 1162 WHEN ep.CaptionTermId = 8859 THEN (CASE WHEN ep.PropName = 'StockMax' THEN 1392 END)
                          WHEN ep.CaptionTermId = 2590 THEN (CASE WHEN ep.PropName = 'PurchaseOrderLevel' THEN 1402 END) ELSE ep.CaptionTermID END) AS TermId, (CASE WHEN ep.propname IN ('IsAssembled', 
                         'IsBackToBackOrder', 'IsBatchItem', 'IsDiscount', 'IsExplode', 'IsOutsourcedItem') THEN (CASE ed.oldvalue WHEN 1 THEN 'True' ELSE 'False' END) 
                         WHEN ep.propname = 'CommissionMethod' THEN (CASE WHEN ed.oldvalue = 'F' THEN 'Vast bedrag' WHEN ed.oldvalue = 'M' THEN 'Marge %' WHEN ed.oldvalue = 'S' THEN 'Verkoopprijs' ELSE ed.oldvalue END)
                          WHEN ep.propname = 'Condition' THEN (CASE WHEN ed.oldvalue = 'A' THEN 'Actief' WHEN ed.oldvalue = 'B' THEN 'Geblokkeerd' WHEN ed.oldvalue = 'D' THEN 'Vervallen' WHEN ed.oldvalue = 'F' THEN 'Toekomstig'
                          WHEN ed.oldvalue = 'E' THEN 'Non-actief' ELSE ed.oldvalue END) 
                         WHEN ep.propname = 'ItemType' THEN (CASE WHEN ed.oldvalue = 'V' THEN 'Verkoop' WHEN ed.oldvalue = 'I' THEN 'Intern' WHEN ed.oldvalue = 'T' THEN 'Tekst' WHEN ed.oldvalue = 'N' THEN 'Vervallen' ELSE ed.oldvalue
                          END) 
                         WHEN ep.propname = 'OrderPolicyCode' THEN (CASE WHEN ed.oldvalue = 'L' THEN 'Klantordergestuurd' WHEN ed.oldvalue = 'F' THEN 'Vast' WHEN ed.oldvalue = 'P' THEN 'Periodiek' WHEN ed.oldvalue = 'R' THEN
                          'Bestelniveau' ELSE ed.oldvalue END) 
                         WHEN ep.propname = 'status' THEN (CASE WHEN ed.oldvalue = 'P' THEN 'Gepland' WHEN ed.oldvalue = 'D' THEN 'Development' WHEN ed.oldvalue = 'I' THEN 'Piot' WHEN ed.oldvalue = 'C' THEN 'Commercially available'
                          WHEN ed.oldvalue = 'W' THEN 'Withdraw' ELSE ed.oldvalue END) 
                         WHEN ep.propname = 'Type' THEN (CASE WHEN ed.oldvalue = 'S' THEN 'Standaard' WHEN ed.oldvalue = 'P' THEN 'Phantom' WHEN ed.oldvalue = 'B' THEN 'Bulkuitgifte' WHEN ed.oldvalue = 'L' THEN 'Arbeidsuur'
                          WHEN ed.oldvalue = 'M' THEN 'Machine-uur' WHEN ed.oldvalue = 'C' THEN 'Contract' WHEN ed.oldvalue = 'R' THEN 'Redencodes' ELSE ed.oldvalue END) 
                         WHEN ep.propname = 'ValuationMethod' THEN (CASE WHEN ed.oldvalue = 'V' THEN 'Vaste verrekenprijs' WHEN ed.oldvalue = 'C' THEN 'Vaste verrekenprijs (Classic)' WHEN ed.oldvalue = 'G' THEN 'Gemiddelde inkoopprijs'
                          WHEN ed.oldvalue = 'F' THEN 'FIFO' WHEN ed.oldvalue = 'L' THEN 'LIFO' WHEN ed.oldvalue = 'T' THEN 'Werkelijke kostprijsberekening' WHEN ed.oldvalue = 'A' THEN 'Bijgewerkte GIP' ELSE ed.oldvalue END) 
                         ELSE ed.oldvalue END) AS OValue, (CASE WHEN ep.propname IN ('IsAssembled', 'IsBackToBackOrder', 'IsBatchItem', 'IsDiscount', 'IsExplode', 'IsOutsourcedItem') 
                         THEN (CASE ed.newvalue WHEN 1 THEN 'True' ELSE 'False' END) 
                         WHEN ep.propname = 'CommissionMethod' THEN (CASE WHEN ed.newvalue = 'F' THEN 'Vast bedrag' WHEN ed.newvalue = 'M' THEN 'Marge %' WHEN ed.newvalue = 'S' THEN 'Verkoopprijs' ELSE ed.newvalue END)
                          WHEN ep.propname = 'Condition' THEN (CASE WHEN ed.newvalue = 'A' THEN 'Actief' WHEN ed.newvalue = 'B' THEN 'Geblokkeerd' WHEN ed.newvalue = 'D' THEN 'Vervallen' WHEN ed.newvalue = 'F' THEN 'Toekomstig'
                          WHEN ed.newvalue = 'E' THEN 'Non-actief' ELSE ed.newvalue END) 
                         WHEN ep.propname = 'ItemType' THEN (CASE WHEN ed.newvalue = 'V' THEN 'Verkoop' WHEN ed.newvalue = 'I' THEN 'Intern' WHEN ed.newvalue = 'T' THEN 'Tekst' WHEN ed.newvalue = 'N' THEN 'Vervallen' ELSE
                          ed.newvalue END) 
                         WHEN ep.propname = 'OrderPolicyCode' THEN (CASE WHEN ed.newvalue = 'L' THEN 'Klantordergestuurd' WHEN ed.newvalue = 'F' THEN 'Vast' WHEN ed.newvalue = 'P' THEN 'Periodiek' WHEN ed.newvalue = 'R'
                          THEN 'Bestelniveau' ELSE ed.newvalue END) 
                         WHEN ep.propname = 'status' THEN (CASE WHEN ed.newvalue = 'P' THEN 'Gepland' WHEN ed.newvalue = 'D' THEN 'Development' WHEN ed.newvalue = 'I' THEN 'Piot' WHEN ed.newvalue = 'C' THEN 'Commercially available'
                          WHEN ed.newvalue = 'W' THEN 'Withdraw' ELSE ed.newvalue END) 
                         WHEN ep.propname = 'Type' THEN (CASE WHEN ed.newvalue = 'S' THEN 'Standaard' WHEN ed.newvalue = 'P' THEN 'Phantom' WHEN ed.newvalue = 'B' THEN 'Bulkuitgifte' WHEN ed.newvalue = 'L' THEN 'Arbeidsuur'
                          WHEN ed.newvalue = 'M' THEN 'Machine-uur' WHEN ed.newvalue = 'C' THEN 'Contract' WHEN ed.newvalue = 'R' THEN 'Redencodes' ELSE ed.newvalue END) 
                         WHEN ep.propname = 'ValuationMethod' THEN (CASE WHEN ed.newvalue = 'V' THEN 'Vaste verrekenprijs' WHEN ed.newvalue = 'C' THEN 'Vaste verrekenprijs (Classic)' WHEN ed.newvalue = 'G' THEN 'Gemiddelde inkoopprijs'
                          WHEN ed.newvalue = 'F' THEN 'FIFO' WHEN ed.newvalue = 'L' THEN 'LIFO' WHEN ed.newvalue = 'T' THEN 'Werkelijke kostprijsberekening' WHEN ed.newvalue = 'A' THEN 'Bijgewerkte GIP' ELSE ed.newvalue END)
                          ELSE ed.newvalue END) AS NValue, h.fullname, [COLOR="#FFFF00"]ed.Created,[/COLOR] i.ItemCode, i.PurchasePrice, i.sysmodifier, dbo.Items.Description, dbo.Items.Condition 
FROM            dbo.ItemAccounts AS i LEFT OUTER JOIN
                         dbo.Items ON i.ItemCode = dbo.Items.ItemCode RIGHT OUTER JOIN
                         dbo.EbcDataLog AS ed INNER JOIN
                         dbo.EbcProps AS ep ON ed.Property = ep.PropId LEFT OUTER JOIN
                         dbo.humres AS h ON ed.Creator = h.res_id ON CAST(i.ID AS varchar(36)) = ed.DataKey
WHERE        ((CASE WHEN ep.CaptionTermID = 4138 THEN 1102 WHEN ep.CaptionTermID = 222 THEN 1162 WHEN ep.CaptionTermId = 8859 THEN (CASE WHEN ep.PropName = 'StockMax' THEN 1392 END) 
                         WHEN ep.CaptionTermId = 2590 THEN (CASE WHEN ep.PropName = 'PurchaseOrderLevel' THEN 1402 END) ELSE ep.CaptionTermID END) LIKE 4850) AND 
                         ((CASE WHEN ep.CaptionTermID = 4138 THEN 1102 WHEN ep.CaptionTermID = 222 THEN 1162 WHEN ep.CaptionTermId = 8859 THEN (CASE WHEN ep.PropName = 'StockMax' THEN 1392 END) 
                         WHEN ep.CaptionTermId = 2590 THEN (CASE WHEN ep.PropName = 'PurchaseOrderLevel' THEN 1402 END) ELSE ep.CaptionTermID END) = 4850) AND (ed.DataKey LIKE 'i%') AND 
                         (ed.Component = 0xD2C366F6262D2C4A96B808DC0B1FD785) AND ([COLOR="#FFFF00"]dbo.Items.Condition LIKE 'a')[/COLOR] AND (i.ItemCode LIKE 'I%') OR
                         (ed.Component = 0x7C6B3262291F9C468A3106F0CD71F9B1)
ORDER BY ep.PropName
 
Als je zulke query ziet, dan weet je dat er een gemakkelijker weg is :) .
Eerst en vooral, in een WHERE clause moet je echt berekeningen vermijden. SELECT TOP (100) PERCENT heeft geen zin, maar wordt standaard toegevoegd wanneer je een order by in een view gebruikt, daarom vermoed ik dat het hier om een SQL view gaat. Aangezien je in een view zelf niet sorteert, maar in de query gebaseerd op de view heeft dit hier geen zin en gaat de view nog eens extra vertragen.
In heel de SQL zie ik niet zo direct een datum/tijds veld (en ik heb ook niet de tijd om dat allemaal uit te zoeken), dus kan je daar ook niet op filtreren.
Mijn advies: bekijk je tabelstructuur en creëer een nieuwe view. Baseer dan een query op die view. Probeer als je de bulk van de "case when" niet kan opvangen via een link naar een tabel.
Dringend vereenvoudigen dus.
 
Je hebt gelijk ik heb het even korter gemaakt en werkt opzich goed.

Alleen met deze code krijg een ik lijst van regel (zie excel). Hoe kan ik een code schrijven zodat hij niet alle regels pakt, maar alleen de laatste mutatie?


Code: 
SELECT        TOP (100) PERCENT h.fullname AS FullName, ep.PropName, i.ItemCode, dbo.Items.Description, ep.CaptionTermId, ed.OldValue, ed.NewValue, YEAR(ed.Created) AS Jaar, i.crdnr, dbo.cicmpy.cmp_name, 
                         ed.Created
FROM            dbo.cicmpy RIGHT OUTER JOIN
                         dbo.ItemAccounts AS i ON dbo.cicmpy.crdnr = i.crdnr LEFT OUTER JOIN
                         dbo.Items ON i.ItemCode = dbo.Items.ItemCode RIGHT OUTER JOIN
                         dbo.EbcDataLog AS ed INNER JOIN
                         dbo.EbcProps AS ep ON ed.Property = ep.PropId LEFT OUTER JOIN
                         dbo.humres AS h ON ed.Creator = h.res_id ON CAST(i.ID AS varchar(36)) = ed.DataKey
WHERE        (i.ItemCode LIKE 'I4107.RHB0029') AND (ep.CaptionTermId = 4850) AND (YEAR(ed.Created) >= 2021)
ORDER BY ep.PropName
 

Bijlagen

  • sql.xlsx
    8,5 KB · Weergaven: 15
Laatst bewerkt:
neem de top 1 en order by de datum DESC. Moet er bij de like operator ook geen % teken gebruikt worden?

Code: 
SELECT        TOP (1) h.fullname AS FullName, ep.PropName, i.ItemCode, dbo.Items.Description, ep.CaptionTermId, ed.OldValue, ed.NewValue, YEAR(ed.Created) AS Jaar, i.crdnr, dbo.cicmpy.cmp_name, 
                         ed.Created
FROM            dbo.cicmpy RIGHT OUTER JOIN
                         dbo.ItemAccounts AS i ON dbo.cicmpy.crdnr = i.crdnr LEFT OUTER JOIN
                         dbo.Items ON i.ItemCode = dbo.Items.ItemCode RIGHT OUTER JOIN
                         dbo.EbcDataLog AS ed INNER JOIN
                         dbo.EbcProps AS ep ON ed.Property = ep.PropId LEFT OUTER JOIN
                         dbo.humres AS h ON ed.Creator = h.res_id ON CAST(i.ID AS varchar(36)) = ed.DataKey
WHERE        (i.ItemCode LIKE 'I4107.RHB0029%') AND (ep.CaptionTermId = 4850) AND (YEAR(ed.Created) >= 2021)
ORDER BY ed.created DESC
 
bedankt voor de feedback maar dit werkt niet helemaal. Als ik dit draai krijg ik alleen maar 1 regel te zien.
Deze code geeft mutaties weer van producten die prijsveranderingen hebben gehad. Ik wil dus alle producten zien, maar wel alleen de laatste mutatie.. per artikel

De code die je hebt gewijzigd voor mij filtert alleen 1 regel..
 
Laatst bewerkt:
OMdat je een top predicaat gebruikte, dacht ik dat het de bedoeling was dat je maar één regel ziet. Dan zullen we een group by of een windowing functie moeten gebruiken. Ik heb nu geen tijd om er verder naar te kijken, maar zal vanavond na het werk of morgen er even naar kijken.
Je kan alvast zelf eens kijken naar de T-SQL manual die ik op 20 oktober in het forum "andere software" - topic database software heb geplaatst.
 
Thanks !

Als je vandaag ernaar kan kijken zou het super zijn. Ik zal zo ook jouw topic bekijken thanks!
 
Moeilijk om jou voorbeeld om te turnen omdat ik je tabelstructuur niet ken, maar dit is een voorbeeld hoe je het kan doen. Hiervoor heb ik mijn les database gebruikt die een heel eenvoudige structuur heeft: 1 klant (tabel Sales.Customers) kan verschillende orders (tabel Sales.orders) plaatsen. Elk Order heeft meerdere orderlijnen (tabel Sales.Orderdetails). In het voorbeeld vraag ik per klant het laatste order op met de totale verkoopsprijs uit orderdetails:

Code: 
use TSQL_NGA;
go


with qryRank(ORDERID, ORDERDATE, COMPANY, TOTPRICE, RNR) as (
select SO.orderid , SO.[orderdate] , C.companyname 
			 ,sum(OD.unitprice * OD.qty) over (order by orderdate desc) 
			, row_number()  over (partition by C.custid order by SO.orderid desc) 
from [Sales].[Orders] SO inner join sales.orderdetails OD on SO.orderid = OD.orderid
		inner join sales.customers C on SO.custid = C.custid
where  SO.orderdate >= '2018-1-1'
 )

select   O.orderdate, R.COMPANY , O.shipname
from sales.orders O inner join qryrank R on O.orderID = R.ORDERID
where R.RNR = 1

Trouwens in je voorbeeld zitten nog wel een paar angels die ik zie nu ik de code iets beter bekijk: je gaat nooit linken op een berekend veld (zie : ON CAST(i.ID AS varchar(36)) = ed.DataKey)
en je gaat ook niet filtreren op een berekend veld (zie: AND YEAR(ed.Created) >= 2021)
 
Ik kan moeilijk een query schrijven zonder de database structuur en context te kennen. Als de database niet te groot is, kan je altijd een full back-up nemen, zippen en doorsturen. Ik werk thuis momenteel met versie 2017, dus dan zul je in die versie (of lager) moeten backupen. Hou er wel rekening mee dat ik geen fan ben om een copie van de werkelijke data bij mij thuis te hebben. Denk aan de GPR.
Je kan de betrokken tabellen ook scripten en de scripts hier plaatsen, dan kunnen we de tabelstructuur al eens bekijken, en als je dan een query uitprobeert en een foutmelding krijgt kunnen we samen bekijken wat de oorzaak van de fout is.
Andere mogelijkheid: een cursus T-SQL volgen zodat je zelf vlot de query kan schrijven, T-SQL is echt een eenvoudige taal, zoek het niet te ver.

Verlies de moed niet, iedereen moet leren.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan