Hi All,

IF OBJECT_ID('tempdb..#Weekending') IS NOT NULL
   DROP TABLE #Weekending


DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2015
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS WeekEnding
into #Weekending
FROM CTE
WHERE DayName LIKE 'Saturday'
and FromDate<getdate()
OPTION (MaxRecursion 370)


Create table table1 (LD_ID int, ProjectLey int, WeekEnding datetime, ASKey int)

Insert into table1 values (1,2,'2015-02-21 00:00:00.000',1)
Insert into table1 values (1,3,'2015-02-28 00:00:00.000',1)
Insert into table1 values (1,2,'2015-04-11 00:00:00.000',0)
Insert into table1 values (1,2,'2015-04-11 00:00:00.000',3)
Insert into table1 values (1,11,'2015-04-18 00:00:00.000',0)
Insert into table1 values (2,3,'2015-02-21 00:00:00.000',1)
Insert into table1 values (2,3,'2015-02-28 00:00:00.000',1)
Insert into table1 values (2,3,'2015-03-07 00:00:00.000',1)
Insert into table1 values (2,5,'2015-03-28 00:00:00.000',0)
Insert into table1 values (2,5,'2015-04-11 00:00:00.000',0)
Insert into table1 values (2,11,'2015-04-18 00:00:00.000',3)
Insert into table1 values (2,11,'2015-04-25 00:00:00.000',3)



And desired Result is :
	Desired Result	
LD_ID	WeekEnding	ProjectKey
1	1/24/2015	2
1	1/31/2015	2
1	2/7/2015	2
1	2/14/2015	2
1	2/21/2015	2
1	2/28/2015	3
1	3/7/2015	3
1	3/14/2015	3
1	3/21/2015	3
1	3/28/2015	3
1	4/4/2015	2
1	4/11/2015	2
1	4/18/2015	11
1	4/25/2015	11
1	5/2/2015	11
1	5/9/2015	11
1	5/16/2015	11
1	5/23/2015	11
1	5/30/2015	11
2	2/21/2015	3
2	2/28/2015	3
2	3/7/2015	3
2	3/14/2015	3
2	3/21/2015	3
2	3/28/2015	5
2	4/4/2015	5
2	4/11/2015	5
2	4/18/2015	11
2	4/25/2015	11
2	5/2/2015	11
2	5/9/2015	11
2	5/16/2015	11
2	5/23/2015	11
2	5/30/2015	11

Any help is appreciated. I tried using left join but can't able to replicate the Project Key non matching weekends. Also I need to get rid of all the weekending previous to min(weekending) of each LD_ID.

Thanks

Punia

• Edited by babbupunia Friday, May 29, 2015 7:58 PM

How does it feel to be told in a language forum by one of the people who worked on that language for decades that everything you are doing is wrong and that they want you to stop programming it it? (Wikipedia me). Surprise! 

An SQL programmer thinks in terms of data. This is why we build a Calendar table with this kind of data in it. Also, we know that the ONLY display form allowed in SQL (and all other ISO standards) is ISO 8601 (yyyy-mm-dd). An SQL programmer would leave the task of stupid local dialect to the  presentation layers.

Try again and learn! If you have a Calendar  table ( static, keyed on the calendar  date) how would you do this? 

Hi babupunia,

Are you looking for some sample like below?

 IF OBJECT_ID('tempdb..#Weekending') IS NOT NULL
   DROP TABLE #Weekending


DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2015
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS WeekEnding
into #Weekending
FROM CTE
WHERE DayName LIKE 'Saturday'
and FromDate<getdate()
OPTION (MaxRecursion 370)


Create table table1 (LD_ID int, ProjectKey int, WeekEnding datetime, ASKey int)

Insert into table1 values (1,2,'2015-02-21 00:00:00.000',1)
Insert into table1 values (1,3,'2015-02-28 00:00:00.000',1)
Insert into table1 values (1,2,'2015-04-11 00:00:00.000',0)
Insert into table1 values (1,2,'2015-04-11 00:00:00.000',3)
Insert into table1 values (1,11,'2015-04-18 00:00:00.000',0)
Insert into table1 values (2,3,'2015-02-21 00:00:00.000',1)
Insert into table1 values (2,3,'2015-02-28 00:00:00.000',1)
Insert into table1 values (2,3,'2015-03-07 00:00:00.000',1)
Insert into table1 values (2,5,'2015-03-28 00:00:00.000',0)
Insert into table1 values (2,5,'2015-04-11 00:00:00.000',0)
Insert into table1 values (2,11,'2015-04-18 00:00:00.000',3)
Insert into table1 values (2,11,'2015-04-25 00:00:00.000',3)

 
 


;WITH Cte AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY LD_ID ORDER BY WeekEnding) rn FROM table1
),
Cte2 AS
(
SELECT c1.*,ISNULL(c2.WeekEnding,(SELECT DATEADD(WEEK,1,MAX(WeekEnding)) FROM #Weekending)) WeekEnding2 FROM Cte c1 LEFT JOIN Cte c2 
					ON c1.LD_ID=c2.LD_ID AND c1.RN=c2.RN-1)
SELECT c.LD_ID,c.ProjectKey,w.WeekEnding FROM Cte2 c JOIN #Weekending w
					ON w.WeekEnding >= c.WeekEnding AND w.WeekEnding< c.WeekEnding2
 

If you have any question, feel free to let me know.