SQL Script Help Please - Too Hard!

Transact-SQL
1

I have 3 Tables. One table is invoicing, the other table is Products (contains what products make parent product), the last table is manufacturing (how long it takes to make each product). What I would like to do is have a statement that looks at the product code in the invoice table, then look at the invoice date on that row. Now use that product code to look at the products table, return what products are used to make the invoice product, then look at the date from invoice table, take the products returned and look at the manufacturing table, look for when the product was last made closest to the invoice row date and return the time take for each product that make up that invoiced product from the manufacturing tables.

Is this possible through SQL please? If someone could help with a demo script, I can work it through from there. If it is unclear with the above, can someone please let me know and I will try to explain further.

2

We need full table DDL and sample data to do this.

3

Thanks for your reply.

Below is one I've written, but it times out. How do I send a DLL? Apologies for this.

Thank you :slight_smile:

SELECT InvoiceItemID, BSLPartNo, SUM(CompSubconPerPart) AS CompSubPerPart
FROM (SELECT InvLine.InvoiceNumber, InvLine.InvoiceItemID, InvLine.BatchNumber, InvLine.InvoiceDate, InvLine.BSLPartNo,
(SELECT TOP (1) SubConPerOne
FROM (SELECT i.BatchNumber, i.BSLPartNumber, SUM(ISNULL((CASE WHEN (i.AmountOrdered = 0) THEN 0 ELSE ((i.UnitPrice * i.AmountOrdered) / i.QuantityOrdered) END), 0)) AS SubConPerOne, i.OrderID, dbo.tblOrders.OrderDate
FROM dbo.tblOrderItems AS i LEFT OUTER JOIN
dbo.tblOrders ON i.OrderID = dbo.tblOrders.OrderID
WHERE (i.TypeID = 4)
GROUP BY i.BatchNumber, i.OrderID, i.BSLPartNumber, dbo.tblOrders.OrderDate) AS derivedtbl_1
WHERE (OrderDate <= InvLine.InvoiceDate) AND (BSLPartNumber = BOM.BottomLevelPart)
ORDER BY OrderDate DESC) AS CompSubconPerPart
FROM dbo.Lewis_InvoiceLineView AS InvLine INNER JOIN
dbo.Lewis_BOMFin AS BOM ON InvLine.BSLPartNo = BOM.BSLPartNumber
WHERE (InvLine.InvoiceDate >= '01 / 01 / 2018') AND (BOM.BottomLevelPart IS NOT NULL) AND (BOM.MaterialPrice = 0)) AS B
GROUP BY InvoiceItemID, BSLPartNo

4

Script out the table via SSMS -- gen the CREATE TABLE dbo.tblOrderItems ( ... -- including all indexes, and post the scripts.

1 Like
5

Hi Scott, Apologies for not coming back sooner I have not been very well. When I script out via right click database, tasks, generate scripts I get an error.

Sorry for my ignorance. Is there another way to get the information you require?

6

Not another easy way that know of. That's the standard way of generating scripts in SQL Server.

7

don't right click on the whole database, right click on the specific table tblOrderItems

8

Oops, I missed that you were clicking at the db level. @yosiasz is correct, right-click on the table level and script out specifically one table. If there are multiple tables to script, just do each individually.

9

You can also use Object Explorer Details from the View menu and select the Tables node in Object Explorer. Then in the Details pane, use Ctrl-click on each table you want to script to select them, then right-click and choose "Script..." to script them all at once.

10

Thank you all!

Appreciate everyone helping.

I have done 1 table. Can you please confirm this is what you need and I will do the same for the rest.

USE [MAIN]
GO

/****** Object: Table [dbo].[tblCustomers] Script Date: 6/18/2021 1:08:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblCustomers](
[CustomerID] [varchar(20) NOT NULL,
[Customer] [varchar(50) NULL,
[Contacts] [varchar(900) NULL,
[Tel] [varchar(50) NULL,
[Fax] [varchar(50) NULL,
[Address1] varchar(50) NULL,
[Address2] [varchar(50) NULL,
[Address3] [varchar(50) NULL,
[Address4] [varchar(50) NULL,
[Address5] [varchar(50) NULL,
[Notes] [text] NULL
[TempID] [int] IDENTITY(1,1) NOT NULL,
[PartsSupplied] [text] NULL,
[VATRate] [decimal](18, 1) NULL,
[InvoiceEmail] [varchar(100) NULL,
[SpecialNotes] [varchar(max) NULL,
CONSTRAINT [PK_tblCustomers] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

11

Hi All,

I'm sorry about my ignorance here. Is someone able to come back to me and let me know if the above is what you need for each table to build the relevant view?

Thank you!

12

You are not helping yourself as most of us have limited telepathic powers and cannot see your system. What have customers got to do with your original description which only mentions invoicing, products and manufacturing? Why are you using views in the code you posted? etc

What we need is a test rig. This needs to include the table definitions (DDL) and test data with dates in ISO format (YYYYMMDD). We will also need what the result is meant to look like for the given test data.

Ideally we need to create/delete the test rig quicky so it is best to post DDL for temp tables and include only the needed columns. eg Maybe for customers:

CREATE TABLE #Customers
(
	CustomerID varchar(20) NOT NULL
		PRIMARY KEY
	,Customer varchar(50) NOT NULL
);
INSERT INTO #Customers
VALUES ('1', 'Customer1')
	,('2', 'Customer2')
	,('3', 'Customer3');
13

@NateWest: I'm not following at all. In your first post, you said you had 3 three tables: invoicing, Products and manufacturing. When I ask for DDL, you post a table called tblCustomers. Huh?! What does that have to do with your first q?

14

Yeah, I can see why posting about customers confused things. I write in views because I save them and then refresh them through excel.

There's a gap between what you need and what I understand.

I'm going to keep plugging and learning. The view works, it's just badly written so times out. I'll work it out.
This question can be closed.
Thanks,

15

Hi NateWest

All People face the same issues as you ..

I have faced FAR WORSE similar things .. Always it happens when you interact with other people ..

Anyhow .. That's ANOTHER P.H.D subject

16

I have attempted to tidy up your query. Please check it still works.

SELECT invoiceitemid,
       bslpartno,
       SUM(compsubconperpart) AS CompSubPerPart
FROM
(
	SELECT InvLine.invoicenumber,
		InvLine.invoiceitemid,
		InvLine.batchnumber,
		InvLine.invoicedate,
		InvLine.bslpartno,
		(
			SELECT TOP (1)
				SUM(ISNULL(
						CASE
						WHEN i.amountordered = 0
						THEN 0
						ELSE
							(i.unitprice * i.amountordered)
								/ i.quantityordered
						END
					, 0)) AS SubConPerOne
			FROM dbo.tblorderitems AS i
				INNER JOIN dbo.tblorders o
					ON i.orderid = o.orderid
			WHERE i.typeid = 4
				AND O.orderdate <= InvLine.invoicedate
				AND i.bslpartnumber = BOM.bottomlevelpart
			GROUP  BY i.batchnumber,
						i.orderid,
						i.bslpartnumber,
						o.orderdate
			ORDER BY O.orderdate DESC
		) AS CompSubconPerPart
	FROM   dbo.lewis_invoicelineview AS InvLine
			INNER JOIN dbo.lewis_bomfin AS BOM
					ON InvLine.bslpartno = BOM.bslpartnumber
	WHERE InvLine.invoicedate >= '20180101'
			AND BOM.bottomlevelpart IS NOT NULL
			AND BOM.materialprice = 0
) AS D
GROUP  BY invoiceitemid,
          bslpartno;

The question now is how are the views dbo.lewis_invoicelineview and dbo.lewis_bomfin defined?

The problem with views within views is that the optimizer often ends up doing a lot more work than it needs to. If we have the definition of the views we can look at incorporating them into the query.

17

ps If you are calling this query from an application (excel) the default timeout is 30s. (ie The query will time out if it takes more than 30s to run.) You could alter the connection string to increase the timeout but it would be better to write a more efficient query.

18

Thank you Ifor! I didn't expect to get something further on this.
Yes your script is better, thank you for tidying it up.

This is the dbo.lewis_invoicelineview

SELECT dbo.tblCustomers.CustomerID, dbo.tblCustomers.Customer, dbo.tblInvoices.InvoiceNumber, dbo.tblInvoiceItemss.InvoiceItemID, dbo.tblInvoiceItemss.BatchNumber, dbo.tblInvoices.InvoiceDate, dbo.tblInvoiceItemss.BSLPartNo,
dbo.tblInvoiceItemss.UnitPrice, dbo.tblInvoiceItemss.Qty, dbo.tblInvoiceItemss.Qty / dbo.tblInvoiceItemss.UnitOfMeasure * dbo.tblInvoiceItemss.UnitPrice AS Total_price, dbo.tblInvoiceItemss.UnitDescription,
dbo.tblInvoiceItemss.UnitOfMeasure
FROM dbo.tblCustomers INNER JOIN
dbo.tblInvoices ON dbo.tblCustomers.CustomerID = dbo.tblInvoices.CustomerID INNER JOIN
dbo.tblInvoiceItemss ON dbo.tblInvoices.InvoiceNumber = dbo.tblInvoiceItemss.InvoiceNumber INNER JOIN
dbo.tblParts ON dbo.tblInvoiceItemss.BSLPartNo = dbo.tblParts.BSLPartNumber

Then this is dbo.lewis_bomfin

WITH PartsCTE

AS

(
SELECT NULL AS BOMID, Level = 0, BSLPartNumber, NULL AS MaterialID, CONVERT(varchar, '') AS ParentBSLPartNumber,
CONVERT(varchar, BSLPartNumber) AS Path,
NULL AS TopLevelBOMID,
NULL AS BottomLevelBOMID,
CONVERT(decimal(8,2), 1) AS TotalQuantity,
NULL As OpNo
FROM tblParts
WHERE (BSLPartNumber IS NULL OR tblParts.BSLPartNumber = BSLPartNumber)
AND ISNULL(BoughtOutComponent, 0) = 0

            UNION ALL

            SELECT b.BOMID, c.Level + 1 AS Level, b.BSLPartNumber, b.MaterialID, CONVERT(varchar, b.ParentBSLPartNumber) AS ParentBSLPartNumber, 
            CONVERT(varchar, CASE WHEN b.BSLPartNumber IS NULL THEN Path ELSE Path + '-' + b.BSLPartNumber END) AS Path,
            CASE WHEN c.Level = 0 THEN b.BOMID ELSE TopLevelBOMID END AS TopLevelBOMID,
            CASE WHEN b.MaterialID IS NULL THEN b.BOMID ELSE BottomLevelBOMID END AS BottomLevelBOMID,
            CONVERT(decimal(8,2), b.Quantity * TotalQuantity) AS TotalQuantity,
            CASE WHEN b.OpNo IS NOT NULL THEN b.OpNo ELSE c.OpNo END AS OpNo
            FROM tblPartsBOM b
            INNER JOIN PartsCTE c ON b.ParentBSLPartNumber = c.BSLPartNumber

)

SELECT Level,
topbom.ParentBSLPartnumber AS BSLPartNumber,
topbom.BSLPartNumber AS TopLevelPart,
topbom.OpNo AS TopLevelOpNo,
botbom.BSLPartNumber AS BottomLevelPart,
MaterialSize,
CASE WHEN p.BoughtOut = 1 THEN bom.BSLPartNumber ELSE NULL END AS BOP,
dbo.FinishedStock(topBOM.ParentBSLPartNumber) AS QuantityInStock,
dbo.LatestMaterialPrice(m.MaterialID) AS MaterialPrice,
dbo.LatestMaterialPricePer(m.MaterialID) AS MaterialPricePer,
dbo.LatestMaterialFreeIssue(m.MaterialID) AS FreeIssueMaterial,
dbo.LatestBOPPrice(bom.BSLPartNumber) AS BOPPrice,
dbo.LatestBOPPricePer(bom.BSLPartNumber) AS BOPPricePer,
bom.Per,
CASE WHEN bom.MaterialID IS NOT NULL THEN m.UnitOfMeasure ELSE 'part(s)' END AS Unit,
TotalQuantity AS TotalQuantityBOM
FROM PartsCTE
INNER JOIN tblPartsBOM topbom ON PartsCTE.TopLevelBOMID = topbom.BOMID
LEFT OUTER JOIN tblPartsBOM botbom ON PartsCTE.BottomLevelBOMID = botbom.BOMID
INNER JOIN tblPartsBOM bom ON PartsCTE.BOMID = bom.BOMID
LEFT OUTER JOIN tblParts p ON p.BSLPartNumber = PartsCTE.BSLPartNumber
LEFT OUTER JOIN tblMaterials m ON PartsCTE.MaterialID = m.MaterialID
WHERE (ISNULL(p.BoughtOutComponent, 0) = 1 OR m.MaterialID IS NOT NULL) --AND pb.DateCompleted IS NULL
AND dbo.FinishedStock(topbom.ParentBSLPartNumber) > 0

It times out within SQL, I think because these views aren't great and then referencing them is slowing the main view down.

From what you said earlier. Could these be incorporated to put in one view so it doesn't time out?

If you need more information or anything further from me, please let me know. Thanks again! You're a genius.

19

Okay, I have just quickly inlined the views and taken out stuff that does not seem to be needed:

WITH partscte
AS
-- Recursive CTEs can be inefficient but do not know enough about
-- system to suggest anything else.
(
	SELECT NULL AS BOMID,
        [Level] = 0,
        bslpartnumber,
        NULL AS MaterialID,
		-- varchar should always have a length!!!
		-- I have made 30, it should be the same length as the table column
        CONVERT(varchar(30), '') AS ParentBSLPartNumber,
        CONVERT(varchar(30), bslpartnumber) AS [Path],
        NULL AS TopLevelBOMID,
        NULL AS BottomLevelBOMID,
        CONVERT(decimal(8, 2), 1) AS TotalQuantity,
        NULL  AS OpNo
	FROM tblparts
	-- Do not see the point in the first condition as always true
	WHERE (bslpartnumber IS NULL OR bslpartnumber = bslpartnumber )
		AND ISNULL(boughtoutcomponent, 0) = 0

	UNION ALL

    SELECT b.bomid,
        c.[level] + 1,
        b.bslpartnumber,
        b.materialid,
        CONVERT(varchar(30), b.parentbslpartnumber),
        CONVERT(varchar(30), 
					CASE
						WHEN b.bslpartnumber IS NULL
						THEN c.[path]
						ELSE c.[path] + '-' + b.bslpartnumber
					END),
        CASE
            WHEN c.[level] = 0
			THEN b.bomid
            ELSE toplevelbomid
        END,
        CASE
            WHEN b.materialid IS NULL
			THEN b.bomid
            ELSE bottomlevelbomid
        END,
        CONVERT(decimal(8, 2), b.quantity * b.totalquantity),
        CASE
            WHEN b.opno IS NOT NULL
			THEN b.opno
            ELSE c.opno
        END 
    FROM tblpartsbom b
        INNER JOIN partscte c
                ON b.parentbslpartnumber = c.bslpartnumber
)
,CTE_bomfin
AS
(
	SELECT --[level],
		   topbom.parentbslpartnumber AS BSLPartNumber,
		   --topbom.bslpartnumber AS TopLevelPart,
		   --topbom.opno  AS TopLevelOpNo,
		   botbom.bslpartnumber AS BottomLevelPart,
		   --    materialsize,
		   --    CASE
		   --      WHEN p.boughtout = 1
				 --THEN bom.bslpartnumber
		   --      ELSE NULL
		   --    END AS BOP,
		   --    dbo.Finishedstock(topBOM.parentbslpartnumber) AS QuantityInStock,
		   dbo.Latestmaterialprice(m.materialid) AS MaterialPrice
			   --,dbo.Latestmaterialpriceper(m.materialid) AS MaterialPricePer,
			--   dbo.Latestmaterialfreeissue(m.materialid) AS FreeIssueMaterial,
			--   dbo.Latestbopprice(bom.bslpartnumber) AS BOPPrice,
			--   dbo.Latestboppriceper(bom.bslpartnumber) AS BOPPricePer,
			--   bom.per,
			--   CASE
			--     WHEN bom.materialid IS NOT NULL THEN m.unitofmeasure
			--     ELSE 'part(s)'
			--   END AS Unit,
			--   totalquantity AS TotalQuantityBOM
	FROM   partscte
		   INNER JOIN tblpartsbom topbom
				   ON partscte.toplevelbomid = topbom.bomid
		   --LEFT OUTER JOIN tblpartsbom botbom
		   --             ON partscte.bottomlevelbomid = botbom.bomid
		   --INNER JOIN tblpartsbom bom
		   --        ON partscte.bomid = bom.bomid
		   LEFT OUTER JOIN tblparts p
						ON p.bslpartnumber = partscte.bslpartnumber
		   LEFT OUTER JOIN tblmaterials m
						ON partscte.materialid = m.materialid
	WHERE  ( ISNULL(p.boughtoutcomponent, 0) = 1
			  OR m.materialid IS NOT NULL ) --AND pb.DateCompleted IS NULL
		   -- scalar functions can be very slow.
		   AND dbo.Finishedstock(topbom.parentbslpartnumber) > 0 
)
,CTE_invoicelineview
AS
(
	SELECT --C.customerid,
		   --C.customer,
		   I.invoicenumber,
		   II.invoiceitemid,
		   II.batchnumber,
		   I.invoicedate,
		   II.bslpartno
		   --,II.unitprice,
		   --II.qty,
		   --II.qty / II.unitofmeasure * II.unitprice AS Total_price,
		   --II.unitdescription,
		   --II.unitofmeasure
	FROM   --dbo.tblcustomers C INNER JOIN
		dbo.tblinvoices I
				   --ON C.customerid = I.customerid
		   INNER JOIN dbo.tblinvoiceitemss II
				   ON I.invoicenumber =
					  II.invoicenumber
		   INNER JOIN dbo.tblparts P
				   ON II.bslpartno = P.bslpartnumber
)
SELECT invoiceitemid,
       bslpartno,
       SUM(compsubconperpart) AS CompSubPerPart
FROM
(
	SELECT InvLine.invoicenumber,
		InvLine.invoiceitemid,
		InvLine.batchnumber,
		InvLine.invoicedate,
		InvLine.bslpartno,
		(
			SELECT TOP (1)
				SUM(ISNULL(
						CASE
						WHEN i.amountordered = 0
						THEN 0
						ELSE
							(i.unitprice * i.amountordered)
								/ i.quantityordered
						END
					, 0)) AS SubConPerOne
			FROM dbo.tblorderitems AS i
				INNER JOIN dbo.tblorders o
					ON i.orderid = o.orderid
			WHERE i.typeid = 4
				AND O.orderdate <= InvLine.invoicedate
				AND i.bslpartnumber = BOM.bottomlevelpart
			GROUP  BY i.batchnumber,
						i.orderid,
						i.bslpartnumber,
						o.orderdate
			ORDER BY O.orderdate DESC
		) AS CompSubconPerPart
	FROM   CTE_invoicelineview AS InvLine
			INNER JOIN CTE_bomfin AS BOM
					ON InvLine.bslpartno = BOM.bslpartnumber
	WHERE InvLine.invoicedate >= '20180101'
			AND BOM.bottomlevelpart IS NOT NULL
			AND BOM.materialprice = 0
) AS D
GROUP  BY invoiceitemid,
          bslpartno;

The remaining potential slow downs are the functions (dbo.Latestmaterialprice and dbo.Finishedstock) and the recursive CTE.
Scalar functions can be very slow. SQL2019 attempts to inline them but all other versions call them once per row.
I do not know enough about your system to comment on the recursive CTE.
How are the two functions defined?

20

Thank you again. I am getting an error on the first view 'Msg 102, level 15, state 1, line 97, incorrect syntax near ')'- It's the last line, but I can't get it to work without the error.