Using the CONVERT function in SQL Server 2005 the following datetime formats can be produced:
SELECT
GETDATE() AS UnconvertedDateTimeUsingGetDate,
CURRENT_TIMESTAMP AS UnconvertedDateTimeUsingCurrentTimestamp,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 100) AS [Default mon dd yyyy hh:miAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 101) AS [USA mm/dd/yy],
CONVERT(nvarchar(30), GETDATE(), 102) AS [ANSI yy.mm.dd],
CONVERT(nvarchar(30), GETDATE(), 103) AS [British/French dd/mm/yy],
CONVERT(nvarchar(30), GETDATE(), 104) AS [German dd.mm.yy],
CONVERT(nvarchar(30), GETDATE(), 105) AS [Italian dd-mm-yy],
CONVERT(nvarchar(30), GETDATE(), 106) AS [dd mon yy],
CONVERT(nvarchar(30), GETDATE(), 107) AS [Mon dd, yy],
CONVERT(nvarchar(30), GETDATE(), 108) AS [hh:mm:ss],
CONVERT(nvarchar(30), GETDATE(), 109) AS [Default+milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 110) AS [USA mm-dd-yy],
CONVERT(nvarchar(30), GETDATE(), 111) AS [Japan yy/mm/dd],
CONVERT(nvarchar(30), GETDATE(), 112) AS [ISO yymmdd],
CONVERT(nvarchar(30), GETDATE(), 113) AS [Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 114) AS [hh:mi:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 120) AS [ODBC canonical yyyy-mm-dd hh:mi:ss(24h)],
CONVERT(nvarchar(30), GETDATE(), 121) AS [ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 126) AS [ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)],
CONVERT(nvarchar(30), GETDATE(), 130) AS [Hijri**** dd mon yyyy hh:mi:ss:mmmAM],
CONVERT(nvarchar(30), GETDATE(), 131) AS [ Hijri**** dd/mm/yy hh:mi:ss:mmmAM]
Tuesday 30 November 2010
Wednesday 24 November 2010
Create a CTE list of dates on the fly - SQL
CTE list of dates on the fly:
WITH D AS
(
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
)
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM D;
2000/pre CTE solution here
WITH D AS
(
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
)
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM D;
2000/pre CTE solution here
Tuesday 23 November 2010
Create a list of dates on the fly - SQL
I've recently had the requirement to produce a list of the last 30 days on the fly (I didn't have the option to refer to a 'Numbers' table). This was to be used as a 'Command' in Crystal Reports and joined to a database table to produce 'missing dates'.
Using the system table, spt_values, the first stage was to produce a list of numbers:
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
This is then extended to produce a list of the last 30 dates.
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
) AS D
This was with help from both Chris Morris & Kevin McKelvey at http://www.sqlservercentral.com/
CTE solution here
Using the system table, spt_values, the first stage was to produce a list of numbers:
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
This is then extended to produce a list of the last 30 dates.
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
) AS D
This was with help from both Chris Morris & Kevin McKelvey at http://www.sqlservercentral.com/
CTE solution here
Monday 15 November 2010
Wednesday 10 November 2010
Column aliases and derived tables in TSQL 2005 - SQL
SELECT YearMonth, COUNT(JobLogID) AS JobCount
FROM
(SELECT JobLogID, CONVERT(VARCHAR(7), JobLogSignOffTimeStamp, 121) AS YearMonth
FROM JobLog WHERE JobLogSignOff ='1') AS J
GROUP BY YearMonth
ORDER BY YearMonth;
FROM
(SELECT JobLogID, CONVERT(VARCHAR(7), JobLogSignOffTimeStamp, 121) AS YearMonth
FROM JobLog WHERE JobLogSignOff ='1') AS J
GROUP BY YearMonth
ORDER BY YearMonth;
Monday 1 November 2010
Row numbering in TSQL 2005 - SQL
New to SQL Server 2005 is ROW_NUMBER function.
SELECT
JobLog.JobLogID,
Establishment.EstablishmentName,
JobSubType.JobSubTypeDescription,
JobLog.JobLogSignOffTimeStamp,
ROW_NUMBER() OVER(ORDER BY EstablishmentName ) AS rownumNonDeterministic,
ROW_NUMBER() OVER(ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumDeterministic,
ROW_NUMBER() OVER(PARTITION BY EstablishmentName ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumPartitioning
FROM JobLog
INNER JOIN
Establishment ON JobLog.JobLog_EstablishmentID = Establishment.EstablishmentID
INNER JOIN
JobSubType ON JobLog.JobLog_JobSubTypeID = JobSubType.JobSubTypeID
WHERE JobLog.JobLogSignOff = '1'
ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp
SELECT
JobLog.JobLogID,
Establishment.EstablishmentName,
JobSubType.JobSubTypeDescription,
JobLog.JobLogSignOffTimeStamp,
ROW_NUMBER() OVER(ORDER BY EstablishmentName ) AS rownumNonDeterministic,
ROW_NUMBER() OVER(ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumDeterministic,
ROW_NUMBER() OVER(PARTITION BY EstablishmentName ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumPartitioning
FROM JobLog
INNER JOIN
Establishment ON JobLog.JobLog_EstablishmentID = Establishment.EstablishmentID
INNER JOIN
JobSubType ON JobLog.JobLog_JobSubTypeID = JobSubType.JobSubTypeID
WHERE JobLog.JobLogSignOff = '1'
ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp
Tuesday 7 September 2010
Select All option with multi-valued parameters (Part 2) - SSRS
Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.
The EstabCount parameter is given the following properties:
The below code simply populates the mult-valued parameter list:
Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.
=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)
Then we need to alter the WHERE clause in the main dataset query:
WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))
So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!
A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:
Friday 3 September 2010
Useful date formulas - SQL
This was posted on http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
declare @ThisDate datetime;
set @ThisDate = getdate();
select dateadd(dd, datediff(dd, 0, @ThisDate), 0) -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year
declare @ThisDate datetime;
set @ThisDate = getdate();
select dateadd(dd, datediff(dd, 0, @ThisDate), 0) -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year
Thursday 2 September 2010
Table metadata - SQL
Useful bit of code to get a tables metadata:
SELECT
COLUMN_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME AS Expr1, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = 'mytable')
ORDER BY ORDINAL_POSITION
SELECT
COLUMN_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME AS Expr1, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = 'mytable')
ORDER BY ORDINAL_POSITION
Tuesday 17 August 2010
Auxiliary Calendar table - SQL
This code is derived from a useful article by Todd Fifield at SQLServerCentral.com and has been modified to include columns based on the ISO8601 date and times standard (here and here).
IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO
CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10)
, DayNumber INT
, NameOfDay VARCHAR(10)
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO
IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO
CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10)
, DayNumber INT
, NameOfDay VARCHAR(10)
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO
Tuesday 10 August 2010
Tuesday 20 July 2010
Select All option with multi-valued parameters - SSRS
A major short coming with multi-valued parameters in SQL Server Reporting Services is that there is no way to disable the 'Select All' option. This may have massive performance issues if 'Select All' is checked and large numbers of records are pulled from the DB. A work-around is to create another parameter and by using the IIF function limit the number of parameters available for selection.
The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.
I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.
=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")
Where xxx is a value that will return zero records from the dataset.
Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).
It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:
=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))
The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.
I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.
=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")
Where xxx is a value that will return zero records from the dataset.
Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).
It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:
=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))
Thursday 15 July 2010
Display a list of all multivalued parameter values - SSRS
To display a list of all the selected multivalued parameters in a report use the Join function:
=Join(Parameters!Field.Value, ", ")
=Join(Parameters!Field.Value, ", ")
Multivalued parameter drop down width - SSRS
I was working on a SSRS project where I was using multivalued parameters. This creates a drop down list whereby the user can select the relevant values. Unfortunately in SSRS 2005 (SP3) the width of the dropdown is fixed, which seems to me as a bit of an oversight.
I believe that this may be corrected by altering the HtmlViewer.CSS file or by programmatically fixing the width of the available parameter values.
Neither is ideal.
I will look into whether this has been rectified in SSRS 2008.
I believe that this may be corrected by altering the HtmlViewer.CSS file or by programmatically fixing the width of the available parameter values.
Neither is ideal.
I will look into whether this has been rectified in SSRS 2008.
Monday 12 July 2010
Crystal Reports rebranded
SAP have recently announced that Crystal Reports is to be rebranded as SAP Crystal Reports, hopefully ensuring their long term commitment to the product. Various new products are also in the pipeline. Further details here
Wednesday 30 June 2010
Display dataset query in the report - SSRS
To display a dataset query in the report, simply place the following code in a textbox expression:
=datasets!datasetname.commandtext
=datasets!datasetname.commandtext
Monday 28 June 2010
Group Header continued... - CR
To display 'continued' or similar when a group continues onto a new page, firstly ensure Repeat Group Header is checked in Group Expert then add the following formula to the Group Header:
IF InRepeatedGroupHeader THEN {?School} & " continued"
ELSE {?School}
IF InRepeatedGroupHeader THEN {?School} & " continued"
ELSE {?School}
Tuesday 22 June 2010
Show Database name - SQL
To show the database name:
SELECT DB_NAME() AS DataBaseName
This also proves useful in Crystal Report development where 'test' and 'live' databases in use. Simply add the above code to a Command in Database Expert and show the DatabaseName field in the report header.
SELECT DB_NAME() AS DataBaseName
This also proves useful in Crystal Report development where 'test' and 'live' databases in use. Simply add the above code to a Command in Database Expert and show the DatabaseName field in the report header.
Wednesday 2 June 2010
Auxiliary CTE of Months - SQL
WITH CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT Mths ='November ' UNION ALL
SELECT Mths ='December '
)
SELECT Mths FROM CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT Mths ='November ' UNION ALL
SELECT Mths ='December '
)
SELECT Mths FROM CTE_Months
Saturday 29 May 2010
Auxiliary Months of Year Table - SQL
IF OBJECT_ID('dbo.CalendarMonth') IS NOT NULL
DROP TABLE dbo.CalendarMonth;
GO
CREATE TABLE dbo.CalendarMonth
(
mon nvarchar(10) NOT NULL
);
INSERT INTO dbo.CalendarMonth(mon)
SELECT 'January ' UNION ALL
SELECT 'February ' UNION ALL
SELECT 'March ' UNION ALL
SELECT 'April ' UNION ALL
SELECT 'May ' UNION ALL
SELECT 'June ' UNION ALL
SELECT 'July ' UNION ALL
SELECT 'August ' UNION ALL
SELECT 'September ' UNION ALL
SELECT 'October ' UNION ALL
SELECT 'November ' UNION ALL
SELECT 'December '
If, for example you are trying to provide an output of sales data for every month and the sale table doesn't have records for all 12 months, by creating an OUTER JOIN to the Calendar table, each month is returned.
IF OBJECT_ID('dbo.yearsales') IS NOT NULL
DROP TABLE dbo.yearsales;
GO
CREATE TABLE dbo.yearsales
(
sales int NOT NULL,saledate smalldatetime NOT NULL
)
INSERT INTO dbo.yearsales (sales, saledate)
SELECT '4','Jan 1 2009 12:00AM' UNION ALL
SELECT '5','Feb 1 2009 12:00AM' UNION ALL
SELECT '1','Apr 3 2009 12:00AM' UNION ALL
SELECT '11','May 5 2009 12:00AM' UNION ALL
SELECT '1','Jun 15 2009 12:00AM' UNION ALL
SELECT '4','Aug 5 2009 12:00AM' UNION ALL
SELECT '3','Oct 12 2009 12:00AM' UNION ALL
SELECT '17','Nov 2 2009 12:00AM' UNION ALL
SELECT '19','Dec 14 2009 12:00AM'
SELECT yearsales.sales, yearsales.saledate, DATENAME(month, yearsales.saledate) AS salesmonthname, CalendarMonth.mon
FROM yearsales RIGHT OUTER JOINCalendarMonth ON DATENAME(month, yearsales.saledate) = CalendarMonth.mon
DROP TABLE dbo.CalendarMonth;
GO
CREATE TABLE dbo.CalendarMonth
(
mon nvarchar(10) NOT NULL
);
INSERT INTO dbo.CalendarMonth(mon)
SELECT 'January ' UNION ALL
SELECT 'February ' UNION ALL
SELECT 'March ' UNION ALL
SELECT 'April ' UNION ALL
SELECT 'May ' UNION ALL
SELECT 'June ' UNION ALL
SELECT 'July ' UNION ALL
SELECT 'August ' UNION ALL
SELECT 'September ' UNION ALL
SELECT 'October ' UNION ALL
SELECT 'November ' UNION ALL
SELECT 'December '
If, for example you are trying to provide an output of sales data for every month and the sale table doesn't have records for all 12 months, by creating an OUTER JOIN to the Calendar table, each month is returned.
IF OBJECT_ID('dbo.yearsales') IS NOT NULL
DROP TABLE dbo.yearsales;
GO
CREATE TABLE dbo.yearsales
(
sales int NOT NULL,saledate smalldatetime NOT NULL
)
INSERT INTO dbo.yearsales (sales, saledate)
SELECT '4','Jan 1 2009 12:00AM' UNION ALL
SELECT '5','Feb 1 2009 12:00AM' UNION ALL
SELECT '1','Apr 3 2009 12:00AM' UNION ALL
SELECT '11','May 5 2009 12:00AM' UNION ALL
SELECT '1','Jun 15 2009 12:00AM' UNION ALL
SELECT '4','Aug 5 2009 12:00AM' UNION ALL
SELECT '3','Oct 12 2009 12:00AM' UNION ALL
SELECT '17','Nov 2 2009 12:00AM' UNION ALL
SELECT '19','Dec 14 2009 12:00AM'
SELECT yearsales.sales, yearsales.saledate, DATENAME(month, yearsales.saledate) AS salesmonthname, CalendarMonth.mon
FROM yearsales RIGHT OUTER JOINCalendarMonth ON DATENAME(month, yearsales.saledate) = CalendarMonth.mon
Wednesday 26 May 2010
Datetime types in SQL - SQL
You cannot store only the date or time portion.
If only the date is specified, 00:00:00.000 is stored in the time portion.
If only the time is specified, 1900-01-01 is stored in the date portion.
For example,
SELECT CAST ('20100526' as datetime)
SELECT CAST ('12:47:59:009' as datetime)
Friday 7 May 2010
SOS! - Please help! - Report Manager times out - ***CULPRIT - AVG antivirus LinkScanner***
Any SQL Server installation/admin experts out there?
I have a development environment on my home PC running:
Windows 7 Ultimate 64-bit
(Windows Ultimate was installed as a 'clean copy' via an upgrade. The previous OS, Vista had SQL Server and all components including Reporting Services running successfully)
IIS spec
Prior to installing SQL Server I ensured the following IIS windows features were enabled:
-Internet Information Services
- -Web Management Tools
- - -IIS 6 Management Compatibility
- - - -IIS 6 Scripting Tools
- - - -IIS 6 WMI Compatibility
- - - -IIS Metabase and IIS 6 configuration compatibility
- - -IIS Management Console
- -World Wide Web Services
- - -Application Development Features
- - - -.NET Extensibility
- - - -ASP.NET
- - - -ISAPI Extensions
- - - -ISAPI Filters
- - -Common Http Features
- - - -Default Document
- - - -Directory Browsing
- - - -HTTP Errors
- - - -HTTP Redirection
- - - -Static Content
- - -Health and Diagnostics
- - - -HTTP Logging
- - - -Request Monitor
- - -Security
- - - -Request Filtering
- - - -Windows Authentication
Navigating to http://mycomputername successfully displays the IIS 7.0 welcome screen
SQL Server spec
All components of SQL Server 2005 Developer Edition 64-bit were then installed successfully, followed by SP3.
I also installed SP1 for Visual Studio 2005 followed by SP1 update.
A default configuration of SSRS was also implemented.
Each service is running under the following:
SQL Server - LocalSystem
SQL Server Agent - LocalSystem
SQL Server Analysis Services - LocalSystem
SQL Server Browser - LocalSystem
SQL Server FullText Search - LocalSystem
SQL Server Integration Services - NT Authority\NetworkService
SQL Server Reporting Services - NT Authority\NetworkService
Shared Memory & TCP/IP enabled
Reporting Services Configuration
All relevant parts are correctly configured including the following:
Windows Service Identity:
Sevice Account - NT Authority\NetworkService
Built-in Account - Network Service
Web Service Identity:
ASP .NET Service Account - NT Authority\NetworkService
Report Service & Report Manager run under ReportServer application Pool
IIS Manager
ReportServer Application Pool runs under the Classic Managed Pipeline mode and NetworkService Identity.
Internet Explorer settings
Running IE8
Local intranet settings:
Include all local (intranet) sites....
Include all sites that bypass....
Include all network paths....
Set my login which has admin rights Content Manager in Report Manager
PROBLEM
I can view and render reports via reportserver
I can view reports but CANNOT render reports in report manager. I get the 'Report is being generated' message and it eventually times out with the message 'The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host'
I've tried with various reports, some that pull data from my local db, some that have parameters and some that simply display text. NONE render in report manager, although the report paramaters are successfully retrieved from the local db.
I tested this using Mozilla firefox and although the layout is poor the reports render successfully.
I'm at a complete loss!
I've tried reinstalling the OS and SQL Server.
Tried setting up a new application pool in IIS using my login account and Classic managed pipeline mode.
In Reporting Services config I've tried using my login account for Windows Service Identity and ASP .NET Service Account.
All to no avail.
NIC & Wireless settings
This may/may not have any bearing on matters, I'm not sure!
When in the process of upgrading from Vista to Windows 7, it mentioned that the DELL Wireless WLAN Card Utility was not compatible with Windows 7 and I removed it.
Also, I was directed to this post (many thanks Jerry Nee) http://blogs.msdn.com/sql_protocols/archive/2008/04/08/understanding-connection-forcibly-closed-by-remote-host-errors-caused-by-toe-chimney.aspx regarding network adapters. Not sure if this is relevant seeing as I'm running a development system all on one PC. But, Im not sure! Please advise!
I hope someone can help me rectify this issue as I've been at it now for a good ten days!
I'm willing to flatten the whole lot and start again if recommended, if someone can state the best setup required for a 'stand-alone' development system such as mine.
Links to my forum posts are at:
http://www.sqlservercentral.com/Forums/Topic917776-162-1.aspx
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/e976a8c9-a8a0-4223-8aec-36a3899a8e77
http://forums.iis.net/t/1167401.aspx
Many thanks,
Dom Horton
==============================
UPDATE
==============================
I've managed to solve this problem after many frustrating hours!
I'm running the free edition of AVG 9.0 and by disabling the 'LinkScanner' component I've got things to work. Funny thing was that I was sure I'd tried disabling the anti-virus software as part of my process of elimination!
==============================
Further info
==============================
http://forums.avg.com/gb-en/avg-forums?sec=thread&act=show&id=151271
I have a development environment on my home PC running:
Windows 7 Ultimate 64-bit
(Windows Ultimate was installed as a 'clean copy' via an upgrade. The previous OS, Vista had SQL Server and all components including Reporting Services running successfully)
IIS spec
Prior to installing SQL Server I ensured the following IIS windows features were enabled:
-Internet Information Services
- -Web Management Tools
- - -IIS 6 Management Compatibility
- - - -IIS 6 Scripting Tools
- - - -IIS 6 WMI Compatibility
- - - -IIS Metabase and IIS 6 configuration compatibility
- - -IIS Management Console
- -World Wide Web Services
- - -Application Development Features
- - - -.NET Extensibility
- - - -ASP.NET
- - - -ISAPI Extensions
- - - -ISAPI Filters
- - -Common Http Features
- - - -Default Document
- - - -Directory Browsing
- - - -HTTP Errors
- - - -HTTP Redirection
- - - -Static Content
- - -Health and Diagnostics
- - - -HTTP Logging
- - - -Request Monitor
- - -Security
- - - -Request Filtering
- - - -Windows Authentication
Navigating to http://mycomputername successfully displays the IIS 7.0 welcome screen
SQL Server spec
All components of SQL Server 2005 Developer Edition 64-bit were then installed successfully, followed by SP3.
I also installed SP1 for Visual Studio 2005 followed by SP1 update.
A default configuration of SSRS was also implemented.
Each service is running under the following:
SQL Server - LocalSystem
SQL Server Agent - LocalSystem
SQL Server Analysis Services - LocalSystem
SQL Server Browser - LocalSystem
SQL Server FullText Search - LocalSystem
SQL Server Integration Services - NT Authority\NetworkService
SQL Server Reporting Services - NT Authority\NetworkService
Shared Memory & TCP/IP enabled
Reporting Services Configuration
All relevant parts are correctly configured including the following:
Windows Service Identity:
Sevice Account - NT Authority\NetworkService
Built-in Account - Network Service
Web Service Identity:
ASP .NET Service Account - NT Authority\NetworkService
Report Service & Report Manager run under ReportServer application Pool
IIS Manager
ReportServer Application Pool runs under the Classic Managed Pipeline mode and NetworkService Identity.
Internet Explorer settings
Running IE8
Local intranet settings:
Include all local (intranet) sites....
Include all sites that bypass....
Include all network paths....
Set my login which has admin rights Content Manager in Report Manager
PROBLEM
I can view and render reports via reportserver
I can view reports but CANNOT render reports in report manager. I get the 'Report is being generated' message and it eventually times out with the message 'The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host'
I've tried with various reports, some that pull data from my local db, some that have parameters and some that simply display text. NONE render in report manager, although the report paramaters are successfully retrieved from the local db.
I tested this using Mozilla firefox and although the layout is poor the reports render successfully.
I'm at a complete loss!
I've tried reinstalling the OS and SQL Server.
Tried setting up a new application pool in IIS using my login account and Classic managed pipeline mode.
In Reporting Services config I've tried using my login account for Windows Service Identity and ASP .NET Service Account.
All to no avail.
NIC & Wireless settings
This may/may not have any bearing on matters, I'm not sure!
When in the process of upgrading from Vista to Windows 7, it mentioned that the DELL Wireless WLAN Card Utility was not compatible with Windows 7 and I removed it.
Also, I was directed to this post (many thanks Jerry Nee) http://blogs.msdn.com/sql_protocols/archive/2008/04/08/understanding-connection-forcibly-closed-by-remote-host-errors-caused-by-toe-chimney.aspx regarding network adapters. Not sure if this is relevant seeing as I'm running a development system all on one PC. But, Im not sure! Please advise!
I hope someone can help me rectify this issue as I've been at it now for a good ten days!
I'm willing to flatten the whole lot and start again if recommended, if someone can state the best setup required for a 'stand-alone' development system such as mine.
Links to my forum posts are at:
http://www.sqlservercentral.com/Forums/Topic917776-162-1.aspx
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/e976a8c9-a8a0-4223-8aec-36a3899a8e77
http://forums.iis.net/t/1167401.aspx
Many thanks,
Dom Horton
==============================
UPDATE
==============================
I've managed to solve this problem after many frustrating hours!
I'm running the free edition of AVG 9.0 and by disabling the 'LinkScanner' component I've got things to work. Funny thing was that I was sure I'd tried disabling the anti-virus software as part of my process of elimination!
==============================
Further info
==============================
http://forums.avg.com/gb-en/avg-forums?sec=thread&act=show&id=151271
Wednesday 5 May 2010
Nth largest value - SSRS
I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).
For example, In Crystal Reports, its simply a case of using Insert>>Summary
I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.
Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)
SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson
The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.
SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc
By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.
For example, In Crystal Reports, its simply a case of using Insert>>Summary
I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.
Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)
SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson
The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.
SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc
By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.
Select All option - SSRS
It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:
For my report I've created three datasets.
Here's a way to do it:
For my report I've created three datasets.
1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.
SELECT '** SELECT ALL **' as PACKAGE
UNION
SELECT PACKAGE.PACKAGE as PACKAGE
FROM PACKAGE
order by PACKAGE.PACKAGE
2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.
SELECT '** SELECT ALL **' as PACKAGE
These two datasets are then used to populate the Report Parameters window as below:
3) The third parameter, in this case named 'ChangedRequest', provides the select logic.
SELECT .....
FROM .....
WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)
order by PACKAGE.PACKAGE
Which results in something like this:
Tuesday 20 April 2010
Green bar matrix report - SSRS
I was recently asked by an anonymous blog poster (if reading please identify yourself!) how to go about creating a 'green-bar' style cross-tab/matrix report in both Crystal and SSRS.
Here's how to do it in SSRS (Cystal to follow at a later date):
1) Create an inner row grouping, in this case named 'Count' and given a grouping expression of =1
2) for the cell 'Count', set the property Value =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")
3) for the cell 'Count', set the property BackgroundColor =value
4) for the matrix data cell, named 'STATUS', set the property BackgroundColor =ReportItems!count.Value
5) for the outer row grouping, named 'TITLE, set the property BackgroundColor =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")
6) To hide the inner row grouping cell, 'Count', set
the following properties:
-set width to a minimum
-set BorderStyle Left =None
-set Color =value
-set CanGrow =False
set FontSize =1pt
for the outer row grouping cell:
-set BorderStyle Right =None
Friday 9 April 2010
Useful Character code chart
Here's a link to a useful character code chart:
http://tlt.its.psu.edu/suggestions/international/bylanguage/mathchart.html
and to get the Windows Character Map Utility:
Start>>Programs>>Accessories>>System Tools
and simply copy and paste the relevant character or obtain the unicode number from the keystoke value, where applicable, or convert the hex to decimal.
Hexadecimal to decimal converter:
http://easycalculation.com/hex-converter.php
http://tlt.its.psu.edu/suggestions/international/bylanguage/mathchart.html
and to get the Windows Character Map Utility:
Start>>Programs>>Accessories>>System Tools
and simply copy and paste the relevant character or obtain the unicode number from the keystoke value, where applicable, or convert the hex to decimal.
Hexadecimal to decimal converter:
http://easycalculation.com/hex-converter.php
Array example - Crystal
Local Numbervar counter := 1;
Local Stringvar Array taskdates;
Local Datetimevar dates := CurrentDate;
Local Datetimevar finishdate := DateAdd("yyyy",5,CurrentDate);
Local Stringvar returnstring;
Do
(
//set array size to counter, initally 1, preserving previous size and values
Redim Preserve taskdates[counter];
//assign dates value, initially CurrentDate to array element numbered counter, initially 1
taskdates[counter] := ToText(dates, "dd-MMM-yyyy");
//increment date variable by 1 year
dates := DateAdd("yyyy",1,dates);
//increment counter by 1
counter := counter+1;
)
//continue do loop while date variable is less than or equal to finishdate
While dates <= finishdate ;
returnstring := Join(taskdates, Chr(13))
Local Stringvar Array taskdates;
Local Datetimevar dates := CurrentDate;
Local Datetimevar finishdate := DateAdd("yyyy",5,CurrentDate);
Local Stringvar returnstring;
Do
(
//set array size to counter, initally 1, preserving previous size and values
Redim Preserve taskdates[counter];
//assign dates value, initially CurrentDate to array element numbered counter, initially 1
taskdates[counter] := ToText(dates, "dd-MMM-yyyy");
//increment date variable by 1 year
dates := DateAdd("yyyy",1,dates);
//increment counter by 1
counter := counter+1;
)
//continue do loop while date variable is less than or equal to finishdate
While dates <= finishdate ;
returnstring := Join(taskdates, Chr(13))
Monday 22 March 2010
Remove Carriage Return, Line Feed and Tab - SQL
Remove Carriage Return, Line Feed and Tab characters in T-SQL.
REPLACE(REPLACE(REPLACE(Field, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
REPLACE(REPLACE(REPLACE(Field, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
Friday 12 March 2010
Check whether a string was all in uppercase - CR
I needed a quick way to check whether a string was all uppercase.
Using the InStr function:
InStr ({fieldname},(UpperCase ({fieldname})) ,0 )
The syntax being:
InStr([start,]string1,string2[,compare])
start- an optional starting position
string1,2 - strings to compare
compare - either 0 or 1, 0 being a binary comparison, 1 being textual.
http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx
Using the InStr function:
InStr ({fieldname},(UpperCase ({fieldname})) ,0 )
The syntax being:
InStr([start,]string1,string2[,compare])
start- an optional starting position
string1,2 - strings to compare
compare - either 0 or 1, 0 being a binary comparison, 1 being textual.
http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx
Wednesday 3 March 2010
Create Button Style - SSRS
To create a reasonable 'button style' for a text box:
BackgroundColor = WhiteSmoke
BorderColor = WhiteSmoke
BorderStyle = Outset
BorderWidth = 2pt
Color = Black
Tuesday 2 March 2010
SSRS URL Parameters - SSRS
To pass a report parameter through to another report via a new browser:
On the Navigation tab for the report objects properties add the following javascript command to the Jump to URL expression box:
="javascript:void(window.open('http://SERVER NAME/ReportServer/Pages/ReportViewer.aspx?%FOLDER NAME%REPORT NAME%&rs:Command=Render&PARAMETER NAME=" +Fields!Pass value.Value+"'))"
For example:
="javascript:void(window.open('http://dom-pc/ReportServer/Pages/ReportViewer.aspx?%2fCAD+GIS+DB+reports%2fJobs+By+Establishment&rs:Command=Render&EstablishmentParameter="+Fields!SITE_NAME.Value+"'))"
Friday 26 February 2010
Colour graduation function - SSRS
I needed to highlight date information dependant on it's proximity to todays date. Using a colour graduation, greater emphasis is placed on dates near todays and those in the future, while less emphasis is placed on historical dates.
I created a custom function:
Public Function MonthColourGraduation(ByVal ActualValue As integer, ByVal NeutralColour As String) As String
Select ActualValue
Case >-1
Return "#FFD2D2"
Case >-2
Return "#FFD5D5"
Case >-3
Return "#FFD9D9"
Case >-6
Return "#FFDDDD"
Case >-9
Return "#FFE1E1"
Case >-12
Return "#FFE4E4"
Case >-15
Return "#FFE8E8"
Case >-18
Return "#FFECEC"
Case >-21
Return "#FFF0F0"
Case >-24
Return "#FFF3F3"
Case >-30
Return "#FFF7F7"
Case >- 36
Return "#FFFBFB"
Case Else
Return NeutralColour
End Select
End Function
and for the table rows background colour:
=Code.MonthColourGraduation((DateDiff("m", now(),Fields!LastEndTime.Value)), "White")
To get the hex colour codes I used:
Monday 25 January 2010
CTE maximum recursion error- SQL
I was running a CTE an got the following Error message:
'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'
The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:
OPTION (MAXRECURSION 500);
Overriding the default of 100 for this particular statement.
'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'
The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:
OPTION (MAXRECURSION 500);
Overriding the default of 100 for this particular statement.
Saturday 16 January 2010
Syntax for checking objects existence - SQL
SET NOCOUNT ON;
USE database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....
USE database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....
Friday 15 January 2010
Example Select Case to get time period bandings - CR
Example Select Case to get time period bandings:
select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"
select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"
Subscribe to:
Posts (Atom)