CREATE FUNCTION [dbo].[GenerateCalendar] ( @FromDate DATETIME, @NoDays INT )RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen) WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16) SELECT [SeqNo] = t.N, [Date] = dt.DT, [Year] = dp.YY, [YrNN] = dp.YY % 100, [YYYYMM] = dp.YY * 100 + dp.MM, [BuddhaYr] = dp.YY + 543, [Month] = dp.MM, [Day] = dp.DD, [WkDNo] = DATEPART(dw,dt.DT), [WkDName] = CONVERT(NCHAR(9),dp.DW), [WkDName2] = CONVERT(NCHAR(2),dp.DW), [WkDName3] = CONVERT(NCHAR(3),dp.DW), [JulDay] = dp.DY, [JulWk] = dp.DY/7+1, [WkNo] = dp.DD/7+1, [Qtr] = DATEPART(qq,dt.Dt), [Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1, [LdOfMo] = DATEPART(dd,dp.LDtOfMo), [LDtOfMo] = dp.LDtOfMo FROM cteTally t CROSS APPLY ( --=== Create the date SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate) ) dt CROSS APPLY ( --=== Create the other parts from the date above using a "cCA" -- (Cascading CROSS APPLY, Acourtesy of ChrisM) SELECT YY = DATEPART(yy,dt.DT), MM = DATEPART(mm,dt.DT), DD = DATEPART(dd,dt.DT), DW = DATENAME(dw,dt.DT), Dy = DATEPART(dy,dt.DT), LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1) ) dpGO

SELECT *FROM dbo.GenerateCalendar(GETDATE(),10);

Create FUNCTION dbo.fxnGetPropertyColsQuery ( )RETURNS NVARCHAR(MAX)ASBEGINdeclare @Return NVARCHAR(MAX) select @Return = STUFF((SELECT distinct ',' + QUOTENAME(c.propName) FROM PropertyDef c where objecttypeid = 3 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') RETURN @returnEnd

create FUNCTION dbo.fxnGetPropertiesQuery( @cols NVARCHAR(MAX))RETURNS NVARCHAR(MAX)ASBEGINdeclare @Return NVARCHAR(MAX) select @Return = 'Select EquipmentNo,' + @cols + ' From ( Select EquipmentNo,PropValue,PropName,PropertyDef.PropertyDefID From PropertyDef Inner Join EPropertyData ON PropertyDef.PropertyDefID=EPropertyData.PropertyDefID INNER JOIN Equipment ON Equipment.EquipID=EPropertyData.ObjectID WHERE ObjectTypeID In(3,4) )AS SourceTable PIVOT ( max(PropValue) For PropName In (' + @cols + ') )AS PivotTable;' RETURN @returnEnd