October 12, 2017 at 3:24 am
Our table holds 200 million rows with 180 columns. We are planning to implement a batch wise records insert for our specific purpose also it is prone from failures, which means we need to start only from the failure block. For this we planned to build a table which holds only three columns
Start_Date_Range, End_Date_Range, Record_Id. Large volume table holds a column called service_provided_date. This date is not a static range sometime the start range may come from 1973 on wards and end range go up to future dates. My request is to populate the row like below
Record_Id Start_Range End_Range Records_Per_Batch
1 16-02-1973 18-02-1973 500000
2 21-03-1973 18-08-1973 500000
| | | 500000
| | | 500000
| | | 500000
N | | 15000
Large volume table holds a column service_provided_date with different ranges. The dates are not in continuous manner. But the ultimate goal is the date range should holds only the 500000 rows. Row count will be an adjustable one. for 200 million rows it should populate 500000 rows per date range.Last batch will holds the remaining records. This can be achievable with while loop using top 500000 in select query with order by, but we dont want this, we need to populate this batch wise count based on date range into the table for our future purpose.
Note the end range of the date must be the start range for the next batch. sometime we may receive 500000 records within a moth or with in 6 months, also the date value in the column is not a continuous date, 1 to 18 next 19, 20 will not be available but 21 is available.
Regards,
Kannan.C
October 12, 2017 at 5:09 am
What is your question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2017 at 6:34 am
Phil Parkin - Thursday, October 12, 2017 5:09 AMWhat is your question?
My table have a column name service_date holds date value
Table script:
create table #temp1 (service_date datetime)
service_date
2010-05-10 00:00:00.000
2010-05-10 00:00:00.000
2010-05-10 00:00:00.000
2010-05-10 00:00:00.000
2010-05-18 00:00:00.000
2010-05-18 00:00:00.000
2010-05-17 00:00:00.000
2010-05-17 00:00:00.000
2010-05-17 00:00:00.000
2010-05-17 00:00:00.000
2010-05-16 00:00:00.000
2010-05-16 00:00:00.000
2010-05-16 00:00:00.000
2010-05-16 00:00:00.000
2009-09-14 00:00:00.000
2009-09-14 00:00:00.000
2009-09-15 00:00:00.000
2009-09-15 00:00:00.000
2009-09-15 00:00:00.000
2009-09-15 00:00:00.000
2009-09-18 00:00:00.000
2009-09-18 00:00:00.000
2009-09-18 00:00:00.000
2009-09-18 00:00:00.000
1998-05-25 00:00:00.000
2009-09-18 00:00:00.000
2009-09-18 00:00:00.000
2002-08-10 00:00:00.000
2002-07-10 00:00:00.000
2002-07-10 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-06 00:00:00.000
2002-07-08 00:00:00.000
2002-07-08 00:00:00.000
2002-07-08 00:00:00.000
2009-01-20 00:00:00.000
2009-01-20 00:00:00.000
1999-07-15 00:00:00.000
1999-07-15 00:00:00.000
1999-07-15 00:00:00.000
1999-07-15 00:00:00.000
2009-01-20 00:00:00.000
2009-01-20 00:00:00.000
1999-07-17 00:00:00.000
1999-07-17 00:00:00.000
1999-07-17 00:00:00.000
1999-07-17 00:00:00.000
2007-09-23 00:00:00.000
2007-09-23 00:00:00.000
2007-11-09 00:00:00.000
2007-11-09 00:00:00.000
2007-11-09 00:00:00.000
2007-11-09 00:00:00.000
2007-11-09 00:00:00.000
2007-11-09 00:00:00.000
2007-11-08 00:00:00.000
2007-11-08 00:00:00.000
2007-11-08 00:00:00.000
2007-11-08 00:00:00.000
2007-11-11 00:00:00.000
2007-11-11 00:00:00.000
2007-11-11 00:00:00.000
2007-11-11 00:00:00.000
2010-05-02 00:00:00.000
2010-04-30 00:00:00.000
2010-04-30 00:00:00.000
2010-04-30 00:00:00.000
2010-04-30 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-01 00:00:00.000
2010-05-02 00:00:00.000
2010-05-02 00:00:00.000
1996-09-28 00:00:00.000
2000-09-09 00:00:00.000
2000-09-09 00:00:00.000
2000-09-09 00:00:00.000
2000-09-09 00:00:00.000
1996-09-08 00:00:00.000
1996-09-20 00:00:00.000
1996-09-20 00:00:00.000
1996-09-20 00:00:00.000
1996-09-27 00:00:00.000
1996-09-27 00:00:00.000
1996-09-27 00:00:00.000
1996-09-27 00:00:00.000
1996-09-14 00:00:00.000
2010-06-18 00:00:00.000
2010-06-18 00:00:00.000
2010-06-18 00:00:00.000
My requirement is to get 10 rows per date range for my date value available in the table
Output should be like this
Rows start_date End_Date
10 1996-09-08 00:00:00.000 1996-09-28 00:00:00.000
10 1996-09-28 00:00:00.000 2000-09-09 00:00:00.000
10 2000-09-09 00:00:00.000 2002-07-06 00:00:00.000
If i use the select query against my table like this
select * from #temp1 where service_date between '1996-09-08 00:00:00.000' and '1996-09-28 00:00:00.000'
It should return only the 10 rows.
If my table contains 101 rows the last row should append with the previous date range value or it should fall with the other date range.
Regards,
Kannan.C
October 12, 2017 at 6:59 am
So your requirement is to create a table of date ranges?
What happens if there are 50 rows with the same date?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2017 at 7:30 am
Phil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
October 12, 2017 at 8:06 am
kannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2017 at 8:37 am
Phil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
October 12, 2017 at 9:17 am
kannan_egd - Thursday, October 12, 2017 8:37 AMPhil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
This gets very awkward because of the possibility of multiple occurrences on the same date.
Date Num Occurrences
1 6
2 7
3 8
4 19
5 3
6 8
You see what I mean? Grouping this in 10s is not possible.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2017 at 9:54 am
Phil Parkin - Thursday, October 12, 2017 9:17 AMkannan_egd - Thursday, October 12, 2017 8:37 AMPhil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
This gets very awkward because of the possibility of multiple occurrences on the same date.
Date Num Occurrences
1 6
2 7
3 8
4 19
5 3
6 8
You see what I mean? Grouping this in 10s is not possible.
Grouping or slicing at what number of rows will be possible, this is mainly for batch processing. Our Batch size will be 500000 records with possible date ranges, 10 is not mandatory any rows but not exceed more than 5 lakhs. Too many ways for batch processing using top and while but we are targetting this for date column for some reasons.
October 12, 2017 at 10:33 am
kannan_egd - Thursday, October 12, 2017 9:54 AMPhil Parkin - Thursday, October 12, 2017 9:17 AMkannan_egd - Thursday, October 12, 2017 8:37 AMPhil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
This gets very awkward because of the possibility of multiple occurrences on the same date.
Date Num Occurrences
1 6
2 7
3 8
4 19
5 3
6 8
You see what I mean? Grouping this in 10s is not possible.Grouping or slicing at what number of rows will be possible, this is mainly for batch processing. Our Batch size will be 500000 records with possible date ranges, 10 is not mandatory any rows but not exceed more than 5 lakhs. Too many ways for batch processing using top and while but we are targetting this for date column for some reasons.
So far, then, the requirement appears to be as follows:
Create a table of Start and End date ranges. When compared with the main table, these ranges should ideally include 10 rows of data. However, if there are multiple occurrences of rows with the same date, it is acceptable for the data ranges to contain more, or less, than 10 rows, whichever is 'possible' (whatever that means).
I do not have time to tackle this right now. Anyone else?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2017 at 11:17 am
Honestly, I suspect a language translation issue here. I don't quite understand what the poster is really looking for, as the words just aren't making enough sense to provide a clear picture of what's desired. I don't have time either if there's not a good clear picture of what the poster wants. Right now, it's about as clear as mud.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 12, 2017 at 11:19 pm
Phil Parkin - Thursday, October 12, 2017 10:33 AMkannan_egd - Thursday, October 12, 2017 9:54 AMPhil Parkin - Thursday, October 12, 2017 9:17 AMkannan_egd - Thursday, October 12, 2017 8:37 AMPhil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
This gets very awkward because of the possibility of multiple occurrences on the same date.
Date Num Occurrences
1 6
2 7
3 8
4 19
5 3
6 8
You see what I mean? Grouping this in 10s is not possible.Grouping or slicing at what number of rows will be possible, this is mainly for batch processing. Our Batch size will be 500000 records with possible date ranges, 10 is not mandatory any rows but not exceed more than 5 lakhs. Too many ways for batch processing using top and while but we are targetting this for date column for some reasons.
So far, then, the requirement appears to be as follows:
Create a table of Start and End date ranges. When compared with the main table, these ranges should ideally include 10 rows of data. However, if there are multiple occurrences of rows with the same date, it is acceptable for the data ranges to contain more, or less, than 10 rows, whichever is 'possible' (whatever that means).
I do not have time to tackle this right now. Anyone else?
Dear Parkin,
Yes, you clearly understand my requirement. Thanks for all your time. Have a Good Day..
October 13, 2017 at 6:50 am
kannan_egd - Thursday, October 12, 2017 11:19 PMPhil Parkin - Thursday, October 12, 2017 10:33 AMkannan_egd - Thursday, October 12, 2017 9:54 AMPhil Parkin - Thursday, October 12, 2017 9:17 AMkannan_egd - Thursday, October 12, 2017 8:37 AMPhil Parkin - Thursday, October 12, 2017 8:06 AMkannan_egd - Thursday, October 12, 2017 7:30 AMPhil Parkin - Thursday, October 12, 2017 6:59 AMSo your requirement is to create a table of date ranges?What happens if there are 50 rows with the same date?
Thanks for swift response. My requirement is to holds the rows count information slicing with 10 rows per range. If the rows exceeds the slicing count but falls in the same date that is 50 rows on the same date then for that case start and end range date would be same.
And the row count would be 50?
Yes, for that date range alone. In that case my 100 rows will be sliced with 50 rows and remaining with 10 * 5 slices.
This gets very awkward because of the possibility of multiple occurrences on the same date.
Date Num Occurrences
1 6
2 7
3 8
4 19
5 3
6 8
You see what I mean? Grouping this in 10s is not possible.Grouping or slicing at what number of rows will be possible, this is mainly for batch processing. Our Batch size will be 500000 records with possible date ranges, 10 is not mandatory any rows but not exceed more than 5 lakhs. Too many ways for batch processing using top and while but we are targetting this for date column for some reasons.
So far, then, the requirement appears to be as follows:
Create a table of Start and End date ranges. When compared with the main table, these ranges should ideally include 10 rows of data. However, if there are multiple occurrences of rows with the same date, it is acceptable for the data ranges to contain more, or less, than 10 rows, whichever is 'possible' (whatever that means).
I do not have time to tackle this right now. Anyone else?
Dear Parkin,
Yes, you clearly understand my requirement. Thanks for all your time. Have a Good Day..
Dear Phil Parkin,
Finally i build my own code, People may make use of this code if they really required for their slicing based on date range. Would request you to review the code for loop holes. Thanks for all your replies...
Perfect Slicing for large volume data code is here
Declare @Max_RowID int, @Slicing_Rowcount INT, @rowid int ,@RowCount_limit int
select @Max_RowID = 0,@Slicing_Rowcount = 0 ,@rowid = 0 ,@RowCount_limit = 500000;
IF OBJECT_ID('tempdb.dbo.#temp_Row_Date_Range', 'U') IS NOT NULL
DROP TABLE #temp_Row_Date_Range;
IF OBJECT_ID('tempdb.dbo.#Temp_Result', 'U') IS NOT NULL
DROP TABLE #Temp_Result;
create table #Temp_Result (Row_Id int, Service_Date Datetime);
With cte_1 as
(
SELECT
Service_Date = CONVERT(DATE, service_date),
Row_Count = COUNT(1)
FROM My_Large_Table
GROUP BY (CONVERT(DATE, service_date))
),Cte_2 as (Select row_number() OVER (ORDER BY service_date)AS Row_Id, Service_Date,Row_Count from cte_1),
Cte_3 as (SELECT Row_Id, service_date, Row_Count, SUM(Row_Count) OVER (ORDER BY row_Id) AS 'Total_Rows' FROM Cte_2)
Select * into #temp_Row_Date_Range from Cte_3
select @Max_RowID = (select max(row_id) from #temp_Row_Date_Range)
while(@rowid < @Max_RowID)
begin
select top 1 @Slicing_Rowcount = total_rows , @rowid = row_id from #temp_Row_Date_Range where total_rows >= @RowCount_limit and row_id > @rowid order by Row_Id
if not exists (select 1 from #temp_Row_Date_Range where total_rows >= @RowCount_limit and row_id > @rowid)
begin
select @rowid = max(row_id) from #temp_Row_Date_Range
end
Insert into #Temp_Result select Row_Id, Service_date from #temp_Row_Date_Range where row_id = @rowid
select * from #temp_Row_Date_Range where row_id = @rowid
select @RowCount_limit = @Slicing_Rowcount + @RowCount_limit
end
SELECT
LAG(A.Service_Date) OVER (ORDER BY A.Row_Id) PreviousValue,
A.Service_Date as Starte_Range,
ISNULL(LEAD(A.Service_Date) OVER (ORDER BY A.Row_Id),'9999-12-31 00:00:00.000') End_Range
FROM #Temp_Result A
GO
Regards,
Kannan.C
(Unless commitment is made, there are only promises and hopes... but no plans.)
October 13, 2017 at 7:03 am
Well done on solving this yourself. I've reformatted your code and posted it below, to make it easier for others to read.DECLARE
@Max_RowID INT
, @Slicing_Rowcount INT
, @rowid INT
, @RowCount_limit INT;
SELECT
@Max_RowID = 0
, @Slicing_Rowcount = 0
, @rowid = 0
, @RowCount_limit = 500000;
IF OBJECT_ID('tempdb.dbo.#temp_Row_Date_Range', 'U') IS NOT NULL
DROP TABLE #temp_Row_Date_Range;
IF OBJECT_ID('tempdb.dbo.#Temp_Result', 'U') IS NOT NULL
DROP TABLE #Temp_Result;
CREATE TABLE #Temp_Result
(
Row_Id INT
, Service_Date DATETIME
);
WITH cte_1
AS
(
SELECT
Service_Date = CONVERT(DATE, service_date)
, Row_Count = COUNT(1)
FROM My_Large_Table
GROUP BY (CONVERT(DATE, service_date))
)
, Cte_2
AS
(
SELECT
Row_Id = ROW_NUMBER() OVER (ORDER BY cte_1.Service_Date)
, cte_1.Service_Date
, cte_1.Row_Count
FROM cte_1
)
, Cte_3
AS
(
SELECT
Cte_2.Row_Id
, Cte_2.Service_Date
, Cte_2.Row_Count
, Total_Rows = SUM(Cte_2.Row_Count) OVER (ORDER BY Cte_2.Row_Id)
FROM Cte_2
)
SELECT *
INTO #temp_Row_Date_Range
FROM Cte_3;
SELECT @Max_RowID =
(
SELECT MAX(Row_Id)
FROM #temp_Row_Date_Range
);
WHILE (@rowid < @Max_RowID)
BEGIN
SELECT TOP 1
@Slicing_Rowcount = Total_Rows
, @rowid = Row_Id
FROM #temp_Row_Date_Range
WHERE
Total_Rows >= @RowCount_limit
AND Row_Id > @rowid
ORDER BY Row_Id;
IF NOT EXISTS
(
SELECT 1
FROM #temp_Row_Date_Range
WHERE
Total_Rows >= @RowCount_limit
AND Row_Id > @rowid
)
BEGIN
SELECT @rowid = MAX(Row_Id)
FROM #temp_Row_Date_Range;
END;
INSERT INTO #Temp_Result
SELECT
Row_Id
, Service_Date
FROM #temp_Row_Date_Range
WHERE Row_Id = @rowid;
SELECT *
FROM #temp_Row_Date_Range
WHERE Row_Id = @rowid;
SELECT @RowCount_limit = @Slicing_Rowcount + @RowCount_limit;
END;
SELECT
PreviousValue = LAG(A.Service_Date) OVER (ORDER BY A.Row_Id)
, Starte_Range = A.Service_Date
, End_Range = ISNULL(LEAD(A.Service_Date) OVER (ORDER BY A.Row_Id), '9999-12-31 00:00:00.000')
FROM #Temp_Result A;
GO
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 13, 2017 at 7:25 am
Phil Parkin - Friday, October 13, 2017 7:03 AMWell done on solving this yourself. I've reformatted your code and posted it below, to make it easier for others to read.DECLARE
@Max_RowID INT
, @Slicing_Rowcount INT
, @rowid INT
, @RowCount_limit INT;SELECT
@Max_RowID = 0
, @Slicing_Rowcount = 0
, @rowid = 0
, @RowCount_limit = 500000;IF OBJECT_ID('tempdb.dbo.#temp_Row_Date_Range', 'U') IS NOT NULL
DROP TABLE #temp_Row_Date_Range;IF OBJECT_ID('tempdb.dbo.#Temp_Result', 'U') IS NOT NULL
DROP TABLE #Temp_Result;CREATE TABLE #Temp_Result
(
Row_Id INT
, Service_Date DATETIME
);WITH cte_1
AS
(
SELECT
Service_Date = CONVERT(DATE, service_date)
, Row_Count = COUNT(1)
FROM My_Large_Table
GROUP BY (CONVERT(DATE, service_date))
)
, Cte_2
AS
(
SELECT
Row_Id = ROW_NUMBER() OVER (ORDER BY cte_1.Service_Date)
, cte_1.Service_Date
, cte_1.Row_Count
FROM cte_1
)
, Cte_3
AS
(
SELECT
Cte_2.Row_Id
, Cte_2.Service_Date
, Cte_2.Row_Count
, Total_Rows = SUM(Cte_2.Row_Count) OVER (ORDER BY Cte_2.Row_Id)
FROM Cte_2
)
SELECT *
INTO #temp_Row_Date_Range
FROM Cte_3;SELECT @Max_RowID =
(
SELECT MAX(Row_Id)
FROM #temp_Row_Date_Range
);WHILE (@rowid < @Max_RowID)
BEGIN
SELECT TOP 1
@Slicing_Rowcount = Total_Rows
, @rowid = Row_Id
FROM #temp_Row_Date_Range
WHERE
Total_Rows >= @RowCount_limit
AND Row_Id > @rowid
ORDER BY Row_Id;IF NOT EXISTS
(
SELECT 1
FROM #temp_Row_Date_Range
WHERE
Total_Rows >= @RowCount_limit
AND Row_Id > @rowid
)
BEGIN
SELECT @rowid = MAX(Row_Id)
FROM #temp_Row_Date_Range;
END;INSERT INTO #Temp_Result
SELECT
Row_Id
, Service_Date
FROM #temp_Row_Date_Range
WHERE Row_Id = @rowid;SELECT *
FROM #temp_Row_Date_Range
WHERE Row_Id = @rowid;SELECT @RowCount_limit = @Slicing_Rowcount + @RowCount_limit;
END;SELECT
PreviousValue = LAG(A.Service_Date) OVER (ORDER BY A.Row_Id)
, Starte_Range = A.Service_Date
, End_Range = ISNULL(LEAD(A.Service_Date) OVER (ORDER BY A.Row_Id), '9999-12-31 00:00:00.000')
FROM #Temp_Result A;
GO
Dear Phil Parkil,
Thanks a lot for your effort in formatting my code to make use for others. Great.Have a Good day
Regards,
Kannan.C
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply