Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this.
SELECT [Id]
,[VehivleTag]
,[ValidDate]
,[INorOUT]
FROM [Taxi].[dbo].[InputOutput]
with a date like this:
Id | VehivleTag | ValidDate | INorOUT |
56 | 163 | 2019-01-02 12:37:56.000 | 1 |
146 | 163 | 2019-01-02 13:14:21.000 | 0 |
248 | 163 | 2019-01-02 13:57:38.000 | 1 |
591 | 163 | 2019-01-02 15:56:27.000 | 0 |
746 | 164 | 2019-01-02 16:46:22.000 | 1 |
2222 | 164 | 2019-01-05 06:50:08.000 | 0 |
2574 | 164 | 2019-01-01 19:06:13.000 | 0 |
2591 | 164 | 2019-01-01 19:12:10.000 | 1 |
3049 | 164 | 2019-01-02 06:44:32.000 | 0 |
3555 | 164 | 2019-01-02 08:12:30.000 | 1 |
3812 | 164 | 2019-01-02 08:58:28.000 | 0 |
3863 | 164 | 2019-01-02 09:10:35.000 | 1 |
4343 | 164 | 2019-01-14 08:24:39.000 | 1 |
4656 | 165 | 2019-01-14 09:30:56.000 | 0 |
4745 | 165 | 2019-01-14 09:55:47.000 | 1 |
4850 | 165 | 2019-01-14 10:25:43.000 | 0 |
4874 | 165 | 2019-01-14 10:31:34.000 | 1 |
4984 | 165 | 2019-01-14 11:05:10.000 | 0 |
I want to find the number of VehivleTag that count of Id if INorOUT=1 is less than 3.
in this sample data I want to get 2. means VehivleTag in 163 and 165.
Try like:
Count(Distinct {<VehivleTag={"=Count({<INorOUT={1}>}VehivleTag)<3"}>} VehivleTag)
If ID can repeat and you only want to check DISTINCT ID to be less than 3, then you can try this
Count(DISTINCT {<VehivleTag={"=Count(DISTINCT {<INorOUT={1}>} ID) < 3"}>} VehivleTag)
No. It dose not work true.
Try this
Count(DISTINCT Aggr(If(Count(DISTINCT {<INorOUT={1}>} ID) < 3, VehicleTag), Date, VehicleTag))