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 11 hours 8 minutes ago

Is this what you're looking for? 

Seems kinda strange, but your requirements are vague:

DECLARE @table1 TABLE (LD_ID INT, ProjectLey INT, WeekEnding DATETIME, ASKey INT)

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

;WITH base AS (
SELECT c.weekEnd, t.*
  FROM calendar c 
    FULL OUTER JOIN @table1 t
	  ON c.weekEnd = t.WeekEnding
 WHERE c.year = 2015
   AND c.today = c.weekEnd
 
)

--SELECT MIN(weekEnding) FROM base WHERE ProjectLey IS NOT NULL AND WeekEnding > '2015-01-03 00:00:00.000'

SELECT b.weekEnd, COALESCE(b.LD_ID,b2.LD_ID) AS LD_ID, COALESCE(b.ProjectLey,b2.ProjectLey) AS ProjectLey, COALESCE(b.ASKey,b2.ASKey) AS ASKey
  FROM base b
    LEFT OUTER JOIN base b2
	  ON b2.weekEnd = (SELECT MIN(weekEnding) FROM base WHERE ProjectLey IS NOT NULL AND WeekEnding > b.weekEnd) --2015-02-21 00:00:00.000
 WHERE COALESCE(b.LD_ID,b2.LD_ID) IS NOT NULL 
ORDER BY COALESCE(b.ProjectLey,b2.ProjectLey), b.weekEnd