• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Counting orders and canceled orders only if not occurring in same month.

I am trying to count some orders/canceled orders where I don't want it to count if ordered/canceled during the same month (for example, someone ordered and canceled right away).

Due to some wierdness the way we handle products, orders and cancles can ocurr at the same time (same order#) or at different days or sequences (not same order#) and orders numbers are quite random and not necessarily unique.

I'm having a hard time getting started, I think, because a separate orders/cancels can offset each other.

Here some sample data:


Order#      Customer      Ordered            Canceled         Product
1            smith      2/28/2011                       Website
2            smith      9/1/2010        2/27/2011   Website
3            jones      1/10/2011                       Search
4            paul        2/1/2011                               Website
5            paul             10/1/2010              11/1/2010    Website
6            jones      2/1/2011                              Website
7            al            1/1/2011                                  Search
8            sam            3/10/2011      3/15/2011    Search
                        
month/yr      Product      orders      cancels      
9/10              website      1            0
10/10      website      1            0
11/10      website      0               1      
1/11               search      1               1      
2/11               website      2            0
(for 2/11, only 2 orders, jones and paul, no cancels due to smith order/cancel on 28th and 27th)

thanks
alan
0
avoorheis
Asked:
avoorheis
  • 3
  • 3
1 Solution
 
tim_csCommented:
I can't test this out right now but give this a try.
;WITH CTE1 AS {
SELECT
   Customer
   ,DatePart(Month,Ordered) orderMonth
   ,DatePart(Year,Ordered) OrderYear
   ,Product
   ,COUNT(1) Orders
FROM
   Table
GROUP BY
   Customer
   ,DatePart(Month,Ordered)
   ,DatePart(Year,Ordered)
   ,Product
},
CTE2 {
SELECT
   CUSTOMER
   ,DatePart(Month,Canceled) cancelMonth
   ,DatePart(Year,Canceled) cancelYear
   ,Product
   ,COUNT(1) Cancels
FROM
   Table
WHERE
	CANCELED IS NOT NULL
GROUP BY
   CUSTOMER
   ,DatePart(Month,Canceled)
   ,DatePart(Year,Canceled)
   ,Product

}


SELECT
   Month
   ,Year
   ,Product
   ,SUM(CASE WHEN A.Orders > B.canceled THEN A.Orders = B.Cancels ELSE 0 END) Orders
   ,SUM(CASE WHEN B.Canceled > A.Orders THEN B.Cancels - A.Orders ELSE 0 END) Cancels
FROM
   CTE1 A
   INNER JOIN CTE2 B
      ON A.Customer = B.Customer
      AND A.Month = B.Month
      AND A.Year = B.Year
GROUP BY
   Month
   ,Year
   ,Product

Open in new window

0
 
avoorheisAuthor Commented:
thanks tim,
I hope to be able to try out tomorrow.
0
 
tim_csCommented:
I was just looking at this and noticed I put in {} instead of () so that will have to be replaced in my code.  
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
avoorheisAuthor Commented:
ok, trying it out. I think I understand, but:
,SUM(CASE WHEN A.Orders > B.canceled THEN A.Orders = B.Cancels ELSE 0 END) Orders
should be
,SUM(CASE WHEN A.Orders > B.cancels THEN A.Orders - B.Cancels ELSE 0 END) Orders
right? (just comparing the counts, not dates)

Also, the last select, Month, Year, Product need to be qualified with CTE1 or CTE2, right? (this part is confusing me)

thanks
0
 
tim_csCommented:
I was able to test this code out so hopefully it's what you're looking for.  
;WITH CTE1 AS ( 
SELECT 
   Customer 
   ,DatePart(Month,Ordered) orderMonth 
   ,DatePart(Year,Ordered) OrderYear 
   ,Product 
   ,COUNT(1) Orders 
FROM 
   TableName
GROUP BY 
   Customer 
   ,DatePart(Month,Ordered) 
   ,DatePart(Year,Ordered) 
   ,Product 
),
CTE2 AS (
SELECT 
   CUSTOMER 
   ,DatePart(Month,Canceled) cancelMonth 
   ,DatePart(Year,Canceled) cancelYear 
   ,Product 
   ,COUNT(1) Cancels 
FROM 
   TableName
WHERE 
        CANCELED IS NOT NULL 
GROUP BY 
   CUSTOMER 
   ,DatePart(Month,Canceled) 
   ,DatePart(Year,Canceled) 
   ,Product 
)
-- 
--SELECT * FROM 
--   CTE1 A 
--   LEFT JOIN CTE2 B 
--      ON A.Customer = B.Customer 
--      AND A.OrderMonth = B.CancelMonth 
--      AND A.OrderYear = B.CancelYear 
 
SELECT 
   ISNULL(A.OrderMonth,B.CancelMonth) [Month]
   ,ISNULL(A.OrderYear,B.CancelYear) [Year]
   ,ISNULL(A.Product,B.Product) Product 
   ,SUM(CASE WHEN A.Orders > ISNULL(B.cancels,0) THEN A.Orders - ISNULL(B.cancels,0) ELSE 0 END) Orders 
   ,SUM(CASE WHEN B.Cancels > ISNULL(A.Orders,0) THEN B.Cancels - ISNULL(A.Orders,0) ELSE 0 END) Cancels 
FROM 
   CTE1 A 
   FULL OUTER JOIN CTE2 B 
      ON A.Customer = B.Customer 
      AND A.OrderMonth = B.CancelMonth 
      AND A.OrderYear = B.CancelYear 
GROUP BY 
  ISNULL(A.OrderMonth,B.CancelMonth)
   ,ISNULL(A.OrderYear,B.CancelYear) 
   ,ISNULL(A.Product,B.Product)
ORDER BY
	ISNULL(A.OrderYear,B.CancelYear)
	,ISNULL(A.OrderMonth,B.CancelMonth)
	,ISNULL(A.Product,B.Product)

Open in new window

0
 
avoorheisAuthor Commented:
thanks Tim,
helped a lot, especially since I've not used with.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now