ouput based on order date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

ouput based on order date

Author

Topic

faqsqlserver
Starting Member

4 Posts

Posted - 2008-06-02 : 20:23:20

Hi all,
I have 2 tables, Order and Payment

Order
Order_Number Cust_Number Order_Date
10001 C1 23-May
10002 C2 24-May
10003 C1 25-May
10004 C3 28-May

Payment
Order_Number|Card_Type|Card_#|Merchant
10001 Gift Card 1234 null
10001 Gift Card 1235 null
10001 Gift Card 1236 null
10001 Credit Card xxxx prd
10002 Credit Card xxxx prd
10003 Credit Card xxxx prd
10004 Credit Card xxxx prod

I have to populate th below table to track last gift card used for each cust_number.
1. last gift card used for each customer, each order
2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order.
3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod
4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order.

[b]Last_Gift_Card
Cust_No|Order_number|card_number|last_gift_card

C1 10001 1243 1234
C1 10001 1235 1235
C1 10001 1236 1236
C1 10001 xxxx 1236
C2 10002 xxxx 99
C1 10003 xxxx 1236
C3 10004 xxxx 88

Please help me with the sql.
I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 00:01:04

May be this:-

SELECT o.CustNumber,o.OrderNumber,p.CardNumber,
CASE WHEN p.CardType='Gift Card' THEN p.CardNumber
     WHEN p.CardType='Credit Card' AND p.Merchant='prd'THEN COALESCE(tmp.MaxCard,99)
     WHEN p.CardType='Credit Card' AND p.Merchant='prod'THEN COALESCE(tmp.MaxCard,88)
END AS LastGiftCard
FROM Order o
INNER JOIN Payment p
ON p.OrderNumber=o.OrderNumber
LEFT JOIN (SELECT o1.CustNumber,o1.OrderNumber,MAX(p1.CardNumber) AS MaxCard
            FROM Order o1
            INNER JOIN Payment p1
            ON p1.OrderNumber=o1.OrderNumber
            WHERE p1.CardType='Gift Card'
            GROUP BY o1.CustNumber,o1.OrderNumber)tmp
ON tmp.CustNumber = o.CustNumber 
AND tmp.OrderNumber=o.OrderNumber

faqsqlserver
Starting Member

4 Posts

Posted - 2008-06-03 : 12:37:08

Thank you visakh16.

This logic works perfectly for all customers, orders except for Customer "C1".

In Order number 10003, he uses only one credit card.
Per requirement # 4, which I did a typo
4. In a new order, a past customer only uses a ~~gift card~~ credit card, then last gift card used is gift card from his previous order.

So for order number 10003, the lastgiftcard is a giftcard from his previous order 10001.

How to update the lastgiftcard for order number 10003 by getting the giftcard from order number 10001. This can happen to multiple customers. So we have to keep track the giftcard from the latest order for each cutomer?

Thank you once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 12:48:20

Are you using SQL 2005?

faqsqlserver
Starting Member

4 Posts

Posted - 2008-06-03 : 14:01:44

Yes.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 14:28:07

May be this:-

SELECT der.CustNumber,der.OrderNumber,der.CardNumber,
CASE WHEN der.CardType='Gift Card' THEN der.CardNumber
    WHEN der.OrderCount>1 AND der.CardType='Credit Card' THEN der.MaxCard
    WHEN der.OrderCount=1 AND der.CardType='Credit Card' AND der.Merchant='prd' THEN 99
    WHEN der.OrderCount=1 AND der.CardType='Credit Card' AND der.Merchant='prod' THEN 88
    WHEN der.CustCount >1 AND der.OrderCount=1 AND der.CardType='Gift Card' THEN der.PrevCard    
END AS LastGiftCard
FROM
(
SELECT o.CustNumber,o.OrderNumber,p.CardType,p.CardNumber,p.Merchant,tmp.MaxCard,tmp1.PrevCard,
 ROW_NUMBER() OVER( PARTITION BY o.CustNumber,o.OrderNumber ORDER BY o.OrderDate) AS OrderCount,
 ROW_NUMBER() OVER( PARTITION BY o.CustNumber ORDER BY o.OrderDate) AS CustCount    
FROM Order o
INNER JOIN Payment p
ON p.OrderNumber=o.OrderNumber
OUTER APPLY (SELECT MAX(Card_Number) AS MaxCard
             FROM Order o1
             INNER JOIN Payment p1
             ON p1.OrderNumber=o1.OrderNumber
             WHERE o1.OrderNumber=o.OrderNumber
             AND  o1.CustNumber=o.CustNumber
             AND p1.Card_Type='Gift Card')tmp
OUTER APPLY (SELECT TOP 1 Card_Number AS PrevCard
             FROM Order o1
             INNER JOIN Payment p1
             ON p1.OrderNumber=o1.OrderNumber
             WHERE o1.OrderNumber=o.OrderNumber
             AND  o1.CustNumber=o.CustNumber
             AND p1.Card_Type='Gift Card'
             AND o1.Order_Date < o.Order_Date
             ORDER BY o1.Order_Date DESC)tmp1
)der

faqsqlserver
Starting Member

4 Posts

Posted - 2008-06-03 : 18:01:21

Thank you visakh16 for the update.
Now it makes easy using APPLY.
I was not aware of this feature before your reply.

Thanks. Have a good day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources