Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- ....
- FROM
- ....
- WHERE
- (CAST((DATEDIFF(dd, Invoice.DateOpened @EndDate)/30) AS INT) - CAST((DATEDIFF(dd, Invoice.DateOpened, @StartDate)/30) AS INT)) >=1
- OR DATEDIFF(dd, Invoice.DateOpened, @StartDate) % 30 = 0 --this line to capture valid records but where From and To dates are the same
- SELECT
- ....
- FROM
- ....
- WHERE
- --In MS T-SQL, BETWEEN is inclusive.
- DateOpened BETWEEN @UserSuppliedFromDate AND @UserSuppliedToDate
- AND DATEDIFF(dd, DateOpened, getdate()) % 30 = 0
- DECLARE @Table TABLE
- (
- ID integer,
- DateOpened datetime
- )
- DECLARE @FromDate as datetime = '1/1/2012'
- DECLARE @ToDate as datetime = '12/31/2012'
- INSERT INTO @Table VALUES (0, '1/1/1982')
- INSERT INTO @Table values (1, '1/1/2012')
- INSERT INTO @Table VALUES (2, '2/17/2012')
- INSERT INTO @Table VALUES (3, '3/16/2012')
- INSERT INTO @Table VALUES (4, '4/16/2012')
- INSERT INTO @Table VALUES (5, '5/28/2012')
- INSERT INTO @Table VALUES (6, '1/31/2012')
- INSERT INTO @Table VALUES (7, '12/12/2013')
- DECLARE @DateLoop as datetime
- DECLARE @ResultIDs as table ( ID integer, DateLoopAtTheTime datetime, DaysDifference integer )
- --Initialize to lowest possible value
- SELECT @DateLoop = @FromDate
- --Loop until we hit the maximum date to check
- WHILE @DateLoop <= @ToDate
- BEGIN
- INSERT INTO @ResultIDs (ID,DateLoopAtTheTime, DaysDifference)
- SELECT ID, @DateLoop, DATEDIFF(dd,@DateLoop, DateOpened)
- FROM @Table
- WHERE
- DATEDIFF(dd,@DateLoop, DateOpened) % 30 = 0
- AND DATEDIFF(dd,@DateLoop,DateOpened) > 0 -- Avoids false positives when @DateLoop and DateOpened are the same
- AND DateOpened <= @ToDate
- SELECT @DateLoop = DATEADD(dd, 1, @DateLoop) -- Increment the iterator
- END
- SELECT distinct * From @ResultIDs
Add Comment
Please, Sign In to add comment