1

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.

Result output

**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');
  • Just as you've nicely provided the QueryResult DDL it would be helpful to also provide sample data for the 3 input tables. Otherwise it's just too much guesswork imo – SteveC Aug 12 '21 at 17:51
  • Added those. Thank you for advice and support – Sasa Oravec Aug 13 '21 at 07:57

0 Answers0