I am trying for some time to create a query that would give the value of stock for a specific item on each date within a given date range.
Tables are as they are (NAVISION) and we can not influence structure so we have to use what we have.
The result is based on querying 3 different tables containing Dates, Items, and Items Ledger Entry (which keeps all Item related movements IN, OUT, Transfer In, Transfer Out...), and the sum of all those movements are giving current stock level
The result of the below query is partly ok, but the issue is that there are rows with NULL for products when there was no change on that day.
What we need is for the query to go to the first next value of Stock for a specific product and show that value instead of NULL. So instead of having Stock NULL it should find first next not NULL value and show this on instead
Query, as we have it now, is:
SELECT
ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY i.[No_] ASC) as BarcodeInDate#
,CAST([Date] as date) as [Date]
,i.No_ as Barcode
,CAST(q.Stock as int) as Stock
FROM [XLS].[dbo].[XLS$XLS Calendar] C
CROSS JOIN XLS$Item i
LEFT JOIN
(
SELECT
DISTINCT [Posting Date]
,[Item No_]
,SUM([Invoiced Quantity]) OVER (PARTITION BY [Item No_] ORDER BY [Posting Date]) Stock
FROM
[dbo].[XLS$Item Ledger Entry]
) q
on C.Date=q.[Posting Date] and i.No_=q.[Item No_]
where
C.[Date] between getdate()-31 and getdate()-1
ORDER BY C.Date DESC
I dont know how to represent output in best way here so below is an image.
**UPDATE
As @SteveC recommended...3 used tables create statements below so you can see the data and structure of queried tables.
Item Ledger Entry table
To big to post here...link for create query on my drive
https://drive.google.com/file/d/1xCq0_lh0S0gq7rx1VMC0ZHK4buu_rkit/view?usp=sharing
Calendar which is simply a basic one-column calendar in order to have all dates in Queries, even when there was no change in items movements. Use this or some dynamic...its just all dates in given range (here for last 30 days for example)
CREATE TABLE [XLS$XLS Calendar](
Date DATE NOT NULL PRIMARY KEY
);
INSERT INTO XLS$Item(Date) VALUES ('14-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('15-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('16-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('17-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('18-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('19-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('20-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('21-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('22-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('23-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('24-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('25-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('26-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('27-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('28-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('29-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('30-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('31-07-2021');
INSERT INTO XLS$Item(Date) VALUES ('01-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('02-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('03-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('04-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('05-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('06-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('07-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('08-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('09-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('10-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('11-08-2021');
INSERT INTO XLS$Item(Date) VALUES ('12-08-2021');
ITEMS simplified since most of the data is not important for this query...its just Items descriptions and codes, weight etz...
CREATE TABLE XLS$Item(
No_ INTEGER NOT NULL PRIMARY KEY
,Description VARCHAR(30) NOT NULL
,Product_Group_Code VARCHAR(9) NOT NULL
);
INSERT INTO mytable(No_,Description,Product_Group_Code) VALUES (3856005147540,'MS MS-500 napajanje 500W','NAPAJANJE');
INSERT INTO mytable(No_,Description,Product_Group_Code) VALUES (8600412596522,'Teracell Flip Cover preklopna','FUTMASMOB');
INSERT INTO mytable(No_,Description,Product_Group_Code) VALUES (8600412615940,'Teracell Evo kabl za iph crni','KABLZAMOB');
INSERT INTO mytable(No_,Description,Product_Group_Code) VALUES (8806090987564,'Samsung Galaxy A52 crni','MOBTELEF');