CODEWITHSUNDEEP
t-sqlsql-server-2014
Timmy
Timmy

Reputation: 553

Counting rows between two dates on JOIN

I have two Tables named String and Float

String
|               DateAndTime           | Val |
---------------------------------------------
|       2016-07-26 18:35:31.000       | AX2 |
|       2016-07-26 18:30:31.000       | G27 |
|       2016-07-26 18:25:31.000       | AX2 |
|       2016-07-26 18:20:31.000       | AX2 |
|       2016-07-26 18:20:31.000       | G27 |

Float
|               DateAndTime           | Val | Marker |
------------------------------------------------------
|       2016-07-26 18:31:31.000       | 234 |   X    |
|       2016-07-26 18:26:31.000       | 612 |   y    |
|       2016-07-26 18:21:31.000       | 12  |   a    |
|       2016-07-26 18:21:31.000       | 123 |   f    |

From these two tables I want to get the min and max date from each Val in the string table that has occurred in the past 24 hours and display that information. I also want to count every row that was inserted into the float table between the min and max time of each Val and display that along side.

Below is my query. The part that that queries the StringTable works great and I get the results I expected. I then tried to do a JOIN on the Float table and count the rows. 

    SELECT
    S.Batch,
    COUNT(F.DateAndTime) AS DataPointsRecorded,
    S.CycleStart,
    S.LastRecordedTime
FROM
    (
        SELECT DISTINCT 
                 Val AS Batch,
                 MIN(DateAndTime) AS CycleStart, 
                 MAX(DateAndTime) AS LastRecordedTime
        FROM     dbo.StringTable
        WHERE    DATEDIFF(HOUR,DateAndTime, GETDATE()) < 24
        AND      Val IS NOT NULL
        GROUP BY Val
    ) S 
JOIN     dbo.FloatTable F ON S.CycleStart = F.DateAndTime
GROUP BY S.Batch, S.CycleStart, S.LastRecordedTime
ORDER BY CycleStart desc, Batch

This resulted in every DataPointsRecorded coming out as the same exact number for every row.

I then changed how I joined the two tables using

JOIN dbo.FloatTable F ON F.DateAndTime BETWEEN S.CycleStart AND S.LastRecordedTime

And that just resulted in very large numbers in each row for DataPoints.

My expected results would be something like this based off the example I gave earlier.

| Batch | DataPoint |          CycleStart         |               LastRecordedTime        |
-----------------------------------------------------------------------------------------------
|  AX2  |     4     |   2016-07-26 18:20:31.000       |     2016-07-26 18:35:31.000       |
|  G27  |     3     |   2016-07-26 18:20:31.000       |     2016-07-26 18:30:31.000       |

Upvotes: 0

Views: 54

Answers (1)

objectNotFound
objectNotFound

Reputation: 1783

try this ( I removed the group by of the 2 columns CycleStart, LastRecordedTime )

    SELECT
    S.Batch,
    COUNT(F.DateAndTime) AS DataPointsRecorded,
    Min(S.CycleStart) as CycleStart ,
    Max(S.LastRecordedTime) as LastRecordedTime
FROM
    (
        SELECT DISTINCT 
                 Val AS Batch,
                 MIN(DateAndTime) AS CycleStart, 
                 MAX(DateAndTime) AS LastRecordedTime
        FROM     dbo.StringTable
        WHERE    DATEDIFF(HOUR,DateAndTime, GETDATE()) < 24
        AND      Val IS NOT NULL
        GROUP BY Val
    ) S 
JOIN     dbo.FloatTable F ON F.DateAndTime BETWEEN S.CycleStart AND S.LastRecordedTime
GROUP BY S.Batch -- S.CycleStart -- S.LastRecordedTime Commented out the last 2
ORDER BY CycleStart desc, Batch

Edit: Explanation

The reason why you were seeing more data with your query was because you were aggregating it at a more granular level that included timestamps which forces sql to include all unique combinations of the Batch+CycleStart+LastRecordedTime

Upvotes: 1

Related Questions