Skip to content
Advertisement

Sql where Condition execution not working

I have the following query logic

  1. Show comments which is not deleted
  2. Show comments which is deleted after created, within a timespan of 30 days before today(Today – Createddate <= 30)

Below is the logic which I wrote, but I don’t think it is working correct.

SELECT string_agg(
    CAST(CONCAT_WS(',',
     c.Id,
     COALESCE(c.ParentCommentId, 0),
     c.TotalRatingsCount,
     c.Pinned,
     c.IsDeleted,
     FORMAT(c.CreatedDate, 'yyyy-MM-dd HH:mm:ss')) AS VARCHAR(MAX))
 , '|')
FROM Comments c
WHERE c.DiscussionId = d.Id
and                             
((c.IsDeleted = 0 and DATEDIFF(day, c.CreatedDate , GETDATE()) >= 30))
or
(((c.IsDeleted = 1 or c.IsDeleted =0) and DATEDIFF(day, c.CreatedDate , GETDATE()) <= 30))
                            

Advertisement

Answer

SELECT string_agg(
    CAST(CONCAT_WS(',',
     c.Id,
     COALESCE(c.ParentCommentId, 0),
     c.TotalRatingsCount,
     c.Pinned,
     c.IsDeleted,
     FORMAT(c.CreatedDate, 'yyyy-MM-dd HH:mm:ss')) AS VARCHAR(MAX))
 , '|')
FROM Comments c
WHERE c.DiscussionId = d.Id
and                             
((c.IsDeleted = 0 )
or
((c.IsDeleted = 1 ) and DATEDIFF(day, c.CreatedDate , GETDATE()) <= 30))
                            
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement