July 13, 2022 at 5:05 pm
I have several SQL tables I'm working with, and I want to join each other. I can perform join operations and see the tables under one roof. But there is an error in the join process I did, I need to sort and select the last one at the same time while joining. However, I couldn't do that 🙁 If I had to explain with an example;
I have the following tables;
Table1: StoreStock
Columns: StoreCode, ProductCode, Stock, Time
Rule: This is my main table, we must join other tables here. StoreCode+ProductCode will be used as the key.
Table2: StockOnTheRoad
Columns: TransferNo, StoreCode, ProductCode, OnTheWayStock, Status, Time
Rule: There is more than one status for 1 transfer here. The current status of the transfer is written in the table according to the process. The number of stocks is taken according to the status. "Pending" if the status of the transfer is S1, S2; if the status of the transfer is S3, S4, it is called "OnTheWay". Here, it is only necessary to get the current status number and write it under "Waiting" or "OnTheWay" to sort the statuses and get the current one. So, if a transfer's final status is S4, it will take this number and write it under "OnTheWay", and under "Waiting" if S2. For a product from different transfers, "Waiting" and "OnTheWay" may come together. I use StoreCode+ProductCode as the key.
Table3: StoreOrder
Columns: StoreCode, ProductCode, OpenOrder, Status
Rule: I join the open order quantities for a store in this table into my first table. Here, I remove the statuses "Cancel" and "Done". I use StoreCode+ProductCode as the key.
The most complicated situation here is the correct numbers of Table2 to Table1. I couldn't make it here. Is there anyone who can help? Thank you in advance 🙂
July 13, 2022 at 5:25 pm
Table schemas ? Sample data ?
July 13, 2022 at 6:05 pm
I'm sorry for that. But if I knew that much, I would answer this question myself 🙂 I can give the attached excel example for the table and query. If you can't help, I understand, I'll try to do something myself 🙂
July 13, 2022 at 6:31 pm
I'm sure that someone here would be able to help you, if you were able to provide sample data (in consumable form, ready for cut & paste into SSMS – CREATE TABLE and INSERT will do the job) and desired output based on that sample data.
If you do that, people would be able to use it in conjunction with the excellent description you have already provided to give you a working solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2022 at 6:46 pm
I wish so much that I could do what you said and that you guys could help me 🙁 But unfortunately, I haven't mastered that much yet 🙁 The best I can do is the following query. My problem is that I can't add and sum join from the "StockOnTheRoad" table according to status.
`
select
ss.StoreCode, ss.ProductCode, ss.Stock, ss.Time,
sotr.TransferNo, sotr.StoreCode, sotr.ProductCode, sum(sotr.OnTheWayStock),
case when status in ('S1', 'S2') then 'Pending'
when status in ('S3', 'S4') then 'OnTheWay' else '' end sotr.Status,
sotr.Time,
so.StoreCode, so.ProductCode, sum(so.OpenOrder), so.Status,
from [dbo].[StoreStock] ss
left join [dbo].[StockOnTheRoad] sotr on sotr.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)
left join [dbo].[StoreOrder] so on so.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)
where so.Status not in ('Cancel', 'Done')`
July 13, 2022 at 7:21 pm
If you <Right-Click> your tables, you can generate "Script Table As" ....CREATE To...., which you can copy-paste here as "CODE" to help everyone understand your table definitions.
Then sample data included as INSERT INTO statements.
And some examples of what you want the SELECT results to look like.
July 13, 2022 at 7:43 pm
What question are you trying to answer? I am guessing what you need, but does it look anything like this? (There may be typos as I do not have tables to write against).
This looks for the most recent time for each transfer, store and product, then assigns a status of 'pending' or 'on the way', then groups by store, product and status to give a sum of OnTheWayStock.
If you put something like this in a CTE, could you join your other tables to it to get what you need? This will return multiple rows per store and product if there are multiple transfers with different statuses, is that what you need, or do you need a single row per store and product, perhaps with the Pending and OnTheWay totals in different columns?
SELECT a.StoreCode, a.ProductCode,
CASE
WHEN a.[Status] IN ('S1', 'S2') THEN 'Pending'
WHEN a.[Status] IN ('S3', 'S4') THEN 'OnTheWay'
ELSE 'Other'
END AS StatusSummary,
SUM(a.OnTheWayStock) AS StockUnits,
FROM (
SELECT TransferNo, StoreCode, ProductCode, OnTheWayStock, [Status], [Time],
ROW_NUMBER() OVER (PARTITION BY TransferNo, StoreCode, ProductCode ORDER BY [Time] DESC) AS RowNum
FROM DBO.StockOnTheRoad
) AS a
WHERE a.RowNum = 1
GROUP BY a.StoreCode, a.ProductCode,
CASE
WHEN a.[Status] IN ('S1', 'S2') THEN 'Pending'
WHEN a.[Status] IN ('S3', 'S4') THEN 'OnTheWay'
ELSE 'Other'
END
July 13, 2022 at 7:46 pm
I'm not really sure what you want to do. Do you want to group rows by "Time"?
SELECT ss.StoreCode,
ss.ProductCode,
ss.Stock,
ss.Time,
sotr.TransferNo,
sotr.StoreCode,
sotr.ProductCode,
sotr.OnTheWayStockSum,
sotr.Status,
sotr.Time
so.StoreCode,
so.ProductCode,
so.OpenOrderSum,
so.Status
FROM StoreStock ss
OUTER APPLY(SELECT case when sotr.TransferNo in ('S1', 'S2') then 'Pending'
when sotr.TransferNo in ('S3', 'S4') then 'OnTheWay'
else ''
end Status,
sotr.TransferNo,
sotr.StoreCode,
sotr.ProductCode,
sotr.Time,
SUM((sotr.OnTheWayStock) OnTheWayStockSum
FROM StockOnTheRoad sotr
WHERE sotr.StoreCode = ss.StoreCode
AND sotr.ProductCode = ss.ProductCode
GROUP BY sotr.TransferNo,
sotr.StoreCode,
sotr.ProductCode,
sotr.Time,
case when sotr.TransferNo in ('S1', 'S2') then 'Pending'
when sotr.TransferNo in ('S3', 'S4') then 'OnTheWay'
else ''
end
) sotr
OUTER APPLY(SELECT so.StoreCode,
so.Status,
so.ProductCode,
sum(so.OpenOrder) OpenOrderSum
FROM StoreOrder so
WHERE so.StoreCode = ss.StoreCode
AND so.ProductCode = ss.ProductCode
GROUP BY so.StoreCode, so.Status, so.ProductCode) so
July 13, 2022 at 8:08 pm
Actually, "Ed B" was right about this "do you need a single row per store and product, perhaps with the Pending and OnTheWay totals in different columns?".
Retail thinking is as follows; In the table where we see the stock of that product in a store, on the basis of product and store, in the form of a non-repeating line, the number of that product on the way, waiting to be collected in the warehouse (Pending) and shipping from the warehouse to the store (OnTheWay), at the same time, coming to that store from the supplier (OpenOrder).
In summary, while there are 12 stocks in 123 stores of SKU1 products, there are 34 waiting to be collected in the warehouse, and there are 5354 on-the-road stocks from the supplier to 123 stores.
To be seen in a single table; the pieces of stocks in the store, the total pieces of stocks on the way from the warehouse to the store, and the total pieces of orders on the way from the supplier to the store.
July 13, 2022 at 10:12 pm
I can't tell what information is stored in StoreOrder and I hesitate to join to it unless StoreCode and ProductCode are unique. I have added the join, but it will duplicate data if StoreCode and ProductCode are not unique. The StoreOrder status filter is included in the join to preserve the outer join. You could put the filter in the where section, as long as you accounted for nulls.
Does this return anything like what you need? I did create some tables with these column names, so the syntax is valid, but I don't understand the relationships between StoreStock and StoreOrder. This is where providing sample data would be helpful.
WITH StockInTransit
AS (
SELECT a.StoreCode, a.ProductCode,
SUM(a.OnTheWayStock * (CASE WHEN a.[Status] IN ('S1', 'S2') THEN 1 ELSE 0 END)) AS PendingStock,
SUM(a.OnTheWayStock * (CASE WHEN a.[Status] IN ('S3', 'S4') THEN 1 ELSE 0 END)) AS OnTheWayStock
FROM (
SELECT TransferNo, StoreCode, ProductCode, OnTheWayStock, [Status],
ROW_NUMBER() OVER (PARTITION BY TransferNo, StoreCode, ProductCode ORDER BY [Time] DESC) AS RowNum
FROM dbo.StockOnTheRoad
) AS a
WHERE a.RowNum = 1
GROUP BY a.StoreCode, a.ProductCode
)
SELECT ss.StoreCode, ss.ProductCode, ss.Stock,
ISNULL(st.PendingStock,0) AS PendingStock,
ISNULL(st.OnTheWayStock,0) AS OnTheWayStock,
so.OpenOrder, so.[Status] AS StoreOrderStatus
FROM dbo.StoreStock AS ss
LEFT JOIN StockInTransit AS st ON ss.StoreCode = st.StoreCode AND ss.ProductCode = st.ProductCode
LEFT JOIN dbo.StoreOrder AS so ON ss.StoreCode = st.StoreCode AND ss.ProductCode = st.ProductCode
AND so.[Status] <> 'Cancel' AND so.[Status] <> 'Done';
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply