I have the following query logic
- Show comments which is not deleted
- 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))