Here's my dillemma (sp?). I am assisting in the development of a check writing program for a company. I'm responsible for the database. The way the company operates is that they pay from invoices sent to them by their vendors (or suppliers). I normalized the invoices so you have a invoice header and invoice details. The I have to generate a temporary batch table that combines the invoiceid, supplierid, due date, and amount to pay (for the ASP.NET programmer...I personally think that table is unnecessary, but I digress...anyways I need to calculate the total of the invoices and put that total in a field and add to the batch table (for informational purposes).
So far, what I have is the following procedure...which creates the batch table:
{CREATE} procedure procSelectInvoices
@daytolook datetime = '1-1-2000',
@supid bigint = 0
/* declare @daytolook datetime
select @daytolook = convert(datetime, '1-1-2005', 102) */
as
begin
set nocount on
if exists (select table_name from information_schema.tables where table_name = 'batchtable')
drop table batchtable
CREATE TABLE [BatchTable] (
[supplierid] [bigint] NOT NULL ,
[invoiceid] [bigint] NOT NULL ,
[duedate] [datetime] NOT NULL ,
[amounttopay] [decimal](19, 4) NOT NULL ,
[originalamount] [decimal] (19, 4) NULL,
[paid] [bit] NOT NULL ,
CONSTRAINT [PK_BatchTable] PRIMARY KEY CLUSTERED
(
[supplierid],
[invoiceid]
) ON [PRIMARY]
) ON [PRIMARY]
/* Populate the data with the query, modified from the original procSelectInvoices by date */
if (@supid <> 0) begin
begin transaction
insert into batchtable (supplierid, invoiceid, duedate, amounttopay, paid)
SELECT distinct dbo.suppliers.supplierid,
dbo.invoices.InvoiceID, dbo.invoices.DueDate,
dbo.invoices.AmountToPay, dbo.invoices.Paid
FROM dbo.invoices INNER JOIN
dbo.invoicedetail ON dbo.invoices.InvoiceID = dbo.invoicedetail.InvoiceID INNER JOIN
dbo.LineItems ON dbo.invoicedetail.LineItemID = dbo.LineItems.LineItemID INNER JOIN
dbo.suppliers ON dbo.invoices.SupplierID = dbo.suppliers.SupplierID
WHERE (dbo.invoices.supplierid = @supid) and (dbo.invoices.Paid = 0)
commit transaction
end else begin
begin transaction
insert into batchtable (supplierid, invoiceid, duedate, amounttopay, paid)
SELECT distinct dbo.suppliers.supplierid,
dbo.invoices.InvoiceID, dbo.invoices.DueDate,
dbo.invoices.AmountToPay, dbo.invoices.Paid
FROM dbo.invoices INNER JOIN
dbo.invoicedetail ON dbo.invoices.InvoiceID = dbo.invoicedetail.InvoiceID INNER JOIN
dbo.LineItems ON dbo.invoicedetail.LineItemID = dbo.LineItems.LineItemID INNER JOIN
dbo.suppliers ON dbo.invoices.SupplierID = dbo.suppliers.SupplierID
WHERE (dbo.invoices.DueDate <= @daytolook) and (dbo.invoices.Paid = 0)
commit transaction
end
end
I'll probably end up just doing a truncate on the table instead of a drop/create pair, but the drop/create was for the ASP.NET programmer who insisted it be done that way.
Since the ASP.NET programmer (
VB.NET specifically) was going to work off the BatchTable, I wrote this piece of code:
declare @invamt decimal (19, 4)
SELECT dbo.invoicedetail.Quantity * dbo.invoicedetail.UnitPrice AS LineTotal
FROM dbo.BatchTable INNER JOIN
dbo.invoicedetail ON dbo.BatchTable.invoiceid = dbo.invoicedetail.InvoiceID INNER JOIN
dbo.invoices ON dbo.invoicedetail.InvoiceID = dbo.invoices.InvoiceID
ORDER BY dbo.BatchTable.invoiceid, dbo.invoicedetail.Quantity * dbo.invoicedetail.UnitPrice
compute sum (dbo.invoicedetail.Quantity * dbo.invoicedetail.UnitPrice) by dbo.BatchTable.invoiceid
However, I found that doing an update with this code would not work.
So I tried this code instead:
SELECT sum(dbo.invoicedetail.Quantity * dbo.invoicedetail.UnitPrice) as Total
FROM dbo.invoicedetail INNER JOIN
dbo.invoices ON dbo.invoicedetail.InvoiceID = dbo.invoices.InvoiceID
But it gives me the sum of all the invoices, useful, but not what I needed, so of course I added a group by invoiceid (invoices.invoiceid), but now I can't update with this result set.
All I want to do is total each invoice and put in the InvoiceAmount field. Below are the affected tables for this part of the code so that you can have the schema...any assistance would be greatly appreciated.
Thanks in advance,
Tony
CREATE TABLE [invoices] (
[InvoiceID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CheckID] [bigint] NULL ,
[SupplierID] [bigint] NOT NULL CONSTRAINT [DF__invoices__Suppli__0519C6AF] DEFAULT ('0'),
[InvoiceNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__invoices__Invoic__0425A276] DEFAULT (null),
[PONumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[DueDate] [datetime] NULL ,
[InvoiceAmount] [decimal](19, 4) NULL ,
[AmountToPay] [decimal](19, 4) NOT NULL ,
[Paid] [bit] NOT NULL CONSTRAINT [DF_invoices_Paid] DEFAULT (0),
[Tax] [decimal](19, 4) NOT NULL CONSTRAINT [DF_invoices_Tax] DEFAULT (0),
[Freight] [decimal](19, 4) NULL ,
[ShipingMethodID] [bigint] NULL ,
[TrackingNumber] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field1] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field2] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field3] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field4] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field5] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__invoices__03317E3D] PRIMARY KEY CLUSTERED
(
[InvoiceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_invoices_Checks] FOREIGN KEY
(
[CheckID]
) REFERENCES [Checks] (
[CheckID]
)
) ON [PRIMARY]
GO
CREATE TABLE [invoicedetail] (
[InvoiceDetailID] [bigint] IDENTITY (1, 1) NOT NULL ,
[InvoiceID] [bigint] NOT NULL ,
[LineItemID] [bigint] NOT NULL ,
[Quantity] [bigint] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
CONSTRAINT [PK__invoicedetail__014935CB] PRIMARY KEY CLUSTERED
(
[InvoiceDetailID]
) ON [PRIMARY] ,
CONSTRAINT [FK_invoicedetail_invoices] FOREIGN KEY
(
[InvoiceID]
) REFERENCES [invoices] (
[InvoiceID]
),
CONSTRAINT [FK_invoicedetail_LineItems] FOREIGN KEY
(
[LineItemID]
) REFERENCES [LineItems] (
[LineItemID]
)
) ON [PRIMARY]
GO
CREATE TABLE [LineItems] (
[LineItemID] [bigint] IDENTITY (1, 1) NOT NULL ,
[SupplierID] [bigint] NULL ,
[ItemNumber] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UnitPrice] [decimal](19, 4) NOT NULL ,
[UnitofMeasure] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FileURL] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LineItemTypeID] [int] NOT NULL CONSTRAINT [DF_LineItems_LineItemTypeID] DEFAULT (1),
PRIMARY KEY CLUSTERED
(
[LineItemID]
) ON [PRIMARY] ,
CONSTRAINT [FK_LineItems_LineItemTypes] FOREIGN KEY
(
[LineItemTypeID]
) REFERENCES [LineItemTypes] (
[LineItemTypeID]
)
) ON [PRIMARY]
GO
CREATE TABLE [LineItemTypes] (
[LineItemTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[LineItemType] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_LineItemTypes] PRIMARY KEY CLUSTERED
(
[LineItemTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Checks] (
[CheckID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CheckNumber] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckDate] [datetime] NULL ,
[PaymentID] [bigint] NULL ,
[VoidStatus] [bit] NULL CONSTRAINT [DF_CheckWriter_VoidStatus] DEFAULT (0),
CONSTRAINT [PK_CheckWriter] PRIMARY KEY CLUSTERED
(
[CheckID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CheckWriter_payments] FOREIGN KEY
(
[PaymentID]
) REFERENCES [payments] (
[PaymentID]
)
) ON [PRIMARY]
GO
CREATE TABLE [suppliers] (
[SupplierID] [bigint] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Suppl__2D27B809] DEFAULT (''),
[ContactName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Conta__2E1BDC42] DEFAULT (''),
[ContactTitle] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Conta__2F10007B] DEFAULT (''),
[Address1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Addre__300424B4] DEFAULT (''),
[Address2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__suppliers__Addre__30F848ED] DEFAULT (''),
[City] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__City__31EC6D26] DEFAULT (''),
[StateID] [bigint] NOT NULL ,
[PostalCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Posta__33D4B598] DEFAULT (''),
[CountryRegion] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Count__34C8D9D1] DEFAULT (''),
[PhoneNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__Phone__35BCFE0A] DEFAULT (''),
[FaxNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__suppliers__FaxNu__36B12243] DEFAULT (''),
[RemitAddress1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RemitAddress2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RemitCity] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RemitStateID] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RemitPostalCode] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK__suppliers__2C3393D0] PRIMARY KEY CLUSTERED
(
[SupplierID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Calculation dillemma....
