1

Source table

dbo.sourcetable

The table has more columns than shown here:

|   ID   |   TrackingID   |      TrackingTime     |....
|--------|----------------|-----------------------|
|   001  |       10       |2017-03-08 10:12:20.240|
|   003  |       50       |2017-03-08 12:30:23.240|
|   001  |       10       |2017-03-03 09:10:23.240|
|   002  |       10       |2017-03-06 10:12:23.240|
|   001  |       15       |2017-03-05 10:12:23.240|
|   001  |       20       |2017-03-08 17:12:23.240|
|   002  |       15       |2017-03-04 00:12:23.240|
|   003  |       10       |2017-03-06 01:18:23.240|
....

I also have a table which provides all possible TrackingIDs and their description. If this is of any use.

Query

I count the last given TrackingIDs für a time range with:

--Initializing---------
DECLARE @Begin    datetime,
DECLARE @End      datetime,

SET     @Begin =  '2017-03-05 00:00:00';
SET     @End   =  '2017-03-06 00:00:00';

--Coding CTE----------
WITH CTE AS
(
    SELECT  *
           ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TrackingTime DEC) rn
    FROM   dbo.sourcetable
    WHERE      (1 = 1)
           AND (TrackingTime BETWEEN @Begin AND @End)
)
--Select CTE----------
SELECT  TrackingID
       ,COUNT(TrackingID) AS TotalIDs
FROM    CTE
WHERE     (1 = 1)
      AND rn = 1
GROUP BY TrackingID
ORDER BY Tracking ID ASC

I receive a table which shows the total of IDs for each TrackingID:

|    TrackingID    |    TotalIDs    |
|------------------|----------------|
|        10        |        12      |
|        15        |         3      |
|        20        |        10      |
...

What I want

In order to create a chart in SSRS I want to split up a time range in many intervals. Then use the beginning and end of each interval as the variable input of my query and in the end receive a table which shows the total IDs for each TrackingID in that interval.

|      TrackingTime     |    10    |    15    |    20    |...
|-----------------------|----------|----------|----------|
|2017-03-05 00:00:00.000|    13    |    10    |     3    |
|2017-03-05 00:00:02.000|    11    |     8    |     5    |
....
|2017-03-06 00:00:00.000|    20    |    11    |     7    |

Query

DECLARE @TotalTable Table
(
    TrackingTime datetime PRIMARY KEY CLUSTERED,
    ----------------------------
        Alle the columns for
          the TrackingIDs
    ----------------------------
)

WHILE @Begin <= @End
BEGIN
      INSERT INTO @TotalTable (TrackingTime) VALUES (@Begin)

      ----------------------------------------
           I don't know, so maybe insert 
                 magic code here?!
      ----------------------------------------

      SET @Begin = DATEADD(SS, 120, @Begin)
END;

SELECT *
FROM   @TotalTable AS TT

I think I have to work with dynamic SQL somehow as not for every interval something can be counted. I am sorry if this is to much to ask for. Thank you for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Phil
  • 113
  • 3
  • Will the reporting range always br one day? Will the intervals always be 2 minutes? Will you always have the same list of TrackingIDs? Could your output have TrackingID as the first column, and then 720 columns counting each interval? – MatBailie Mar 07 '17 at 23:26
  • Is there a reason you can't use a Matrix in SSRS to pivot your data? Then you only need a query with three columns; TrackingID, Interval, CountIDs – MatBailie Mar 07 '17 at 23:28
  • No the range will be given by the user. The intervals though are constant (2min). The possible TrackingIDs are given in a other table but are not alway all given in one interval as they are than zero and therefor not counted. And yes the result table could have the intervals as columns . – Phil Mar 07 '17 at 23:32
  • I wasn't aware that the Matrix could handle that and than create a chart out of it. That would be fantastic if that is easy to set up but I have only little knowledge in SSRS. – Phil Mar 07 '17 at 23:34
  • @MatBailie If you could help me how to set this Matrix up that would be great. Thank you. – Phil Mar 08 '17 at 00:19

0 Answers0