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
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.