- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- add OCRD to query
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Options
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
add OCRD to query
MarkOenema
Explorer
Options
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 2024 Jul 31 6:32 PM
363
- SAP Managed Tags:
- SAP Business One
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
Labels:
SonTran
Active Contributor
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
2024 Aug 01
3:22 AM
Hi,
Try this
DECLARE @YEAR AS INT = 2024
select T2.TrnspName, T1.* from OCRD T0 right join
(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) T1 on T0.CardCode=T1.CardCode
left join OSHP T2 on T0.ShipType=T2.TrnspCodeHope this helps,
Son Tran
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ask a Question
Related Content
- How to access comments in BW/4HANA for reporting purposes in Technology Q&A
- SAP Analytics Cloud Transactional Data Upload in Technology Q&A
- SAP CAP: Is it ok to use req._query[$filter] in a custom handler? in Technology Q&A
- MDX Statement in Technology Q&A
- Consumption filter default in Technology Q&A
Top Q&A Solution Author
| User | Count |
|---|---|
| 66 | |
| 11 | |
| 10 | |
| 10 | |
| 9 | |
| 8 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.