SQLTeam.com | Weblogs | Forums

SQL Script Help Please - Too Hard!

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.

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

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

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

1 Like

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?

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

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

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.

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.

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