Count MAX(BillDate) WHERE PostDate = BillDate - 1

[egghi]

Hi,

I am not sure if this can be done, but I sincerely hope someone can help me out. I have a table with data like this:

counter salesrep PostDate BillDate
-001234 Adam 2006-9-23 00:00:00 2006-9-24 00:00:00
-001234 Alice 2006-9-25 00:00:00 2006-9-26 00:00:00
-001256 Danny 2006-9-23 00:00:00 2006-9-24 00:00:00
-001345 Danny 2006-9-25 00:00:00 2006-9-25 00:00:00

Counter is the unique ID for each order, and PostDate is the date the order is posted in the system. An order can be posted and billed multiple time since customer sometimes ask to change the order.

I need to write a query that counts the number of unique order posted and billed successfully by each salesrep by date. So, for example, if a sales rep posted an order today, and the order was successfully billed the next day, the sales rep gets 1 point for today's work. The challenging part in the query is that IF postdate = billdate, the order is attributed to the sales rep on the date the order is billed, not on the date the order is posted. Also, if an order is re-posted and re-billed, the sales rep only gets a point based on the re-billed date, not the first billed date. The query should return results like:

Salesrep Date # of unique order posted & billed
Alice 2006-9-25 00:00:00 1
Danny 2006-9-23 00:00:00 1
Danny 2006-9-25 00:00:00 1

Thank you in advance!

Egghi

 

[bborissov]

SELECT SalesRep,
       PostDate,
       SUM(CASE WHEN PostDate = BillDate-1 OR PostDate = BillDate
                THEN 1
                ELSE 0 END) AS UniqueOrderPostedAndBilled
FROM MyTable
GROUP BY SalesRep,
         PostDate

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

I'm certain this can be done. The query will likely be rather large with derived tables and such.

Before giving advice, I'm a little confused by this part.
[tt][blue]The challenging part in the query is that IF postdate = billdate, the order is attributed to the sales rep on the date the order is billed, not on the date the order is posted.[/blue][/tt]

If postdate = billdate, then it shouldn't matter which date is used.

[tt][blue]Also, if an order is re-posted and re-billed, the sales rep only gets a point based on the re-billed date, not the first billed date.[/blue][/tt]

Given the following records:

[tt]-001234 Adam 2006-9-23 00:00:00 2006-9-24 00:00:00
-001234 Alice 2006-9-25 00:00:00 2006-9-26 00:00:00[/tt]

Shouldn't Alice get credit for 2006-9-26 (not sept 25)?




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[egghi]

Hi George,

Sales rep gets credit based on the post date AND IF the order posted is successfully billed in later days. So, Alice gets a credit when order was re-posted on 9/25 and then successfully re-billed on 9/26.

Basically, the query needs to find the last billdate for each order and then add the credits to sales rep on the date when the order was last posted.

If an order is posted today and billed later today, we add one point to the sales rep for today (I guess this business rule is not that cruicial now that I think again...)

Thank you!!

Egghi

 

[gmmastros]

Did you try Boris's query?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[egghi]

Hello George and Borislav,

I tried Borislav's query and it got REALLY REALLY close to what I want! I validated the result with the data on the table, and there are two problems left!

The first problem is my fault... PostDate does not always have to be BillDate - 1. As long as the order is billed on a later day, the sales rep gets a credit for the date the order is billed. So, should I just change the query to:

SELECT salesrep,
PostDate,
SUM(CASE WHEN PostDate < BillDate OR PostDate = BillDate
THEN 1
ELSE 0 END) AS UniqueOrderPostedAndBilled
FROM MyTable
GROUP BY salesrep,PostDate

The second problem is to count the "rebilldate" instead of the original "billdate". With Borislav's query, if an order is posted on 9/19/2006 and billed on 9/20/2006 by ANDY, and then the same order is posted again on 9/22/2006 and rebilled on 9/25/2006 by GARY, the query will give ANDY a credit for 9/19/2006, and GARY gets nothing (because of the first problem I caused... sorry)

Is there anyway to modify the query so that it only gives the credit to the Postdate based on MAX(billdate) for a unique order?

THANK YOU SO MUCH FOR BOTH OF YOUR HELP!

Egghi

 

[gmmastros]

Select Count(B.Counter), B.SalesRep, A.PostDate
From   [!]MyTable[/!] As B
       Inner Join (
         Select Counter, Max(PostDate) As PostDate
         From   [!]MyTable[/!]
         ) As A On B.Counter = A.Counter And B.PostDate = A.PostDate
Group By B.SalesRep, A.PostDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[egghi]

Hello George,

I ran your query and got an error message...

Server: Msg 8118, Level 16, State 1, Line 1
Column 'MyTable.counter' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any idea how we can modify the query?

Thanks a bunch!

Egghi

 

[gmmastros]

Sorry, I missed a group by.

Select Count(B.Counter), B.SalesRep, A.PostDate
From   MyTable As B
       Inner Join (
         Select Counter, Max(PostDate) As PostDate
         From   MyTable
         [!]Group By Counter[/!]
         ) As A On B.Counter = A.Counter And B.PostDate = A.PostDate
Group By B.SalesRep, A.PostDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Change inner join to be:

Inner Join (
         Select Counter, Max(PostDate) As PostDate
         From   MyTable
         GROUP BY Counter
         ) As A

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[egghi]

Bingo!! I think we have it!! I can't thank both of you (genius!!!) enough

Thank you, thank you, thank you!! You deserve many stars!!

Egghi

 

[bborissov]

Give them all to George. He gives you the full answer.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP