March 28, 2011 at 12:33 pm
Hi All,
I am a C# developer who is learning T-SQL as I go. There may be a better way to do what I'm trying to do, so I respectfully ask for any ideas that you might have.
I am working to create a table-valued function on Microsoft SQL Server 2005. I have a table called "Events", which holds all the information about events that someone might get paged to. The table holds information about the event itself, as well as the person assigned to handle the event, and the timestamps related to that person's involvement. (When they acknowledged receipt of the event, and when they completed the event.)
What I'm trying to do is create output that would show me how many events were generated throughout the course of a user-specified period of time, within the 3 different daily shifts (1st shift, 7-3, 2nd shift, 3-11, and 3rd shift, 11-7).
What I was thinking about doing was accepting two parameters into the function, a start date, and an end date, and then iterate, using the start date as a sort of loop control variable, until I hit the end date. Within the loop, I'd run a SELECT COUNT(*) statement on the events table for a count of events that happenned on that day between the times of 7-3, 3-11, and then 11-7. That all works okay, but it returns a number of tables (with the correct data) equal to the number of days in the date range I specified. What I want is one table.
What I get:
*******7-3****3-11****11-7****
Day 1 2 9 6 *
****************************
*******7-3****3-11****11-7****
Day 2 6 4 8 *
****************************
*******7-3****3-11****11-7****
Day 3 9 1 3 *
****************************
What I'd like:
*******7-3****3-11****11-7****
Day 1 2 9 6 *
Day 2 6 4 8 *
Day 3 9 1 3 *
****************************
Anyone who has any ideas for me, or if you have another way you think would be better to approach this task with, I'm all ears. My existing code is below. Thanks!
DECLARE @endDate datetime;
SET @endDate = current_timestamp;
DECLARE @currentDate datetime;
SET @currentDate = '2011-02-01 00:00:00.000';
WHILE @currentDate < @endDate
BEGIN
SELECT
firstOrderly.firstShiftOrderlyRequests,
firstTransport.firstShiftTransportRequests
FROM
(SELECT
COUNT (*) AS 'firstShiftOrderlyRequests'
FROM
[Events]
WHERE
([type] = 2 OR [type] = 3 OR [type] = 4 OR [type] = 6 OR [type] = 7)
AND
class = 4
AND
timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 06:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 14:29:00.000') AND timestampCompleted != timestampOpen
)firstOrderly,
(SELECT
COUNT (*) AS 'firstShiftTransportRequests'
FROM
[Events]
WHERE
[type] = 1
AND
class = 4
AND
timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 06:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 14:29:00.000') AND timestampCompleted != timestampOpen
)firstTransport
;
SET @currentDate = DATEADD(day, 1, @currentDate);
END
March 28, 2011 at 3:01 pm
Without knowing exactly what you are doing, have you considered using your existing T-SQL to insert the results of each T-SQL statement into a temp table, And then doing a single select from the temp table using a pivot ?
March 28, 2011 at 3:04 pm
Here's where my inexperience comes in. What is a pivot?
March 28, 2011 at 3:26 pm
From BOL Boks On Line
The following is annotated syntax for PIVOT.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Basic PIVOT Example
The following code example produces a two-column table that has four rows.
Copy Code
USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Here is the result set.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
No products are defined with three DaysToManufacture.
The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3] days, even though the results are NULL.
Copy Code
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Here is the result set.
Cost_Sorted_By_Production_Days 0 1 2 3 4
Averagecost 5.0885 223.88 359.1082 NULL 949.4105
Please pardon my display of the result, they are a #$% problem to display properly.
March 29, 2011 at 6:35 am
Oh, I get it! You're literally pivoting the table, turning the rows into columns and the columns into rows. That is a neat trick! I think that might work for what I want to do. I will try it. Thanks!
March 29, 2011 at 2:48 pm
I'm not having any luck.
Maybe I should explain better what I'm trying to do...
I receive a start date and an end date from a user.
I need to select everything from my Events table between those two dates, but seperated out into the three different shifts. So, here's som pseudo code that might better illustrate my goal:
--This would be passed into the function by the user
DECLARE @startDate datetime;
SET @startDate = '2011-03-01 00:00:00.000';
--This would also be passed into the function by the user
DECLARE @endDate datetime;
SET @endDate = '2011-03-05 00:00:00.000';
--The table-valued function would return something sort of like this, if it can be done
SELECT
COUNT (*) AS 'firstShiftOrderlyRequests'
FROM
[Events]
WHERE
([type] = 2 OR [type] = 3 OR [type] = 4 OR [type] = 6 OR [type] = 7)
AND
class = 4
AND
[date part of timestamp of event] BETWEEN (@startDate AND @endDate)
AND
[time part of timestamp of event] BETWEEN ('06:30:00.000') AND ('15:00:00.000')
AND
timestampCompleted != timestampOpen
;
March 29, 2011 at 3:33 pm
Do you want a count of each shift?
You have not provided a table structure, sample data and expected results. If you did we wouldn't have to guess. Here's a guess.
This assumes you have a ShiftType field with 3 shift types. Example morning, afternoon and night.
SELECT
ShiftType,
Count(type)
FROM YourTable
GROUP BY ShiftType
Then you would get results like:
morning,23
afternoon,65
night,67
Or are you going to group into your three shifts based on a date time field?
March 29, 2011 at 3:42 pm
Does this help?
CREATE TABLE Shifts
(
ShiftStart datetime
)
INSERT INTO Shifts
SELECT '2011-02-01 01:10:00.000' UNION
SELECT '2011-02-01 01:20:00.000' UNION
SELECT '2011-02-01 01:30:00.000' UNION
SELECT '2011-02-01 13:10:00.000' UNION
SELECT '2011-02-01 13:20:00.000' UNION
SELECT '2011-02-01 13:30:00.000' UNION
SELECT '2011-02-01 13:40:00.000' UNION
SELECT '2011-02-01 13:50:00.000' UNION
SELECT '2011-02-01 13:55:00.000' UNION
SELECT '2011-02-01 23:10:00.000'
SELECT
CASE
WHEN DATEPART(hour, ShiftStart)< 12 THEN 'Early'
WHEN DATEPART(hour, ShiftStart)> 18 THEN 'Late'
ELSE 'Middle'
END AS Shift,
COUNT(ShiftStart)
FROM Shifts
GROUP BY
CASE
WHEN DATEPART(hour, ShiftStart)< 12 THEN 'Early'
WHEN DATEPART(hour, ShiftStart)> 18 THEN 'Late'
ELSE 'Middle'
END
DROP TABLE Shifts
March 29, 2011 at 3:43 pm
pdonley (3/29/2011)
I'm not having any luck.;
Just a suggestion... If you want some really good help, study the article at the first link in my signature line below. Post simple table structure and readily consumable test data in that format and you'll very likely get some pretty good coded answers that hit the nail squarely on the head.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2011 at 3:44 pm
And I see that Chrissy beat me to it while I was typing...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2011 at 4:17 pm
It helps. Can I ask a question? Why a table valued function? How specifically are you planning on using this data?
The query you want (as a proc) will look a lot like this (assuming I understood all the nuts and bolts). Let's nail it down this way, then we'll modify it to the function that you need. There's a couple of comments in the code and I rebuilt a bit of your DDL and data to have multiple days, and changed the name of it to Events (since Shifts implies, to me, the definition of the first, middle, and late shifts. 🙂 ).
USE [TEST] --Choose what DB you actually have here.
GO
IF OBJECT_ID('Events') IS NOT NULL
DROP TABLE Events
CREATE TABLE Events
(EventTime datetime )
INSERT INTO Events
SELECT '2011-02-01 01:10:00.000' UNION
SELECT '2011-02-01 01:20:00.000' UNION
SELECT '2011-02-01 01:30:00.000' UNION
SELECT '2011-02-01 13:10:00.000' UNION
SELECT '2011-02-01 13:20:00.000' UNION
SELECT '2011-02-01 13:30:00.000' UNION
SELECT '2011-02-01 13:40:00.000' UNION
SELECT '2011-02-01 13:50:00.000' UNION
SELECT '2011-02-01 13:55:00.000' UNION
SELECT '2011-02-02 01:10:00.000' UNION
SELECT '2011-02-02 01:20:00.000' UNION
SELECT '2011-02-02 01:30:00.000' UNION
SELECT '2011-02-02 13:10:00.000' UNION
SELECT '2011-02-02 13:20:00.000' UNION
SELECT '2011-02-02 13:30:00.000' UNION
SELECT '2011-02-02 13:40:00.000' UNION
SELECT '2011-02-02 13:50:00.000' UNION
SELECT '2011-02-02 13:55:00.000' UNION
SELECT '2011-02-03 01:10:00.000' UNION
SELECT '2011-02-03 01:20:00.000' UNION
SELECT '2011-02-03 01:30:00.000' UNION
SELECT '2011-02-03 13:10:00.000' UNION
SELECT '2011-02-03 13:20:00.000' UNION
SELECT '2011-02-03 13:30:00.000' UNION
SELECT '2011-02-03 13:40:00.000' UNION
SELECT '2011-02-03 13:50:00.000' UNION
SELECT '2011-02-03 13:55:00.000' UNION
SELECT '2011-02-03 23:10:00.000'
GO
/****** Object: StoredProcedure [dbo].[ShiftsShowByDateRange] Script Date: 03/29/2011 15:11:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShiftsShowByDateRange]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ShiftsShowByDateRange]
GO
CREATE PROC ShiftsShowByDateRange (@StartDate DATETIME, @EndDate DATETIME)
AS
SELECT
'Day ' + CONVERT( VARCHAR(5), DayNumber) AS DayNumber,
--Poor man's pivot
SUM( CASE ShiftAssociation WHEN 'First' THEN 1 ELSE 0 END) AS FirstShiftEventCount,
SUM( CASE ShiftAssociation WHEN 'Middle' THEN 1 ELSE 0 END) AS MiddleShiftEventCount,
SUM( CASE ShiftAssociation WHEN 'Late' THEN 1 ELSE 0 END) AS LateShiftEventCount
FROM
(SELECT
DATEDIFF( dd, @startDate, EventTime) + 1 AS DayNumber,
CASE WHEN datepart( hh, EventTime) BETWEEN 7 AND 15
THEN 'Middle'
WHEN datepart(hh, EventTime) BETWEEN 16 AND 23
THEN 'Late'
ELSE 'First' -- Use the one that crosses days as the odd man out
END AS ShiftAssociation
FROM
Events AS e
WHERE
e.EventTime between @startdate and @enddate
) AS drv
GROUP BY
DayNumber
GO
EXEC ShiftsShowByDateRange '20110201', '20110203 23:59:59.999'
I'm sure you'll have questions, but first let's see if we can confirm this hits the results you were looking for?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 29, 2011 at 5:00 pm
Actually the late, middle and early sample data was not from the original poster. I was just trying to use my intuitive powers to provide some code that might be useful to the original poster.
This did provide me with a valuable learning experience since I can compare my somewhat amateurish code with your polished example.:-)
March 29, 2011 at 5:44 pm
Chrissy321 (3/29/2011)
Actually the late, middle and early sample data was not from the original poster. I was just trying to use my intuitive powers to provide some code that might be useful to the original poster.This did provide me with a valuable learning experience since I can compare my somewhat amateurish code with your polished example.:-)
Heheh, whoops. Well, hopefully it'll help show him the value of the sample data/DDL. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 30, 2011 at 6:52 am
Okay. I am thoroughly confused. I'm trying to do what, in my mind, should be a very simple task, but it always seems to be the simplest tasks that are the hardest to code. 🙂
Here is a schema of my table, scaled down to the fields that matter for this question.
CREATE TABLE [Events] (id int, timestamp datetime)
Each row of this table represents a different event. Each event has an ID and a timestamp in the datetime format.
What I want to do is pull a list out of this table with the COUNTS of events that occured within a specified date range, grouped by day and shift. I am only choosing a table valued function because I thought that was the best way to do it, but if it isn't, I'm open to suggestion. Like I mentioned, I am a C# coder, not really much with the SQL side of things. What I'm trying to do is build something in SQL so I can have a very clean SQL statement to send from my application, have the database (which is on a much, much more powerful server in my environment), do the "dirty work", and then send the data cleanly back to my application. It just helps to keep my C# application code as clean as possible. So, that was my motivation for usign the table-valued function in the first place.
So, here's some sample data:
id timestamp
0 2011-03-01 8:00:00.000
1 2011-03-01 9:30:00.000
2 2011-03-01 10:00:00.000
3 2011-03-01 17:00:00.000
4 2011-03-01 23:00.00.000
5 2011-03-02 9:00:00.000
6 2011-03-02 14:00:00.000
7 2011-03-02 18:00:00.000
So, let's say the user entered a start date of March 1st, and an end date of March 3rd (these would be my parameters). What I'd like to return to them would be this:
Day 1st shift 2nd Shift 3rd Shift
3/1 3 1 1
3/2 2 1 0
3/3 0 0 0
First shift is between 7:00 and 15:00, second shift is between 15:00 and 22:00, and third shift is between 22:00 and 7:00
Make sense?
March 30, 2011 at 12:08 pm
Okay. I have a solution now that is at least working, but it's really...well...clunky. It creates a table, loops the select statement to find the values, inserts them into that table, when the loop is done, it selects everything out of that table to return to my application, and then drops the table. It works but, I still feel like there's a better way...(this is the exact code below)
USE [BedManagement_1_16]
GO
/****** Object: StoredProcedure [dbo].[supportiveServicesMonthlyReport] Script Date: 03/30/2011 14:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[supportiveServicesMonthlyReport]
@startDate datetime,
@endDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @currentDate datetime;
SET @currentDate = @startDate;
DECLARE @firstOrderly int;
DECLARE @firstTransport int;
DECLARE @secondOrderly int;
DECLARE @secondTransport int;
DECLARE @thirdOrderly int;
DECLARE @thirdTransport int;
CREATE TABLE supportiveServicesMonthlyReportTemporaryTable (
[day] datetime,
firstTransport int,
firstOrderly int,
secondTransport int,
secondOrderly int,
thirdTransport int,
thirdOrderly int
)
WHILE @currentDate < @endDate
BEGIN
SET @firstOrderly = (SELECT COUNT (*) AS 'firstShiftOrderlyRequests' FROM [Events] WHERE ([type] = 2 OR [type] = 3 OR [type] = 4 OR [type] = 6 OR [type] = 7) AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 06:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 14:29:00.000') AND timestampCompleted != timestampOpen);
SET @firstTransport = (SELECT COUNT (*) AS 'firstShiftTransportRequests' FROM [Events] WHERE [type] = 1 AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 06:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 14:29:00.000') AND timestampCompleted != timestampOpen);
SET @secondOrderly = (SELECT COUNT (*) AS 'secondShiftOrderlyRequests' FROM [Events] WHERE ([type] = 2 OR [type] = 3 OR [type] = 4 OR [type] = 6 OR [type] = 7) AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 14:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 22:29:00.000') AND timestampCompleted != timestampOpen);
SET @secondTransport = (SELECT COUNT (*) AS 'secondShiftTransportRequests' FROM [Events] WHERE [type] = 1 AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 14:30:00.000') AND (CONVERT(varchar(8), @currentDate, 112) + ' 22:29:00.000') AND timestampCompleted != timestampOpen);
SET @thirdOrderly = (SELECT COUNT (*) AS 'thirdShiftOrderlyRequests' FROM [Events] WHERE ([type] = 2 OR [type] = 3 OR [type] = 4 OR [type] = 6 OR [type] = 7) AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 22:30:00.000') AND (CONVERT(varchar(8), DATEADD(day, 1, @currentDate), 112) + ' 06:29:00.000') AND timestampCompleted != timestampOpen);
SET @thirdTransport = (SELECT COUNT (*) AS 'thirdShiftTransportRequests' FROM [Events] WHERE [type] = 1 AND class = 4 AND timestampCompleted BETWEEN (CONVERT(varchar(8), @currentDate, 112) + ' 22:30:00.000') AND (CONVERT(varchar(8), DATEADD(day, 1, @currentDate), 112) + ' 06:29:00.000') AND timestampCompleted != timestampOpen);
INSERT INTO supportiveServicesMonthlyReportTemporaryTable VALUES (@currentDate, @firstOrderly, @firstTransport, @secondOrderly, @secondTransport, @thirdOrderly, @thirdTransport);
SET @currentDate = DATEADD(day, 1, @currentDate);
END
SELECT * FROM [dbo].[supportiveServicesMonthlyReportTemporaryTable];
DROP TABLE [dbo].[supportiveServicesMonthlyReportTemporaryTable];
END
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply