I have an application that needs to create invoices on a daily basis to
multiple clients based on orders shipped that day. This is easy to do on th
e
front-end. But how can I do this on the back end in SQL Server.
I want to sort orders by Client ID and put all orders belonging to one
customer on one invoice. When customer id changes, I change the Invoice ID.
Is this possible in SQL?
WRYou're already doing it the right way. Why does the database need to do the
iteration? Is sa or dbo going to be reviewing / paying the invoices?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
news:2D4504ED-3016-4D04-8D77-E9538284A2F0@.microsoft.com...
>I have an application that needs to create invoices on a daily basis to
> multiple clients based on orders shipped that day. This is easy to do on
> the
> front-end. But how can I do this on the back end in SQL Server.
> I want to sort orders by Client ID and put all orders belonging to one
> customer on one invoice. When customer id changes, I change the Invoice
> ID.
> Is this possible in SQL?
> WR
>|||This sounds like an INSERT to me. For example:
INSERT INTO Invoices (...)
FROM Orders
WHERE ...
GROUP BY client
INSERT INTO InvoiceItems (...)
FROM Orders
WHERE ...
Iteration shouldn't come into it.
If you need more help please post DDL and sample data (CREATE and
INSERT statements) and show your required end result.
David Portas
SQL Server MVP
--|||You're already doing it the right way. Why does the database need to do the
iteration? --> We need a way to assign invoice numbers to orders without
involving someone having to open the application and clicking on a button.
We want this part to be completely hands off.
Is sa or dbo going to be reviewing / paying the invoices? --> I have no
comment to this remark.
WR
"Aaron [SQL Server MVP]" wrote:
> You're already doing it the right way. Why does the database need to do t
he
> iteration? Is sa or dbo going to be reviewing / paying the invoices?
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "WhiskyRomeo" <WhiskyRomeo@.discussions.microsoft.com> wrote in message
> news:2D4504ED-3016-4D04-8D77-E9538284A2F0@.microsoft.com...
>
>|||> You're already doing it the right way. Why does the database need to do
> the
> iteration? --> We need a way to assign invoice numbers to orders without
> involving someone having to open the application and clicking on a button.
> We want this part to be completely hands off.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||For each client_id, i need to insert a record into the tblInvoice and update
all orders records with that Client Id to the Invoice_ID (identity column).
Here is the Invoice table ddl
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblCustomerOrder_tblInvoice]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblCustomerOrder] DROP CONSTRAINT
FK_tblCustomerOrder_tblInvoice
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblPayInvoice_tblInvoice]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPayInvoice] DROP CONSTRAINT FK_tblPayInvoice_tblInvoice
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblInvoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInvoice]
GO
CREATE TABLE [dbo].[tblInvoice] (
[Client_ID] [int] NOT NULL ,
[Invoice_ID] [int] IDENTITY (1, 1) NOT NULL ,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GenDT] [smalldatetime] NOT NULL ,
[DueDT] [smalldatetime] NOT NULL ,
[AmtDue] [money] NOT NULL ,
[PaidFlag] [bit] NOT NULL
) ON [PRIMARY]
GO
Here is the CustomerOrder table ddl:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblCustomerOrderItem_tblCustomerOrde
r]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblCustomerOrderItem] DROP CONSTRAINT
FK_tblCustomerOrderItem_tblCustomerOrder
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCustomerOrder]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblCustomerOrder]
GO
CREATE TABLE [dbo].[tblCustomerOrder] (
[OrderTrkNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BatchNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Client_ID] [int] NULL ,
[Invoice_ID] [int] NULL ,
[ClientCCSAcctNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipTrkNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientOrderNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[NameFirst] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameLast] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProdCnt] [tinyint] NULL ,
[ShipBoxCnt] [tinyint] NULL ,
[Shipper] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipService] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipFee] [money] NULL ,
[ShipWeight] [decimal](6, 2) NULL ,
[ShipDateEst] [smalldatetime] NULL ,
[ShipDate] [datetime] NULL ,
[ReplacedByOrder] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USPSZone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADone] [bit] NULL ,
[AHold] [bit] NULL ,
[QAPass] [bit] NULL ,
[QAFail] [bit] NULL ,
[RDone] [bit] NULL ,
[RHold] [bit] NULL ,
[Ship] [bit] NULL ,
[ShipTrkNoFlg] [bit] NULL ,
[Inventoried] [bit] NULL
) ON [PRIMARY]
GO
"David Portas" wrote:
> This sounds like an INSERT to me. For example:
> INSERT INTO Invoices (...)
> FROM Orders
> WHERE ...
> GROUP BY client
> INSERT INTO InvoiceItems (...)
> FROM Orders
> WHERE ...
> Iteration shouldn't come into it.
> If you need more help please post DDL and sample data (CREATE and
> INSERT statements) and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>|||Where are the keys? I'll take a guess that ordertrkno is the key of the
Order table just because it's the only non-nullable column (Is that
sensible? What is the meaning of an order that has only an ordertrkno
but no other information at all? How can you have an order but not know
the date the order was placed or which customer it belongs to?, etc,
etc)
Here's my guess for the INSERT:
INSERT INTO tblInvoice
(client_id, invoiceno, gendt, duedt, amtdue, paidflag)
SELECT O.client_id, COUNT(*)+
(SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
FROM tblInvoice),
O.orderdate,
DATEADD(DAY,30,O.orderdate),
(SELECT SUM(O.shipfee)
FROM tblCustomerOrder
WHERE client_id = O.client_id), 0
FROM tblCustomerOrder AS O
JOIN tblCustomerOrder AS P
ON O.ordertrkno >= P.ordertrkno
WHERE O.orderdate = '20050415' /* Today's orders */
AND P.orderdate = '20050415'
GROUP BY O.client_id, O.orderdate
The Invoice_ID in tblCustomerOrder is perhaps redundant but try this:
UPDATE tblCustomerOrder
SET invoice_id =
(SELECT invoice_id
FROM tblInvoice
WHERE client_id = tblCustomerOrder.client_id
AND gendt = tblCustomerOrder.orderdate)
WHERE orderdate = '20050415'
(Both untested)
Hope this helps.
David Portas
SQL Server MVP
--|||Oops. Let's try that INSERT a different way:
INSERT INTO tblInvoice
(client_id, invoiceno, gendt, duedt, amtdue, paidflag)
SELECT O.client_id,
(SELECT COUNT(DISTINCT client_id)
FROM tblCustomerOrder
WHERE orderdate = O.orderdate
AND client_id <= O.client_id)+
(SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
FROM tblInvoice),
O.orderdate,
DATEADD(DAY,30,O.orderdate),
SUM(O.shipfee), 0
FROM tblCustomerOrder AS O
WHERE O.orderdate = '20050415' /* Today's orders */
GROUP BY O.client_id, O.orderdate
David Portas
SQL Server MVP
--|||The primary key of tblInvoice is Invoice_ID (identity) it has one-many
relationship with tblOrder. In the ddl version I gave you Client_ID in
tblInvoice was inappropriate.
In regard to tblCustomerOrder, you are correct, OrderTrkNo is the PK. Sorry
about the confusion with all the nullable columns. The order information
comes from several outside sources and we are just looking at the quality an
d
consisentency of this data before locking down what can be null and what
can't. But you are right Client_ID, OrderDate, FirstName, LastName,
Address1, City, State, Zip, Country, etc cannot be null.
Finally, I did not give you all the information to caculcate the total on
the invoice. There is actually a tblOrderItem a child of tblOrder, that
contains the Price and Quantity for each line item.
I just needed to be pointed in the right direction. I think this might do
it. I appreciate the time you took on this.
WR
"David Portas" wrote:
> Where are the keys? I'll take a guess that ordertrkno is the key of the
> Order table just because it's the only non-nullable column (Is that
> sensible? What is the meaning of an order that has only an ordertrkno
> but no other information at all? How can you have an order but not know
> the date the order was placed or which customer it belongs to?, etc,
> etc)
> Here's my guess for the INSERT:
> INSERT INTO tblInvoice
> (client_id, invoiceno, gendt, duedt, amtdue, paidflag)
> SELECT O.client_id, COUNT(*)+
> (SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
> FROM tblInvoice),
> O.orderdate,
> DATEADD(DAY,30,O.orderdate),
> (SELECT SUM(O.shipfee)
> FROM tblCustomerOrder
> WHERE client_id = O.client_id), 0
> FROM tblCustomerOrder AS O
> JOIN tblCustomerOrder AS P
> ON O.ordertrkno >= P.ordertrkno
> WHERE O.orderdate = '20050415' /* Today's orders */
> AND P.orderdate = '20050415'
> GROUP BY O.client_id, O.orderdate
> The Invoice_ID in tblCustomerOrder is perhaps redundant but try this:
> UPDATE tblCustomerOrder
> SET invoice_id =
> (SELECT invoice_id
> FROM tblInvoice
> WHERE client_id = tblCustomerOrder.client_id
> AND gendt = tblCustomerOrder.orderdate)
> WHERE orderdate = '20050415'
> (Both untested)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||I have really messed you up on the Orderdate. These orders are orders from
clients who have taken the order from the customer. The orderdate is their
order date.
We process all Orders from clients in Batches. The BatchDT is the really
our orderdate. This is a system that make plaques for photography studios.
The photographere has taken the picture and his customer placed an order. I
f
the order is a plaque, they send us the photo, engraving info, etc, basicall
y
most everything you see in tblCustomerOrder and what you didn't see in the
order item table. We process anywhere from 100 to a 1,000 plaques a day.
The way we will tell that an order is ready to be invoiced is the Invoice_ID
is null and the ShipDate = Today.
"David Portas" wrote:
> Oops. Let's try that INSERT a different way:
> INSERT INTO tblInvoice
> (client_id, invoiceno, gendt, duedt, amtdue, paidflag)
> SELECT O.client_id,
> (SELECT COUNT(DISTINCT client_id)
> FROM tblCustomerOrder
> WHERE orderdate = O.orderdate
> AND client_id <= O.client_id)+
> (SELECT CAST(MAX(invoiceno) AS INTEGER) /* ? is char */
> FROM tblInvoice),
> O.orderdate,
> DATEADD(DAY,30,O.orderdate),
> SUM(O.shipfee), 0
> FROM tblCustomerOrder AS O
> WHERE O.orderdate = '20050415' /* Today's orders */
> GROUP BY O.client_id, O.orderdate
> --
> David Portas
> SQL Server MVP
> --
>
