Hi Experts !
I need your help the senario is currently the sql select statement runs on range basis example selecting date range 1-Jan-19 to 20-Jan-19 the results reterives , but i dont know how to go backward to select data for 20 days from 11-Dec-18 to 31-Dec-18. The selection data the same 1-Jan-19 to 20-Jan-19.
The select range is dynamic forward direction data ,suppose if i select 5 days in a range the query shows me the forward data for 5 days as well as previous data for 5 days depend on range select side by side?
Sorry if i cannot expalin well .
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='1-Jan-19' --- starting date
SET @EndDate ='20-Jan-19' --- ending date
SELECT
min([Start date]) 'Range start date',
max([Start date]) 'Range end date',
Fields,
sum([Vol]) 'Vol'
from Table
Where DATETIME >= @StartDate
AND DATETIME <= @EndDate
Group by [Fields]
Order by desc worked within a range right, the issue is to go backward from the given range example if data is 1-Jan-19 to 20-Jan-19. The select statement shows range 1-Jan-19 to 20-Jan-19 parallel next columns to the backward range will be 11-Dec-18 to 31-Dec-18. Hope I explain ?
Not sure this is what you are looking for - but maybe it will get you closer:
Declare @testTable Table (
id int Identity(1,1) Primary Key
, StartDate datetime
, Fields varchar(10)
, Vol int
);
Insert Into @testTable (StartDate, Fields, Vol)
Values ('2019-01-02', 'field1', 1)
, ('2019-01-19', 'field2', 1)
, ('2019-01-21', 'field2', 1)
, ('2018-12-31', 'field4', 1)
, ('2018-12-11', 'field4', 1)
, ('2018-12-10', 'field5', 1);
Declare @startDate datetime = '2019-01-01'
, @endDate datetime = '2019-01-20'
Select Fields
, min(tt.StartDate)
, max(tt.StartDate)
, sum(vol)
From @testTable tt
Where tt.StartDate >= dateadd(day, -(datediff(day, @startDate, @endDate) + 2), @startDate)
And tt.StartDate < dateadd(day, 1, @endDate)
Group By
Fields;
If I am reading this correctly - the start/end dates that are selected determine the range, so if there are 20 days in the range then you want 20 days before and after the start date.
Jeff thanks for reply !
I go through your solution and closer to solve the issue, while testing the script I have added data for date 2019-01-01 for field4 but the result is adding the data of date 2019-01-01. Since we need to go beyond /backward to 2019-01-01 there only required to sum the data for dates available.
, ('2018-12-31', 'field4', 1)
, ('2018-12-11', 'field4', 1)
will you please help to modify the script the previous data (backward) shows just after column number 4. parallel.
Where tt.StartDate > dateadd(day, -(datediff(day, @startDate, @endDate) + 2), @startDate)
And tt.StartDate < dateadd(day, 1, @endDate)
The above post 6 issue still exists I go through your solution and closer to solve the issue, while testing the script I have added data for date 2019-01-01 for field4 but the result is adding the data of date 2019-01-01. Since we need to go beyond /backward to 2019-01-01 there only required to sum the data for dates available. , ('2018-12-31', 'field4', 1, ('2018-12-11', 'field4', 1)
will you please help to modify the script the previous data (backward) shows just after column number 4. parallel.
one request will you please help to modify the script the previous data (backward) shows just after column number 4. parallel.
You explanation does not make sense - you put in a range from 09/01/2019 (inclusive) but then want it excluded?
Now - if you want to group the data as 'before range' and 'after range' then you need to create another grouping level.
Select Fields
, RangeGroup = iif(tt.StartDate < @startDate, 0, 1)
, min(tt.StartDate)
, max(tt.StartDate)
, sum(vol)
From @testTable tt
Where tt.StartDate >= dateadd(day, -(datediff(day, @startDate, @endDate) + 2), @startDate)
And tt.StartDate < dateadd(day, 1, @endDate)
Group By
Fields
, iif(tt.StartDate < @startDate, 0, 1);
You do not want to do this...that would exclude any rows that have a date/time at midnight on the first day of the check. In other words...you would now be excluding the 2018-12-11. If you do not want to include any rows with a date of 2018-12-11 (for this range) then you need to change the above to:
