Announcements
cancel
Showing results for
Did you mean:
Creator

## Calculated fields from multiple tables

Please be nice I'm new!  so I have 2 sql load statements in my script.  see examples below

sql select part_number, creation_date

from Table A;

sql select part_number, ship_date

from table B;

Then I have an expression for a graph which calculates the last receipt:

expression- Max(if(creation_date<=ship_date,creation_date))

I would like to write the last receipt calculation into my script but I don't know how to calculate it when it's pulling from 2 different tables.  Please help!

1 Solution

Accepted Solutions
Champion III

Do a left join like

TableA:

sql select part_number, creation_date

from Table A;

LEFT JOIN(TableA)

TableB:

sql select part_number, ship_date

from table B;

Left Join(TableA)

FinalTab:

Max(if(creation_date<=ship_date,creation_date)) AS LastReceiptDate

Resident TableA

Group By part_number;

17 Replies
Champion III

Do a left join like

TableA:

sql select part_number, creation_date

from Table A;

LEFT JOIN(TableA)

TableB:

sql select part_number, ship_date

from table B;

Left Join(TableA)

FinalTab:

Max(if(creation_date<=ship_date,creation_date)) AS LastReceiptDate

Resident TableA

Group By part_number;

Creator
Author

thank you! The join works perfectly but now the formula is giving me an incorrect answer.  Any idea why this the formula would work perfectly as an expression but not in the script?  it may have to do with specifying what instance of ship date i am referring to.  If that's the case would this script work if i brought in the order number?  maybe?

Champion III

Probably yes. I am not sure about the details so if you can provide more information on the script you are trying on or paste your script with the output you are looking for will help me to work on it.

Creator
Author

Here's the script:  There's quite a bit here and like I said, I'm a new user so this may not be the best way to write all this.  Please don't judge me! The end result is that I want the "lastreceiptdate" field to pull the last creation date (in table b) that is less than the actual ship date in (table a).

TableA:

SQL SELECT

dbo.V_ItemBranchMaster.IBSTKT__Stocking_Type,

dbo.V_OTD_Outbound_Americas.Item_Classification_Detail,

dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM as [Short],

dbo.V_OTD_Outbound_Americas.IMLITM__World_Wide_Item_Number,

dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO as [Order_Number],

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO as [Order_Type],

dbo.V_OTD_Outbound_Americas.Late_Reason_Code,

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID as [Line_Number],

dbo.V_OTD_Outbound_Americas.Bill_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Ship_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Customer_Service_Level_Bill_To,

dbo.V_OTD_Outbound_Americas.Quantity_Shipped__SDSOQS,

dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ,

dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.Requested_Date_SDDRQJ,

dbo.V_OTD_Outbound_Americas.Scheduled_Pick_Date__SDPDDJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Time__SLTDAY,

dbo.V_OTD_Outbound_Americas.Promised_Ship_Date__SDPPDJ,

dbo.V_OTD_Outbound_Americas.Promised_Delivery_Date__SDRSDJ,

dbo.V_OTD_Outbound_Americas.Requested_On_Time,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Requested,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Ship,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Delivery,

dbo.V_OTD_Outbound_Americas.Supplier_Name,

dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN,

case when dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ=dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ

then datediff(dd,Requested_Date_SDDRQJ,Order_Date__SDTRDJ)

else datediff(dd,Requested_Date_SDDRQJ,Order_Date__SLUPMJ)

end  as [Request date- order date],

case when Requested_Date_SDDRQJ<Promised_Ship_Date__SDPPDJ

then 'True'

else 'False'

end as [Request date before PSD],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Requested_Date_SDDRQJ ) as [Packslip Print-Req Date],

DATEDIFF(dd,Scheduled_Pick_Date__SDPDDJ,Requested_Date_SDDRQJ ) as [Days between scheduled pick and request],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Actual_Ship_Date__SDADDJ) as [Days between packslip print and actual ship],

DATEDIFF(dd,PickData_Transmission_Date__SLUPMJ,Scheduled_Pick_Date__SDPDDJ) as [Days between pack slip and sched pick date],

case when  Line_Number__SDLNID%1000 =0

then 'Not a Split Line'

else 'Split Line'

end as [Split Line],

then  'Order entered inside LT'

end as [LT respected],

Requested_Date_SDDRQJ+8 as [Requested_plus_eight]

FROM         dbo.V_ItemBranchMaster INNER JOIN

dbo.V_ItemBranchMaster.IBITM__Item_Number_Short = dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM INNER JOIN

dbo.V_Item_Ledger_Cardex ON dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO = dbo.V_Item_Ledger_Cardex.Order_Number__ILDOCO AND

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO = dbo.V_Item_Ledger_Cardex.Order_Type__ILDCTO AND

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID = dbo.V_Item_Ledger_Cardex.Order_Line__ILLNID

WHERE     (dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ >= CONVERT(DATETIME, '2013-01-01 00:00:00', 102));

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM         dbo.V_Item_Ledger_Cardex

WHERE     (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

Resident TableA

Group By Lot_Number__ILLOTN;

Champion III

Your script looks absolutely fine. So the tableA leftjoin with TableB gets the LOT Number and CreationDate for those lot numbers. Then we are using the Field LotNumber to group by to get the Max creation date from those dates where creation date is <= ActualShipDate. So you said this expr works in front end so what dimension are you using? If you use LotNumber as dimension and try this expr are you not getting the right result?

Creator
Author

order number and line number, these would have to be added to the join right?  like this??  Table a and table b would join on lot number since its the only field they both share so do I even need the lot number?

for each distinct order number and line number combination , I need to know what the last receipt was prior to shipment.

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM         dbo.V_Item_Ledger_Cardex

WHERE     (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

Order_Number__SDDOCO ,

Line_Number__SDLNID

Resident TableA

Group By Order_Number__SDDOCO ,Line_Number__SDLNID

Champion III

This seems convincing. So did you try this code and checked if it is picking right receiptdate?

Creator
Author

I tried, there was an error on the reload.  I will have to keep trying.  thank you

Champion III

What is the error can you share a snap shot of the error?

Community Browser