SQL/T-SQL : Query to find count of records inserted for a file for the latest date-CodePudding

I'm trying to create an SQL query where I could get the count of records every day for the latest date.

I've 4 columns in data table:

File_Name, Insert_TimeStamp, MobileNumber, Message_Id (unique)

Suppose a file is inserted with 100 records for Insert_TimeStamp 13/12/2021, File_Name would be same for all records as per date XYZ_1312021. Like wise everyday file will be inserted.

I'm just trying to create 1 new record everyday with help of SQL by looking at this datatable. Result would be like

TotalNumberRecords, File_Name, Insert_TimeStamp(latestdate), Status(received).

But I'm getting an error

Error saving the query field. Invalid column name 'Total_Record_Received'

SQL which I created:

    Select Top 1
file_name,
INSERT_TIMESTAMP,
Total_Record_Received,
'Received' as Status
from
(
Select 
file_name,
INSERT_TIMESTAMP,
Count(*) as Total_Record_Received,
ROW_NUMBER() OVER(PARTITION by file_name ORDER BY INSERT_TIMESTAMP ASC ) as rn
from [Master-SMS MessageALL] a
Group by file_name, INSERT_TIMESTAMP) t
Where t.rn = 1

Sample Data :

File_Name     Insert_TimeStamp   Mobile_Number  MessageID
xyz_13122021   13/12/2021         7814154        mm_001
xyz_13122021   13/12/2021         7516546        mm_002
xyz_13122021   13/12/2021         1541646        mm_003
xyz_13122021   13/12/2021         5446561        mm_004
xyz_13122021   13/12/2021         5456456        mm_005
xyz_13122021   13/12/2021         8749849        mm_006
xyz_12122021   12/12/2021         7814154        mm_007
xyz_12122021   12/12/2021         8749849        mm_008
xyz_11122021   11/12/2021         8749848        mm_009

Result :

File_Name     TotalRecords     Insert_TimeStamp   Status
xyz_13122021      6               13/12/2021      Received

CodePudding user response：

what you need is a simple GROUP BY query

SELECT File_Name,
       COUNT(*) AS TotalRecords,
       Insert_TimeStamp,
       'Received' AS Status
FROM   [Master-SMS MessageALL]
WHERE  Insert_TimeStamp = (SELECT MAX(Insert_TimeStamp) FROM [Master-SMS MessageALL])
GROUP BY File_Name, Insert_TimeStamp

OR you can use TOP 1 row order by Insert_TimeStamp descending

SELECT TOP 1
       File_Name,
       COUNT(*) AS TotalRecords,
       Insert_TimeStamp,
       'Received' AS Status
FROM   [Master-SMS MessageALL]
GROUP BY File_Name, Insert_TimeStamp
ORDER BY Insert_TimeStamp DESC

OR if you wish to use the row_number()

SELECT *
FROM
(
    SELECT File_Name,
           COUNT(*) AS TotalRecords,
           Insert_TimeStamp,
           'Received' AS Status,
           RN = ROW_NUMBER() OVER (ORDER BY Insert_TimeStamp DESC)
    FROM   [Master-SMS MessageALL]
    GROUP BY File_Name, Insert_TimeStamp
) AS D
WHERE D.RN = 1

CodePudding user response：

Don't Group by Total_Record_Received