I need to find the lead time/no.of days it took from bill sent to payment received from the table

Hi all,
         I need to write a query where I should find the lead time/ time taken from billsent to payment received for the transactions in the given table. I have the detail explanation of table below, have a look.


Step 1:  create a table "T_Account_Details" with 3 columns AccountId, TransactionDate, ActionTaken.  (1001, 1002, 1003 .......)and(6/1/2013,6/4/2013,7/1/2013......)and(billsent, paymentReceived....)
Step 2:  There might be some "Action taken"s that correspond to the previous "billsent"s . We should not consider these records.
step 3: Find the turn time between bill sent and payment received.

In this way I have 200 rows, for some accountid's there is only billsent and no payment recieved, and for some accountid's there is only payment received but no bill was sent, and for some accountid's the billsent date is greater than the payment recieved date.

So, for all this criteria's I need to find the turn time/lead time/time taken from billsent to payment received. As I am new to sql server, its bit complex for me, can you please help me out.

Thanks,
Aparanjit
AparanjithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
There are several solutions possible for this, but I'd like to ask for some more information first.

q1: do you use SQL Server 2012? (it has new features that could be useful)
q2. could you provide some more sample data to mimic:
a: Some records that should be ignored, and
b: A record that should be matched to calculate the lead time
q3. perhaps list the "actions taken" values/code that are to be considered too.

The amount of sample data does NOT have to be extensive, but enough to mimic the wanted logic.

Thanks.
0
PortletPaulfreelancerCommented:
q4. are there any other fields that need to be considered OR that may be helpful?

& FYI, taking the acts provided I cam up with the following. Is it a relevant sample?
    ;WITH
     T_Account_Details AS (
       
             select
             *
             from (
               SELECT 1001 AS AccountId UNION ALL
               SELECT 1002 UNION ALL
               SELECT 1003
                  ) accounts
          
             CROSS JOIN (
               SELECT convert(date,'6/1/2013',101) AS TransactionDate UNION ALL
               SELECT convert(date,'6/4/2013',101) UNION ALL
               SELECT convert(date,'7/1/2013',101)
               ) trandate
          
             CROSS JOIN (
               SELECT 'billsent' AS ActionTaken UNION ALL
               SELECT 'paymentReceived'
               ) action
           )
    SELECT
    *
    FROM T_Account_Details
    order by
          AccountId
        , TransactionDate
        , ActionTaken
    

**[Results][2]**:
    
    | ACCOUNTID | TRANSACTIONDATE |     ACTIONTAKEN |
    |-----------|-----------------|-----------------|
    |      1001 |      2013-06-01 |        billsent |
    |      1001 |      2013-06-01 | paymentReceived |
    |      1001 |      2013-06-04 |        billsent |
    |      1001 |      2013-06-04 | paymentReceived |
    |      1001 |      2013-07-01 |        billsent |
    |      1001 |      2013-07-01 | paymentReceived |
    |      1002 |      2013-06-01 |        billsent |
    |      1002 |      2013-06-01 | paymentReceived |
    |      1002 |      2013-06-04 |        billsent |
    |      1002 |      2013-06-04 | paymentReceived |
    |      1002 |      2013-07-01 |        billsent |
    |      1002 |      2013-07-01 | paymentReceived |
    |      1003 |      2013-06-01 |        billsent |
    |      1003 |      2013-06-01 | paymentReceived |
    |      1003 |      2013-06-04 |        billsent |
    |      1003 |      2013-06-04 | paymentReceived |
    |      1003 |      2013-07-01 |        billsent |
    |      1003 |      2013-07-01 | paymentReceived |



  [1]: http://sqlfiddle.com/#!3/d41d8/22604

Open in new window

0
AparanjithAuthor Commented:
Hey PortletPaul,
                           I am attaching the excel file, which has some of the entries have a look, I am using sql server 2008 only, here as you can see the last rows with account id's 1009 and 1010 where billsent is greater than payment received, and some say 1011 there is no payment received and 1012 there is no billsent. In this way there will be some other rows too. For all of them we need to calculate the time/days between bill sent and payment received
a.docx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
Mmmmmmm, and mmmmm

is the ANY way of identifying a particular 'bill'?
         (such as other fields in the table, or another table)
otherwise we are really just making an educated guess.

thanks for the sample data - this helps a lot.
ps: there is no need to use a .doc for such text - e.g.
AccountId	Transaction_date	Action_Taken
1001	2013-01-01 10:37:09.000	BS
1001	2013-01-15 05:37:09.000	PR
1001	2013-01-20 12:37:09.000	BS
1001	2013-01-30 11:37:09.000	PR
1002	2013-01-10 11:37:09.000	PR
1002	2013-01-01 12:54:09.000	BS
1003	2013-01-05 12:54:09.000	BS
1003	2013-01-15 11:54:09.000	PR
1004	2013-02-05 12:30:09.000	BS
1004	2013-02-28 08:25:09.000	PR
1005	2013-02-15 02:30:09.000	BS
1005	2013-02-25 01:10:09.000	PR
1006	2013-03-15 02:54:09.000	BS
1006	2013-03-25 04:10:09.000	PR
1007	2013-01-10 05:30:09.000	BS
1007	2013-01-20 02:10:09.000	PR
1008	2013-05-15 08:54:09.000	BS
1008	2013-05-25 08:56:09.000	PR
1009	2013-02-10 05:08:09.000	BS
1009	2013-02-20 04:10:09.000	PR
1009	2013-04-15 07:54:55.000	BS
1009	2013-04-05 05:56:05.000	PR
1010	2013-02-10 05:05:45.000	BS
1010	2013-02-01 06:10:56.000	PR
1011	2013-01-15 05:54:31.000	BS
1012	2013-01-05 07:54:45.000	PR

