May 20, 2011 at 9:30 am
So, basically, have this ticketing system and need a script to return records (count) by shift. Say, mydttime is the col that has datetime.
They are 12-8, 8-4, 4-12 (8 hour shifts). Say, tickets by shift.
Need to specify date range as well. So output should look like:
count Shift
----------------
10 Shift1
123 Shift2
78 Shift3
TIA!
May 20, 2011 at 9:48 am
sql_jr (5/20/2011)
So, basically, have this ticketing system and need a script to return records (count) by shift. Say, mydttime is the col that has datetime.They are 12-8, 8-4, 4-12 (8 hour shifts). Say, tickets by shift.
Need to specify date range as well. So output should look like:
count Shift
----------------
10 Shift1
123 Shift2
78 Shift3
TIA!
You may wanto to provide more information as specified in the followig URK:
http://www.sqlservercentral.com/articles/Best+Practices/61537[/URL]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 9:55 am
If I was sure how to go about this, I would have posted my code. I don't have any b/c I need help. I figured this is a common thing, so an experienced tsql expert would give me something lile
select count(*),
case
when mydttime='????' then shift1
when mydttime='????' then shift2
when mydttime='????' then shift3
end AS Shift
group by Shift
Can you fill in my missing details? Thanks
May 20, 2011 at 10:04 am
sql_jr (5/20/2011)
select count(*),case
when mydttime='????' then shift1
when mydttime='????' then shift2
when mydttime='????' then shift3
end AS Shift
group by Shift
One thing I noticed is don't you want to use the Datepart Function to get the Hour and specify the between operator to get the hour?
You are only listing the Count, not the shit in your output. You could get the count via a subquery.
http://www.sqlservercentral.com/articles/Best+Practices/61537
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 10:19 am
No, I am totally open to using datepart. Show me how.
In my ex, I do get shift. (by name if not time)
Please advise
May 20, 2011 at 10:28 am
sql_jr (5/20/2011)
No, I am totally open to using datepart. Show me how.In my ex, I do get shift. (by name if not time)
Please advise
The following article should give you the information that you need as well as for future reference:
http://www.technoreader.com/SQL-server-Date-Time-Function.aspx
DECLARE @CurrentDateTime DateTime
DECLARE @Hour int
SET @CurrentDateTime = GETDATE()
SET @Hour = (SELECT DATEPART(hour, @CurrentDateTime))
SELECT @CurrentDateTime AS CurrentDateTime, @Hour AS Hour
CurrentDateTime Hour
----------------------- -----------
2011-05-20 12:28:01.607 12
(1 row(s) affected)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 10:34 am
sql_jr (5/20/2011)
If I was sure how to go about this, I would have posted my code. I don't have any b/c I need help. I figured this is a common thing, so an experienced tsql expert would give me something lileselect count(*),
case
when mydttime='????' then shift1
when mydttime='????' then shift2
when mydttime='????' then shift3
end AS Shift
group by Shift
Can you fill in my missing details? Thanks
This is really not difficult from a t-sql perspective but we don't have any details about what you are trying to do. Like in the article you need to provide ddl and some sample data in the way of inserts and desired output. We can make assumptions about your data but unless you give us some details we are guessing. It wasn't until your reply that we even knew the column name where the date even resided. Is this field a datetime? date? time? varchar? We don't know. If you want solid, tested code that works you have to do a little of the legwork to help us help you.
_______________________________________________________________
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/
May 20, 2011 at 2:40 pm
Wes, the script is interesting. I'm trying this, but not working:
Select COUNT(Tickets),
CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 8 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 16 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 24 THEN 'SHIFT 3'
END
FROM MyTable
GROUP BY ??? (SHIFT)
Help!
May 20, 2011 at 2:42 pm
sql_jr (5/20/2011)
Wes, the script is interesting. I'm trying this, but not working:Select COUNT(Tickets),
CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 8 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 16 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 24 THEN 'SHIFT 3'
END
FROM MyTable
GROUP BY ??? (SHIFT)
Help!
Select COUNT(Tickets),
CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 8 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 16 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 24 THEN 'SHIFT 3'
END
FROM MyTable
GROUP BY CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 8 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 16 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 24 THEN 'SHIFT 3'
END
_______________________________________________________________
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/
May 20, 2011 at 2:53 pm
I tried that before, but only got one "SHIFT". I was doing it on a test, and realized my data was messed up. BUT, THAT'S IT! THANKS!.
Only thing is I can't us 'between', guess I should use >= to get the outlyers. THX!
May 20, 2011 at 3:17 pm
sql_jr (5/20/2011)
I tried that before, but only got one "SHIFT". I was doing it on a test, and realized my data was messed up. BUT, THAT'S IT! THANKS!.Only thing is I can't us 'between', guess I should use >= to get the outlyers. THX!
You should be able to use between. I think you just have your numbers a little off.
Select COUNT(Tickets),
CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 7 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 15 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 23 THEN 'SHIFT 3'
END
FROM MyTable
GROUP BY CASE WHEN DATEPART(hour,(CrDate)) between 0 AND 7 THEN 'SHIFT 1'
WHEN DATEPART(hour,(CrDate)) between 8 AND 15 THEN 'SHIFT 2'
WHEN DATEPART(hour,(CrDate)) between 16 AND 23 THEN 'SHIFT 3'
END
The between is inclusive so the first case will get any values of 0, 1, 2, 3, 4, 5, 6, 7. Hope that helps.
_______________________________________________________________
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/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply