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