Open in new window

0
AparanjithAuthor Commented:
Well these are the only columns and only table we have, BS is the  (bill sent) date for that particular account id and PR is the payment received for that particular account id. Basing them, we need to find the days difference/lead time between bill sent and payment received for all the accounts
0
AparanjithAuthor Commented:
Hey  PortletPaul,
I thought of taking the datediff function to calculate the difference of dates in the accounts but there is only one column with transaction date, so little confused how to find the difference of days between bill sent and payment received for all account id's.
0
PortletPaulfreelancerCommented:
this result:
| ACCOUNTID | BS |     PR |                 TD1 |                 TD2 | DAY_DIFF |     NOTE | RN1 |    RN2 |
|-----------|----|--------|---------------------|---------------------|----------|----------|-----|--------|
|      1001 | BS |     PR | 2013-01-01 10:37:09 | 2013-01-15 05:37:09 |       14 |          |   2 |      4 |
|      1001 | BS |     PR | 2013-01-20 12:37:09 | 2013-01-30 11:37:09 |       10 |          |   1 |      3 |
|      1002 | BS |     PR | 2013-01-01 12:54:09 | 2013-01-10 11:37:09 |        9 |          |   1 |      2 |
|      1003 | BS |     PR | 2013-01-05 12:54:09 | 2013-01-15 11:54:09 |       10 |          |   1 |      2 |
|      1004 | BS |     PR | 2013-02-05 12:30:09 | 2013-02-28 08:25:09 |       23 |          |   1 |      2 |
|      1005 | BS |     PR | 2013-02-15 02:30:09 | 2013-02-25 01:10:09 |       10 |          |   1 |      2 |
|      1006 | BS |     PR | 2013-03-15 02:54:09 | 2013-03-25 04:10:09 |       10 |          |   1 |      2 |
|      1007 | BS |     PR | 2013-01-10 05:30:09 | 2013-01-20 02:10:09 |       10 |          |   1 |      2 |
|      1008 | BS |     PR | 2013-05-15 08:54:09 | 2013-05-25 08:56:09 |       10 |          |   1 |      2 |
|      1009 | BS |     PR | 2013-02-10 05:08:09 | 2013-02-20 04:10:09 |       10 |          |   2 |      4 |
|      1009 | BS |     PR | 2013-04-15 07:54:55 | 2013-04-05 05:56:05 |   (null) | mismatch |   1 |      3 |
|      1010 | BS |     PR | 2013-02-10 05:05:45 | 2013-02-01 06:10:56 |   (null) | mismatch |   1 |      2 |
|      1011 | BS | (null) | 2013-01-15 05:54:31 |              (null) |   (null) |          |   1 | (null) |
|      1012 | PR | (null) | 2013-01-05 07:54:45 |              (null) |   (null) |          |   1 | (null) |

Open in new window

Produced by this query:
    ;WITH
     CTE AS (
             SELECT
                    AccountId
                  , Transaction_date
                  , Action_Taken
                  , row_number() over (partition BY AccountId
                                           ORDER BY Action_Taken, Transaction_date DESC) AS rn
                  , count(CASE WHEN Action_Taken = 'BS' THEN Action_Taken END)
                                 over  (partition BY AccountId) AS count_bs
                  , count(CASE WHEN Action_Taken = 'PR' THEN Action_Taken END)
                                 over  (partition BY AccountId) AS count_pr
             FROM T_Account_Details
           )
    
    SELECT
            tad1.AccountId
          , tad1.Action_Taken                                                   AS BS
          , tad2.Action_Taken                                                   AS PR
          , convert(varchar(19),tad1.transaction_date,121)                          AS td1
          , convert(varchar(19),tad2.transaction_date, 121)                         AS td2
          , CASE WHEN tad1.transaction_date < tad2.transaction_date
                 THEN datediff(DAY,tad1.transaction_date,tad2.transaction_date)
                 ELSE NULL
            END                                                                 AS day_diff
          , CASE WHEN tad1.transaction_date >= tad2.transaction_date
                 THEN 'mismatch'
                 ELSE ''
            END                                                                 AS note
          , tad1.rn AS rn1
          , tad2.rn AS rn2
    FROM CTE AS TAD1
    LEFT JOIN CTE AS TAD2 ON tad1.AccountId = tad2.AccountId
                         AND tad1.rn = (tad2.rn - tad2.count_pr)
                         AND tad2.Action_Taken = 'PR'
    WHERE (
              tad1.Action_Taken = 'BS'
           OR
             (tad1.Action_Taken = 'PR' AND tad1.count_bs = 0)
          )
    ORDER BY
          tad1.AccountId
        , tad1.Transaction_date
        , tad1.Action_Taken

 http://sqlfiddle.com/#!3/bb65e/1

Open in new window

Comments expected on this please as I don't have expected results to refer to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
>>so little confused how to find the difference of days between bill sent and payment received for all account id's.
understood, and it isn't going to be dead simple either, as I trust you can now see from the results above.

I IMPLORE you to locate some better way of identifying a particular bill by some reliable reference (e.g. a document number/id)
you will be chasing shadows otherwise.
0
AparanjithAuthor Commented:
Thank you so much PortelPaul, I do have some more questions, on this query only, please help me out when I post a comment  later this evening.
0
PortletPaulfreelancerCommented:
No problem, I was hoping we would discuss it further as I'm not happy with this overall (not just the code seen here) I think there's a need to look harder at the data for uniqueness of the bills.

just add the url to the next question here please.
0
AparanjithAuthor Commented:
Hi PortletPaul,
                           Here are some questions which I need to query to get the results.

1. What are the bills which are sent and received payment for a particular month say as January.
2. What are the accounts which did not received any payments.
3. What is the average lead time taken for the accounts to get the payments.
4. Which account/accounts are taking more time/ delaying in payments.
                                                                                                 In this way there are also more questions related to the task. Can you help in this?

Thanks,
Aparanjit


Sample data I have for some records, have a look

AccountId	Transaction_date	Action_Taken
1001	2013-01-01 10:37:09.000	BS
1001	2013-01-15 05:37:09.000	PR
1001	2013-01-20 12:37:09.000	BS
1001	2013-01-30 11:37:09.000	PR
1001	2013-10-09 23:19:48.853	BS
1002	2013-01-10 11:37:09.000	PR
1002	2013-01-01 12:54:09.000	BS
1003	2013-01-05 12:54:09.000	BS
1003	2013-01-15 11:54:09.000	PR
1004	2013-02-05 12:30:09.000	BS
1004	2013-02-28 08:25:09.000	PR
1005	2013-02-15 02:30:09.000	BS
1005	2013-02-25 01:10:09.000	PR
1006	2013-03-15 02:54:09.000	BS
1006	2013-03-25 04:10:09.000	PR
1007	2013-01-10 05:30:09.000	BS
1007	2013-01-20 02:10:09.000	PR
1008	2013-05-15 08:54:09.000	BS
1008	2013-05-25 08:56:09.000	PR
1009	2013-02-10 05:08:09.000	BS
1009	2013-02-20 04:10:09.000	PR
1009	2013-04-15 07:54:55.000	BS
1009	2013-04-05 05:56:05.000	PR
1010	2013-02-10 05:05:45.000	BS
1010	2013-02-01 06:10:56.000	PR
1011	2013-01-15 05:54:31.000	BS
1012	2013-01-05 07:54:45.000	PR
1013	2013-07-05 09:54:54.000	BS
1013	2013-07-15 10:56:15.000	PR
1014	2013-03-01 09:05:34.000	BS
1014	2013-03-10 05:10:45.000	PR
1015	2013-08-10 08:54:25.000	BS
1015	2013-04-25 06:56:35.000	PR
1016	2013-02-04 10:05:42.000	BS
1016	2013-02-14 09:10:25.000	PR
1017	2013-09-02 07:54:56.000	BS
1017	2013-09-22 04:56:45.000	PR
1018	2013-03-08 09:05:34.000	BS
1018	2013-03-15 05:45:10.000	PR
1019	2013-05-11 06:54:52.000	BS
1019	2013-05-24 06:56:14.000	PR
1020	2013-02-15 10:05:42.000	BS
1020	2013-02-28 09:10:45.000	PR
1021	2013-02-15 04:56:12.000	PR
1021	2013-02-02 05:54:52.000	BS
1022	2013-03-14 11:05:14.000	BS
1022	2013-03-25 05:35:18.000	PR
1023	2013-05-12 07:54:56.000	BS
1023	2013-05-24 14:56:44.000	PR
1024	2013-05-16 15:05:42.000	BS
1024	2013-05-26 22:10:20.000	PR
1025	2013-05-15 06:54:45.000	BS
1025	2013-05-30 03:56:52.000	PR
1026	2013-04-24 09:05:24.000	BS
1026	2013-05-10 08:35:48.000	PR
1027	2013-08-22 04:54:36.000	BS
1027	2013-09-14 23:45:12.000	PR
1028	2013-05-30 18:05:42.000	BS
1028	2013-05-16 21:10:52.000	PR
1029	2013-01-05 17:54:25.000	PR
1030	2013-08-05 21:54:12.000	PR
1031	2013-02-15 01:54:52.000	PR
1031	2013-12-15 01:54:52.000	BS
1032	2013-07-22 12:54:58.000	BS
1033	2013-12-23 08:45:12.000	PR
1034	2013-10-15 03:54:45.000	BS
1035	2013-11-07 21:17:08.000	PR
1036	2013-06-03 05:12:53.000	BS
1037	2013-01-15 01:54:52.000	BS
1037	2013-01-22 12:54:58.000	PR
1040	2013-12-23 08:45:12.000	BS
1040	2013-12-30 03:54:45.000	PR
1041	2013-11-07 21:17:08.000	BS
1042	2013-02-15 04:54:52.000	BS
1042	2013-02-27 17:54:18.000	PR
1043	2013-12-23 07:45:45.000	BS
1043	2013-12-30 04:56:25.000	PR
1044	2013-11-15 19:17:56.000	BS
1045	2013-06-03 05:12:53.000	PR
1046	2013-12-03 15:02:53.000	PR

