jeudi 21 mai 2015

Get firstweekdaydate in sql server

Hi Friends I Have small doubt in sql server please tell me how to solve below issue

Table :  testdate


    date          |weekno
    1/1/2015      | 1
    1/2/2015      | 1
    1/3/2015      | 1
    1/1/2014      | 1
    1/1/2014      | 1
    1/2/2014      | 1
    1/3/2014      | 1
    1/4/2014      | 1
    1/4/2015      | 2
    1/5/2015      | 2
    1/6/2015      | 2
    1/7/2015      | 2
    1/8/2015      | 2
    1/9/2015      | 2
    1/10/2015     | 2
    1/11/2015     | 3
    1/12/2015     | 3
    1/13/2015     | 3
    1/14/2015     | 3
    1/15/2015     | 3
    1/16/2015     | 3
    1/17/2015     | 3
    1/18/2015     | 4
    1/19/2015     | 4
    1/20/2015     | 4
    1/21/2015     | 4
    1/22/2015     | 4
    1/23/2015     | 4
    1/24/2015     | 4
    1/25/2015     | 5
    1/26/2015     | 5
    1/27/2015     | 5
    1/28/2015     | 5
    1/29/2015     | 5
    1/30/2015     | 5
    1/31/2015     | 5
    2/1/2015      | 6

based on above data I need to display one more column firstdayofweekwithdate and data look like below.this data for one month but in orgianly table data have 30 years data similary to above data.

        date         |weekno  |   Firstdayofweeknodate
        1/1/2015     |  1     | "1   1/1/2015"
        1/2/2015     |  1     | "1   1/1/2015"
        1/3/2015     |  1     | "1   1/1/2015"
        1/1/2014     |  1     | "1  1/1/2014" 
        1/1/2014     |  1     | "1  1/1/2014" 
        1/2/2014     |  1     | "1  1/1/2014" 
        1/3/2014     |  1     | "1  1/1/2014" 
        1/4/2014     |  1     | "1  1/1/2014" 
        1/4/2015     |  2     | " 2  1/4/2015"
        1/5/2015     |  2     | " 2  1/4/2015"
        1/6/2015     |  2     | " 2  1/4/2015"
        1/7/2015     |  2     | " 2  1/4/2015"
        1/8/2015     |  2     | " 2  1/4/2015"
        1/9/2015     |  2     | " 2  1/4/2015"
        1/10/2015    |  2     | " 2  1/4/2015"
        1/11/2015    |  3     | "3   1/11/2015"
        1/12/2015    |  3     | "3   1/11/2015"
        1/13/2015    |  3     | "3   1/11/2015"
        1/14/2015    |  3     | "3   1/11/2015"
        1/15/2015    |  3     | "3   1/11/2015"
        1/16/2015    |  3     | "3   1/11/2015"
        1/17/2015    |  3     | "3   1/11/2015"
        1/18/2015    |  4     | "4  1/18/2015"
        1/19/2015    |  4     | "4  1/18/2015"
        1/20/2015    |  4     | "4  1/18/2015"
        1/21/2015    |  4     | "4  1/18/2015"
        1/22/2015    |  4     | "4  1/18/2015"
        1/23/2015    |  4     | "4  1/18/2015"
        1/24/2015    |  4     | "4  1/18/2015"
        1/25/2015    |  5     | "5  1/25/2015"
        1/26/2015    |  5     | "5  1/25/2015"
        1/27/2015    |  5     | "5  1/25/2015"
        1/28/2015    |  5     | "5  1/25/2015"
        1/29/2015    |  5     | "5  1/25/2015"
        1/30/2015    |  5     | "5  1/25/2015"
        1/31/2015    |  5     | "5  1/25/2015"
        2/1/2015     |  6     | "6  2/1/2015" 

please tell me how to write query to achive all years related data task in sql server.

Aucun commentaire:

Enregistrer un commentaire