Retrieve last 12 months
-
I have this code in mssql that i want to exclude de value 0 when it appears, so don't bug the average value of the query, also the query return this even in days like the one used n the code below, how can in fix both of the problems
the return of the date because it is not in a closed period, for example 00:00:00 on 05/20/2022 it returns from the day 05/03/2022
the second problem is that when making the difference between the maximum and minimum values of the period of time I cannot have a value returning zero
here is how my db looks like
Corrente_L1 E3TimeStamp
0 2015-01-01 00:00:00.000
1 2015-01-01 00:15:00.000
2 2015-01-01 00:30:00.000
3 2015-01-01 00:45:00.000
4 2015-01-01 01:00:00.000
5 2015-01-01 01:15:00.000
6 2015-01-01 01:30:00.000
7 2015-01-01 01:45:00.000
8 2015-01-01 02:00:00.000
9 2015-01-01 02:15:00.000
10 2015-01-01 02:30:00.000
11 2015-01-01 02:45:00.000
12 2015-01-01 03:00:00.000
13 2015-01-01 03:15:00.000
14 2015-01-01 03:30:00.000
15 2015-01-01 03:45:00.000
16 2015-01-01 04:00:00.000
17 2015-01-01 04:15:00.000
18 2015-01-01 04:30:00.000
19 2015-01-01 04:45:00.000
20 2015-01-01 05:00:00.000
each day has a total of 96 records
SELECT
DATEADD ( MINUTE , ( DATEDIFF ( MINUTE , 0 , E3TimeStamp ) / 43800 ) * 43800 , 0 ) AS E3TimeStamp , /* 15 -15min */
max(Current_L1) - min(Current_L1) as TotalDC,
COUNT ( * ) AS Total /* total of points*/
FROM
Table1
WHERE E3TimeStamp >= DATEADD ( MINUTE , -525600 , DATEADD ( MINUTE , DATEDIFF (MINUTE , 0, ''2022-20-05') , 0) )
AND E3TimeStamp <= DATEADD ( MINUTE , DATEDIFF ( MINUTE , 0, '2022-20-05' ) , 0 )
GROUP BY
DATEADD ( MINUTE , ( DATEDIFF ( MINUTE , 0 , E3TimeStamp ) / 43800 ) * 43800 , 0)
ORDER BY E3TimeStamp asc
E3TimeStamp Current_L1 TotalDC
2021-05-03 02:00:00.000 1295 1296
2021-06-02 12:00:00.000 2919 2920
2021-07-02 22:00:00.000 2919 2920
2021-08-02 08:00:00.000 2919 2920
2021-09-01 18:00:00.000 2919 2920
2021-10-02 04:00:00.000 2919 2920
2021-11-01 14:00:00.000 2919 2920
2021-12-02 00:00:00.000 2919 2920
2022-01-01 10:00:00.000 2919 2920
2022-01-31 20:00:00.000 2919 2920
2022-03-03 06:00:00.000 2919 2920
2022-04-02 16:00:00.000 2919 2920
2022-05-03 02:00:00.000 1624 1625
- This topic was modified 2 years, 6 months ago by usertest.
-
Imo the question needs input data in SQL and the expected output
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
This could be pretty easy but, I have to ask, why are you basing your reporting periods on 365*24/12 - 730 hours = 43,800 minutes = 30.4 days? All that does is make thing more complicated and, since you taking daily averages, really makes no sense to me. Why aren't you using just whole calendar months, which will also save on the bit of agony that your method will cause on leap years?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I need the code to work with days, weeks and months to so I tought that working with minutes and the average number of days in the month would simplify things
-
Ok... That was an important part for us to know there. With that out of the way, we need to know a few things to be able to help you the best.
Ok... about your columns or source data...
Is Corrente_L1 just and INTEGER column that counts up like an IDENTITY or SEQUENCE column would?
Second... you shouldn't be trying to determine the number of rows based on Corrente_L1 the difference of Min and Max values for a given time period because there is no guarantee that there are no missing incremental values especially if an SQL Server Service restart occurs.
You say you have 96 points per day and I agree that's simply 24 hours 15 minutes at a time. Those rows just be there.
I think that your question is based solely on how to establish periods from the mistake of trying to do the report based on proper time intervals.
So... let's stop mucking around with that.
Instead, see the article at the first link in my signature line below for how we'd like for you to provide some actual data for the report(s) you're trying to build. Please follow the method in the article because it would really help if the data were in such a readily consumable format.
Also, what is the first day of the week for you?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
so the code used to create the table was this it's a identity table so none value would repeat
CREATE TABLE Table1 (Corrente_L1 INT IDENTITY(0,1), E3TimeStamp DATETIME);
DECLARE @start DATETIME;
DECLARE @end DATETIME;
SET @start = '20150101';
SET @end = '20230101';
WITH CTE_DT AS
(
SELECT @start AS DT
UNION ALL
SELECT DATEADD(MINUTE,15,DT) FROM CTE_DT
WHERE DT< @end
)
INSERT INTO Table1
SELECT DT FROM CTE_DT
OPTION (MAXRECURSION 0);the first day that is beign selected to the query is 2022-20-05
when I set this code to run in month and weeks it returns the expected result, and in years the first day that returns is almost always the the 03 of the month.
it was used the Corrente_L1 because it's the value that will be variable that is being monitored and will be displayed
-
You're still working on your Calendar table. I'll be happy to show you how to do that but that's not the real issue. The issue is, what do you want to report on by day, week, month, and year?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I would like the total of each period separated by each respective research period, for example the week of 05/18/2022 would return the total of each of the last seven days
-
"Each record"? Which table and could you please provide some test data for the data you want to report on?
All you've provided so far is a table with an IDENTITY column an a sample date that occurs every 15 minutes but no actual data to aggregate or count on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
the Corrente_L1 will receive values every 15 min, I need the total of Corrente_L1 for the respective period, if it is for the month, it should return the total for each of the last 30 days, if it's year I need the total for each of the last 12 months
the identity column was a way I found to have a huge enough period of time and no repeated value to be able to carry out the query and see if they returned the expected values
in the image below there is another example of how the data will be stored
-
Here is that link again: https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have no way to provide the code that generated the image table because it was a scada software that created it and it will store only data in the sql database and will use the sql code to perform searches in the database.
And that's where the code with the identity comes in because I needed to make sure that I wouldn't have any repeated values so that I could be sure that the search is returning the correct values as a total of 96 values per day
And I thank you in advance for the help I receive.
- usertest wrote:
the Corrente_L1 will receive values every 15 min, I need the total of Corrente_L1 for the respective period, if it is for the month, it should return the total for each of the last 30 days, if it's year I need the total for each of the last 12 months
the identity column was a way I found to have a huge enough period of time and no repeated value to be able to carry out the query and see if they returned the expected values
in the image below there is another example of how the data will be stored
If you look at that graphic, why is it that you think that Corrente_L_1 is an Identity column? Can't you look at the schema for the table that you're getting the datafrom even though it's a 3rd party app? And why are all the entries identical except for the time?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. What I'm trying to get you to do is that your per minute stuff is flat out the wrong way to do thing and I'm trying to get you to cough up some raw data in a readily consumable format so that I can show you how to easily do it correctly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Checking back in here. The OP generated the 15 minute data from the recursive CTE and that's all the data they currently have? As an aside: a recursive CTE is a slow way to generate rows and it takes many seconds to run. The OP is using an ID column to "be sure that the search is returning the correct values as a total of 96 values per day". Maybe they're looking for relative date queries with summarizations. The goal appears to be to confirm the DB Engine works properly
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply