antiguedad-QRh.sql
· 932 B · MySQL
Raw
SELECT
IL.[Item No_] AS [sku],
DATEDIFF(DAY, CASE WHEN MAX(IL.[Last Invoice Date]) = '1753-01-01' THEN MAX(IL.[Posting Date]) ELSE MAX(IL.[Last Invoice Date]) END, GETDATE()) AS [age_in_days],
(ITEM.[Unit Cost] + (ITEM.[Unit Cost] * 0.15)) AS [regular_price],
IA.[Inmediate Sales Stock] AS [stock_quantity]
FROM
[Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] IL
LEFT JOIN [Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$Item$437dbf0e-84ff-417a-965d-ed2bb9650972] ITEM ON IL.[Item No_] = ITEM.[No_]
LEFT JOIN [Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$DynES TFB Item Availability$ef62f6b4-0ebd-4de3-923e-eb0dd0b74447] IA ON IL.[Item No_] = IA.[No_]
WHERE
IL.[Location Code] = 'ICP' AND IA.[Location Code] = 'ICP'
AND IA.[Inmediate Sales Stock] > 0
GROUP BY
IL.[Item No_],
ITEM.[Unit Cost],
IA.[Inmediate Sales Stock]
ORDER BY [age_in_days] DESC
| 1 | SELECT |
| 2 | IL.[Item No_] AS [sku], |
| 3 | DATEDIFF(DAY, CASE WHEN MAX(IL.[Last Invoice Date]) = '1753-01-01' THEN MAX(IL.[Posting Date]) ELSE MAX(IL.[Last Invoice Date]) END, GETDATE()) AS [age_in_days], |
| 4 | (ITEM.[Unit Cost] + (ITEM.[Unit Cost] * 0.15)) AS [regular_price], |
| 5 | IA.[Inmediate Sales Stock] AS [stock_quantity] |
| 6 | FROM |
| 7 | [Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] IL |
| 8 | LEFT JOIN [Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$Item$437dbf0e-84ff-417a-965d-ed2bb9650972] ITEM ON IL.[Item No_] = ITEM.[No_] |
| 9 | LEFT JOIN [Demo Database BC (17-0)].dbo.[TRADE FORCE BRANS$DynES TFB Item Availability$ef62f6b4-0ebd-4de3-923e-eb0dd0b74447] IA ON IL.[Item No_] = IA.[No_] |
| 10 | WHERE |
| 11 | IL.[Location Code] = 'ICP' AND IA.[Location Code] = 'ICP' |
| 12 | AND IA.[Inmediate Sales Stock] > 0 |
| 13 | GROUP BY |
| 14 | IL.[Item No_], |
| 15 | ITEM.[Unit Cost], |
| 16 | IA.[Inmediate Sales Stock] |
| 17 | ORDER BY [age_in_days] DESC |