March 29, 2016 at 8:19 pm
I would like to run for 100 days of data.
getdate() - 6 april
7april - 14 april
and so on..
How can I break into 7 days for each file into tables?
I tried something like this but it will only run for the first 7 days.
How can I run it without running it manually?
where stdutc >= GETDATE()
and stdutc <= DATEADD(d,7,GETDATE())
March 29, 2016 at 8:37 pm
March 29, 2016 at 9:51 pm
If you had a calendar table available the you can do it like this (note my code and comments):
USE tempdb
GO
-- Simplified calendar table with only the columns you need
IF OBJECT_ID('dbo.calendar') IS NOT NULL DROP TABLE dbo.calendar;
CREATE TABLE dbo.calendar(DateTxt date primary key, DayOfWeekNbr tinyint NOT NULL);
-- On a real calendar table you want this index present with the required INCLUDE columns
CREATE UNIQUE NONCLUSTERED INDEX uq_xxx ON dbo.calendar(DateTxt, DayOfWeekNbr);
-- Populate the calendar table
WITH dates(DateTxt) AS
(
SELECT TOP (1000)
CAST(DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'20150101') AS date)
FROM sys.all_columns
)
INSERT dbo.calendar
SELECT DateTxt, DayOfWeekNbr = DATEPART(WEEKDAY,DateTxt) FROM dates;
-- routine
DECLARE @weeks int = 10; -- some kind of variable or parameter to say where to stop.
SELECT TOP(@weeks) DateTxt, DATEADD(DAY,6,DateTxt)
FROM dbo.calendar
WHERE DateTxt >= CAST(getdate() AS date) -- to avoid losing the first week
AND DayOfWeekNbr = DATEPART(WEEKDAY,getdate());
I recently posted a tally table script named getnumsAB[/url] that was designed for this exactly this kind of thing. Using GetNumsAB you could do this:
DECLARE @StartDate datetime = '1/1/2015', @EndDate datetime = '2/28/2015';
SELECT
WeekNbr = 'Week #'+CAST(RN AS varchar(2)),
WeekStart = CONVERT(DATE, DATEADD(DAY,N1,@StartDate)),
WeekEnd = CONVERT(DATE, DATEADD(DAY,N2-1,@StartDate))
FROM dbo.getnumsAB(0,datediff(DAY,@StartDate,@EndDate),7,1);
My experience has been that the calendar table will perform slightly better when indexed correctly.
-- Itzik Ben-Gan 2001
March 29, 2016 at 10:02 pm
It will be continuous. I am just testing for 1 month on how it works. The weekly data will be send as an output file.
Is there any way that I can set in parameter to run week after week? Or just calander will be the option?
March 29, 2016 at 10:50 pm
girl_bj (3/29/2016)
It will be continuous. I am just testing for 1 month on how it works. The weekly data will be send as an output file.Is there any way that I can set in parameter to run week after week? Or just calander will be the option?
I'm not understanding exactly what you're asking. At what point would you want the solution to begin and stop returning rows?
-- Itzik Ben-Gan 2001
March 30, 2016 at 12:44 am
I would like to breakdown 7 days each for 100 days.
fileA: getdate() - 6 april
fileB: 7 april - 14 april
fileC: 15 april - 22 april
and so on.
Currenctly im doing it manually
where columna between getdate() and '2016-04-06'
To get the next file will be where columna between 2016-04-07 and 2016-04-14
Any way i can schedule it?
March 30, 2016 at 5:29 am
To get a period of 100 days starting "now" (whenever now is), use a range that starts at "CAST(CURRENT_TIMESTAMP AS date)" and ends just before "DATEADD(day, 100, CAST(CURRENT_TIMESTAMP AS date))".
To get periodds of 7 days, use "GROUP BY DATEDIFF(day, CURRENT_TIMESTAMP, TransactionDate) / 7" - integer division means that the remainder is discarded, so for today until today + 6 days the result will be 0, then 7 days will return 1, etc.
If you need columns in the result that show the start and end of each 7-day period, use "DATEADD(week, DATEDIFF(day, CURRENT_TIMESTAMP, TransactionDate) / 7, CAST(CURRENT_TIMESTAMP AS date))" and "DATEADD(day, 6, DATEADD(week, DATEDIFF(day, CURRENT_TIMESTAMP, TransactionDate) / 7, CAST(CURRENT_TIMESTAMP AS date)))"
Note that a 100-day period is not a full multiple of 7, so the last week will overstate the end date. If you don't want that, you'll have to add some fancy CASE logic to the computation for the end date of the last 7-day period.
March 30, 2016 at 8:59 pm
I still need to do it manually?
getdate till 7 days will be 7 april
Then 8 april till 7 days and so on.
March 30, 2016 at 10:57 pm
This query will give you the start and end date over a 7 day interval for a period of 100 days depending upon the current date.
;WITH cte
AS (SELECT CONVERT(DATE, DATEADD(dd, number, Getdate())) StartDate
FROM master..spt_values
WHERE type = 'P'
AND number%7 = 0
AND number <= 100)
SELECT StartDate,
Lead(DATEADD(dd, -1, startdate),1,DATEADD(dd, 6, startdate))
OVER(
ORDER BY startdate)EndDate
FROM cte c1
ORDER BY startdate
getdate till 7 days will be 7 april
Why should it be 7 April if you are taking today March 31st into account ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 30, 2016 at 11:19 pm
If you are still not aware how to use my earlier query here is the sample usage on AdventureWorks2012 DB that SUMS Totaldue for 100 days spilt into 7 day interval based on the startdate set.You can set the interval and the startdate in the variables.
DECLARE @STARTDATE DATE='2005-07-01'
DECLARE @INTERVAL INT=100
;WITH cte
AS (SELECT CONVERT(DATE, DATEADD(dd, number,@STARTDATE)) StartDate
FROM master..spt_values
WHERE type = 'P'
AND number%7 = 0
AND number <= @INTERVAL)
SELECT StartDate,
Lead(DATEADD(dd, -1, startdate),1,DATEADD(dd, 6, startdate))
OVER(
ORDER BY startdate)EndDate
INTO #tbl
FROM cte c1
ORDER BY startdate
SELECT startdate,
enddate,
SUM(totaldue)TotalDueOverSevenDays
FROM Adventureworks2012.sales.salesorderheader S
CROSS APPLY(SELECT TOP 1 *
FROM #tbl
WHERE S.orderdate BETWEEN startdate AND enddate
ORDER BY startdate)T
GROUP BY startdate, enddate
DROP TABLE #tbl
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 31, 2016 at 12:49 am
How can I replace the LEAD function?
I'm using 2008.
March 31, 2016 at 1:46 am
Sorry did not realize that you had posted in 2008 section.Lead can be easily replaced with a left join.
DECLARE @STARTDATE DATE='2005-07-01'
DECLARE @DAYS INT=100
DECLARE @INTERVAL INT=7;
WITH cte
AS (SELECT CONVERT(DATE, DATEADD(dd, number, @STARTDATE)) StartDate,
number
FROM master..spt_values
WHERE type = 'P'
AND number%7 = 0
AND number <= @DAYS)
SELECT c1.startdate,
Isnull(DATEADD(dd, -1, c2.startdate), DATEADD(dd, 6,
c1.startdate))EndDate
INTO #tbl FROM cte c1 LEFT JOIN cte c2
ON c2.number = c1.number + 7
ORDER BY c1.startdate
SELECT startdate,
enddate,
SUM(totaldue)TotalDueOverSevenDays
FROM adventureworks2012.sales.salesorderheader S
CROSS APPLY(SELECT TOP 1 *
FROM #tbl
WHERE S.orderdate BETWEEN startdate AND enddate
ORDER BY startdate)T
GROUP BY startdate,enddate
DROP TABLE #tbl
One doubt..What happens for the last 2 days of your 100 day period when they dont cover up the 7 day interval.For example 14 seven days interval will cover 98 days so what would happen for the remaining 2 days ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 31, 2016 at 9:02 am
girl_bj (3/30/2016)
I still need to do it manually?getdate till 7 days will be 7 april
Then 8 april till 7 days and so on.
No. Just take the query you use to get the raw data, add a WHERE to limit the results to only data for (today) until (today + 100 days), then add the GROUP BY to produce a single row from each 7-day period (of which the last is actually just 2 days) and use the appropriate aggregation functions in the SELECT list to get the data you need.
NOTE: If the above does not help, then you should post your actual problem. Post CREATE TABLE statements for the tables that have to be used (please simplify as much as you can!), INSERT statements with a handful of rows of well-chosen sample data to illustrate your problem, and the expected results. Also add the query you have so far so that we don't waste time solving issues you already figured out, but can move straight to the part where you are stuck.
April 1, 2016 at 3:19 am
I manage to run in every 7 days continuously. Added in ROW_NUMBER() OVER (ORDER BY startdate) AS Row too.
How can I push this record as an output. I tried SSIS to push output to file. But for this how to do looping in SSIS?
Example row 1 records will push to fileA.txt
row 2 records will push to fileB.txt and so on.
Any sample?
April 1, 2016 at 3:36 am
girl_bj (4/1/2016)
I manage to run in every 7 days continuously. Added in ROW_NUMBER() OVER (ORDER BY startdate) AS Row too.How can I push this record as an output. I tried SSIS to push output to file. But for this how to do looping in SSIS?
Example row 1 records will push to fileA.txt
row 2 records will push to fileB.txt and so on.
Any sample?
Insert it into a recordset destination, then use a foreach loop ADO enumerator, setting it for every row in the table. Then inside the loop, set the file name as required, and then export to a file.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply