Multiple COUNTs with different qualifiers in a single query, grouped on another field?

Hi. I was wondering if this could be done simply...

I have a table of daily work shift data that contains an ID for the worker and also a status field that indicates whether the worker logged in correctly. I want to develop a query that for a given period (say, a month) counts the number of total shifts for that worker and the number of those shifts that are incorrectly logged. So, an example result set would be:

worker ID total shifts incorrect shifts

90012 27 6

90036 28 2

Getting either count grouped on the worker ID is easy, getting them both in the same overall select statement is driving me crazy. I'm betting that there is some slick way to do this, but my newbie T-SQL status prevents me from seeing it. Can anyone help?

Thank you!!!

try this

select t.WorkerID,(select Count(WorkerID) from Tablename where [date] between @.fromdate and @.todate and WorkerID=t.WorkerID) [TotalShifts],(select Count(WorkerID) from Tablename where [date] between @.fromdate and @.todate and WorkerID=t.WorkerID and loginstatus = @.status ) [IncorrectShifts] from Tablename t group by t.WorkerID

regards

|||I love the Internet! Thank you!! You even wrote the code for me. I am very grateful! All the best!

Multiple Contain Statements

I need some help with this syntax
Select * FROM PROJECTS
WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
CONTAINS(Problem_Status, '"Open"')
Can you have multiple CONTAINS Statements?
I am getting a syntax error with the above code snippet. What am I missing?
Thanks!You have an extra WHERE clause. CONTAINS is a predicate, and you can
logically combine predicates using AND or OR; you only need one WHERE
clause:
WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
But in your case, I'm wondering if you really want to use full-text search
for your problem status column? Will the status really be more than one
word? You'd probably have much better luck using:
WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
AND Problem_Status = 'Open'
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>I need some help with this syntax
> Select * FROM PROJECTS
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
> CONTAINS(Problem_Status, '"Open"')
> Can you have multiple CONTAINS Statements?
> I am getting a syntax error with the above code snippet. What am I
> missing?
> Thanks!
>|||Thanks for your help!
"Adam Machanic" wrote:

> You have an extra WHERE clause. CONTAINS is a predicate, and you can
> logically combine predicates using AND or OR; you only need one WHERE
> clause:
> WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
> But in your case, I'm wondering if you really want to use full-text search
> for your problem status column? Will the status really be more than one
> word? You'd probably have much better luck using:
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
> AND Problem_Status = 'Open'
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>
>

multiple columns search with 'AND'

Hi
Can someone tell me why I can search multiple columns in 1 table using a
single CONTAINS with 'OR' operator but it dosn't work for 'AND' queries? I
want to be able to do 'AND' searches accross 50 odd columns in a table.
Here's an example to show you what I mean (A Full-Text index is working on
the table and includes the FirstName and LastName columns):
CREATE TABLE Users
(
UID int IDENTITY(1000,1) PRIMARY KEY,
FirstName varchar(50) DEFAULT '' NOT NULL,
LastName varchar(50) DEFAULT '' NOT NULL
)
INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John OR Smith')
/* The above gives me the resulst I expect, i.e. 2 records are returned*/
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')
/* This one dosn't return any results even though 'John' and 'Smith' clearly
exists in 1 record but different columns */
Please tell me what I'm doing wrong or what a pracical solution would be to
search 50ish columns in a table using 'AND' operator and 1 CONTAINS
pedicate. I can't find a suitable explaination in BOL
Many thanks
Andrew
this works
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John') and contains (*, 'Smith')
this doesn't
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')
i believe this doesn't work because it is searching each individual column
(firstname and lastname) for both john and smith. there are no rows that have
both john and smith in either the firstname column only or the lastname column
only. same thing as this
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, '"john smith"')
the or works because it finds rows with john or smith in the firstname column
and then finds rows with john or smith in the lastname column.
Andrew Jocelyn wrote:

> Hi
> Can someone tell me why I can search multiple columns in 1 table using a
> single CONTAINS with 'OR' operator but it dosn't work for 'AND' queries? I
> want to be able to do 'AND' searches accross 50 odd columns in a table.
> Here's an example to show you what I mean (A Full-Text index is working on
> the table and includes the FirstName and LastName columns):
> CREATE TABLE Users
> (
> UID int IDENTITY(1000,1) PRIMARY KEY,
> FirstName varchar(50) DEFAULT '' NOT NULL,
> LastName varchar(50) DEFAULT '' NOT NULL
> )
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John OR Smith')
> /* The above gives me the resulst I expect, i.e. 2 records are returned*/
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John AND Smith')
> /* This one dosn't return any results even though 'John' and 'Smith' clearly
> exists in 1 record but different columns */
> Please tell me what I'm doing wrong or what a pracical solution would be to
> search 50ish columns in a table using 'AND' operator and 1 CONTAINS
> pedicate. I can't find a suitable explaination in BOL
> Many thanks
> Andrew