January 29, 2013 at 11:44 am
CREATE TABLE [t_Appointment_TimeStamp](
[ApptTimestampID] [int] IDENTITY(1,1) NOT NULL,
[ApptID] [int] NULL,
[TimeStampId] [int] NULL,
[TimeStamp] [time](7) NULL,
CONSTRAINT [PK_ApptTimestampID] PRIMARY KEY CLUSTERED
(
[ApptTimestampID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [t_Appointment_TimeStamp]
([ApptID]
,[TimeStampId]
,[TimeStamp]
)
VALUES
(1442640,1,'13:10:00.0000000'),
(1442640,2,'13:23:00.0000000'),
(1442640,3,'13:26:00.0000000'),
(1442763,1,'14:45:00.0000000'),
(1442763,2,'14:50:00.0000000'),
(1443049,1,'10:00:00.0000000'),
(1443049,2,'10:11:00.0000000'),
(1443067,1,'11:00:00.0000000'),
(1443067,2,'10:55:00.0000000'),
(1443067,3,'10:55:00.0000000')
Go
CREATE TABLE [t_ApptDuration_Monthly](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ApptTimeStampFromID] [int] NULL,
[ApptTimeStampToID] [int] NULL,
[NumberOfAppts] [int] NULL,
[TotalDuration] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
Above have 2 table script and sample data for table t_Appointment_TimeStamp.
I want aggregate data into t_ApptDuration_Monthly table
Below is my sample Stored Procedure which I created but don't know How to calculate sum Timestamp field for TotalDuration column.
CREATE PROCEDURE [p_ApptDuration_Monthly_Populate]
AS
BEGIN
Declare @ApptTimeStampFromID int
Declare @ApptTimeStampToID int
---- clear prev data
DELETE FROM t_ApptDuration_Monthly
----
Begin
Set @ApptTimeStampFromID=1
Set @ApptTimeStampToID=2
INSERT INTO common.t_ApptDuration_Monthly ([ApptTimeStampFromID],[ApptTimeStampToID],
[NumberOfAppts] ,
[TotalDuration])
SELECT @ApptTimeStampFromID, @ApptTimeStampToID,
COUNT(A.ApptCode) As NumOfAppts,
( select SUM(DATEdiff(MI,TimeStamp,TimeStamp)) from Common.t_Appointment_TimeStamp at
where t.TimeStampId=@ApptTimeStampFromID and t.TimeStampId= @ApptTimeStampToID
and (@ApptTimeStampToID > @ApptTimeStampFromID)
) AS TotalDuration
FROM Common.t_Appointment A INNER JOIN
Common.t_Appointment_TimeStamp T on T.ApptID = A.ApptID
END
Select * FROm common.t_ApptDuration_Monthly
END
Now, problem is TotalDuration data not calculate correctly.It's giving 0 result because of same field used on datediff function.
Please Help me out with this.(Have to calculate minutes)
Thank You for your time and reply.
January 29, 2013 at 12:32 pm
Nice job posting ddl and sample data. However you missed t_Appointment.
Also if you could post what you expect as output along with an explanation of the business rules it would be very helpful.
_______________________________________________________________
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 29, 2013 at 1:09 pm
CREATE TABLE [Common].[t_Appointment](
[ApptID] [int] IDENTITY(1,1) NOT NULL,
[ApptCode] [varchar](50) NULL,
[ApptDate] [date] NULL,
[ApptTime] [time](7) NULL,
[ApptType] [varchar](10) NULL
CONSTRAINT [PK_ApptID] PRIMARY KEY CLUSTERED
(
[ApptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Above is t_Appointment table structure.
-------And I want Out put
ApptTimeStampFromID ApptTimeStampToID NoOFAppointment(Count of ApptCode) TotalDuration(sum (Mins of Timestamp))
1 2 61 (10:00:00 -10:15:00) 15 mins
1 3 25 (10:00:00 - 10:45:00) 45 mins
2 3 12 (10:15:00 - 10:45:00) 30 mins
Above format of output,suppose to my result.
Now, I have problem of calculating timestamp between ApptTimeStampFromID and ApptTimeStampToID.
I used
(select SUM(DATEdiff(MI,TimeStamp,TimeStamp)) from Common.t_Appointment_TimeStamp at
where (@ApptTimeStampToID > @ApptTimeStampFromID))
but giving wrong result.I am not able to show SUM of difference between ApptTimeStampFromID and ApptTimeStampToID.
and TimeStamp field datatype is Time(7). In Sql we cann't directly use SUM function on time datatype field.
Thanks for replying me back.
January 29, 2013 at 1:19 pm
Can you provide data for that table?
_______________________________________________________________
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 29, 2013 at 1:47 pm
Sample data for t_Appointment
INSERT INTO [t_Appointment]([ApptID]
,[ApptCode]
,[ApptDate]
,[ApptTime]
,[ApptType])
VALUES
(1442640,852396,'2010-11-01','13:10:00.0000000','FW'),
(1442763,1434815,'2010-11-01','14:45:00.0000000','FW'),
(1443049,1176439,'2010-11-01','10:00:00.0000000' ,'NP'),
(1443067,73933,'2010-11-01','11:00:00.0000000','RSLT'),
(1442099,830076,'2010-11-01','09:15:00.0000000','FW'),
(1442096,734653,'2010-11-01','08:30:00.0000000','NTD')
GO
January 29, 2013 at 2:06 pm
zoom19 (1/29/2013)
Sample data for t_Appointment
INSERT INTO [t_Appointment]([ApptID]
,[ApptCode]
,[ApptDate]
,[ApptTime]
,[ApptType])
VALUES
(1442640,852396,'2010-11-01','13:10:00.0000000','FW'),
(1442763,1434815,'2010-11-01','14:45:00.0000000','FW'),
(1443049,1176439,'2010-11-01','10:00:00.0000000' ,'NP'),
(1443067,73933,'2010-11-01','11:00:00.0000000','RSLT'),
(1442099,830076,'2010-11-01','09:15:00.0000000','FW'),
(1442096,734653,'2010-11-01','08:30:00.0000000','NTD')
GO
You are going to have to explain your logic here. I don't get it. In your desired output you said.
-------And I want Out put
ApptTimeStampFromID ApptTimeStampToID NoOFAppointment(Count of ApptCode) TotalDuration(sum (Mins of Timestamp))
1 2 61 (10:00:00 -10:15:00) 15 mins
1 3 25 (10:00:00 - 10:45:00) 45 mins
2 3 12 (10:15:00 - 10:45:00) 30 mins
However your sample table has only 6 rows. In your query you selected your variables for the 2 columns. I don't understand what your output is based on.
What is ApptTimeStampFromID? Is that your variable? How and why is it changing? What about the value of ApptTimeStampToID? Given the sample data the NoOFAppointment(Count of ApptCode) does not make sense. Can you explain the business logic of TotalDuration?
I will be willing to help but you have to paint a more clear picture of what you are trying to do.
_______________________________________________________________
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 29, 2013 at 2:15 pm
we calculate time of duration depends upon Appointment TimeStamp type.
business logic behind is client wants to see how much it will taking time between appointment type for patient Appointment.
(Schedule to Arrival ,Arrival to CheckIn,CheckIn to ExamRoom and ExamRoom to Departure)
5 types of TimeStampID of patients appointment in hospital.
below is Appointment TimeStampID description
-----
Appointment Type
TimeStampID TimeStampDesc
1 Schedule
2 Arrival
3 Check in
4 Exam Room
5 Departure.
-----
So, ApptTimeStampFromID and ApptTimeStampToID indicate TimeStampType of patient appointment.
Now, we want to calculate time of between each TimeStampType.
and discard noofcountAppt. main calculation is totalduration between each appintment type
I just provide few records.
Thanks ,
January 29, 2013 at 2:49 pm
OK that certainly helps. What is the desired output based on your sample 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 29, 2013 at 2:59 pm
Desired Output:-
Suppose ApptID:-1442640
ApptTimeStampFromID ApptTimeStampToID TotalDuration(sum (Mins of Timestamp))
1 (Schedule) 2 (Arrival) (13:10:00 -13:23:00) 13 mins
1 (Schedule) 3 (Check IN) (13:10:00 - 13:26:00) 16 mins
2 (Arrival) 3 (Check IN) (13:23:00 - 13:26:00) 3 mins
January 29, 2013 at 3:44 pm
OK I am following you now. I am out of here for a couple days. Hopefully somebody else will stop by and lend a hand. I think you can probably do this with the quirky update of running totals. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
If nobody else jumps in before I get back I will see if I can 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/
February 4, 2013 at 8:48 am
I come out with something using cursor but still I am not satisfied with result.Below is my code..
/****** Object: StoredProcedure [Common].[p_ApptDuration_Monthly_Populate] Script Date: 01/31/2013 15:07:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=============================================
-- Create date: 1/29/2013
-- Description:Get ApptDuration Monthly Data
Exec [common].[p_ApptDuration_Monthly_Populate] '2012-01-01',1
-- =============================================*/
ALTER PROCEDURE [Common].[p_ApptDuration_Monthly_Populate]
@endDate datetime,
@monthRange int
AS
BEGIN
Declare @startDate datetime
set @startDate = DATEADD(mm, -@monthRange, @endDate)
set @startDate = CONVERT(datetime,convert(char(4),Year(@startDate)) + '-' + convert(char(2),Month(@startDate)) + '-01')
set @endDate = DateAdd(day,-1,DATEADD(mm, @monthRange+1, @startDate)) -- get last day of last month
---- clear prev data
DELETE FROM common.t_ApptDuration_Monthly where
FirstOfMonth >= @startDate and
FirstOfMonth < @endDate
----
Begin
--===== Declare the cursor storage variables
DECLARE @TimeStamp INT
DECLARE @FromStampID INT
--===== Declare the working variables
DECLARE @TotalTimeStamp INT
DECLARE @ToStampID INT
SET @ToStampID =0
--========================================================================================
CREATE TABLE #Appointment(ApptID int,Year int,Month int,FirstofMonth date,ApptTimeStampFromID int,ApptTimeStampToID int,
TotalDuration decimal(18,0),NumberOfAppts int,FacilityID int,DoctorID int,CreatedDateTime datetime)
INSERT INTO #Appointment ([ApptID],Year,[Month],[FirstOfMonth],ApptTimeStampFromID,
[TotalDuration],
[NumberOfAppts] ,[FacilityID],[DoctorID],[CreatedDateTime] )
SELECT Distinct T.ApptID,Year,Month ,
FirstofMonth,TimeStampId,
SUM(CONVERT(decimal(18,0),DATEPART(MI,TimeStamp))) AS TotalDuration,
COUNT(A.ApptCode) As NumOfAppts,
f.FacilityID,
DoctorID,
GetDate()as CurrentDate
FROM Common.t_Facility f INNER JOIN
Common.t_Appointment A ON A.FacilityID = f.FacilityID INNER JOIN
Common.t_Appointment_TimeStamp T on T.ApptID = A.ApptID
WHERE ApptDate BETWEEN @startDate and @endDate
GROUP BY T.ApptID,f.FacilityID,DoctorID, YEAR, Month,FirstofMonth,TimeStampId
--Select * FROm #Appointment order By ApptTimeStampFromID
--========================================================================================
DECLARE ApptTimeStampFromID CURSOR LOCAL FORWARD_ONLY
FOR
SELECT ApptTimeStampFromID,TotalDuration
FROM #Appointment
--order by ApptTimeStampFromID
WHERE ApptTimeStampFromID < 5
OPEN ApptTimeStampFromID
--===== Read the information from the first row of the cursor
FETCH NEXT FROM ApptTimeStampFromID
INTO @FromStampID, @TimeStamp
WHILE @@FETCH_STATUS = 0
BEGIN
print 'something'
print @ToStampID
SELECT
@TotalTimeStamp = CASE
WHEN @FromStampID < @ToStampID
THEN @TotalTimeStamp + @TimeStamp
ELSE @TimeStamp
END,
@ToStampID = @FromStampID + 1
----===== Update
UPDATE #Appointment
SET TotalDuration = @TotalTimeStamp,
ApptTimeStampToID = @ToStampID
WHERE CURRENT OF ApptTimeStampFromID
--===== Read the information from the next row of the cursor
FETCH NEXT FROM ApptTimeStampFromID
INTO @FromStampID,@TimeStamp
END --End of the cursor ApptTimeStampFromID
--========
CLOSE ApptTimeStampFromID
DEALLOCATE ApptTimeStampFromID
Select Year,Month,FirstofMonth,ApptTimeStampFromID,ApptTimeStampToID,
SUM(TotalDuration) AS TotalDuration,count(NumberOfAppts),FacilityID
,DoctorID
from #Appointment
Where ApptTimeStampToID IS NOT NULL
GROUP BY Year,Month,FirstofMonth,ApptTimeStampFromID,ApptTimeStampToID,FacilityID,DoctorID--,NumberOfAppts
ORDER BY ApptTimeStampFromID,ApptTimeStampToID,Year,Month,FacilityID
DROP TABLE #Appointment
END
END
February 4, 2013 at 9:16 am
zoom19 (2/4/2013)
I come out with something using cursor but still I am not satisfied with result.Below is my code..
That looks like it will work but I am guessing you are not satisfied because it takes too long? The quirky update method I suggested should work for this. I would help you but you have introduced even more tables that we don't have (t_Facility).
_______________________________________________________________
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/
February 4, 2013 at 11:19 am
/****** Object: Table [Common].[t_Facility] Script Date: 02/04/2013 12:41:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Common].[t_Facility]') AND type in (N'U'))
DROP TABLE [Common].[t_Facility]
GO
/****** Object: Table [Common].[t_Facility] Script Date: 02/04/2013 12:41:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Common].[t_Facility](
[FacilityID] [int] IDENTITY(1,1) NOT NULL,
[FacilityName] [varchar](50) NULL,
[FacilityGroupID] [int] NULL,
CONSTRAINT [PK_Facility] PRIMARY KEY CLUSTERED
(
[FacilityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Common].[t_Facility]
(
[FacilityName]
,[FacilityGroupID])
VALUES
('Egg Harbor',2),
('Bensalem',1),
('Bryn Mawr - RI',1),
('Center City',1),
('King of Prussia',1),
('Lankenau',1),
('Manahawkin',2),
('Marlton',2),
('Northeast',1),
('Pioneer',1),
('Media',1),
('South Philly',1),
('Velocity',2)
GO
February 4, 2013 at 11:24 am
Above if my facility table and sample data .And add to one more thing.....I got result set with my Stored Procedure combination of TimestampID like
1 to 2
2 to 3
3 to 4
4 to 5
But I want all combination of my TimeStampID LIke
1 To 2
1 to 3
1 to 4
2 to 3
3 to 4
4 to 5
February 4, 2013 at 11:37 am
Below is which I actually getting result set (it is just few rows here...)
YearMonthFirstofMonthApptTimeStampFromIDApptTimeStampToIDTotalDuration(No column name)FacilityIDDoctorID
201212012-01-0112231222351562
201212012-01-011225383015123
201212012-01-01124836511510
201212012-01-01128151891513
201212012-01-0112149261481545
2011122011-12-012326962370145
2011122011-12-012325511333168
2011122011-12-012314234169174
2011122011-12-012314410199197
2011122011-12-01238911132
2011122011-12-012315492202180
2011122011-12-01231875827215
2011122011-12-0123410148137
2011122011-12-012310983156198
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply