Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
it was a syntax error. I was referring to the wrong field name. It worked! Thank you for all your help!
No problem.
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?
Why not. Did you try that in your expression or in a chart?
I'd like to write it in the script. right now its an expression in the chart.
I have to see the data model, are we joining this datefield to TableA or is that datefield in a separate table?
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]);
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],