SQL to filter news date and check publish from

Jenilyn Sanchez asked on October 12, 2018 06:33

I have a news page where I need to display DISTINCT News date month and year in the drop down. So I created a Query Repeater with Effect web part and I have this query inside

SELECT 
    DISTICT DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) AS [YearMonth]
FROM mysite_newsroom
ORDER BY
    DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) DESC;

The query is successfully getting the distinct Month and Year in my news pages. The problem is one of my news page has set to be publish from December 2018 but the News Date field value is October 2018. So October 2018 should be excluded.

What I'm trying now is adding this in where condition

[DocumentCanBePublished] = 1
AND
([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE())

But I giving me a Invalid column name error. Thank you for your help.

sql filter published field

Correct Answer

Jenilyn Sanchez answered on October 12, 2018 13:28

This solved my problem.

SELECT 
    DISTINCT DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) AS [YearMonth]
FROM  
  mysite_newsroom INNER JOIN View_CMS_Tree_Joined ON DocumentForeignKeyValue = NewsID
WHERE
  ClassName = 'mysite.newsroom'
  AND ((DocumentPublishFrom IS NULL) OR (DocumentPublishFrom <= GETDATE()))
ORDER BY
    DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) DESC;

0 votesVote for this answer Unmark Correct answer

Recent Answers

Arun Kumar answered on October 12, 2018 09:05 (last edited on October 12, 2018 09:06)

Please verify if you have added all these columns under Colunms field of your repeater properties screen.

0 votesVote for this answer Mark as a Correct answer

Arun Kumar answered on October 12, 2018 09:11 (last edited on October 12, 2018 09:18)

Or in case of Repeater with custom query you need to return your columns in Select clause

SELECT DISTINCT DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) AS 
[YearMonth],DocumentCanBePublished,DocumentPublishFrom,DocumentPublishFrom FROM mysite_newsroom
WHERE ##WHERE## ORDER BY DATEADD(MM,DATEDIFF(MM,0, [NewsDate] ),0) DESC;

0 votesVote for this answer Mark as a Correct answer