Dear all,
Hope you can help me with the following query:
DECLARE @YEAR AS INT = 2024
sELECT *
,Alles.Januari +
Alles.Februari +
Alles.Maart +
Alles.April +
Alles.Mei +
Alles.Juni +
Alles.Juli +
Alles.Augustus +
Alles.September +
Alles.Oktober +
Alles.November +
Alles.December AS [Totaal]
fROM (
SELECT c.CardCode
,c.CardName
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 1
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 1
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Januari]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 2
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 2
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Februari]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 3
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 3
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Maart]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 4
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 4
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [April]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 5
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 5
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Mei]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 6
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 6
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Juni]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 7
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 7
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Juli]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 8
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 8
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Augustus]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 9
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 9
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [September]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 10
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 10
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [Oktober]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 11
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 11
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [November]
,(Select Cast(Isnull(Sum(x.sales), 0) As Money) From
(select (sales.DocTotal - sales.VatSum) as [sales]
from OINV sales
where month(sales.DocDate) = 12
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode
union all
select 0 - (sales.DocTotal - sales.VatSum)
from ORIN sales
where month(sales.DocDate) = 12
and year(sales.DocDate) = @YEAR
and sales.CardCode = c.CardCode) x) AS [December]
FROM OCRD c
WHERE c.CardType = 'C'
AND ISNULL(c.frozenFor, 'N') = 'N') [Alles]
wHERE Alles.Januari +
Alles.Februari +
Alles.Maart +
Alles.April +
Alles.Mei +
Alles.Juni +
Alles.Juli +
Alles.Augustus +
Alles.September +
Alles.Oktober +
Alles.November +
Alles.December <> 0
oRDER bY 15 dESC
I would like to see at the end of each customer with region in my country they are. We filled this in the BP master data under 'Route' (OCRD,ShipType). If I have this in my query it's easier to send my co-workers to certain clients because they are in the same region.
Please advise.
Thanks in advance.
Regards,
Mark Oenema
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.