3
\$\begingroup\$

I have the following SQL query that computes for every date the week it falls in (a week begins on Sunday and ends on Saturday):

SELECT EntryDate
      ,CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) AS 'SundayDate'
      ,CAST(DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) AS 'SaturdayDate'
      ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
       CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) <=
      CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS DATE)
ORDER BY CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE)

It works fine, but I don't like the repeated function calls because they cluster the query and make it less readable.

How can I make the query cleaner and more readable? (I'm using SQL Server 2008.)

\$\endgroup\$
  • \$\begingroup\$ I am not sure that you can make this "cleaner" or more readable. it looks pretty straight forward. \$\endgroup\$ – Malachi Nov 6 '13 at 16:02
3
\$\begingroup\$
WITH Dates AS (
    SELECT EntryDate
         , DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SundayDate
         , DATEADD(DAY, 7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SaturdayDate
        FROM MyTable
), Coming AS (
    SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS Sunday
)
SELECT EntryDate
     , SundayDate
     , SaturdayDate
     , CONVERT(VARCHAR, SundayDate, 103) + ' - ' + CONVERT(VARCHAR, SaturdayDate, 103) AS Week
    FROM Dates, Coming
    WHERE SundayDate <= Coming.Sunday
    ORDER BY 2;

The simplifications I've made are:

  • Extracted most of the query into a Common Table Expression named Dates to reduce redundancy.
  • Removed the pointless CAST(... AS DATE), since DATEADD() already produces dates.
  • Used a column number for ORDER BY. With the Common Table Expression, though, we could just as easily ORDER BY SundayDate.
  • Extracted DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) into a Common Table Expression named Coming. This doesn't reduce the complexity of the query, but helps make the WHERE-clause read more like English.

SQLFiddle


Edit

Since the benefits of the last two suggestions are debatable, you may prefer a milder approach that incorporates just the first two suggestions:

WITH Dates AS (
    SELECT EntryDate
         , DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SundayDate
         , DATEADD(DAY, 7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SaturdayDate
        FROM MyTable
)
SELECT EntryDate
     , SundayDate
     , SaturdayDate
     , CONVERT(VARCHAR, SundayDate, 103) + ' - ' + CONVERT(VARCHAR, SaturdayDate, 103) AS Week
    FROM Dates
    WHERE SundayDate <= DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())
    ORDER BY SundayDate;
\$\endgroup\$
  • \$\begingroup\$ what are you doing? can you elaborate on this answer? I am a little lost myself. \$\endgroup\$ – Malachi Nov 27 '13 at 0:05
  • \$\begingroup\$ @Malachi Sorry, the previous revision didn't actually work with SQL Server. I've edited it into a proper answer now. \$\endgroup\$ – 200_success Nov 27 '13 at 5:42
  • \$\begingroup\$ I don't like it, but it is functional. personally I like my query better than this one. your Query and mine have the exact same Execution plan. :) sqlfiddle.com/#!3/cf783/11 \$\endgroup\$ – Malachi Nov 27 '13 at 6:23
  • 1
    \$\begingroup\$ @Malachi The Coming CTE might be a bit excessive; you can easily undo that part if you prefer. \$\endgroup\$ – 200_success Nov 27 '13 at 7:13
  • 1
    \$\begingroup\$ @Malachi It's not that hard to remove the Coming CTE. \$\endgroup\$ – 200_success Nov 27 '13 at 17:25
2
\$\begingroup\$

You can lose the CAST that surrounds the DATEADD function.

The DATEADD function should spit out a DATETIME datatype.

It should look like this:

SELECT EntryDate
  ,DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SundayDate'
  ,DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SaturdayDate'
  ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
   CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate)  <=
      DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 
ORDER BY DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate)

In the ORDER BY statement you might be able to use the Alias from your SELECT Statement, which would really speed this up.

SELECT EntryDate
  ,DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SundayDate'
  ,DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SaturdayDate'
  ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
   CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate)  <=
      DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 
ORDER BY 'Sunday_Date`

I would put DESC on that order by because I would want the newest dates to show up first, if you are grabbing dates from the past

If you are grabbing from the future, then you would want 'ASC' in there, but that is default.

I Can't remember who said it but

it is as simple as it can be when there is nothing left that can be taken away

\$\endgroup\$
  • \$\begingroup\$ OUCH!! MY EYES BLEED!! (that's just me I guess) \$\endgroup\$ – Mathieu Guindon Nov 19 '13 at 18:34
  • \$\begingroup\$ @retailcoder, unfortunately, when you use a function in the SELECT and give it an alias, you can't just use the alias in the WHERE clause, you have to calculate it again. the ORDER BY the OP Might be able to use the alias... \$\endgroup\$ – Malachi Nov 19 '13 at 18:40
  • 1
    \$\begingroup\$ I meant all these caps aren't making it an easy read imho :) \$\endgroup\$ – Mathieu Guindon Nov 19 '13 at 18:52
  • 4
    \$\begingroup\$ that is the coding syntax of SQL, most of the SQL engines are not case sensitive on these keywords though. but it makes it easier to pick them out when the code gets bigger, @retailcoder \$\endgroup\$ – Malachi Nov 19 '13 at 18:53
  • 1
    \$\begingroup\$ @kodkod, if you need the date without the time part for reporting purposes, that should be done on the report side not on the database side. that should speed things up a little bit, because that is less functions running in the query. the reporting software would be able to do it easier I think. \$\endgroup\$ – Malachi Nov 26 '13 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.