June 15, 2010 at 9:39 pm
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.
June 16, 2010 at 1:20 am
Please post DDL, sample data and the code you have so far.
If you do you will probably get better responses.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply