After re-running all of the SOLUTION code in https://campus-no-cache.datacamp.com/courses/20239/63394?ex=1, I've found the following exercises to have errors about unshaped nodes. I've also linked to the AST viewer for each exercise: ### Chapter 2 - [x] [Ch2Ex4 - Build dates and times with offsets from parts](https://www.datacamp.com/teach/editor/4472/edit/330cbdc63e?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09--+Fill+in+the+PRIOR+millisecond+before+chaos%0A%09DATETIMEOFFSETFROMPARTS%282038%2C+01%2C+19%2C+03%2C+14%2C+07%2C+999%2C+0%2C+0%2C+3%29+AS+LastMoment%2C%0A++++--+Fill+in+the+exact+millisecond+chaos+begins%0A++++--+Then+convert+to+the+correct+time+zone%0A%09DATETIMEOFFSETFROMPARTS%282038%2C+01%2C+19%2C+03%2C+14%2C+08%2C+0%2C+0%2C+0%2C+3%29+AT+TIME+ZONE+%27Eastern+Standard+Time%27+AS+TimeForChaos%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex8 - Parse strings to dates](https://www.datacamp.com/teach/editor/4472/edit/b91a3d2087?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09DateText+AS+String%2C%0A%09--+Parse+the+input+strings+as+DATE%0A%09PARSE%28DateText+AS+DATE+USING+%27de-de%27%29+AS+StringAsDate%2C%0A%09--+Parse+the+date+strings+as+DATETIME2%0A%09PARSE%28DateText+AS+DATETIME2%287%29+USING+%27de-de%27%29+AS+StringAsDateTime2%0AFROM+%23Dates%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex14 - Try out type-safe date functions (Step 1)](https://www.datacamp.com/teach/editor/4472/edit/4590c5c5d0?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ADECLARE%0A%09%40GoodDateINTL+NVARCHAR%2830%29+%3D+%272019-03-01+18%3A23%3A27.920%27%2C%0A%09%40GoodDateDE+NVARCHAR%2830%29+%3D+%2713.4.2019%27%2C%0A%09%40GoodDateUS+NVARCHAR%2830%29+%3D+%274%2F13%2F2019%27%2C%0A%09%40BadDate+NVARCHAR%2830%29+%3D+N%27SOME+BAD+DATE%27%3B%0A%0ASELECT%0A%09--+Fill+in+the+correct+data+type+based+on+our+input%0A%09TRY_CONVERT%28DATETIME2%283%29%2C+%40GoodDateINTL%29+AS+GoodDateINTL%2C%0A%09--+Fill+in+the+correct+function%0A%09TRY_CONVERT%28DATE%2C+%40GoodDateDE%29+AS+GoodDateDE%2C%0A%09TRY_CONVERT%28DATE%2C+%40GoodDateUS%29+AS+GoodDateUS%2C%0A%09--+Fill+in+the+correct+input+parameter+for+BadDate%0A%09TRY_CONVERT%28DATETIME2%283%29%2C+%40BadDate%29+AS+BadDate%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex14 - Try out type-safe date functions (Step 2)](https://www.datacamp.com/teach/editor/4472/edit/40cf90bdc0?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ADECLARE%0A%09%40GoodDateINTL+NVARCHAR%2830%29+%3D+%272019-03-01+18%3A23%3A27.920%27%2C%0A%09%40GoodDateDE+NVARCHAR%2830%29+%3D+%2713.4.2019%27%2C%0A%09%40GoodDateUS+NVARCHAR%2830%29+%3D+%274%2F13%2F2019%27%2C%0A%09%40BadDate+NVARCHAR%2830%29+%3D+N%27SOME+BAD+DATE%27%3B%0A%0A--+The+prior+solution+using+TRY_CONVERT%0ASELECT%0A%09TRY_CONVERT%28DATETIME2%283%29%2C+%40GoodDateINTL%29+AS+GoodDateINTL%2C%0A%09TRY_CONVERT%28DATE%2C+%40GoodDateDE%29+AS+GoodDateDE%2C%0A%09TRY_CONVERT%28DATE%2C+%40GoodDateUS%29+AS+GoodDateUS%2C%0A%09TRY_CONVERT%28DATETIME2%283%29%2C+%40BadDate%29+AS+BadDate%3B%0A%0ASELECT%0A%09--+Fill+in+the+correct+data+type+based+on+our+input%0A%09TRY_CAST%28%40GoodDateINTL+AS+DATETIME2%283%29%29+AS+GoodDateINTL%2C%0A%09TRY_CAST%28%40GoodDateDE+AS+DATE%29+AS+GoodDateDE%2C%0A%09TRY_CAST%28%40GoodDateUS+AS+DATE%29+AS+GoodDateUS%2C%0A%09TRY_CAST%28%40BadDate+AS+DATETIME2%283%29%29+AS+BadDate%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex14 - Try out type-safe date functions (Step 3)](https://www.datacamp.com/teach/editor/4472/edit/a40ef6c9dd?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ADECLARE%0A%09%40GoodDateINTL+NVARCHAR%2830%29+%3D+%272019-03-01+18%3A23%3A27.920%27%2C%0A%09%40GoodDateDE+NVARCHAR%2830%29+%3D+%2713.4.2019%27%2C%0A%09%40GoodDateUS+NVARCHAR%2830%29+%3D+%274%2F13%2F2019%27%2C%0A%09%40BadDate+NVARCHAR%2830%29+%3D+N%27SOME+BAD+DATE%27%3B%0A%0A--+The+prior+solution+using+TRY_CAST%0ASELECT%0A%09TRY_CAST%28%40GoodDateINTL+AS+DATETIME2%283%29%29+AS+GoodDateINTL%2C%0A%09TRY_CAST%28%40GoodDateDE+AS+DATE%29+AS+GoodDateDE%2C%0A%09TRY_CAST%28%40GoodDateUS+AS+DATE%29+AS+GoodDateUS%2C%0A%09TRY_CAST%28%40BadDate+AS+DATETIME2%283%29%29+AS+BadDate%3B%0A%0ASELECT%0A%09TRY_PARSE%28%40GoodDateINTL+AS+DATETIME2%283%29%29+AS+GoodDateINTL%2C%0A++++--+Fill+in+the+correct+region+based+on+our+input%0A%09TRY_PARSE%28%40GoodDateDE+AS+DATE+USING+%27de-de%27%29+AS+GoodDateDE%2C%0A%09TRY_PARSE%28%40GoodDateUS+AS+DATE+USING+%27us-en%27%29+AS+GoodDateUS%2C%0A%09TRY_PARSE%28%40BadDate+AS+DATETIME2%283%29+USING+%27sk-sk%27%29+AS+BadDate%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex15 - Convert imported data to dates with time zones](https://www.datacamp.com/teach/editor/4472/edit/186d925903?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0AWITH+EventDates+AS%0A%28%0A++++SELECT%0A++++++++--+Fill+in+the+missing+conversion+function%0A++++++++TRY_CONVERT%28DATETIME2%283%29%2C+it.EventDate%29+AT+TIME+ZONE+it.TimeZone+AS+EventDateOffset%2C%0A++++++++it.TimeZone%0A++++FROM+dbo.ImportedTime+it%0A++++++++INNER+JOIN+sys.time_zone_info+tzi%0A%09%09%09ON+it.TimeZone+%3D+tzi.name%0A%29%0ASELECT%0A++++--+Fill+in+the+approppriate+event+date+to+convert%0A%09CONVERT%28NVARCHAR%2850%29%2C+ed.EventDateOffset%29+AS+EventDateOffsetString%2C%0A%09CONVERT%28DATETIME2%280%29%2C+ed.EventDateOffset%29+AS+EventDateLocal%2C%0A%09ed.TimeZone%2C%0A++++--+Convert+from+a+DATETIMEOFFSET+to+DATETIME+at+UTC%0A%09CAST%28ed.EventDateOffset+AT+TIME+ZONE+%27UTC%27+AS+DATETIME2%280%29%29+AS+EventDateUTC%2C%0A++++--+Convert+from+a+DATETIMEOFFSET+to+DATETIME+with+time+zone%0A%09CAST%28ed.EventDateOffset+AT+TIME+ZONE+%27US+Eastern+Standard+Time%27++AS+DATETIME2%280%29%29+AS+EventDateUSEast%0AFROM+EventDates+ed%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex16 - Test type-safe conversion function performance (Step 1)](https://www.datacamp.com/teach/editor/4472/edit/7a83cabc1f?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0A--+Try+out+how+fast+the+safe+CAST%28%29+function+is%0ADECLARE+%40StartTimeCast+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0ASELECT+TRY_CAST%28DateText+AS+DATE%29+AS+TestDate+FROM+%23DateText%3B%0ADECLARE+%40EndTimeCast+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0A%0A--+Determine+how+much+time+the+conversion+took%0ASELECT%0A++++DATEDIFF%28MILLISECOND%2C+%40StartTimeCast%2C+%40EndTimeCast%29+AS+ExecutionTimeCast%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex16 - Test type-safe conversion function performance (Step 2)](https://www.datacamp.com/teach/editor/4472/edit/4b830b6811?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0A--+Try+out+how+fast+the+safe+CONVERT%28%29+function+is%0ADECLARE+%40StartTimeConvert+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0ASELECT+TRY_CONVERT%28DATE%2C+DateText%29+AS+TestDate+FROM+%23DateText%3B%0ADECLARE+%40EndTimeConvert+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0A%0A--+Determine+how+much+time+the+conversion+took%0ASELECT%0A++++DATEDIFF%28MILLISECOND%2C+%40StartTimeConvert%2C+%40EndTimeConvert%29+AS+ExecutionTimeConvert%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch2Ex16 - Test type-safe conversion function performance (Step 3)](https://www.datacamp.com/teach/editor/4472/edit/2039e24e7f?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0A--+Try+out+how+fast+the+safe+CONVERT%28%29+function+is%0ADECLARE+%40StartTimeConvert+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0ASELECT+TRY_CONVERT%28DATE%2C+DateText%29+AS+TestDate+FROM+%23DateText%3B%0ADECLARE+%40EndTimeConvert+DATETIME2%287%29+%3D+SYSUTCDATETIME%28%29%3B%0A%0A--+Determine+how+much+time+the+conversion+took%0ASELECT%0A++++DATEDIFF%28MILLISECOND%2C+%40StartTimeConvert%2C+%40EndTimeConvert%29+AS+ExecutionTimeConvert%3B%0A&start=tsql_file&parser=tsql) ### Chapter 3 - [x] [Ch3Ex7 - Calculating median in SQL Server](https://www.datacamp.com/teach/editor/4472/edit/c2b30f85ce?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT+DISTINCT%0A%09it.IncidentType%2C%0A%09AVG%28CAST%28ir.NumberOfIncidents+AS+DECIMAL%284%2C2%29%29%29%0A%09++++OVER%28PARTITION+BY+it.IncidentType%29+AS+MeanNumberOfIncidents%2C%0A++++---+Fill+in+the+missing+values%0A%09PERCENTILE_CONT%280.5%29%0A++++%09WITHIN+GROUP+%28ORDER+BY+ir.NumberOfIncidents+DESC%29%0A++++++++OVER+%28PARTITION+BY+it.IncidentType%29+AS+MedianNumberOfIncidents%2C%0A%09COUNT%281%29+OVER+%28PARTITION+BY+it.IncidentType%29+AS+NumberOfRows%0AFROM+dbo.IncidentRollup+ir%0A%09INNER+JOIN+dbo.IncidentType+it%0A%09%09ON+ir.IncidentTypeID+%3D+it.IncidentTypeID%0A%09INNER+JOIN+dbo.Calendar+c%0A%09%09ON+ir.IncidentDate+%3D+c.Date%0AWHERE%0A%09c.CalendarQuarter+%3D+2%0A%09AND+c.CalendarYear+%3D+2020%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch3Ex13 - Generate a summary with ROLLUP](https://www.datacamp.com/teach/editor/4472/edit/33e732ecb0?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09c.CalendarYear%2C%0A%09c.CalendarQuarterName%2C%0A%09c.CalendarMonth%2C%0A++++--+Include+the+sum+of+incidents+by+day+over+each+range%0A%09SUM%28ir.NumberOfIncidents%29+AS+NumberOfIncidents%0AFROM+dbo.IncidentRollup+ir%0A%09INNER+JOIN+dbo.Calendar+c%0A%09%09ON+ir.IncidentDate+%3D+c.Date%0AWHERE%0A%09ir.IncidentTypeID+%3D+2%0AGROUP+BY%0A%09--+GROUP+BY+needs+to+include+all+non-aggregated+columns%0A%09c.CalendarYear%2C%0A%09c.CalendarQuarterName%2C%0A%09c.CalendarMonth%0A--+Fill+in+your+grouping+operator%0AWITH+ROLLUP%0AORDER+BY%0A%09c.CalendarYear%2C%0A%09c.CalendarQuarterName%2C%0A%09c.CalendarMonth%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch3Ex14 - View all aggregations with CUBE](https://www.datacamp.com/teach/editor/4472/edit/d2252c9e57?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09--+Use+the+ORDER+BY+clause+as+a+guide+for+these+columns%0A%09ir.IncidentTypeID%2C%0A%09c.CalendarQuarterName%2C%0A%09c.WeekOfMonth%2C%0A%09SUM%28ir.NumberOfIncidents%29+AS+NumberOfIncidents%0AFROM+dbo.IncidentRollup+ir%0A%09INNER+JOIN+dbo.Calendar+c%0A%09%09ON+ir.IncidentDate+%3D+c.Date%0AWHERE%0A%09ir.IncidentTypeID+IN+%283%2C+4%29%0AGROUP+BY%0A%09--+GROUP+BY+should+include+all+non-aggregated+columns%0A%09ir.IncidentTypeID%2C%0A%09c.CalendarQuarterName%2C%0A%09c.WeekOfMonth%0A--+Fill+in+your+grouping+operator%0AWITH+CUBE%0AORDER+BY%0A%09ir.IncidentTypeID%2C%0A%09c.CalendarQuarterName%2C%0A%09c.WeekOfMonth%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch3Ex15 - Generate custom groupings with GROUPING SETS](https://www.datacamp.com/teach/editor/4472/edit/c5e6fe1093?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09c.CalendarYear%2C%0A%09c.CalendarQuarterName%2C%0A%09c.CalendarMonth%2C%0A%09SUM%28ir.NumberOfIncidents%29+AS+NumberOfIncidents%0AFROM+dbo.IncidentRollup+ir%0A%09INNER+JOIN+dbo.Calendar+c%0A%09%09ON+ir.IncidentDate+%3D+c.Date%0AWHERE%0A%09ir.IncidentTypeID+%3D+2%0A--+Fill+in+your+grouping+operator+here%0AGROUP+BY+GROUPING+SETS%0A%28%0A%09%28c.CalendarMonth%2C+c.CalendarQuarterName%2C+c.CalendarYear%29%2C%0A%09%28c.CalendarYear%29%2C%0A++++--+This+remains+blank%3B+it+gives+us+the+grand+total%0A%09%28%29%0A%29%0AORDER+BY%0A%09c.CalendarYear%2C%0A%09c.CalendarQuarterName%2C%0A%09c.CalendarMonth%3B%0A&start=tsql_file&parser=tsql) - [x] [Ch3Ex16 - Combine multiple aggregations in one query](https://www.datacamp.com/teach/editor/4472/edit/74979f362a?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09c.CalendarYear%2C%0A%09c.CalendarMonth%2C%0A%09c.DayOfWeek%2C%0A%09c.IsWeekend%2C%0A%09SUM%28ir.NumberOfIncidents%29+AS+NumberOfIncidents%0AFROM+dbo.IncidentRollup+ir%0A%09INNER+JOIN+dbo.Calendar+c%0A%09%09ON+ir.IncidentDate+%3D+c.Date%0AGROUP+BY+GROUPING+SETS%0A%28%0A++++--+Include+each+non-aggregated+column+at+least+once%0A%09%28c.CalendarMonth%2C+c.CalendarYear%29%2C%0A%09%28c.DayOfWeek%29%2C%0A%09%28c.IsWeekend%29%2C%0A++++--+This+remains+empty%3B+it+gives+us+the+grand+total%0A%09%28%29%0A%29%0AORDER+BY%0A%09c.CalendarYear%2C%0A%09c.CalendarMonth%2C%0A%09c.DayOfWeek%2C%0A%09c.IsWeekend%3B%0A&start=tsql_file&parser=tsql) ### Chapter 4 - [x] [Ch4Ex9 - Seeing prior and future periods](https://www.datacamp.com/teach/editor/4472/edit/2bb80639f8?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09ir.IncidentDate%2C%0A%09ir.IncidentTypeID%2C%0A++++--+Get+the+prior+day%27s+number+of+incidents%0A%09LAG%28ir.NumberOfIncidents%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29+AS+PriorDayIncidents%2C%0A%09ir.NumberOfIncidents+AS+CurrentDayIncidents%2C%0A++++--+Get+the+next+day%27s+number+of+incidents%0A%09LEAD%28ir.NumberOfIncidents%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29+AS+NextDayIncidents%0AFROM+dbo.IncidentRollup+ir%0AWHERE%0A%09ir.IncidentDate+%3E%3D+%272019-07-02%27%0A%09AND+ir.IncidentDate+%3C%3D+%272019-07-31%27%0A%09AND+ir.IncidentTypeID+IN+%281%2C+2%29%0AORDER+BY%0A%09ir.IncidentDate%2C%0Air.IncidentTypeID%3B%0A%0A&start=tsql_file&parser=tsql) - [x] [Ch4Ex10 - Seeing the prior three periods](https://www.datacamp.com/teach/editor/4472/edit/1e72a5d49d?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09ir.IncidentDate%2C%0A%09ir.IncidentTypeID%2C%0A++++--+Fill+in+two+periods+ago%0A%09LAG%28ir.NumberOfIncidents%2C+2%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29+AS+Trailing2Day%2C%0A++++--+Fill+in+one+period+ago%0A%09LAG%28ir.NumberOfIncidents%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29+AS+Trailing1Day%2C%0A%09ir.NumberOfIncidents+AS+CurrentDayIncidents%2C%0A++++--+Fill+in+next+period%0A%09LEAD%28ir.NumberOfIncidents%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29+AS+NextDay%0AFROM+dbo.IncidentRollup+ir%0AWHERE%0A%09ir.IncidentDate+%3E%3D+%272019-07-01%27%0A%09AND+ir.IncidentDate+%3C%3D+%272019-07-31%27%0A%09AND+ir.IncidentTypeID+IN+%281%2C+2%29%0AORDER+BY%0A%09ir.IncidentDate%2C%0Air.IncidentTypeID%3B%0A%0A&start=tsql_file&parser=tsql) - [x] [Ch4Ex11 - Calculating days elapsed between incidents](https://www.datacamp.com/teach/editor/4472/edit/0024124d13?branch=final-review): [AST viewer](https://ast-viewer.datacamp.com/editor?code=%0ASELECT%0A%09ir.IncidentDate%2C%0A%09ir.IncidentTypeID%2C%0A++++--+Fill+in+the+days+since+last+incident%0A%09DATEDIFF%28DAY%2C+LAG%28ir.IncidentDate%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29%2C+ir.IncidentDate%29+AS+DaysSinceLastIncident%2C%0A++++--+Fill+in+the+days+until+next+incident%0A%09DATEDIFF%28DAY%2C+ir.IncidentDate%2C+LEAD%28ir.IncidentDate%2C+1%29+OVER+%28%0A%09%09PARTITION+BY+ir.IncidentTypeID%0A%09%09ORDER+BY+ir.IncidentDate%0A%09%29%29+AS+DaysUntilNextIncident%0AFROM+dbo.IncidentRollup+ir%0AWHERE%0A%09ir.IncidentDate+%3E%3D+%272019-07-02%27%0A%09AND+ir.IncidentDate+%3C%3D+%272019-07-31%27%0A%09AND+ir.IncidentTypeID+IN+%281%2C+2%29%0AORDER+BY%0A%09ir.IncidentTypeID%2C%0Air.IncidentDate%3B%0A%0A&start=tsql_file&parser=tsql)
After re-running all of the SOLUTION code in https://campus-no-cache.datacamp.com/courses/20239/63394?ex=1, I've found the following exercises to have errors about unshaped nodes. I've also linked to the AST viewer for each exercise:
Chapter 2
Chapter 3
Chapter 4