Create a CTE within a CTE

Transact-SQL
1

I have the following code:

;With Alldate_cte (xDate,xDiv) As
(
With cte AS (
SELECT 1 AS DayID,
DATEADD(yyyy, Year(Getdate()) - 1900, 0) AS FromDate,
DATENAME(dw, DATEADD(yyyy, Year(Getdate()) - 1900, 0)) 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) < DATEADD(yyyy, Year(Getdate()) - 1900 + 1, 0)
)
SELECT FromDate AS Date , cm.[Category ]
FROM CTE Left Outer Join Reporting..Category_Managers_Targets cm On Year(Cte.FromDate)=cm.Year and cm.Type like 'NAF%'
WHERE DayName Not IN ('Saturday','Sunday') and FromDate <= Convert(datetime,Convert(date,Getdate()))
OPTION (MaxRecursion 370)

I am trying to acheive the following but am not having much success at the moment, can anyone help please?

Date Category
2019-01-01 00:00:00.000 Civils
2019-01-01 00:00:00.000 Merchant
2019-01-01 00:00:00.000 Property group
2019-01-01 00:00:00.000 No sector defined in CRM
2019-01-01 00:00:00.000 North
2019-01-01 00:00:00.000 South
2019-01-01 00:00:00.000 Pumps
2019-01-01 00:00:00.000 Utilities
2019-01-02 00:00:00.000 Civils
2

hi

can you please show data how data is in ???
table ... Reporting..Category_Managers_Targets

this is a very easy simple SQL Query !!!

i was able to see till there

please click arrow to the left for PARTIAL SQL 
;WITH cte 
     AS (SELECT 1                                                      AS DayID, 
                Dateadd(yyyy, Year(Getdate()) - 1900, 0)               AS 
                FromDate, 
                Datename(dw, Dateadd(yyyy, Year(Getdate()) - 1900, 0)) 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) < Dateadd(yyyy, Year(Getdate()) 
                                                            - 1900 + 
                                                            1, 
                                              0)) 
SELECT * 
FROM   cte 
WHERE  dayname NOT IN ( 'Saturday', 'Sunday' ) 
       AND fromdate <= CONVERT(DATETIME, CONVERT(DATE, Getdate())) 
OPTION (maxrecursion 370) 
go

image
image853×1337 20.7 KB

image
image1569×115 2.21 KB

3
Year Category Type
2019 Civils NAFReport
2019 Merchant NAFReport
2019 Property group NAFReport
2019 No sector defined in CRM NAFReport
2019 North NAFReport
2019 South NAFReport
2019 Pumps NAFReport
2019 Utilities NAFReport
4

hi

please see my query .. i think its what you want !! :slight_smile:

please click arrow to the left for sample data 
drop table Category_Managers_Targets
go 

create table Category_Managers_Targets
(
Year  int , 
Type  varchar(100),
[Category ] varchar(100)
)
go 

insert into Category_Managers_Targets select 2019, 'NAFReport','Civils'
insert into Category_Managers_Targets select 2019, 'NAFReport','Merchant'
insert into Category_Managers_Targets select 2019, 'NAFReport','Property group'
insert into Category_Managers_Targets select 2019, 'NAFReport','No sector defined in CRM'
insert into Category_Managers_Targets select 2019, 'NAFReport','North'
insert into Category_Managers_Targets select 2019, 'NAFReport','South'
insert into Category_Managers_Targets select 2019, 'NAFReport','Pumps'
insert into Category_Managers_Targets select 2019, 'NAFReport','Utilities'
go 

select 'sample data',* from Category_Managers_Targets
go

image
image1089×565 13 KB

please click arrow to the left for SQL 
;WITH cte 
     AS (SELECT 1                                                      AS DayID, 
                Dateadd(yyyy, Year(Getdate()) - 1900, 0)               AS 
                FromDate, 
                Datename(dw, Dateadd(yyyy, Year(Getdate()) - 1900, 0)) 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) < Dateadd(yyyy, Year(Getdate()) 
                                                            - 1900 + 
                                                            1, 
                                              0)) 
SELECT * 
FROM   cte 
       JOIN category_managers_targets cm 
         ON Year(cte.fromdate) = cm.year 
            AND cm.type LIKE 'NAF%' 
WHERE  dayname NOT IN ( 'Saturday', 'Sunday' ) 
       AND fromdate <= CONVERT(DATETIME, CONVERT(DATE, Getdate())) 
ORDER  BY fromdate 
OPTION (maxrecursion 370) 

go

image
image1875×1231 132 KB