SQLTeam.com | Weblogs | Forums

Get weekly based data from DB between the given from and To datetime


#1

I have From DateTime and To DateTime entered by user. So how to get one record for week.
Table - Table_101
Columns - TimeStamp, LitresPerWeek
If from datetime is 06-07-18 2:07:38 PM and to datetime is 27-07-18 2:07:38 PM. then query should return 3 records.How t do this.
Thank you


#2

This should get you there:

/* RCIT Database Support
   By:   Joe Torre
   On:   Aug 9th 2018
   For:  This funtion returns weekly inerval values for the range passed in
   DECLARE @d date = GetDate();
   SELECT
      BegDt
   ,  EndDt
   FROM dbo.WeeklyCalendar ('20150101', @d);
*/
CREATE FUNCTION [dbo].[WeeklyCalendar] (@BegDate datetime, @EndDate datetime) RETURNS TABLE
AS RETURN
(

	WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
      , n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
      , n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
      , nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
      , Calendar (BegDt, EndDt) AS (
                  SELECT 
                        DateAdd(week, nums.num, @BegDate) 
                      , DateAdd(MILLISECOND, -3, DateAdd(week, 1, DateAdd(week, nums.num,@BegDate)))
             
                  FROM nums
                  )
   SELECT
        c.BegDt
      , c.EndDt
   FROM 
      Calendar c
   WHERE
      c.BegDt < @EndDate
)
GO

BTW Your datetime strings are dependent on the regional settings. To avoid SQL misinterpreting a datetime string use '20180708 14:07:38' to assign datetime in T-SQL.