How to use count in your query

Question

Hi Team I am struggling to get number of times, unit passed(Date-Passed column from Checkpoint-Movement table), PNR(column from Checkpoint-Movement table), Station Description(column from Station table). Given to the below tables; I want to filter between 06:00am-20:00pm today /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [Station Code] ,[Machine Name ] ,[Station Description] ,[IP Address] ,[Tracking Client Name ] ,[Previous Station Code] ,[ Next Station Code] ,[ Transaction Code] ,[SQL_Instance] ,[rowguid] FROM [Tracking_Server_DB].[dbo].[TS_Station] SELECT TOP 1000 [Plant Code] ,[Production Year ] ,[PNR] ,[KNR] ,[Colour] ,[Model Code] ,[Market] ,[Country] ,[ Transaction Code] ,[DateTime Passed] ,[Telegram] ,[Hold Get] ,[ Option Code 1] ,[EQS Carstate] ,[SeqNum] ,[RBSCode] ,[Return_CP_Name] FROM [Tracking_Server_DB].[dbo].[ Checkpoint Movement]

Debarshi Chakraborty · Answer

Few points: I don't see duplicate records as each of the TIMESTAMP is different - they vary in seconds / millisecods. If you don't want the minutes-seconds-milliseconds part , then format your SELECT accordingly to get only the date part or whatever you need. The tables you consider below - [TS_STATION], [CHECKPOINT MOVEMENT], [READER_INFO] -seems like multiple records are inserted based on the same transaction code , that vary in seconds or milliseconds Try to find out condition (field OR set of fields) that is unique when you join all the three tables and use them in your where clause to bring the uniqueness - if that's what you want. Formulate your group by clause accordingly.

Guest User · Answer

Hi Ruchi Well i am using this logic on this query, have a look there is called "DateTime Passed" SELECT TOP 1000 [Station Description] ,[DateTime Passed] ,[PNR] ,[Detect_Or_Misread] ,[KNR] FROM [Tracking_Server_DB].[dbo].[TS_Station], [Tracking_Server_DB].[dbo].[ Checkpoint Movement], [Tracking_Server_DB].[dbo].[Reader_Info] where [Tracking_Server_DB].[dbo].[TS_Station].[ Transaction Code] = [Tracking_Server_DB].[dbo].[ Checkpoint Movement].[ Transaction Code] and [Tracking_Server_DB].[dbo].[TS_Station].[ Transaction Code] = [Tracking_Server_DB].[dbo].[Reader_Info].[Transaction_Code] and ( [Tracking_Server_DB].[dbo].[TS_Station].[Station Description] = 'M100' or [Tracking_Server_DB].[dbo].[TS_Station].[Station Description] = 'M700' ) order by [PNR] ,[DateTime Passed]

Ruchi Sharavat · Answer

Where do you have time column in database to add filter for time 6AM to 20 PM and what values (i mean format like HH:mm) you store in that column?

Debarshi Chakraborty · Answer

Sorry, didn't understand ! Please share a picture of the result you are getting and highlight in that picture, what is the problem..

Guest User · Answer

SELECT TOP 1000 [KNR] ,[Station Description] ,[PNR] ,[Detect_Or_Misread] ,[ Timestamp ] FROM [Tracking_Server_DB].[dbo].[TS_Station], [Tracking_Server_DB].[dbo].[ Checkpoint Movement], [Tracking_Server_DB].[dbo].[Reader_Info] where [Tracking_Server_DB].[dbo].[TS_Station].[ Transaction Code] = [Tracking_Server_DB].[dbo].[ Checkpoint Movement].[ Transaction Code] and [Tracking_Server_DB].[dbo].[TS_Station].[ Transaction Code] = [Tracking_Server_DB].[dbo].[Reader_Info].[Transaction_Code] The only thing i dont dont get on the record that is empty, is KNR column record is empty

Debarshi Chakraborty · Answer

Hey buddy, can you share the desired output , and the format you are hoping to get ?

How to use count in your query

Answers (6)