How to generate a line chart with on the nth data points

Question

How to generate a line chart with on the nth data points

mballentine

SSC Eights!

Points: 884
More actions
January 10, 2017 at 12:45 pm

#333397

Hi,
I have to generate a line graph with only every 10th record of data. I currently have a chart that displays all the data points that meet the parameters, which are start date, end date and widget#. The customer is complaining that the graph is too busy and only wants to see every 10th data point.
Here is the query I currently have.
SELECT Data.[Run Start], Data.[Run Tag], Data.Accepted, Data.Aborted, Temps.[Run ID], Temps.Camera, Temps.Time, Temps.[Roi Number], Temps.Temperature,
Data.[Machine ID]
FROM Data INNER JOIN
Temps ON Data.ID = Temps.[Run ID]
WHERE (Data.[Run Start] >= @StartDate) AND (Data.[Run Start] <= @EndDate) AND (Data.[Run Tag] = @LadleNumber)
ORDER BY Data.[Machine ID] DESC, Temps.Camera, Temps.Time
Can someone please head me in the right direction. I have read everything I could find on the internet, but have not had luck with finding an example that relates to this.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

doug.brown SSCertifiable Points: 5608 More actions · Answer 1

You can define the interval for each axis in the Axis Properties. Click on an axis and it should draw a box around the values. Now right click there and go to Vertical (or Horizontal) Axis Properties. In Axis Options, you'll see the "Interval" and "Interval Type" default to Auto; you can set these to what you wish. The Major Tick Marks tab might also be off assistance, as that is another place you can set the interval.

mballentine SSC Eights! Points: 884 More actions · Answer 2

Hi,

I just tried changing the intervals to 10 milliseconds. It did not change the number of data points, it changed the number of tick marks and labels. 🙁

pietlinden SSC Guru Points: 63512 More actions · Answer 3

One way might be something like this... use a CTE to select every nth record, and then graph that.

with Demo as

(

select custID

, OrderMonth

, qty

, ROW_NUMBER() over (partition by CustID order by OrderMonth) as rn

from Sales.CustOrders

)

select CustID

,OrderMonth

,qty

, rn

from Demo

WHERE rn = 1

;

mballentine SSC Eights! Points: 884 More actions · Answer 4

pietlinden - Wednesday, January 11, 2017 8:45 PM
One way might be something like this... use a CTE to select every nth record, and then graph that.with Demo as(select custID, OrderMonth, qty, ROW_NUMBER() over (partition by CustID order by OrderMonth) as rnfrom Sales.CustOrders)select CustID ,OrderMonth ,qty , rnfrom DemoWHERE rn = 1;

Hi,

Thank you for the reply. I have tried what you suggested but I am only getting one record to the graph.
Below is the code for the stored procedure I am trying to use. Also, yesterday I tried to use a cursor with the same result of only one record to the graph. When I ran the stored procedure, with cursor, in Mgt Studio I would get numerous records but only one goes to the graph. I still have the cursor code in there, but have commented it out as you will see.

USE [SDI_LMF] GO /****** Object: StoredProcedure [dbo].[GraphData] Script Date: 1/13/2017 10:18:43 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER procedure [dbo].[GraphData] @StartDate datetime, @EndDate datetime, @LadleNumber int as SELECT Data.[Run Start], Data.[Run Tag], Data.Accepted, Data.Aborted, Temps.[Run ID], Temps.Camera, Temps.Time, Temps.Temperature, Data.[Machine ID], Temps.[Include In Graph], Temps.[Roi Name] INTO #TempTable

FROM Data INNER JOIN Temps ON Data.ID = Temps.[Run ID]

WHERE (Data.[Run Start] >= @StartDate) AND (Data.[Run Start] <= @EndDate) AND (Data.[Run Tag] = @LadleNumber) ORDER BY Data.[Run Tag], Data.[Machine ID] DESC, Temps.Camera, Temps.Time;

/**DECLARE TempCursor CURSOR SCROLL FOR SELECT [Run Start], [Run Tag], Aborted, [Run ID], Camera, Time, Temperature, [Machine ID], [Include In Graph], [Roi Name] from #TempTable

OPEN TempCursor

FETCH ABSOLUTE 10 FROM TempCursor

WHILE @@Fetch_Status = 0 FETCH RELATIVE 10 FROM TempCursor

CLOSE TempCursor DEALLOCATE TempCursor

drop table #TempTable**/

with Graph_CTE([Run Start], [Run Tag], Aborted, [Run ID], Camera, Time, Temperature, [Machine ID], [Include In Graph], [Roi Name], ROW_NUMBER) as ( select [Run Start], [Run Tag], Aborted, [Run ID], Camera, Time, Temperature, [Machine ID], [Include In Graph], [Roi Name], ROW_NUMBER() over (partition by [Run ID] order by Time) as rn from #TempTable )

select[Run Start], [Run Tag], Aborted, [Run ID], Camera, Time, Temperature, [Machine ID], [Include In Graph], [Roi Name], rn from Graph_CTE WHERE rn = 1

doug.brown SSCertifiable Points: 5608 More actions · Answer 5

In Piet's code, try replacing the last line:
WHERE rn = 1

[p][/p]
with something like this, using Modulo:
WHERE rn % 10 = 0

mballentine SSC Eights! Points: 884 More actions · Answer 6

mballentine

SSC Eights!

Points: 884

January 17, 2017 at 10:42 am

#1923229

Thank you, that helped.

pietlinden SSC Guru Points: 63512 More actions · Answer 7

doug.brown - Friday, January 13, 2017 2:03 PM
In Piet's code, try replacing the last line:
WHERE rn = 1
[p][/p]
with something like this, using Modulo:
WHERE rn % 10 = 0

Thanks Doug... Helps a lot if I'm paying attention while posting... =)