Query - weight delivered each day

[Former Member]

Hi all.

I'm currently working at a query to calculate the weight delivered each day. My thougt is to first calculate from DLN1 table and then subtract from RDN1 table. Deliveries minus returns.

I can easily create these queries as stand alone queries, but I would need some help combining them into one query. I'm thinking coulmns like this; A = date, B = weight deliveries, C = weight returns, D = B minus C

Any takers?

Thanks and regards.

Runar Wigestrand.

[Former Member · Accepted solution]

Hi!

It is very much possible. Please put your codings here to merge it.

[Former Member · Accepted solution]

Thanks for reply.

SELECT T1.[DocDate], SUM(cast(T0.[Weight1] as int)) as 'Total Weight' 
FROM DLN1 T0 inner join ODLN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),[%0]) and Convert(char(10),[%1])
Group by T1.[DocDate]


SELECT T1.[DocDate], SUM(cast(T0.[Weight1] as int)) as 'Total Weight' 
FROM RDN1 T0 inner join ORDN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),[%0]) and Convert(char(10),[%1])
Group by T1.[DocDate]

Regards, Runar.

Edited by: Runar Wigestrand on Sep 8, 2009 1:15 PM

[Former Member · Accepted solution]

Hi!

Try this..

Select
a.Docdate,sum(a.DW) as [Delivery Weight],sum(a.RW) as [Return Weight],
sum(a.DW)-sum(a.RW) as [Total Weight] from (

SELECT T1.[DocDate], SUM(cast(T0.[Weight1] as int)) as DW,0 as RW
FROM DLN1 T0 inner join ODLN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),[%0]) and Convert(char(10),[%1])
Group by T1.[DocDate]

 Union all

 SELECT T1.[DocDate], 0 as DW,SUM(cast(T0.[Weight1] as int)) as RW
FROM RDN1 T0 inner join ORDN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),[%0]) and Convert(char(10),[%1])
Group by T1.[DocDate] )a
Group By
a.Docdate

[Former Member · Accepted solution]

Hi!

Check this ....

Declare @FromDate Datetime
Declare @ToDate Datetime
Set @FromDate = (Select min(s0.Docdate) from ODLN S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(s1.Docdate) from ODLN S1 where S1.Docdate <='[%1]')

Select
a.Docdate,sum(a.DW) as [Delivery Weight],sum(a.RW) as [Return Weight],
sum(a.DW)-sum(a.RW) as [Total Weight] from (
 
SELECT T1.[DocDate], SUM(cast(T0.[Weight1] as int)) as DW,0 as RW
FROM DLN1 T0 inner join ODLN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),@FromDate) and Convert(char(10),@ToDate)
Group by T1.[DocDate]
 
 Union all
 
 SELECT T1.[DocDate], 0 as DW,SUM(cast(T0.[Weight1] as int)) as RW
FROM RDN1 T0 inner join ORDN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between Convert(Char(10),@FromDate) and Convert(char(10),@ToDate)
Group by T1.[DocDate] )a
Group By
a.Docdate

[Former Member · Accepted solution]

Hi.

I tried the query, it almost gets me what I need. I can't seem to get the date formats right though.

I get the following error message when applying query in SAP:

08/09/2009 13:53:24: 1). [Microsoft][SQL Native Client][SQL Server]The conversion of a char

data type to a datetime data type resulted in an out-of-range datetime value.

'Brukerdefinerte verdier' (CSHS)

Any tips on how to solve this?

Thanks, Runar.

Edited by: Runar Wigestrand on Sep 8, 2009 1:56 PM

Edited by: Runar Wigestrand on Sep 8, 2009 1:57 PM

Edited by: Runar Wigestrand on Sep 8, 2009 1:57 PM

[Former Member · Accepted solution]

Hi!

Try this.

Declare @FromDate Datetime
Declare @ToDate Datetime
Set @FromDate = (Select min(s0.Docdate) from ODLN S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(s1.Docdate) from ODLN S1 where S1.Docdate <='[%1]')
 
Select
a.Docdate,sum(a.DW) as [Delivery Weight],sum(a.RW) as [Return Weight],
sum(a.DW)-sum(a.RW) as [Total Weight] from (
 
SELECT T1.[DocDate], SUM(cast(T0.[Weight1] as int)) as DW,0 as RW
FROM DLN1 T0 inner join ODLN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between @FromDate and @ToDate
Group by T1.[DocDate]
 
 Union all
 
 SELECT T1.[DocDate], 0 as DW,SUM(cast(T0.[Weight1] as int)) as RW
FROM RDN1 T0 inner join ORDN T1
on T0.DocEntry = T1.DocEntry
 WHERE 
T1.DocDate between @FromDate and @ToDate
Group by T1.[DocDate] )a
Group By
a.Docdate

[Former Member · Accepted solution]

Hi again.

Thank you - this was exactly what I needed. Much appreciated.

Kind regards, Runar.

[Former Member · Accepted solution]

Hi!

Great !