Choosing two fields from only the two most rec the first and second records only

Question

Choosing two fields from only the two most rec the first and second records only

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
August 13, 2021 at 7:20 pm

#3919777
I have a table and the data looks like the results of this query:
```
SELECT TOP 10 [CallCount], [timestamp] FROM [a2wh].[dbo].[CallLogCommonCount] ORDER BY [timestamp] desc
```
CallCount Timestamp
24198779 2021-08-13 14:08:52.783
24198659 2021-08-13 14:02:08.020
24198659 2021-08-13 14:01:11.897
24198377 2021-08-13 14:00:31.427
24197761 2021-08-13 13:43:57.933
24197761 2021-08-13 13:36:23.360
24197407 2021-08-13 13:24:18.800
24197407 2021-08-13 13:23:30.240
24197407 2021-08-13 13:23:27.270
24197407 2021-08-13 13:22:07.520
I need to write (will be part of a stored procedure) a query that has as one of its features the ability to select the first two most recent Timestamps and returning the respective CallCount for a comparison (that will EXEC another stored procedure).
Problem is with the second block of code to get the second record gives an error and has bad logic too: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Any ideas of a better way to do this?:
```
DECLARE @Count1 INT = (SELECT [CallCount] FROM [a2wh].[dbo].[CallLogCommonCount]
WHERE [timestamp] IN (SELECT MAX([timestamp]) FROM [a2wh].[dbo].[CallLogCommonCount]))
SELECT @Count1 

DECLARE @Count2 INT = (SELECT [CallCount] FROM [a2wh].[dbo].[CallLogCommonCount]
WHERE [timestamp] NOT IN (SELECT MAX([timestamp]) FROM [a2wh].[dbo].[CallLogCommonCount]))
SELECT @Count2 -- NOT IN obviously will return every value except that from the first record

IF @Count1 = @Count2 EXEC [a2wh].[dbo].[sp_CLC_Count_email]
```
So the only values I want returned are 24198779 and 24198659

Viewing 5 posts - 1 through 4 (of 4 total)

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 1

I found a similar problem on another site that I modified and got to work:

DECLARE @Count2 INT = (SELECT [CallCount]
FROM [a2wh].[dbo].[CallLogCommonCount] Count1
WHERE (2-1) = ( 
SELECT COUNT(DISTINCT(Count2.[timestamp]))
FROM [a2wh].[dbo].[CallLogCommonCount] Count2
WHERE Count2.[timestamp] > Count1.[timestamp]))

kaj SSCommitted Points: 1605 More actions · Answer 2

Hmmmm.... May I offer you a couple of alternatives that I find a tiny bit more elegant?

-- Set up test data...
DECLARE @CallLogCommonCount TABLE (
    CallCount int,
    Timestamp datetime2(3)
    );

INSERT INTO @CallLogCommonCount (CallCount, Timestamp)
VALUES
    (24198779           ,'2021-08-13T14:08:52.783'),
    (24198659           ,'2021-08-13T14:02:08.020'),
    (24198659           ,'2021-08-13T14:01:11.897'),
    (24198377           ,'2021-08-13T14:00:31.427'),
    (24197761           ,'2021-08-13T13:43:57.933'),
    (24197761           ,'2021-08-13T13:36:23.360'),
    (24197407           ,'2021-08-13T13:24:18.800'),
    (24197407           ,'2021-08-13T13:23:30.240'),
    (24197407           ,'2021-08-13T13:23:27.270'),
    (24197407           ,'2021-08-13T13:22:07.520');

Alternative one - assign both counters in one go:

-- Find the two counts...
DECLARE 
    @Count1 int,
    @Count2 int;

WITH
    LastTwoLogRows AS (
        SELECT TOP(2)
            CallCount,
            Timestamp,
            ROW_NUMBER() OVER(ORDER BY Timestamp DESC) AS RowNbr
        FROM @CallLogCommonCount
        ORDER BY Timestamp DESC
        )
SELECT 
    @Count1 = SUM(CASE WHEN RowNbr = 1 THEN CallCount END),
    @Count2 = SUM(CASE WHEN RowNbr = 2 THEN CallCount END)
FROM LastTwoLogRows;

-- Check outcome...
SELECT @Count1 as Count1, @Count2 as Count2

Alternative two - utilizing the not often used OFFSET-FETCH filter:

SELECT @Count1 = CallCount
FROM @CallLogCommonCount
ORDER BY Timestamp DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

SELECT @Count2 = CallCount
FROM @CallLogCommonCount
ORDER BY Timestamp DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

-- Check outcome...
SELECT @Count1 as Count1, @Count2 as Count2

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

Another alternative using kaj's set up data

with cc_cte(cc1, cc2) as (
    select iif(row_number() over (order by Timestamp desc)=1, CallCount, 0) cc1,
           iif(row_number() over (order by Timestamp desc)=2, CallCount, 0) cc2
    from @CallLogCommonCount)
select @Count1=max(cc1), @Count2=max(cc2)
from cc_cte;

select @Count1, @Count2;
--check 24198779 and 24198659

if not IIF then

with cc_cte(cc1, cc2) as (
    select case when row_number() over (order by Timestamp desc)=1 then CallCount end cc1,
           case when row_number() over (order by Timestamp desc)=2 then CallCount end cc2
    from @CallLogCommonCount)
select @Count1=max(cc1), @Count2=max(cc2)
from cc_cte;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 4

This is a more elegant and more readable solution... thanks!!