Open in new window

0
PortletPaulfreelancerCommented:
I don't have a problem with helping you - but I thought you were opening another question.

The BEST advice I can give you is:

Tell me more about the data model!
I guarantee you will go around in circles answering these extra questions until you/we can find a reliable method to uniquely identify a "bill".

Let's start with the full table definition of [T_Account_Details]
Is this a 'base table' or a temporary table?
If it is a temporary table - what is the code that generates it?

I repeat, what you/we need to do is find a reliable way of matching a payment to a bill - it won't work reliably from just the 3 fields you are sharing with me.
0
PortletPaulfreelancerCommented:
The reliability is questionable as I CANNOT guarantee matching of a payment is made to the relevant bill - it's quite arbitrary; AND there are other very clear issues identified below:
dates wrong way
    | ACCOUNTID | STARTACTION |     PR |                 TD1 |                 TD2 | DAY_DIFF |     NOTE | RN1 |    RN2 |
    |-----------|-------------|--------|---------------------|---------------------|----------|----------|-----|--------|
    |      1001 |          BS |     PR | 2013-01-20 12:37:09 | 2013-01-15 05:37:09 |   (null) | mismatch |   2 |      4 |
    |      1001 |          BS |     PR | 2013-10-09 23:19:48 | 2013-01-30 11:37:09 |   (null) | mismatch |   3 |      5 |
    |      1009 |          BS |     PR | 2013-04-15 07:54:55 | 2013-04-05 05:56:05 |   (null) | mismatch |   2 |      4 |
    |      1010 |          BS |     PR | 2013-02-10 05:05:45 | 2013-02-01 06:10:56 |   (null) | mismatch |   1 |      2 |
    |      1015 |          BS |     PR | 2013-08-10 08:54:25 | 2013-04-25 06:56:35 |   (null) | mismatch |   1 |      2 |
    |      1028 |          BS |     PR | 2013-05-30 18:05:42 | 2013-05-16 21:10:52 |   (null) | mismatch |   1 |      2 |
    |      1031 |          BS |     PR | 2013-12-15 01:54:52 | 2013-02-15 01:54:52 |   (null) | mismatch |   1 |      2 |

starts at PR
    | ACCOUNTID | STARTACTION |     PR |                 TD1 |                 TD2 | DAY_DIFF |     NOTE | RN1 |    RN2 |
    |-----------|-------------|--------|---------------------|---------------------|----------|----------|-----|--------|
    |      1012 |          PR | (null) | 2013-01-05 07:54:45 |              (null) |   (null) |          |   1 | (null) |
    |      1029 |          PR | (null) | 2013-01-05 17:54:25 |              (null) |   (null) |          |   1 | (null) |
    |      1030 |          PR | (null) | 2013-08-05 21:54:12 |              (null) |   (null) |          |   1 | (null) |
    |      1033 |          PR | (null) | 2013-12-23 08:45:12 |              (null) |   (null) |          |   1 | (null) |
    |      1035 |          PR | (null) | 2013-11-07 21:17:08 |              (null) |   (null) |          |   1 | (null) |
    |      1045 |          PR | (null) | 2013-06-03 05:12:53 |              (null) |   (null) |          |   1 | (null) |
    |      1046 |          PR | (null) | 2013-12-03 15:02:53 |              (null) |   (null) |          |   1 | (null) |

Maybe OK
    | ACCOUNTID | STARTACTION |     PR |                 TD1 |                 TD2 | DAY_DIFF |     NOTE | RN1 |    RN2 |
    |-----------|-------------|--------|---------------------|---------------------|----------|----------|-----|--------|
    |      1001 |          BS | (null) | 2013-01-01 10:37:09 |              (null) |   (null) |          |   1 | (null) |
    |      1002 |          BS |     PR | 2013-01-01 12:54:09 | 2013-01-10 11:37:09 |        9 |          |   1 |      2 |
    |      1003 |          BS |     PR | 2013-01-05 12:54:09 | 2013-01-15 11:54:09 |       10 |          |   1 |      2 |
    |      1004 |          BS |     PR | 2013-02-05 12:30:09 | 2013-02-28 08:25:09 |       23 |          |   1 |      2 |
    |      1005 |          BS |     PR | 2013-02-15 02:30:09 | 2013-02-25 01:10:09 |       10 |          |   1 |      2 |
    |      1006 |          BS |     PR | 2013-03-15 02:54:09 | 2013-03-25 04:10:09 |       10 |          |   1 |      2 |
    |      1007 |          BS |     PR | 2013-01-10 05:30:09 | 2013-01-20 02:10:09 |       10 |          |   1 |      2 |
    |      1008 |          BS |     PR | 2013-05-15 08:54:09 | 2013-05-25 08:56:09 |       10 |          |   1 |      2 |
    |      1009 |          BS |     PR | 2013-02-10 05:08:09 | 2013-02-20 04:10:09 |       10 |          |   1 |      3 |
    |      1011 |          BS | (null) | 2013-01-15 05:54:31 |              (null) |   (null) |          |   1 | (null) |
    |      1013 |          BS |     PR | 2013-07-05 09:54:54 | 2013-07-15 10:56:15 |       10 |          |   1 |      2 |
    |      1014 |          BS |     PR | 2013-03-01 09:05:34 | 2013-03-10 05:10:45 |        9 |          |   1 |      2 |
    |      1016 |          BS |     PR | 2013-02-04 10:05:42 | 2013-02-14 09:10:25 |       10 |          |   1 |      2 |
    |      1017 |          BS |     PR | 2013-09-02 07:54:56 | 2013-09-22 04:56:45 |       20 |          |   1 |      2 |
    |      1018 |          BS |     PR | 2013-03-08 09:05:34 | 2013-03-15 05:45:10 |        7 |          |   1 |      2 |
    |      1019 |          BS |     PR | 2013-05-11 06:54:52 | 2013-05-24 06:56:14 |       13 |          |   1 |      2 |
    |      1020 |          BS |     PR | 2013-02-15 10:05:42 | 2013-02-28 09:10:45 |       13 |          |   1 |      2 |
    |      1021 |          BS |     PR | 2013-02-02 05:54:52 | 2013-02-15 04:56:12 |       13 |          |   1 |      2 |
    |      1022 |          BS |     PR | 2013-03-14 11:05:14 | 2013-03-25 05:35:18 |       11 |          |   1 |      2 |
    |      1023 |          BS |     PR | 2013-05-12 07:54:56 | 2013-05-24 14:56:44 |       12 |          |   1 |      2 |
    |      1024 |          BS |     PR | 2013-05-16 15:05:42 | 2013-05-26 22:10:20 |       10 |          |   1 |      2 |
    |      1025 |          BS |     PR | 2013-05-15 06:54:45 | 2013-05-30 03:56:52 |       15 |          |   1 |      2 |
    |      1026 |          BS |     PR | 2013-04-24 09:05:24 | 2013-05-10 08:35:48 |       16 |          |   1 |      2 |
    |      1027 |          BS |     PR | 2013-08-22 04:54:36 | 2013-09-14 23:45:12 |       23 |          |   1 |      2 |
    |      1032 |          BS | (null) | 2013-07-22 12:54:58 |              (null) |   (null) |          |   1 | (null) |
    |      1034 |          BS | (null) | 2013-10-15 03:54:45 |              (null) |   (null) |          |   1 | (null) |
    |      1036 |          BS | (null) | 2013-06-03 05:12:53 |              (null) |   (null) |          |   1 | (null) |
    |      1037 |          BS |     PR | 2013-01-15 01:54:52 | 2013-01-22 12:54:58 |        7 |          |   1 |      2 |
    |      1040 |          BS |     PR | 2013-12-23 08:45:12 | 2013-12-30 03:54:45 |        7 |          |   1 |      2 |
    |      1041 |          BS | (null) | 2013-11-07 21:17:08 |              (null) |   (null) |          |   1 | (null) |
    |      1042 |          BS |     PR | 2013-02-15 04:54:52 | 2013-02-27 17:54:18 |       12 |          |   1 |      2 |
    |      1043 |          BS |     PR | 2013-12-23 07:45:45 | 2013-12-30 04:56:25 |        7 |          |   1 |      2 |
    |      1044 |          BS | (null) | 2013-11-15 19:17:56 |              (null) |   (null) |          |   1 | (null) |

Open in new window

produced with modified query
;WITH
 CTE AS (
         SELECT
                AccountId
              , Transaction_date
              , Action_Taken
              , row_number() over (partition BY AccountId
                                       ORDER BY Action_Taken, Transaction_date ASC) AS rn --<< changed to ASC
              , count(CASE WHEN Action_Taken = 'BS' THEN Action_Taken END)
                             over  (partition BY AccountId) AS count_bs
              , count(CASE WHEN Action_Taken = 'PR' THEN Action_Taken END)
                             over  (partition BY AccountId) AS count_pr
         FROM T_Account_Details
       )

