Daily Invoicing of Orders - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Daily Invoicing of Orders

Author

Topic

jasonconner
Starting Member

5 Posts

Posted - 2008-06-10 : 13:23:09

I am trying to Invoice all of the records in my 'Orders' table. After each of the records has been invoiced I would like SQL to flag that record as having been completed so that when I run my query the next day it will ignore those having been completed already. Any feedback would be greatly appreciated.

This is the query I wrote to invoice one Order at a time by specifying each Order_Num seperately. As you can tell...I'm a n00b. Thanks all.

select convert(varchar, getdate(), 107) as Date
select order_num as 'Invoice No.' from orders
where order_num = '20009'
select c.cust_name as Customer, c.cust_address as 'Street Address',
c.cust_city as City, c.cust_state as State, c.cust_zip as 'Zip Code'
from customers as c, orders as o
where c.cust_id = o.cust_id and order_num = '20009'
select oi.order_item as 'Line Item', oi.quantity as QTY, p.prod_name as 'Product Name',
oi.item_price as 'Sale Price', oi.quantity*oi.item_price as Total
from orderitems as oi, products as p
where oi.prod_id = p.prod_id and order_num = '20009'
order by oi.order_item
select sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total
from orderitems
where order_num = '20009'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:30:01

You put the results of these query in some table?

jasonconner
Starting Member

5 Posts

Posted - 2008-06-10 : 13:31:52

No, I haven't created a table to store an Invoice. If that is the next step I can do so, however I would like to also have the query print each Invoice. Is that something SQL should do or should the front end take care of that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:33:23

quote:
Originally posted by jasonconner

No, I haven't created a table to store an Invoice. If that is the next step I can do so, however I would like to also have the query print each Invoice. Is that something SQL should do or should the front end take care of that?

So you require the aggregated fields (total,subtotal,..) for display only and dont reuire them for future comparative or cumulative analysis?

jasonconner
Starting Member

5 Posts

Posted - 2008-06-10 : 13:35:52

I suppose at some point in the future I will want to see how much a customer has spent over a period of time. Or, revenue over a period of time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:37:39

quote:
Originally posted by jasonconner

I suppose at some point in the future I will want to see how much a customer has spent over a period of time. Or, revenue over a period of time.

Then you surely need to keep them stored in a table rather than calaculating on the fly. And you can compare to this table each time and take only those records which dont exist in table yet (not invoiced) for processing.

jasonconner
Starting Member

5 Posts

Posted - 2008-06-10 : 13:46:48

Ok I will create an Invoice table now and see if I can figure out how to insert the order data into this table. I will post back after I figure or don't figure it out. Thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:48:14

quote:
Originally posted by jasonconner

Ok I will create an Invoice table now and see if I can figure out how to insert the order data into this table. I will post back after I figure or don't figure it out. Thx.

Cool

. Let us know if you got stuck up with something

jasonconner
Starting Member

5 Posts

Posted - 2008-06-10 : 14:01:22

Ok, sorry I'm back so soon. I am creating the table and have this so far:

Invoices
---------
invoices_pk nvarchar(50)
date timestamp
invoice_num nvarchar(10) <---was thinking this should be a FK linked to the orders_pk
subtotal money
tax money
total money

sooooo, how do insert order_item, quantity, product_name? each order could, and most likely will, have different amounts of products ordered.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 14:07:18

[code]Insert into Invoices (date,invoice_num,subtotal,tax,total)
select GETDATE(),order_num,sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total
from orderitems
group by order_num[/code]
i'm assuming pk column is of type identity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources