asked
I Need Duration Column in Order 8am-9am,9am-10am...5pm-6pm
![alt text][1] [1]: /storage/temp/3161-grid.png The query is: CREATE TABLE [dbo].[DurationMaster]( [Duration] [DateTime] NULL, [FromTime] [varchar](5) NULL, [ToTime] [varchar](5) NULL ) ON [PRIMARY] Go INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'08:00 AM - 09:00 AM', N'08:01', N'09:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'09:00 AM - 10:00 AM', N'09:01', N'10:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'10:00 AM - 11:00 AM', N'10:01', N'11:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'11:00 AM - 12:00 PM', N'11:01', N'12:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'12:00 PM - 01:00 PM', N'12:01', N'13:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'01:00 PM - 02:00 PM', N'13:01', N'14:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'02:00 PM - 03:00 PM', N'14:01', N'15:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'03:00 PM - 04:00 PM', N'15:01', N'16:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'04:00 PM - 05:00 PM', N'16:01', N'17:00') INSERT [dbo].[DurationMaster] ([Duration], [FromTime], [ToTime]) VALUES (N'05:00 PM - 06:00 PM', N'17:01', N'18:00') GO USE [EMSCore] GO /****** Object: StoredProcedure [EMS].[USP_FetchFieldScoreCallReport] Script Date: 01/08/2016 05:23:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [EMS].[USP_FetchFieldScoreCallReport] @StartDate datetime, @EndDate datetime, @UserID int, @EntityID int As Begin DECLARE @TblFormatedDate AS TABLE ( Duration varchar(20), FromTime smalldatetime, ToTime smalldatetime ) INSERT INTO @TblFormatedDate SELECT Duration, --'2015-11-19 '+FromTime, CAST(@StartDate+FromTime as smalldatetime), CAST(+@EndDate+ToTime as smalldatetime) --'2015-11-19 '+ToTime FROM DurationMaster --SELECT * FROM @TblFormatedDate --select top 20 * FROM EMS.tbl_event (NOLOCK) -- ORDER BY fld_event_created_on DESC select CreatedBy.fld_user_full_name AS CreatedUser,FormatedDate.Duration,COUNT(Event.fld_event_id ) AS LogACallCount, Sum(Case when fld_event_subject='Fax Sent' THEN 1 ELSE 0 END) AS FaxSent, Sum(Case when fld_event_subject='Contract Prepared' THEN 1 ELSE 0 END) As ContractPrepared, Sum(Case when fld_event_subject='Hot Lead' THEN 1 ELSE 0 END) As HotLead, Sum(Case when fld_event_subject='Email Sent' THEN 1 ELSE 0 END) AS EmailSent FROM EMS.tbl_event (NOLOCK) AS Event INNER JOIN EMS.tbl_user AS CreatedBy (NOLOCK) ON CreatedBy.fld_user_id = Event.fld_event_created_by_id LEFT JOIN @TblFormatedDate AS FormatedDate ON Event.fld_event_created_on BETWEEN FormatedDate.FromTime AND FormatedDate.ToTime WHERE fld_event_isactive=1 AND Event.fld_event_status = 'Completed' AND fld_event_created_by_id IN(@UserID) AND Event.fld_event_istask=1 AND Event.fld_event_entity_id =100100 AND Event.fld_event_isactive = 1 AND FormatedDate.Duration is not null GROUP BY FormatedDate.Duration, CreatedBy.fld_user_full_name END GO
I'm not clear what you're asking. What does the query do wrong?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Could it be as simple as: /*Last Line of example:*/ GROUP BY FormatedDate.Duration, CreatedBy.fld_user_full_name /*Add order by column to GROUP BY list:*/ , FormatedDate.FromTime /*Add Order by*/ ORDER BY FormatedDate.FromTime END
Not really sure what you're asking, but by the look of it you could use a "time dimension" (not date) table. See [this][1] for more details, but bear in mind that you can add the columns you've specified above to a table such as the one Louis Davidson describes. [1]:
http://sqlblog.com/blogs/louis_davidson/archive/2010/02/04/creating-and-using-a-time-not-date-table-dimension.aspx
22 People are following this question.
