Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count for Order Expand / Collapse
Author
Message
Posted Tuesday, June 15, 2010 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 17, 2010 1:09 AM
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.
Post #937914
Posted Wednesday, June 16, 2010 1:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #937967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse