Thursday, 1 January 2015

select report using continue dates

[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

No comments:

Post a Comment