none
Select one record for a certain day

    Question

  • Hi all, I'm having a small problem trying to select one record for a particular day.

    The application in question is a sync log, where users are required to synchronise 4 times a week. The most important thing, is that they synchronise 4 times a week ON DIFFERENT DAYS. If they sync 4 times on Monday for example, this should count as 1 synchronisation for that day.

    Code so far which counts all syncs for the current week, last week, current month and last month :

    SELECT
     
    S.TerritoryCode,
    S.BusinessManager,
    S.AreaManager,
    COALESCE(P.Employee_ChosenName, P.Employee_FirstNames) + ' ' + P.Employee_Surname AS Employee_Name,
    P.Employee_TopazID,
    S.SyncStart,
    S.SyncFinish,
    (Select COUNT(*) FROM Synchronisation AS SCount WHERE SCount.SyncStart Between (select dateadd(wk,datediff(wk,0,getdate()),0)) AND (select dateadd(wk,datediff(wk,0,getdate()),6)) AND SCount.KNumber = S.KNumber) AS 'Syncsthisweek',
    (Select COUNT(*) FROM Synchronisation AS SCount WHERE SCount.SyncStart Between (select dateadd(wk,datediff(wk,7,getdate()),0)) AND (select dateadd(wk,datediff(wk,7,getdate()),6)) AND SCount.KNumber = S.KNumber) AS 'Syncslastweek',
    (Select COUNT(*) FROM Synchronisation AS SCount WHERE SCount.SyncStart Between (select dateadd(mm,datediff(mm,0,getdate()),0)) AND (select dateadd(ms,- 3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate())+1,0)))) AND SCount.KNumber = S.KNumber) AS 'Syncsthismonth',
    (Select COUNT(*) FROM Synchronisation AS SCount WHERE SCount.SyncStart Between (select dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))) AND (select dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))) AND SCount.KNumber = S.KNumber) AS 'Syncslastmonth'

    FROM Synchronisation AS S

    LEFT OUTER JOIN PeopleFinder.dbo.tblEmployee AS P ON S.KNumber = P.Employee_TopazID

    Any help would be much appreciated !

    Monday, October 27, 2008 2:44 PM

All replies

  • You should use row number and partition by the converted date.  You can then the first row only.  something like this.

     

    Code Snippet

    DECLARE @t1 TABLE(

    id INT,

    last_sync datetime

    )

     

    INSERT INTO @t1 VALUES(1,GETDATE())

    INSERT INTO @t1 VALUES(1,DATEADD(hour,2,GETDATE()))

    INSERT INTO @t1 VALUES(1,GETDATE()+1)

    INSERT INTO @t1 VALUES(2,GETDATE()+1)

     

    ;WITH cte

    AS

    (

    SELECT

    id,

    CONVERT(VARCHAR(10),Last_Sync,101) AS Dt,

    ROW_NUMBER() OVER(PARTITION BY id,CONVERT(VARCHAR(10),Last_Sync,101) ORDER BY last_sync DESC) AS seq

    FROM @t1

    )

    SELECT

          id,

          COUNT(*)

    FROM [cte]

    WHERE seq = 1

    GROUP BY id

     

     

     

    Monday, October 27, 2008 3:15 PM
    Moderator
  • Thanks for your reply. I couldnt get your example to work, so I've managed to achieve what I needed through .net, rather than using my lack of knowledge with SQL
    Thursday, October 30, 2008 2:09 PM