locked
week to date RRS feed

  • Question

  • Hi,

    I use ssms tool to run sql query.

    I’d really like to add a Field that only shows the Weeks to Date (ie:  if we are in week 9, then have the field to filter on Weeks 1 through to 9 as one field called ‘WeektoDate’) and have this automated so it updates depending on the current week you are in,  if we move to week 10, then the Field dynamics for this changes to week 1 – 10.

    The below query gives result from 1 to 12 (1,2,3.....9,10,11,12)

    select distinct weekofqtr from Table1
    where weekofqtr<=dbo.fnweekofqtr(getdate()) and qtr = '15Q2'

    i would like to include the above result as additional column to one of my select query which has many columns selected for many quarters.

    below is the one that i tried and did not work

    select distinct case when weekofqtr=dbo.fnweekofqtr(getdate()) then weekofqtr<=dbo.fnweekofqtr(getdate()) else '0' end as 'weektodate'
    from Table1 where qtr = '15Q2'

    Friday, June 17, 2016 3:17 PM

Answers

  • After reading this a couple of times, I think that what you are looking for is:

    select distinct case when weekofqtr <= dbo.fnweekofqtr(getdate())                      then weekofqtr                      else 0                 end as 'weektodate'
    from Table1 where qtr = '15Q2'

    But I will have to admit that it is a bit of a guess.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by 5Ant Saturday, June 18, 2016 10:19 AM
    Friday, June 17, 2016 10:18 PM

All replies

  • After reading this a couple of times, I think that what you are looking for is:

    select distinct case when weekofqtr <= dbo.fnweekofqtr(getdate())                      then weekofqtr                      else 0                 end as 'weektodate'
    from Table1 where qtr = '15Q2'

    But I will have to admit that it is a bit of a guess.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by 5Ant Saturday, June 18, 2016 10:19 AM
    Friday, June 17, 2016 10:18 PM
  • thanks for the response, I have already tried with this option and it gives only one result (current week number) and i am looking for 1 to 12(current week )

    Saturday, June 18, 2016 6:52 AM
  • thanks for the response, I have already tried with this option and it gives only one result (current week number) and i am looking for 1 to 12(current week )

    The problem is that we don't know your table nor your function. Or for that matter what you mean with week numbers. It would certainly help if you posted CREATE TABLE statements for your table, INSERT statemetns with sample data and the code for your function. This would make it possible to develop a tested solution.

    Saturday, June 18, 2016 9:16 AM
  • Thanks, you are right, sure I will come with more detail view.
    Saturday, June 18, 2016 10:32 AM
  • >> I’d really like to add a Field [sic] that only shows the Weeks to Date (ie:  if we are in week 9, then have the field [sic] to filter on Weeks 1 through to 9 as one field [sic] called ‘WeektoDate’) and have this automated so it updates depending on the current week you are in,  if we move to week 10, then the Field [sic] dynamics for this changes to week 1 – 10. <<

    You might want to actually read the ANSI/ISO SQL standards and learn what a field is in this language. What you want is called a column. It is also good to read other ISO standards, such as ISO 8601 which defines a week within year date format.  It looks like "yyyyW[0-5][0-9]-[0-7]" and maps directly to the standard calendar year. The first four positions are the calendar year, the letter W is a token separator, the next two digits are the week within the year (01 through 52 or 53) and the final digit is the is the day of the week.

    Google around and you will find several websites you can download years of date data in this format. It is popular in the Nordic countries

    >> dbo.fnweekofqtr(CURRENT_TIMESTAMP) and qtr = '15Q2' <<

    This code fragment has some implications. First of all, we do not like user-defined functions; they cannot be optimized in general, they are proprietary and instead of explaining what is going on. They tend to hide it. The name implies that you got something called quarters defined in some way in your data model. But we have no idea what it is. I will guess that you could use the ISO 8601 standard format and a few string functions to get what you want and not have to write procedural code. 

    As a historical note, the "FN_" is a bit of syntax from Fortran I; the first compilers were not very sophisticated and they needed hints in the first letters of names to parse programs. This prefix was also carried over to early versions of basic, and the "SP_" prefix in T-SQL. Today it is considered bad practice to write this way in modern languages.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, June 18, 2016 12:10 PM