mercredi 27 mai 2015

Getfirstweek monday 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 tried like below query

;WITH CTE AS (
      SELECt Date, Weekno ,ROW_NUMBER() OVER(PARTITION BY WEEKno, YEAR(date) ORDER BY date ASC) AS RN
      FROM dateinfo)
    select a.Date,a.Weekno,

    CAST(a.WEEKno AS VARCHAR)    + '  ' +
    convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,a.Date),0),110) Firstdayofweeknodate

    FROM dateinfo AS A
    JOIN CTE AS B
    ON A.WEEKno=B.Weekno
    AND YEAR(A.Date)=YEAR(B.Date)
    WHERE B.RN = 1

its given output like below

Date           |Weekno  |Firstdayofweeknodate
    2014-01-01     |1   |1  12-30-2013
    2014-01-02     |1   |1  12-30-2013
    2014-01-03     |1   |1  12-30-2013
    2014-01-04  |1  |1  12-30-2013
    2015-01-01  |1  |1  12-29-2014
    2015-01-02  |1  |1  12-29-2014
    2015-01-03  |1  |1  12-29-2014
    2015-01-04  |2  |2  01-05-2015
    2015-01-05  |2  |2  01-05-2015
    2015-01-06  |2  |2  01-05-2015
    2015-01-07  |2  |2  01-05-2015
    2015-01-08  |2  |2  01-05-2015
    2015-01-09  |2  |2  01-05-2015
    2015-01-10  |2  |2  01-05-2015
    2015-01-11  |3  |3  01-12-2015
    2015-01-12  |3  |3  01-12-2015
    2015-01-13  |3  |3  01-12-2015
    2015-01-14  |3  |3  01-12-2015
    2015-01-15  |3  |3  01-12-2015
    2015-01-16  |3  |3  01-12-2015
    2015-01-17  |3  |3  01-12-2015
    2015-01-18  |4  |4  01-19-2015
    2015-01-19  |4  |4  01-19-2015
    2015-01-20  |4  |4  01-19-2015
    2015-01-21  |4  |4  01-19-2015
    2015-01-22  |4  |4  01-19-2015
    2015-01-23  |4  |4  01-19-2015
    2015-01-24  |4  |4  01-19-2015
    2015-01-25  |5  |5  01-26-2015
    2015-01-26  |5  |5  01-26-2015
    2015-01-27  |5  |5  01-26-2015
    2015-01-28  |5  |5  01-26-2015
    2015-01-29  |5  |5  01-26-2015
    2015-01-30  |5  |5  01-26-2015
    2015-01-31  |5  |5  01-26-2015
    2015-02-01  |6  |6  02-02-2015

What I expect That result not come properly.I want output like below

Date           |Weekno  |Firstdayofweeknodate
2014-01-01     |1   |1  01-01-2014
2014-01-02     |1   |1  01-01-2014
2014-01-03     |1   |1  01-01-2014
2014-01-04  |1  |1  01-01-2014
2015-01-01  |1  |1  01-01-2015
2015-01-02  |1  |1  01-01-2015
2015-01-03  |1  |1  01-01-2015
2015-01-04  |2  |2  01-05-2015
2015-01-05  |2  |2  01-05-2015
2015-01-06  |2  |2  01-05-2015
2015-01-07  |2  |2  01-05-2015
2015-01-08  |2  |2  01-05-2015
2015-01-09  |2  |2  01-05-2015
2015-01-10  |2  |2  01-05-2015
2015-01-11  |3  |3  01-12-2015
2015-01-12  |3  |3  01-12-2015
2015-01-13  |3  |3  01-12-2015
2015-01-14  |3  |3  01-12-2015
2015-01-15  |3  |3  01-12-2015
2015-01-16  |3  |3  01-12-2015
2015-01-17  |3  |3  01-12-2015
2015-01-18  |4  |4  01-19-2015
2015-01-19  |4  |4  01-19-2015
2015-01-20  |4  |4  01-19-2015
2015-01-21  |4  |4  01-19-2015
2015-01-22  |4  |4  01-19-2015
2015-01-23  |4  |4  01-19-2015
2015-01-24  |4  |4  01-19-2015
2015-01-25  |5  |5  01-26-2015
2015-01-26  |5  |5  01-26-2015
2015-01-27  |5  |5  01-26-2015
2015-01-28  |5  |5  01-26-2015
2015-01-29  |5  |5  01-26-2015
2015-01-30  |5  |5  01-26-2015
2015-01-31  |5  |5  01-26-2015
2015-02-01  |6  |6  02-02-2015

this data for one month but in orgianly table data have more years data similary to above data.

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

Aucun commentaire:

Enregistrer un commentaire