Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 04:49:22

I have the following Query which counts the no. of top priority calls for all our customers:

SELECT Count(id) as pricnt from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1

I want to adjust the code so on the summary table we can make it count for the specific customer that is logged in.... i.e. Company X- can see they have 2 priority calls and Company Y can see they have 0 priority calls.

I have the session variable that logs the company that is logged on:

$_SESSION[company]

I just can't seem to add that statement to the original query to get it to work...

Any Ideas?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-23 : 05:02:35

please provide table structure what is column for company identifier.

Vabhav T

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 05:05:37

[code]
SELECT Count(id) as pricnt
, SUM(CASE WHEN faultlog.CompanyID = @MySessionCompanyID THEN 1 ELSE 0 END) AS MyCOmpanyCount
from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1
[/code]
you will need to figure out how to pass $_SESSION[company] as a parameter - depends if you are using Dynamic SQL, Stored procedures, or something else ...

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 05:14:18

P.S. you are using

Count(id)

can ID ever be NULL? - i.e. are you trying to explicitly count only the NON-NULL IDs?

If not change that to

Count(*)

which will let SQL Server choose which column to use. As you have not used [ID] column anywhere else in your query that may be faster. COUNT(*) will count all rows

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 05:14:35

quote:
Originally posted by vaibhavktiwari83

please provide table structure what is column for company identifier.

Vabhav T

It's not a table....
Its just a session variable that is logged when the user logs in...

$result = mssql_fetch_array(mssql_query("SELECT id,(forename + ' ' + surname) as contact,company FROM contact WHERE username='" . $_POST['user'] . "' AND PWDCOMPARE('" . $_POST['pass'] . "',password)=1",$db));

      //Check if the user is a customer
      if ($result!=NULL){
      $_SESSION['user'] = $result['contact'];
      $_SESSION['company'] = $result['company'];
      $_SESSION['guid'] = $result['id'];
      $_SESSION['level']=1;
      $_SESSION['rights']=3;
      $_SESSION['pfx'] = "C" . date('Y');
      $_SESSION['conts'] = array ($result['id']);

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 05:16:36

quote:
Originally posted by Kristen

SELECT Count(id) as pricnt
, SUM(CASE WHEN faultlog.CompanyID = @MySessionCompanyID THEN 1 ELSE 0 END) AS MyCOmpanyCount
from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1

you will need to figure out how to pass $_SESSION[company] as a parameter - depends if you are using Dynamic SQL, Stored procedures, or something else ...

how would you normally pass something as a parameter?

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 05:25:11

I have no clue about PHP. Something like this I guess:

$result = mssql_fetch_array(mssql_query("SELECT Count(id) as pricnt , SUM(CASE WHEN faultlog.CompanyID = '" . $_SESSION[company] . "' THEN 1 ELSE 0 END) AS MyCompanyCount
from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1",$db));

You'll have to sort out what the correct CompanyID column is. And make sure that $_SESSION[company] is handled properly to prevent SQL Injection (again, not something I know how to do in PHP)

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 06:14:50

This is my original query:

$query = mssql_query("SELECT Count(id) as pricnt from faultlog where (queueid=1 or queueid=2) and status<15 and priority=1", $db);

$PriorityCount=mssql_result($query,0,'pricnt');

I need to add the

$_SESSION[company]

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 06:27:26

Can you not insert the piece of code from my post into your query?

If you don't understand some part of it please ask, but I'm here as a volunteer not to do your work for you ... it is important to me that you understand what my suggested code is supposed to do rather than blindy cut & paste something I have created - after all, I have no idea what the rest of your system does and I may answer your question correctly, but with a solution that is totally inappropriate, or in some way insecure, for the rest of your system.

As I say, if there is some part of my suggestion that you don't understand please ask.

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 06:34:59

quote:
Originally posted by Kristen

Can you not insert the piece of code from my post into your query?

If you don't understand some part of it please ask, but I'm here as a volunteer not to do your work for you ... it is important to me that you understand what my suggested code is supposed to do rather than blindy cut & paste something I have created - after all, I have no idea what the rest of your system does and I may answer your question correctly, but with a solution that is totally inappropriate, or in some way insecure, for the rest of your system.

As I say, if there is some part of my suggestion that you don't understand please ask.

I know you can't give me the answer....
Just frustrating as i have had this problem for ages and haven't solved it.
The guy who i would normally ask in the office is always on site to clients and this code is driving me nuts!!

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 06:44:34

I think I have given you the answer - what I don't know is which bit you don't understand.

If you need the code written for you then you need your programmer.

At this point you haven't said what the column name is for Company ID in faultlog - or if some other table will be needed to
connect to that. If that's not in your skill-set then you are going to need to get help from the guy who is away! to find that out.

If you don't know how to edit the PHP code to change the syntax to include the SESSION variable I would leave it alone
- you run the risk of introducing a SQL Injection threat.

Jay87
Starting Member

41 Posts

Posted - 2010-02-23 : 07:01:50

quote:
Originally posted by Kristen

I think I have given you the answer - what I don't know is which bit you don't understand.

If you need the code written for you then you need your programmer.

At this point you haven't said what the column name is for Company ID in faultlog - or if some other table will be needed to
connect to that. If that's not in your skill-set then you are going to need to get help from the guy who is away! to find that out.

If you don't know how to edit the PHP code to change the syntax to include the SESSION variable I would leave it alone
- you run the risk of introducing a SQL Injection threat.

I am currently using a 'dummy' helpdesk so i can break it whist experimenting... I am only on placement so this is all quite new to me as you can tell.

There is a company field in the faultlog table which is varchar but no unique identifier (i.e. company ID)

I changed the CompanyID to company to match this but the number it returned was the total of priority calls for all companies and not unique to that customer.

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 09:16:17

Sounds like you have to JOIN faultlog to some other table to get the company ID (that matches what you have in your Session variable)


SELECT TOP 100 DISTINCT company
FROM faultlog

will show you the sort of values being stored in company column in the faultlog table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources