Calculated fields from multiple tables

[nburton78]

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!

[nburton78]

it was a syntax error.  I was referring to the wrong field name. It worked! Thank you for all your help! 

[vishsaggi]

No problem.

[nburton78]

If I take table A and join it with table B and create the expression lastreceiptdate, correct?  is there any way to  compare lastreceiptdate to other tables?  like table A  has a field called requested date, can I compare lastreceiptdate to requested date?

[vishsaggi]

Why not. Did you try that in your expression or in a chart?

[nburton78]

I'd like to write it in the script.  right now its an expression in the chart. 

[vishsaggi]

I have to see the data model, are we joining this datefield to TableA or is that datefield in a separate table?

[nburton78]

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWH_V1;Data Source=tssstrscm003;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TSSSMDL381;Use Encryption for Data=False;Tag with column collation when possible=False];

TableA:

SQL SELECT      

dbo.V_ItemBranchMaster.IBSTKT__Stocking_Type,

dbo.V_OTD_Outbound_Americas.Header_Business_Unit__SDEMCU,

                      dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU,

                      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_Address_Number__SDAN8 as [Bill_To],

                      dbo.V_OTD_Outbound_Americas.Bill_To_Customer_Name,

                      dbo.V_OTD_Outbound_Americas.Ship_To_Address_Number__SDSHAN,

                      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.IBLTCM__Leadtime_Cumulative,

                      dbo.V_OTD_Outbound_Americas.Requested_Date_SDDRQJ,

                      dbo.V_OTD_Outbound_Americas.Leadtime_Date_at_SLUPMJ_Order_Date,

                      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.Actual_Ship_Date__SDADDJ,

                      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,

                      MONTH(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Month,

                      YEAR(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Year,

                      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 [PackslipPrint-ReqDate],

                       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],

case when (Leadtime_Date_at_SLUPMJ_Order_Date-Order_Date__SLUPMJ)+Order_Date__SDTRDJ>Scheduled_Pick_Date__SDPDDJ

then  'Order entered inside LT'

else 'Lead Time respected'

end as [LT respected],

Requested_Date_SDDRQJ+8 as [Requested_plus_eight]

FROM         dbo.V_ItemBranchMaster INNER JOIN

                      dbo.V_OTD_Outbound_Americas ON dbo.V_ItemBranchMaster.IBMCU__Business_Unit = dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU AND

                      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:

load

Order_Number,

Order_Type,                     

Line_Number,

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Order_Number, Order_Type, Line_Number;

sql SELECT     ABAN8__Address_Number as [Bill_To],  AIINMG__Print_Message as [LG_NO_Early_Ship]

FROM         dbo.V_AddressBookMaster

WHERE     AIINMG__Print_Message IN ('CN83642',

'CNMESSIE1',

'CRT-MFCOCN',

'CS100870',

'CS101278',

'CS102266',

'CS102502',

'CS103327',

'CS104673',

'CS104846',

'CS104856',

'CS104857',

'CS1055',

'CS105531',

'CS105541',

'CS105542',

'CS105548',

'CS105549',

'CS105550',

'CS106892',

'CS107473',

'CS109383',

'CS111588',

'CS115033',

'CS115741',

'CS1158',

'CS116029',

'CS116089',

'CS116100',

'CS116547',

'CS116889',

'CS14255',

'CS14845',

'CS15624',

'CS16115',

'CS17664',

'CS17735',

'CS17939',

'CS1852',

'CS2100',

'CS22403',

'CS22678',

'CS22706',

'CS22745',

'CS25144',

'CS2624',

'CS2895',

'CS3357',

'CS3476',

'CS38864',

'CS4136',

'CS81014',

'CS84091',

'CS84232',

'CS84661',

'CS84672',

'CS86550',

'CS87188',

'CS9586',

'CS99550',

'DS17510',

'DS17925',

'DS20285',

'DS20286',

'DSCLARK',

'DSGD',

'DSJD',

'DSNE',

'ELC01',

'FW22937',

'FW84898',

'FW86972',

'FWARKWIN',

'FWPARKERAB',

'FWROTEM',

'GABUE1',

'GL20715',

'LG101660',

'LG106790',

'LG17783',

'LG21870',

'LG2841',

'LG3290',

'LG84218',

'LG84232',

'LGASNNE',

'LGGMSPO',

'LGNOEARLY',

'LGNOEARLYD',

'LGSKF00001',

'STW00008',

'STW00009',

'STW00103',

'VRF-DOCKNE',

'WEB-NOTRAC',

'WHMAN',

'WOODW11779'

);

sql SELECT   distinct  IL1.Lot_Number__ILLOTN,

CASE WHEN IL1.Document_Type__ILDCT = 'OV' THEN NULL ELSE IL2.Lot_Number__ILLOTN END AS [LL Lot],

CASE WHEN IL1.Document_Type__ILDCT = 'OV' THEN NULL ELSE IL3.Creation_Date__ILCRDJ END AS [LL Creation]

FROM         dbo.V_Item_Ledger_Cardex AS IL1 LEFT OUTER JOIN

                      dbo.V_Item_Ledger_Cardex AS IL2 ON IL1.Document_Number__ILDOC = IL2.Document_Number__ILDOC LEFT OUTER JOIN

                      dbo.V_Item_Ledger_Cardex AS IL3 ON IL2.Lot_Number__ILLOTN = IL3.Lot_Number__ILLOTN

WHERE     (IL1.Explanation__ILTREX <> 'Landed Cost') AND (IL1.Document_Type__ILDCT IN ( 'IC')) AND (IL1.Order_Type__ILDCTO <> 'OX') AND

                      (IL2.Document_Type__ILDCT IN ('IM', 'OV')) AND (IL3.Document_Type__ILDCT IN ('OV', 'IC')) AND (IL3.Explanation__ILTREX <> 'Landed Cost') AND

                      (IL1.Lot_Number__ILLOTN IN

(SELECT     dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN

FROM          dbo.V_OTD_Outbound_Americas 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));

sql SELECT   [IBMCU__Business_Unit]

      ,[IBITM__Item_Number_Short]as [Short]

      ,[IBLITM__World_Wide_Item_Number]

      ,[Item_Classification]

      ,[IBSAFE__Safety_Stock_Qty]

FROM    [DWH_V1].[dbo].[V_ItemBranchMaster]

WHERE  IBMCU__Business_Unit = 'LG01';

LOAD [Causer Main Group],

     [Late Reason Causer],

     Late_Reason_Code,

     Late_Reason_Code_Description

FROM

(ooxml, embedded labels, table is LRC_Detail_Text);

Directory;

LOAD CONCAT, [Start Date (F554311A)], [Complete Date (F554311A)], [Primary Supplier (F554311A)],

     [Short Item No (F554311B)]as [Short],

      [2nd Item Number (F554311B)],

      [Order Number (F554311A)] as [Order_Number],

     [Or Ty (F554311A)] as [Order_Type],

     [Line Number (F554311A)],

     Line_Number_K as [Line_Number],

     [Count of PR]as [Count_of_PR]

FROM [PR Report.xlsx] (ooxml, embedded labels, table is Sheet1) WHERE(CONCAT <> '0');

Directory;

LOAD Order_Number__SDDOCO as [Order_Number],

Order_Type__SDDCTO  as [Order_Type],

Line_Number__SDLNID as [Line_Number],

Group as [Scotts_Group],

Detail as [Scotts_Detail]

FROM [combined 2.xlsx] (ooxml, embedded labels, table is [Combine Sheet]);

[nburton78]

So I joined table A to table B to create the expression LastReceiptDate.  Now I want to compare Lastreceiptdate to other tables in table A.  Can I do that?  like

case when (lastreceiptdate>Requested_Date_SDDRQJ

then  ''Inventory not available'

else 'inventory avaiable'

end as [Inventory available],