Goedendag,
ik heb bijgaande Query uit Exact gekopieerd (voor Exact kenners: vanuit de spilanalyse) en wil deze verder gebruiken in Excel.
Deze Query heb ik geplakt in de Exact Excel Add-In en wil hier een paar variabele in opnemen. Ik heb begrepen dat dat middels ?1 kan maar het lukt mij niet. Wat ik heb kunnen vinden is dat op de plaats waar de variabele moet komen een ?! geplaatst moet worden en dat dan de variabele in de rij met parameters kan worden gezet. Deze parameters kun je vervolgens weer opnemen in het Excelsheet.
B.v.d.
WST
[sql]SELECT gbkmut.res_id AS res_id,reshumres.Fullname AS resFullName
, SUM(CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_hfl >= 0 THEN (bdr_hfl) ELSE 0 END ELSE CASE WHEN bdr_hfl < 0 THEN (bdr_hfl) ELSE 0 END END ELSE 0 END) AS Debit,
SUM(CASE WHEN gbkmut.valcode <> 'EUR' THEN CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_val >= 0 THEN (bdr_val) ELSE 0 END ELSE CASE WHEN bdr_val < 0 THEN (bdr_val) ELSE 0 END END ELSE 0 END ELSE 0 END) AS DebitFC,
SUM(CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_hfl >= 0 THEN 0 ELSE -(bdr_hfl) END ELSE CASE WHEN bdr_hfl < 0 THEN 0 ELSE -(bdr_hfl) END END ELSE 0 END) AS Credit,
SUM(CASE WHEN gbkmut.valcode <> 'EUR' THEN CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_val >= 0 THEN 0 ELSE -(bdr_val) END ELSE CASE WHEN bdr_val < 0 THEN 0 ELSE -(bdr_val) END END ELSE 0 END ELSE 0 END) AS CreditFC,
SUM(CASE WHEN transtype IN ('N','C','P') THEN gbkmut.aantal ELSE 0 END) AS Aantal,
0 AS Budget,
0 AS BudgetQuantity,
0 AS TotalProd FROM gbkmut INNER JOIN grtbk ON gbkmut.reknr = grtbk.reknr
LEFT OUTER JOIN prproject ON gbkmut.project = prproject.projectnr
LEFT OUTER JOIN humres reshumres ON gbkmut.res_id = reshumres.res_id
WHERE transtype IN ('N', 'C', 'P') AND remindercount <= 31 AND grtbk.reknr BETWEEN ' 52100' AND ' 52110' AND gbkmut.datum BETWEEN {d '2012-01-01'} AND {ts '2012-12-31 23:59:59'} AND cast(floor(cast(datum as float)) as datetime) BETWEEN {d '2012-01-01'} AND {ts '2012-08-31 23:59:59'} AND ISNULL(gbkmut.project, '--') BETWEEN '80002 ' AND '80013 ' AND gbkmut.TransSubType <> 'X' GROUP BY gbkmut.res_id,reshumres.Fullname ORDER BY gbkmut.res_id,reshumres.Fullname[/sql]
ik heb bijgaande Query uit Exact gekopieerd (voor Exact kenners: vanuit de spilanalyse) en wil deze verder gebruiken in Excel.
Deze Query heb ik geplakt in de Exact Excel Add-In en wil hier een paar variabele in opnemen. Ik heb begrepen dat dat middels ?1 kan maar het lukt mij niet. Wat ik heb kunnen vinden is dat op de plaats waar de variabele moet komen een ?! geplaatst moet worden en dat dan de variabele in de rij met parameters kan worden gezet. Deze parameters kun je vervolgens weer opnemen in het Excelsheet.
B.v.d.
WST
[sql]SELECT gbkmut.res_id AS res_id,reshumres.Fullname AS resFullName
, SUM(CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_hfl >= 0 THEN (bdr_hfl) ELSE 0 END ELSE CASE WHEN bdr_hfl < 0 THEN (bdr_hfl) ELSE 0 END END ELSE 0 END) AS Debit,
SUM(CASE WHEN gbkmut.valcode <> 'EUR' THEN CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_val >= 0 THEN (bdr_val) ELSE 0 END ELSE CASE WHEN bdr_val < 0 THEN (bdr_val) ELSE 0 END END ELSE 0 END ELSE 0 END) AS DebitFC,
SUM(CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_hfl >= 0 THEN 0 ELSE -(bdr_hfl) END ELSE CASE WHEN bdr_hfl < 0 THEN 0 ELSE -(bdr_hfl) END END ELSE 0 END) AS Credit,
SUM(CASE WHEN gbkmut.valcode <> 'EUR' THEN CASE WHEN transtype IN ('N','C','P') THEN CASE WHEN TransSubType NOT IN ('R','S') THEN CASE WHEN bdr_val >= 0 THEN 0 ELSE -(bdr_val) END ELSE CASE WHEN bdr_val < 0 THEN 0 ELSE -(bdr_val) END END ELSE 0 END ELSE 0 END) AS CreditFC,
SUM(CASE WHEN transtype IN ('N','C','P') THEN gbkmut.aantal ELSE 0 END) AS Aantal,
0 AS Budget,
0 AS BudgetQuantity,
0 AS TotalProd FROM gbkmut INNER JOIN grtbk ON gbkmut.reknr = grtbk.reknr
LEFT OUTER JOIN prproject ON gbkmut.project = prproject.projectnr
LEFT OUTER JOIN humres reshumres ON gbkmut.res_id = reshumres.res_id
WHERE transtype IN ('N', 'C', 'P') AND remindercount <= 31 AND grtbk.reknr BETWEEN ' 52100' AND ' 52110' AND gbkmut.datum BETWEEN {d '2012-01-01'} AND {ts '2012-12-31 23:59:59'} AND cast(floor(cast(datum as float)) as datetime) BETWEEN {d '2012-01-01'} AND {ts '2012-08-31 23:59:59'} AND ISNULL(gbkmut.project, '--') BETWEEN '80002 ' AND '80013 ' AND gbkmut.TransSubType <> 'X' GROUP BY gbkmut.res_id,reshumres.Fullname ORDER BY gbkmut.res_id,reshumres.Fullname[/sql]
Laatst bewerkt door een moderator:
