Getting the count for every page

[nelco]

I have this requirement:

Page Page No:

AccountType 1
PrimaryPersonalInfoAddress 2
PrimaryPersonalInfoIdentification 3
PrimaryPersonalInfoEmployment 4
FinancialInfoPersonalW8/W9 5
FinancialInfoPersonalTrading 6
WarningPage 7
FinancialInfoHearAboutUs 8
Review 9
AcceptAgreements 10
AcceptAgreements-Next 11

In the table I am looking, there are records which look as follows:
CustomerID PageAbandoned Day
123 AcceptAgreements 2009-01-28
456 Review 2009-01-29

Basically, it means that customer 123 filled all out all pages uptil AcceptAgreements (he came to Accept Agreements page but abandoned the application), which means he filled up pages 1 to 9. Same thing for customer 456. He filled out pages 1 to 8. I want to keep a count of each page that got filled out considering the above requirement.

I am not sure on how to go about this though. Any help would be appreciated. Thanks.

 

[bborissov]

And what you want as final result?
If you want all customers that satisfy some pages that you could use 11 UNIONS.
Something like:

SELECT ...., 1 AS PageNo
FROM YourTable
INNER JOIN PagesTable ON ???????????
WHERE PagesTable.PageId >=1
UNION
SELECT ...., 2 AS PageNo
FROM YourTable
INNER JOIN PagesTable ON ???????????
WHERE PagesTable.PageId >=2
....
SELECT ...., 11 AS PageNo
FROM YourTable
INNER JOIN PagesTable ON ???????????
WHERE PagesTable.PageId > 10

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[nelco]

Thanks for your response 'bborissov'. Sorry I wasn't clear of the output I am looking for.

This is the output I am looking for:

Day Page PageVisited
2009-01-28 AccountType 1
2009-01-28 PrimaryPersonalInfoAddress 1
2009-01-28 PrimaryPersonalInfoIdentification 1
2009-01-28 PrimaryPersonalInfoEmployment 1
2009-01-28 FinancialInfoPersonalW8/W9 1
....(and so on)

Basically, I want to mark each page visited by each customer each day. I just want to mark them as visited. It might have repeated rows (if 2 different customers visited the page). But that is fine. I will aggregate the information later.

 

[bborissov]

O!

SELECT TheTable.Day,
       PageTable.Name AS Page,
       SUM(CASE WHEN TheTable.PageId < PageTable.Id
                      THEN 1
                ELSE 0 END) AS PageVisited
FROM TheTable
INNER JOIN PageTable ON TheTable.PageId < PageTable.Id
GROUP BY TheTable.Day,
         PageTable.Name

(NOT TESTED!)
And please don't tell me that you didn't have a table with pages.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[nelco]

Thanks 'bborissov' for your response. The result is not right though. I'll work on making the changes.

 

[bborissov]

It could be. As I said it is not tested. But I post it just to give you an idea how to do it.
I don't know your database, not your tables so I just assume everything
Also I just guess what you want. Maybe if you post some example data from both tables and desired result somebody could do the job

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.