SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count for Order


Count for Order

Author
Message
azwa
azwa
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
Tbl.Order

OrderID | OrderDate | RequiredDate | OrderStatus
--------------------------------------------------


Tbl.OrderDate

OrderID | NewRequiredDate
-------------------------


OUTPUT:

TotalOrder | CompletedOrderBeforeRequiredMonth|CompletedWithinDueMonth |
               |                                                  |--------------------------|
                |                                                 | Before Or On|After Required|
                |                                                 | RequiredDate| Date           |
-----------------------------------------------------------------------------


Total Order = All the total order not include the order that had been cancelled. But if the order is already completed before the month of
required month (month of requiredDate), the total order should deduct this order that hv been completed before the required month.
(Ex: Total Order that must be completed in May is 23.Then 2 had been cancelled and another 5 hd been completed during April
So the total order in May = 23-2-5
= 16)

CompletedOrderBeforeRequiredMonth = Order that hd been completed before reach its month of required date or new required date.

CompletedWithinDueMonth : Before or on required date = Order is completed within the month that is before reach its required date or on required date.

CompletedWithinDueMonth : After requred date = Order is completed after the required date but still in the same month



*Required date mentioned above is refer to either RequiredDate in Tbl.Order or NewRequiredDate in Tbl.OrderDate
*Status of order would be Completed,In progress or Cancelled.

**I don't need update on required date since i need both RequiredDate and NewRequiredDate.
If there is NewRequiredDate available, the calculation would be based on NewRequiredDate
else if the NewRequiredDate is null, the RequiredDate will be taken.
My major problem is how to calculate the Total Order.
I am doing Stored Proc for repotring services.
I already do outer join for both table but my calculation is wrong.
Any idea would be great.I am new in SQL.I am using SQL Server 2005.
Dave Ballantyne
Dave Ballantyne
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3892 Visits: 8370
Please post DDL, sample data and the code you have so far.
If you do you will probably get better responses.



Clear Sky SQL
My Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search