[Help with complex query]

Cheers Rhodesa, this works well. I modified it a bit to this for my needs:

 

<?php
  $result = mssql_query('SELECT u.UserID,u.CompanyName,u.Postcode,p.url,COUNT(p.timestamp) as visits FROM PageVisits p LEFT JOIN Users u ON P.UserID = u.UserID GROUP BY u.UserID,u.CompanyName,u.Postcode,p.url')
    or die(mssql_get_last_message());
  // $urlList contains a list of all site URLs to be counted
  $urlList = array('/', '/contact.html', '/products.html');
  $data = array();
  while($row = mssql_fetch_assoc($query)){
    if(!isset($data[$row['UserID']])){
      $data[$row['UserID']] = array(
        'UserID' => $row['UserID'],
        'CompanyName' => $row['CompanyName'],
        'Postcode' => $row['Postcode'],
      );
      foreach ($urlList as $url) {
       $data[$row['UserID']][$url] = 0;
      }
    }
    $data[$row['UserID']][$row['url']] = $row['visits'];
  }
  ksort($data);
  print_r($data);
?>

 

Now each user has the same number of entries, and any pages not visited have 0 in the counter. When we add a new page we just need to add an entry in the $urlList array and it will be counted.

 

Thanks for your help!

[Help with complex query]

I'd love to - but the format of the output has already been agreed so I can't change that - and the data format id from a legacy database too so I can't change that!

 

Thanks anyway rhodesa, I'll look into doing a procedure now.

[PHP query to MSSQL TimeStamp Field]

The query you are using looks suitable for a DATETIME field - can you switch the types from TIMESTAMP to DATETIME?

[Help with complex query]

Hello (first post  ),

 

I am using SQL Server 2005 and I'm having trouble generating a complex query.

 

I have the following tables:

 

+--------------------+

| Users              |

+--------------------+

| UserID            |

| CompanyName        |

| Postcode          |

+--------------------+

 

And a table called PageVisits

 

+--------------------+

| PageVisits        |

+--------------------+

| url                |

| UserID            |

| timestamp          |

+--------------------+

 

Each time a user visits a page a new row is added to PageVisits with the respective UserID and page url.

 

I need to list each user and the respective number of page visits they have made in the following format:

 

+---------------------------------------------------------------+

| UserID | Company | Postcode | index.html | contact.html | ... |

+---------------------------------------------------------------+

| user  | company | NW6 3AB  | 2          | 10          | ... |

+---------------------------------------------------------------+

 

Where the list of URLs is generated from the unique URLs in the pageVisits table. Pages can be added at any point so explicitly declaring these is not ideal.

 

Can any SQL whizz help me? I'm stuck!