[dbo].[getAllDaysBetweenTwoDate] Script Date: 01/02/2015 09:24:40 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getAllDaysBetweenTwoDate]
(
@FromDate DATETIME='06/20/2014',
@ToDate DATETIME='06/23/2014'
)
AS
BEGIN
DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);
WITH d AS
(
SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
OVER (ORDER BY object_id), REPLACE(@FromDate,'/',''))
FROM sys.all_objects
)
(
SELECT convert(varchar(30),AllDays,101) as MsgDate,
(SELECT COUNT(*) AS MSG FROM JAN_TBL_NOTE_TASK SUB WHERE convert(varchar(30),SUB.Submit_Date,101)=convert(varchar(30),AllDays,101)) AS Replyonmsg,
(SELECT COUNT(*) AS REPLY FROM JAN_TASK_TBL_MAIN_TASK SUB WHERE convert(varchar(30),SUB.Task_Create_Date,101)=convert(varchar(30),AllDays,101)) AS Totalmsg
From d
)
RETURN
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getAllDaysBetweenTwoDate]
(
@FromDate DATETIME='06/20/2014',
@ToDate DATETIME='06/23/2014'
)
AS
BEGIN
DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);
WITH d AS
(
SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
OVER (ORDER BY object_id), REPLACE(@FromDate,'/',''))
FROM sys.all_objects
)
(
SELECT convert(varchar(30),AllDays,101) as MsgDate,
(SELECT COUNT(*) AS MSG FROM JAN_TBL_NOTE_TASK SUB WHERE convert(varchar(30),SUB.Submit_Date,101)=convert(varchar(30),AllDays,101)) AS Replyonmsg,
(SELECT COUNT(*) AS REPLY FROM JAN_TASK_TBL_MAIN_TASK SUB WHERE convert(varchar(30),SUB.Task_Create_Date,101)=convert(varchar(30),AllDays,101)) AS Totalmsg
From d
)
RETURN
END
No comments:
Post a Comment