cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query: select billaddress if no delivery address exist.

Former Member
0 Kudos

hi sql expert,


The Query shall filter all invoices by date, articles and show the delivery address. Problem: Not every customer has deposited a delivery address. In this case the billing address is to be taken.

How can this be done ?


This query



SELECT T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], (T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt],substring(T1.Freetxt,0,5) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", T1.[Quantity] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode WHERE CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]'

and T1.Itemcode = [%0] GROUP BY T0.[DocNum], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], T1.[FreeTxt], T1.[Quantity]

UNION ALL

SELECT T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], -T1.[Price], -(T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt], ('-' + substring(T1.Freetxt,0,5)) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", -T1.[Quantity] FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode WHERE CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]' and T1.Itemcode = [%0]

GROUP BY  T0.[DocNum], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], T1.[FreeTxt], T1.[Quantity]

Thanks a lot!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

hi,

I think I got it now 🙂

First: Show me priority all  delivery addresses . Otherwise billing addresses:


SELECT T1.[CardCode], T2.[Street] FROM OCRD T1  INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode WHERE NOT EXISTS

(

  SELECT *

  FROM CRD1 as T2

  WHERE T2.[AdresType] = 's' and t1.CardCode = t2.Cardcode

)

union

SELECT T1.[CardCode], T2.[Street] FROM OCRD T1  INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode

where T2.[AdresType] = 's'

secondly:

Filter all invoices by date, articles and show priority the delivery address else billingaddress and subtract the credits:


SELECT T0.[Cardcode], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], (T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt],substring(T1.Freetxt,0,5) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", T1.[Quantity]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode

WHERE CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]'

and T1.Itemcode = [%0] and not exists  (

  SELECT *

  FROM CRD1 as T3

  WHERE T3.[AdresType] = 's' and t2.CardCode = t3.Cardcode

)

GROUP BY T0.[Cardcode], T0.[DocNum],T0.[CardCode], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], T1.[FreeTxt], T1.[Quantity]

union

SELECT T0.[Cardcode], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], (T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt],substring(T1.Freetxt,0,5) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", T1.[Quantity]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode

Where T3.[AdresType] = 's' and T1.Itemcode = [%0] and CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]'

-- ab hier werden die Gutschriften abgezogen

union

SELECT T0.[Cardcode], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], -T1.[Price], -(T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt], ('-' + substring(T1.Freetxt,0,5)) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", -T1.[Quantity] FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode WHERE CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]' and T1.Itemcode = [%0]

and not exists  (

  SELECT *

  FROM CRD1 as T3

  WHERE T3.[AdresType] = 's' and t2.CardCode = t3.Cardcode

)

GROUP BY  T0.[Cardcode], T0.[DocNum], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], T1.[FreeTxt], T1.[Quantity]

union

SELECT T0.[Cardcode], T0.[CardName], T3.[ZipCode],T3.[Street], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Price], (T1.[Price] * T1.[Quantity]) as "Gesamtpreis in €", T1.[FreeTxt],substring(T1.Freetxt,0,5) as "Grundpreis in €",substring(T1.Freetxt,9,6) as "TS-Wert in %", T1.[Quantity]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode

Where T3.[AdresType] = 's' and T1.Itemcode = [%0] and CONVERT(nvarchar(8), T1.ShipDate, 112) BETWEEN '[%FromDate]' AND '[%ToDate]'

Answers (0)