Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 7th, 2005, 03:59 PM
Authorized User
 
Join Date: Oct 2004
Location: Eastpointe, MI, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to cuzintone Send a message via Yahoo to cuzintone
Default Calculation dillemma....

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









__________________
---------------------------
\"I finally understand the true value of time, for it is the bonds between us that give time its meaning.\"
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation in access ashik112 Access 1 February 26th, 2007 08:38 AM
Time Calculation surendran MySQL 1 June 24th, 2006 01:38 PM
Time calculation rdfernandez Access VBA 6 March 24th, 2005 11:51 AM
How to do calculation between 2 tables hwsc SQL Language 2 January 13th, 2005 09:29 PM
Calculation Grantm Access 3 February 16th, 2004 10:14 AM



All times are GMT -4. The time now is 05:40 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.