0

I have 3 columns (DateTime, GroupName, Value), some of these groups are closely related and I would like to display these in a stacked graph. The problem I am facing (I THINK) is that I don't have entries for all groups at all times.

(cannot find a decent way to add a table, so here is some code)

Datetime  Groupname   Value
1         a   whatever
1         b   whatever
1         c   whatever
2         a   whatever
2         b   whatever
3         a   whatever
3         b   whatever
3         c   whatever
4         b   whatever

So in the example I don't have an entry for C at time 2. And I also don't have an entry for A and B at time 4.

Resulting in: edit: added to onedrive link

With my limited SQL skills I am not sure how to fix this. How do I get the graph to connect the points from the DateTime points where we do have data, and ignore DateTime points where we do not have data?

11-07-2016 Edit

Ok, so here some pictures of the actual data

No rep - Onedrive it is https://1drv.ms/f/s!AhKMFQBAmZ7GgYEMzdpTBvuXTi5gAQ

the graph looks different than my first example because I set the X-axis to scalar.

On 7/5/2016 and 7/6/2016 (month/day/year notation) the CH3 is low and 0. If I remove CH3 from results the graph looks ok.

@sqlandmore.com This is the query. Very basic. Data is coming from a database were the datetime is not in a proper datetime format so thats gets converted into the wimsview.

SELECT
 WimsView.TagID
 ,WimsView.SampleDateTime
 ,WimsView.SampleValue
 ,WimsView.TagName
FROM
  WimsView
WHERE
 WimsView.SampleDateTime > N'07/4/2016 00:00:00'
 AND ((WimsView.TagName LIKE N'%MBA%') OR (WimsView.TagName LIKE N'%MBB%')    OR (WimsView.TagName LIKE N'%GF1_DPC%')OR (WimsView.TagName LIKE N'%KF1_DPC%')OR    (WimsView.TagName LIKE N'%CH3_DPC%')) 
 AND WimsView.SampleValue IS NOT NULL
N2GO
  • 1
  • 1
  • This really looks like data is causing this. Are you certain that you do not have times when all values drop to 0 for all categories? I can't reproduce this based on the scenario you describe. See real data would help. – R. Richards Jul 10 '16 at 11:04
  • Thanks. I have added the info in the post. It is a onedrive link. – N2GO Jul 11 '16 at 10:32
  • Can anyone offer some more suggestions? Still stuck. It is not only a problem in stacked graphs. Every graph I make with several series has this problem. My data for each series comes in at inconsistent times causing gaps in my graph where one serie has a value and the other series do not. – N2GO Aug 12 '16 at 12:09
  • Thought this post could help: http://stackoverflow.com/questions/24470/sql-server-examples-of-pivoting-string-data Following the post of @mxasim But whenever I run those queries in SSRS query builder I keep getting "Action" unknown column. Any ideas? – N2GO Aug 13 '16 at 05:08
  • From your question: "If I remove CH3 from results the graph looks ok". There is the issue. So, either don't report CH3, or report it in a separate chart. Is that possible? The values in CH3 are too different from the other `TagName` data in your dataset. Report it separately, or think about converting the sample date/time to a date without the minute and seconds, or event hours to see if that smooth's thing out. – R. Richards Aug 13 '16 at 12:40
  • No CH3 is just a clear example of where it goes wrong. If I graph other data I have the same problem. I have been trying to use pivot and the other method from that post by mxasim but am not getting it to work. – N2GO Aug 14 '16 at 01:13

1 Answers1

0

You can't ignore specific values on the graph. you can either change your select statement not to include them you can calculate an "average" (if possible) for the missing value in order to fill the missing "points" in your graph or another calculation (i.e - same value as previous one on the graph) whatever you decide - it should be handled on a query level, not on a drwaing level

sqlandmore.com
  • 163
  • 1
  • 8
  • Another calculation to keep the same value as the previous would probably work best. Can you point me in the right direction on how to do that? SQL is not my first language... And I was not anticipating getting a graph out of some data would become this complicated :) – N2GO Jul 11 '16 at 11:07
  • What's the query you are using right now? Send it over – sqlandmore.com Jul 12 '16 at 05:59
  • I have added it to my original post. – N2GO Jul 22 '16 at 09:26