SELECT
        tad1.AccountId
      , tad1.Action_Taken                                                   AS StartAction
      , tad2.Action_Taken                                                   AS PR
      , convert(varchar(19),tad1.transaction_date,121)                          AS td1
      , convert(varchar(19),tad2.transaction_date, 121)                         AS td2
      , CASE WHEN tad1.transaction_date < tad2.transaction_date
             THEN datediff(DAY,tad1.transaction_date,tad2.transaction_date)
             ELSE NULL
        END                                                                 AS day_diff
      , CASE WHEN tad1.transaction_date >= tad2.transaction_date
             THEN 'mismatch'
             ELSE ''
        END                                                                 AS note
      , tad1.rn AS rn1
      , tad2.rn AS rn2
FROM CTE AS TAD1
LEFT JOIN CTE AS TAD2 ON tad1.AccountId = tad2.AccountId
                     AND tad1.rn = (tad2.rn - tad2.count_pr)
                     AND tad2.Action_Taken = 'PR'
WHERE (
          tad1.Action_Taken = 'BS'
       OR
         (tad1.Action_Taken = 'PR' AND tad1.count_bs = 0)
      )
ORDER BY
      note desc --<< changed
    , case when tad1.Action_Taken = 'PR' then 1 else 0 end desc --<< changed
    , tad1.AccountId
    , tad1.Transaction_date
    , tad1.Action_Taken

;
-- http://sqlfiddle.com/#!3/99943/7

Open in new window

0
AparanjithAuthor Commented:
Hey,
        This is the base table, and all that I have is these three columns as of now, my manager gave this task, as per I shared it with you, may be we can add another column or so to find the uniqueness of the bill, but for now, they have given this table, which contain these three columns that is it.
                                 Can we proceed further with these, or do you want to add something to the table structure to find the uniqueness of the bill.
0
PortletPaulfreelancerCommented:
I absolutely understand "my manager gave this task"
you feel pressure to produce a result.

BUT, if that result is unreliable - your reputation will suffer

Tell your manager that unless there is more information available you CANNOT guarantee the accuracy or reliability of any derived metrics.

That is the truth.

With respect to [T_Account_Details] being a base table with only those three fields - I'm sorry but are you saying there is no ID (unique row identity) and that there are no other tables in the database?

"If there is a will, there is a way."
If you want to impress the manager - find the way
You need to learn more about the other tables that will be there - somewhere in those we will find the solution.

This is what I would tell the manager if it was me who had been asked:
If I am restricted to just those 3 fields - I will not put my name on the report. It will never be reliable and any decisions such an unreliable report might produce I will not be responsible for.
0
AparanjithAuthor Commented:
Sure, thanks for the suggestion man. And thanks for the code which you shared with me, if I get any further extra details regarding the task i will let you know, do you want me to open a new question if I get the details or, do you want me to continue in the same chain. Let me know.

Thanks,
Aparanjit
0
PortletPaulfreelancerCommented:
as this question has already been accepted I would advise opening a new question.

it will also attract new participants - maybe someone else has has a better way
0
PortletPaulfreelancerCommented:
Hi, not sure if you have discussed this with your boss or not, but here is a great example of why I keep saying this isn't reliable. Please consider these 2 records:

          (1031, '2013-02-15 01:54:52', 'PR'),       -- call this "rec1"
          (1031, '2013-12-15 01:54:52', 'BS'),       -- call this "rec2"

rec1 is 10 months earlier than rec2, but rec1 is a PR so it must be referring to a bill issued before - but that bill is not listed

rec2 is a BS, but there is not PR that follows it

so although the query logic (see below) matches theses 2 dates together, they are just not correct and therefore "mismatched", AND, this can only be fully solved by having a unique reference for A bill (not unique per transaction)

Below is an improved query, which from the sample data produces the following result:
| ACCOUNTID | STARTACTION |     PR |                 TD1 |                 TD2 |              BS_MIN | DAY_DIFF |     NOTE | RN1 |    RN2 |
|-----------|-------------|--------|---------------------|---------------------|---------------------|----------|----------|-----|--------|
|      1001 |          BS |     PR | 2013-01-01 10:37:09 | 2013-01-15 05:37:09 | 2013-01-01 10:37:09 |       14 |          |   1 |      4 |
|      1001 |          BS |     PR | 2013-01-20 12:37:09 | 2013-01-30 11:37:09 | 2013-01-01 10:37:09 |       10 |          |   2 |      5 |
|      1001 |          BS | (null) | 2013-10-09 23:19:48 |              (null) | 2013-01-01 10:37:09 |   (null) |          |   3 | (null) |
|      1002 |          BS |     PR | 2013-01-01 12:54:09 | 2013-01-10 11:37:09 | 2013-01-01 12:54:09 |        9 |          |   1 |      2 |
|      1003 |          BS |     PR | 2013-01-05 12:54:09 | 2013-01-15 11:54:09 | 2013-01-05 12:54:09 |       10 |          |   1 |      2 |
|      1004 |          BS |     PR | 2013-02-05 12:30:09 | 2013-02-28 08:25:09 | 2013-02-05 12:30:09 |       23 |          |   1 |      2 |
|      1005 |          BS |     PR | 2013-02-15 02:30:09 | 2013-02-25 01:10:09 | 2013-02-15 02:30:09 |       10 |          |   1 |      2 |
|      1006 |          BS |     PR | 2013-03-15 02:54:09 | 2013-03-25 04:10:09 | 2013-03-15 02:54:09 |       10 |          |   1 |      2 |
|      1007 |          BS |     PR | 2013-01-10 05:30:09 | 2013-01-20 02:10:09 | 2013-01-10 05:30:09 |       10 |          |   1 |      2 |
|      1008 |          BS |     PR | 2013-05-15 08:54:09 | 2013-05-25 08:56:09 | 2013-05-15 08:54:09 |       10 |          |   1 |      2 |
|      1009 |          BS |     PR | 2013-02-10 05:08:09 | 2013-02-20 04:10:09 | 2013-02-10 05:08:09 |       10 |          |   1 |      3 |
|      1009 |          BS |     PR | 2013-04-15 07:54:55 | 2013-04-05 05:56:05 | 2013-02-10 05:08:09 |   (null) | mismatch |   2 |      4 |
|      1010 |          BS |     PR | 2013-02-10 05:05:45 | 2013-02-01 06:10:56 | 2013-02-10 05:05:45 |   (null) | mismatch |   1 |      2 |
|      1011 |          BS | (null) | 2013-01-15 05:54:31 |              (null) | 2013-01-15 05:54:31 |   (null) |          |   1 | (null) |
|      1012 |          PR |     PR | 2013-01-05 07:54:45 | 2013-01-05 07:54:45 |              (null) |        0 |          |   1 |      1 |
|      1013 |          BS |     PR | 2013-07-05 09:54:54 | 2013-07-15 10:56:15 | 2013-07-05 09:54:54 |       10 |          |   1 |      2 |
|      1014 |          BS |     PR | 2013-03-01 09:05:34 | 2013-03-10 05:10:45 | 2013-03-01 09:05:34 |        9 |          |   1 |      2 |
|      1015 |          BS |     PR | 2013-08-10 08:54:25 | 2013-04-25 06:56:35 | 2013-08-10 08:54:25 |   (null) | mismatch |   1 |      2 |
|      1016 |          BS |     PR | 2013-02-04 10:05:42 | 2013-02-14 09:10:25 | 2013-02-04 10:05:42 |       10 |          |   1 |      2 |
|      1017 |          BS |     PR | 2013-09-02 07:54:56 | 2013-09-22 04:56:45 | 2013-09-02 07:54:56 |       20 |          |   1 |      2 |
|      1018 |          BS |     PR | 2013-03-08 09:05:34 | 2013-03-15 05:45:10 | 2013-03-08 09:05:34 |        7 |          |   1 |      2 |
|      1019 |          BS |     PR | 2013-05-11 06:54:52 | 2013-05-24 06:56:14 | 2013-05-11 06:54:52 |       13 |          |   1 |      2 |
|      1020 |          BS |     PR | 2013-02-15 10:05:42 | 2013-02-28 09:10:45 | 2013-02-15 10:05:42 |       13 |          |   1 |      2 |
|      1021 |          BS |     PR | 2013-02-02 05:54:52 | 2013-02-15 04:56:12 | 2013-02-02 05:54:52 |       13 |          |   1 |      2 |
|      1022 |          BS |     PR | 2013-03-14 11:05:14 | 2013-03-25 05:35:18 | 2013-03-14 11:05:14 |       11 |          |   1 |      2 |
|      1023 |          BS |     PR | 2013-05-12 07:54:56 | 2013-05-24 14:56:44 | 2013-05-12 07:54:56 |       12 |          |   1 |      2 |
|      1024 |          BS |     PR | 2013-05-16 15:05:42 | 2013-05-26 22:10:20 | 2013-05-16 15:05:42 |       10 |          |   1 |      2 |
|      1025 |          BS |     PR | 2013-05-15 06:54:45 | 2013-05-30 03:56:52 | 2013-05-15 06:54:45 |       15 |          |   1 |      2 |
|      1026 |          BS |     PR | 2013-04-24 09:05:24 | 2013-05-10 08:35:48 | 2013-04-24 09:05:24 |       16 |          |   1 |      2 |
|      1027 |          BS |     PR | 2013-08-22 04:54:36 | 2013-09-14 23:45:12 | 2013-08-22 04:54:36 |       23 |          |   1 |      2 |
|      1028 |          BS |     PR | 2013-05-30 18:05:42 | 2013-05-16 21:10:52 | 2013-05-30 18:05:42 |   (null) | mismatch |   1 |      2 |
|      1029 |          PR |     PR | 2013-01-05 17:54:25 | 2013-01-05 17:54:25 |              (null) |        0 |          |   1 |      1 |
|      1030 |          PR |     PR | 2013-08-05 21:54:12 | 2013-08-05 21:54:12 |              (null) |        0 |          |   1 |      1 |
|      1031 |          BS |     PR | 2013-12-15 01:54:52 | 2013-02-15 01:54:52 | 2013-12-15 01:54:52 |   (null) | mismatch |   1 |      2 |
|      1032 |          BS | (null) | 2013-07-22 12:54:58 |              (null) | 2013-07-22 12:54:58 |   (null) |          |   1 | (null) |
|      1033 |          PR |     PR | 2013-12-23 08:45:12 | 2013-12-23 08:45:12 |              (null) |        0 |          |   1 |      1 |
|      1034 |          BS | (null) | 2013-10-15 03:54:45 |              (null) | 2013-10-15 03:54:45 |   (null) |          |   1 | (null) |
|      1035 |          PR |     PR | 2013-11-07 21:17:08 | 2013-11-07 21:17:08 |              (null) |        0 |          |   1 |      1 |
|      1036 |          BS | (null) | 2013-06-03 05:12:53 |              (null) | 2013-06-03 05:12:53 |   (null) |          |   1 | (null) |
|      1037 |          BS |     PR | 2013-01-15 01:54:52 | 2013-01-22 12:54:58 | 2013-01-15 01:54:52 |        7 |          |   1 |      2 |
|      1040 |          BS |     PR | 2013-12-23 08:45:12 | 2013-12-30 03:54:45 | 2013-12-23 08:45:12 |        7 |          |   1 |      2 |
|      1041 |          BS | (null) | 2013-11-07 21:17:08 |              (null) | 2013-11-07 21:17:08 |   (null) |          |   1 | (null) |
|      1042 |          BS |     PR | 2013-02-15 04:54:52 | 2013-02-27 17:54:18 | 2013-02-15 04:54:52 |       12 |          |   1 |      2 |
|      1043 |          BS |     PR | 2013-12-23 07:45:45 | 2013-12-30 04:56:25 | 2013-12-23 07:45:45 |        7 |          |   1 |      2 |
|      1044 |          BS | (null) | 2013-11-15 19:17:56 |              (null) | 2013-11-15 19:17:56 |   (null) |          |   1 | (null) |
|      1045 |          PR |     PR | 2013-06-03 05:12:53 | 2013-06-03 05:12:53 |              (null) |        0 |          |   1 |      1 |
|      1046 |          PR |     PR | 2013-12-03 15:02:53 | 2013-12-03 15:02:53 |              (null) |        0 |          |   1 |      1 |

Open in new window

and here is the improved (but still imperfect) query:
;WITH
  CTE1 AS (
            SELECT
                    AccountId
                  , MIN(Transaction_date) AS BS_min
                  , Action_Taken
            FROM T_Account_Details
            WHERE Action_Taken = 'BS'
            GROUP BY
                    AccountId
                  , Action_Taken
          )
, CTE2 AS (
            SELECT
                    tad.AccountId
                  , tad.Action_Taken
                  , tad.Transaction_date
                  , cte1.bs_min
                  , row_number() over (partition BY tad.AccountId ORDER BY tad.Action_Taken, ISNULL(cte1.bs_min,getdate()) ASC, tad.Transaction_date ASC ) AS rn
                  , count(CASE WHEN tad.Action_Taken = 'BS' THEN tad.Action_Taken END) over  (partition BY tad.AccountId) AS count_bs
                  , count(CASE WHEN tad.Action_Taken = 'PR' THEN tad.Action_Taken END) over  (partition BY tad.AccountId) AS count_pr
            FROM T_Account_Details AS tad
            LEFT JOIN CTE1 ON tad.AccountId = cte1.AccountId
                          AND tad.Transaction_date >= cte1.BS_min
           )
SELECT
        tad1.AccountId
      , tad1.Action_Taken                                                   AS StartAction
      , tad2.Action_Taken                                                   AS PR
      , convert(varchar(19),tad1.transaction_date,121)                      AS td1
      , convert(varchar(19),tad2.transaction_date, 121)                     AS td2
      , convert(varchar(19),tad1.bs_min,121)                                AS bs_min
      , CASE WHEN tad1.transaction_date <= tad2.transaction_date
             THEN datediff(DAY,tad1.transaction_date,tad2.transaction_date)
             ELSE NULL
        END                                                                 AS day_diff
      , CASE WHEN tad1.transaction_date > tad2.transaction_date
             THEN 'mismatch'
             ELSE ''
        END                                                                 AS note
      , tad1.rn AS rn1
      , tad2.rn AS rn2
FROM CTE2 AS TAD1
LEFT JOIN CTE2 AS TAD2 ON tad1.AccountId = tad2.AccountId
                     AND tad1.rn = (tad2.rn - tad2.count_bs)
                     AND tad2.Action_Taken = 'PR'
WHERE (
          tad1.Action_Taken = 'BS'
       OR
         (tad1.Action_Taken = 'PR' AND tad1.count_bs = 0)
      )
ORDER BY
      tad1.AccountId
    , tad1.Transaction_date
    , tad1.Action_Taken
;

 http://sqlfiddle.com/#!3/99943/41

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.