Skip to content

Instantly share code, notes, and snippets.

@chriskooken
Created November 30, 2012 18:10
Show Gist options
  • Save chriskooken/4177469 to your computer and use it in GitHub Desktop.
Save chriskooken/4177469 to your computer and use it in GitHub Desktop.
select c.name, c.is_nullable, o.name as TableName, t.name as TypeName from sys.columns c inner join Sys.objects o on c.object_id = o.object_id inner join
Sys.types t on t.system_type_id = c.system_type_id where o.name = 'v_SpreadsheetExport' ORDER BY column_id
TargetId 0 v_SpreadsheetExport uniqueidentifier
StudentCaseId 0 v_SpreadsheetExport uniqueidentifier
SkillArea 1 v_SpreadsheetExport varchar
Program 1 v_SpreadsheetExport varchar
Name 0 v_SpreadsheetExport varchar
Type 1 v_SpreadsheetExport varchar
DurationInSec 1 v_SpreadsheetExport varchar
SD 1 v_SpreadsheetExport varchar
QueuePriority 1 v_SpreadsheetExport varchar
steps 1 v_SpreadsheetExport nvarchar
steps 1 v_SpreadsheetExport sysname
Baselined 1 v_SpreadsheetExport varchar
DateOpened 1 v_SpreadsheetExport varchar
DateMastered 1 v_SpreadsheetExport varchar
MaintenanceLvl 1 v_SpreadsheetExport varchar
Notes 1 v_SpreadsheetExport varchar
IsDeleted 0 v_SpreadsheetExport bit
CREATE VIEW [dbo].[v_SpreadsheetExport]
AS
SELECT dbo.targets.TargetId, dbo.ProgramCategories.StudentCaseId, dbo.ProgramCategories.Name AS SkillArea, Programs.Name AS Program, dbo.Targets.Name,
CASE WHEN TargetKind = 1 THEN 'DTT' WHEN TargetKind = 2 THEN 'JUMP' WHEN TargetKind = 3 THEN 'TA' WHEN TargetKind = 4 THEN 'Duration' WHEN TargetKind
= 5 THEN 'Frequency' WHEN TargetKind = 6 THEN 'Toileting' WHEN TargetKind = 10 THEN 'Echoic' END AS Type,
CASE WHEN DurationInSeconds = 0 THEN '' WHEN DurationInSeconds IS NULL THEN '' ELSE CAST(DurationInSeconds AS VARCHAR(50))
END AS DurationInSeconds, SD, CASE WHEN QueuePriority IS NULL THEN '' ELSE CAST(QueuePriority AS VARCHAR(50)) END AS QueuePriority,
(SELECT Name + ', '
FROM dbo.Steps s
WHERE s.TargetId = dbo.Targets.TargetId
ORDER BY s.DisplayOrder FOR XML PATH('')) AS Steps, CASE WHEN Baselined IS NULL THEN '' ELSE CONVERT(VARCHAR, DateOpened, 101) END AS Baselined,
CASE WHEN DateOpened IS NULL THEN '' ELSE CONVERT(VARCHAR, DateOpened, 101) END AS DateOpened, CASE WHEN DateMastered IS NULL
THEN '' ELSE CONVERT(VARCHAR, DateMastered, 101) END AS DateMastered, CASE WHEN dbo.MaintainenceLevels.Name IS NULL
THEN '' ELSE dbo.MaintainenceLevels.Name END AS MaintenanceLevel, CASE WHEN Notes IS NULL THEN '' ELSE Notes END AS Notes,
dbo.Targets.IsDeleted
FROM targets INNER JOIN
dbo.Programs ON dbo.Targets.ProgramId = dbo.Programs.ProgramId INNER JOIN
dbo.ProgramCategories ON dbo.Programs.ProgramCategoryId = dbo.ProgramCategories.ProgramCategoryId INNER JOIN
dbo.StudentCases ON dbo.ProgramCategories.StudentCaseId = dbo.StudentCases.StudentCaseId LEFT OUTER JOIN
dbo.MaintainenceLevels ON MaintainenceLevelId = targets.MaintainenceLevel
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment