January 20, 2012 at 6:22 am
I was wondering how easy it would be to count occurances across multiple fields? I've a table made of several fields.
date Jim Dave Tony Mike .....
01/Dec/2011 In In out In
03/Dec/2011 In out out In
04/Dec/2011 out in out In
05/Dec/2011 out in in In
06/Dec/2011 In in in In
07/Dec/2011 out in in In
As per the table above I'd like to be able to pass in 2 dates ie and a search term 03/Dec/2011,06/Dec2011,'In' and get a result like
Jim 2
Dave 3
Tony 2
Mike 4
I had asked on here about a way of searching across fields and got some brilliant results, I've tried adapting the solutions I recieved without any luck. I've included a script for a test table if there any kind souls out there who could possibly help
GO
-- Creating Test Table
CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('01/Dec/2011','In','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('03/Dec/2011','In','Out','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('04/Dec/2011','Out','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('05/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('06/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('07/Dec/2011','Out','In','In','In')
January 20, 2012 at 8:27 am
If at all possible you should normalize that table. You don't want to have columns for each person. You should probably have two tables for this. One table is your Timeclock table with a foreign key to your employee table. The employee table has your people.
If that is not possible you would have to do a count(*) and union to each column.
The following will get your desired results...but...
select 'Jim', COUNT(*) from testtable where Jim = 'In'
union all
select 'Dave', COUNT(*) from testtable where Dave = 'In'
union all
select 'Tony', COUNT(*) from testtable where Tony = 'In'
union all
select 'Mike', COUNT(*) from testtable where Mike = 'In'
Notice how there is nothing dynamic in here? It all has to be hard coded because of the table structure. When a person comes or goes you will have alter your table and rebuild your query. Not exactly the best approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 8:32 am
think this works...its horrible....and your data doesnt match your expected results (Jim)
the question is why are you storing your data this way (excel as source ???)
WITH CTE
AS ( SELECT DATE ,
JIM AS DATA ,
'Jim' AS PERSON
FROM TESTTABLE
WHERE(JIM = 'in')
UNION ALL
SELECT DATE ,
DAVE AS DATA ,
'dave' AS PERSON
FROM TESTTABLE
WHERE(DAVE = 'in')
UNION ALL
SELECT DATE ,
TONY AS DATA ,
'tony' AS PERSON
FROM TESTTABLE
WHERE(TONY = 'in')
UNION ALL
SELECT DATE ,
MIKE AS DATA ,
'mike' AS PERSON
FROM TESTTABLE
WHERE(MIKE = 'in'))
SELECT PERSON ,
COUNT( * )AS CNT
FROM CTE
WHERE(DATE >= CONVERT(datetime , '2011-12-03 00:00:00' , 102))
AND (DATE <= CONVERT(datetime , '2011-12-06 00:00:00' , 102))
GROUP BY PERSON;
this could also be coded using "CROSS APPLY".
The main problem you are going to get is where your list of "names"...currently set as individual columns...grows.....take for example how you would go about this is you had 400 hunderd people to track 😉
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 20, 2012 at 8:41 am
both examples look good to me, is there a way of making that dynamic? the amount of names are 10 in number at present and although that could change the maximum will only be about 15 people or so, although the names could change hence my question about dynamic. Thanks for answering my query.
January 20, 2012 at 8:56 am
can you please explain the business reason why cannot you store your data as below
this will treat you better long term, rather than someone conjuring up some dynamic code for you now, that may well require further mods as your requirements change.
DATE DATA PERSON
----------------------- -------------------- ------
2011-12-01 00:00:00.000 In dave
2011-12-01 00:00:00.000 In Jim
2011-12-01 00:00:00.000 In mike
2011-12-03 00:00:00.000 In mike
2011-12-03 00:00:00.000 In Jim
2011-12-04 00:00:00.000 In dave
2011-12-04 00:00:00.000 In mike
2011-12-05 00:00:00.000 In mike
2011-12-05 00:00:00.000 In dave
2011-12-05 00:00:00.000 In tony
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 20, 2012 at 8:57 am
mick burden (1/20/2012)
both examples look good to me, is there a way of making that dynamic? the amount of names are 10 in number at present and although that could change the maximum will only be about 15 people or so, although the names could change hence my question about dynamic. Thanks for answering my query.
The only way to change this in a way that works better is to restructure your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 8:57 am
With the sample data & DDL that you shared with us, it’s difficult to come up with good solution. I agree with above recommendation & join them to suggest you to normalize your data.
Union or even the dynamic query (if we arrive at one) will be poor performing solutions.
January 20, 2012 at 8:58 am
The table is part of a larger table and is used for other things, I just want to be able extract the piece of data I explained in my original post
January 20, 2012 at 9:02 am
Well if you can't change the structure then you will have to use one of the ways presented. They are both management sinkholes. You are going to constantly be fighting this due to the poor data structure. The only thing you could do would be to use dynamic sql and query sys.syscolumns and build a dynamic sql string. This is probably going to perform worse but given the small amount of data and columns you suggest it might not be too bad.
You said this is part of a bigger table? Maybe you can query that table to populate a temp table with the structure that JLivingstonSQL suggested. Then you can query against your temp table and it would be easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 9:07 am
mick burden (1/20/2012)
The table is part of a larger table and is used for other things, I just want to be able extract the piece of data I explained in my original post
sorry Mick...but I am struggling to visualise your "larger table".
What happens when a new person joins or exg person leaves...do you add / delete columns accordingly?
trying to be helpful (but failing miserably me thinks 🙂 )
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 20, 2012 at 9:13 am
I'm going to go and be a little different, because I always am 😀
Here's the query I'd use:
SELECT [name], [count]
FROM (SELECT
SUM(CASE WHEN jim = 'In' THEN 1 ELSE 0 END) AS jim,
SUM(CASE WHEN dave = 'In' THEN 1 ELSE 0 END) AS dave,
SUM(CASE WHEN tony = 'In' THEN 1 ELSE 0 END) AS tony,
SUM(CASE WHEN mike = 'In' THEN 1 ELSE 0 END) AS mike
FROM testtable) innerQuery
UNPIVOT ([count] FOR [name] IN (jim,dave,tony,mike))AS pvt
If we don't know the names before hand, then we could do it like this:
CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('01/Dec/2011','In','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('03/Dec/2011','In','Out','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('04/Dec/2011','Out','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('05/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('06/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('07/Dec/2011','Out','In','In','In')
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = COALESCE(@sql,'') + ',' + CHAR(13) + CHAR(10) + 'SUM(CASE WHEN '+ name +' = ''In'' THEN 1 ELSE 0 END) AS '+ QUOTENAME(name)
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
SELECT @sql = 'FROM (SELECT ' + CHAR(13) + CHAR(10) + STUFF(@sql,1,3,'') + ' FROM testtable) innerQuery'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'UNPIVOT ([count] FOR [name] IN ('+ STUFF((SELECT ',' + name
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
FOR XML PATH('')), 1, 1, '') + '))AS pvt'
SELECT @sql = 'SELECT [name], [count] ' + CHAR(13) + CHAR(10) + @sql
PRINT @sql
EXEC sp_executesql @sql
January 20, 2012 at 9:14 am
I do just delete or add columns, or sometimes I simply rename a column, although if this happens once or twice a year it's a lot
January 20, 2012 at 9:21 am
Cadavre (1/20/2012)
I'm going to go and be a little different, because I always am 😀Here's the query I'd use:
SELECT [name], [count]FROM (SELECT
SUM(CASE WHEN jim = 'In' THEN 1 ELSE 0 END) AS jim,
SUM(CASE WHEN dave = 'In' THEN 1 ELSE 0 END) AS dave,
SUM(CASE WHEN tony = 'In' THEN 1 ELSE 0 END) AS tony,
SUM(CASE WHEN mike = 'In' THEN 1 ELSE 0 END) AS mike
FROM testtable) innerQuery
UNPIVOT ([count] FOR [name] IN (jim,dave,tony,mike))AS pvt
If we don't know the names before hand, then we could do it like this:
CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike VARCHAR(20))GO
-- Inserting Data into Table
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('01/Dec/2011','In','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('03/Dec/2011','In','Out','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('04/Dec/2011','Out','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('05/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('06/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('07/Dec/2011','Out','In','In','In')
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = COALESCE(@sql,'') + ',' + CHAR(13) + CHAR(10) + 'SUM(CASE WHEN '+ name +' = ''In'' THEN 1 ELSE 0 END) AS '+ QUOTENAME(name)
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
SELECT @sql = 'FROM (SELECT ' + CHAR(13) + CHAR(10) + STUFF(@sql,1,3,'') + ' FROM testtable) innerQuery'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'UNPIVOT ([count] FOR [name] IN ('+ STUFF((SELECT ',' + name
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
FOR XML PATH('')), 1, 1, '') + '))AS pvt'
SELECT @sql = 'SELECT [name], [count] ' + CHAR(13) + CHAR(10) + @sql
PRINT @sql
EXEC sp_executesql @sql
Whoops, missed the date part.
BEGIN TRAN
CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('01/Dec/2011','In','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('03/Dec/2011','In','Out','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('04/Dec/2011','Out','In','Out','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('05/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('06/Dec/2011','Out','In','In','In')
INSERT INTO testtable (date,jim,dave,tony,mike)
VALUES('07/Dec/2011','Out','In','In','In')
--Static Version
SELECT [name], [count]
FROM (SELECT
SUM(CASE WHEN jim = 'In' THEN 1 ELSE 0 END) AS jim,
SUM(CASE WHEN dave = 'In' THEN 1 ELSE 0 END) AS dave,
SUM(CASE WHEN tony = 'In' THEN 1 ELSE 0 END) AS tony,
SUM(CASE WHEN mike = 'In' THEN 1 ELSE 0 END) AS mike
FROM testtable
WHERE date >= '01/Dec/2011' AND date <= '07/Dec/2011') innerQuery
UNPIVOT ([count] FOR [name] IN (jim,dave,tony,mike))AS pvt
--Dynamic Version
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = COALESCE(@sql,'') + ',' + CHAR(13) + CHAR(10) + 'SUM(CASE WHEN '+ name +' = ''In'' THEN 1 ELSE 0 END) AS '+ QUOTENAME(name)
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
SELECT @sql = 'FROM (SELECT ' + CHAR(13) + CHAR(10) + STUFF(@sql,1,3,'') + ' FROM testtable'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'WHERE date >= ''01/Dec/2011'' AND date <= ''07/Dec/2011'') innerQuery'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'UNPIVOT ([count] FOR [name] IN ('+ STUFF((SELECT ',' + name
FROM sys.columns
WHERE object_id=object_id('testtable') AND name <> 'date'
FOR XML PATH('')), 1, 1, '') + '))AS pvt'
SELECT @sql = 'SELECT [name], [count] ' + CHAR(13) + CHAR(10) + @sql
PRINT @sql
EXEC sp_executesql @sql
ROLLBACK
January 23, 2012 at 1:03 am
thanks for the various solutions from you guys, I've tried the last piece of code 'as is' and got the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
but it still produced results
January 23, 2012 at 1:06 am
EDIT
please excuse me, I've gone back over the code and discovered a typo caused by me. Everything appears ok, thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply