D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Wednesday, 19 January 2011

FInd current financial year start and end dates - CR

To find the start date and finish date of the current financial year for subsequent use in various calculations:

Start of year formula:

Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar startfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN startfinancialyear := (DATESERIAL (YEAR(dates)-1,4,1))
ELSE startfinancialyear := (DATESERIAL (YEAR(dates),4,1));


End of year formula:
 
Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := (DATESERIAL (YEAR(dates),3,31))
ELSE endfinancialyear := (DATESERIAL (YEAR(dates)+1,3,31));


Alteration: The End of year formula gets the last day of the financial year, i.e. 31st March. However, it returns the value for the very start of the day (31/03/xxxx 00:00:00) which when incorporated into a selection formula could potential lead to records logged during that final day being excluded.

A correction would be:

End of year formula (correction):
 
Local Datetimevar dates := CURRENTDATETIME();
Local Datetimevar endfinancialyear;

IF MONTH(dates) IN [1,2,3]
THEN endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates),4,1)))
ELSE endfinancialyear := DATEADD("s" , -1, (DATESERIAL (YEAR(dates)+1,4,1)));

Thus, returning a value of "31/03/xxxx 23:59:59"





(Currently set up for use in the UK)


Tuesday, 4 January 2011

Using the EXCEPT clause wrapped in a CTE - SQL

--
--example EXCEPT clause wrapped in CTE
--
WITH RogueEstab
AS
(
SELECT EstablishmentID FROM Establishment
EXCEPT
SELECT JobLog_EstablishmentID FROM JobLog
)
SELECT RE.EstablishmentID, E.EstablishmentName FROM RogueEstab AS RE
INNER JOIN Establishment AS E
ON RE.EstablishmentID = E.EstablishmentID

Using the EXCEPT clause - SQL

--
--example EXCEPT clause....lists EstablishmentID without corresponding Jobs
--
SELECT EstablishmentID FROM Establishment
EXCEPT
SELECT JobLog_EstablishmentID FROM JobLog
ORDER BY EstablishmentID

Using the OVER clause - SQL

--
--using OVER clause to get count of SubTypes
--
SELECT E.EstablishmentName, S.JobSubTypeDescription
,COUNT(*) OVER(PARTITION BY S.JobSubTypeDescription) AS nums
FROM Establishment AS E LEFT OUTER JOIN
JobLog AS J ON E.EstablishmentID=J.JobLog_EstablishmentID INNER JOIN
JobSubType AS S ON J.JobLog_JobSubTypeID=S.JobSubTypeID
ORDER BY E.EstablishmentName