orders completed/total orders per month

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

SQL Server 2005 Forums

Transact-SQL (2005)

orders completed/total orders per month

Author

Topic

ms
Starting Member

11 Posts

Posted - 2008-10-27 : 11:05:22

I'm working on a query whick computes the % of orders completed per month. This is calculated by counting all the orders completed in a month divided by total no of orders rx for that month. For ex

Name Jan Feb Mar Apr May June
Warehouse 1 89% 56% 78% 42% 61% 90%
Warehouse 2 19% 46% 58% 49% 71% 92%

So on and so forth..I'm using case statements to work out the month and then trying to do the count based on it. What I'm not able to do is the calculation of percentage. Could anyone please help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:14:11

what all columns does you table have? what designates completed and uncompleted orders? does it have a status column?

ms
Starting Member

11 Posts

Posted - 2008-10-27 : 11:16:49

If the order column is populated then it is considered as orders complete. If it is not then it is not completed. So the total no of orders will be a total number of records regardsless of whether order column is null or not.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:28:46

show structure of the table with some sample data please.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 11:30:45

We want to see your table structure, the result won't help us to help you.

It is most likely that you'll need a pivot to make it work. Please show us all your columns.

ms
Starting Member

11 Posts

Posted - 2008-10-27 : 11:38:49

Name Order Item Qty Order_rx_date Order_complete_date Value Dispatch Date
Warehouse 1 XXXXX 1010 20 10/10/2008 15/10/2008 2000 18/10/2008
Warehouse 1 1898 15 10/10/2008 1545
Warehouse 1 1728 27 12/10/2008 1890
Warehouse 1 HHHHH 1900 45 11/10/2008 20/10/2008 2200 22/10/2008
Warehouse 1 YYYYY 900 11 11/10/2008 20/10/2008 1200 22/10/2008

Warehouse 2 1234 11 10/10/2008 1600
Warehouse 2 UUUUU 1234 11 10/10/2008 16/10/2008 1600 20/10/2008
Warehouse 2 1111 22 10/10/2008 1000
Warehouse 2 4444 60 10/10/2008 5600

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 00:37:57

[code]SELECT Name,DATEADD(mm,DATEDIFF(mm,0,Order_rx_date),0) AS YearDate,
SUM(CASE WHEN MONTH(Order_complete_date)=1 THEN 1 ELSE 0 END) *100.0/SUM(CASE WHEN MONTH(Order_rx_date)=1 THEN 1 ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(Order_complete_date)=2 THEN 1 ELSE 0 END) *100.0/SUM(CASE WHEN MONTH(Order_rx_date)=2 THEN 1 ELSE 0 END) AS Feb,SUM(CASE WHEN MONTH(Order_complete_date)=3 THEN 1 ELSE 0 END) *100.0/SUM(CASE WHEN MONTH(Order_rx_date)=3 THEN 1 ELSE 0 END) AS Mar
....
FROM Table
GROUP BY Name,DATEADD(mm,DATEDIFF(mm,0,Order_rx_date),0)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources