Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 advice on query

Author  Topic 

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-07-29 : 05:47:52
Hi Guys,

I'm currently working on a query for a report I need to create and i'm trying to find the best/most efficient way of doing it.

I have created a view for the base data as there are quite a few joins, but I need to retrieve data for 3 different date ranges. For one date (the date range is for one month) my query will return about 30 columns so I'm trying to think of a way to not repeat the columns for the 2nd and 3rd date range which are the same month in the previous two years.

Is there anyway I can somehow pivot the results to get the diffent values for the date ranges?

Below is a snippet of my current query for 1 of the 30 columns:

SELECT
[2010] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2009] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2008] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END)
FROM
CR_NEWCASES_VIEW NV


Hopefully this all makes sense

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-07-29 : 09:12:47
Just in case I didn't make any sense in my initial post, if I was to define all the columns manually my query would look like the following:

SELECT
[1.1 - 2010] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.1 - 2009] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.1 - 2008] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.2 - 2010] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.2 - 2009] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.2 - 2008] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END)
[1.3 - 2010] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.3 - 2009] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.3 - 2008] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END)
....... etc
FROM
CR_NEWCASES_VIEW NV


This would result in about 90 columns. Is there any better way I can do this?
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2010-07-29 : 10:33:21
[code]
-- Create a covering index
CREATE NONCLUSTERED INDEX IX_CR_NEWCASES_VIEW
ON CR_NEWCASES_VIEW ([-16DATE], CASETYPE, PROPERTYTYPE, IRN)

-- Allow query to work in parallel
SELECT D1.StartYearCount
,D2.StartMinusOneYearCount
,D3.StartMinusTwoYearCount
FROM
(
SELECT
StartYearCount = COUNT(CASE WHEN CASETYPE = 'F' AND PROPERTYTYPE = 'P' AND RIGHT(IRN, 2) = 'PA' THEN IRN ELSE NULL END)
FROM CR_NEWCASES_VIEW
WHERE [-16DATE] BETWEEN @STARTDATE AND @ENDDATE
) D1
CROSS JOIN
(
SELECT
StartMinusOneYearCount = COUNT(CASE WHEN CASETYPE = 'F' AND PROPERTYTYPE = 'P' AND RIGHT(IRN, 2) = 'PA' THEN IRN ELSE NULL END)
FROM CR_NEWCASES_VIEW
WHERE [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE)
) D2
CROSS JOIN
(
SELECT
StartMinusTwoYearCount = COUNT(CASE WHEN CASETYPE = 'F' AND PROPERTYTYPE = 'P' AND RIGHT(IRN, 2) = 'PA' THEN IRN ELSE NULL END)
FROM CR_NEWCASES_VIEW
WHERE [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE)
) D3
OPTION(MAXDOP 4)
[/code]
Go to Top of Page

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-07-29 : 10:41:59
Thanks for the suggestion. I'm not sure the index will be suitable because I have other date columns in my view that wil be referenced for other rows.

Also, will this not be similar to me creating all the rows I need manually, or am I missing something?
Go to Top of Page

Celko
Starting Member

23 Posts
Posted - 2010-07-29 : 13:31:08
>> I need to retrieve data for 3 different date ranges. <<

The usual way of doing reports is to set a table of reporting periods, something like this skeleton:

CREATE TABLE ReportPeriods
(report_period_name VARCHAR(20) NOT NULL PRIMARY KEY,
report_period_type CHAR(3) NOT NULL,
report_period_start_date DATE NOT NULL,
report_period_end_date DATE NOT NULL,
CHECK ( report_period_start_date <= report_period_end_date));

SQL is a database language and we like to avoid computations, especially temporal ones. Join on a simple BETWEEN predicate to drop them into the right reporting periods. Notice that periods can be subsets of each other and they can overlap. You can have fiscal, marketing, calendar, etc. type ranges for various departments. A 100 year table is small.





Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL
Go to Top of Page

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-07-30 : 03:36:42
It makes sense what you have suggested.

Would you mind just giving a small example as I think this would help for getting values for our financial year?
Go to Top of Page

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-07-30 : 10:16:40
Here is my complete query with all of the required columns:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2010-06-01 00:00:00.000'
SET @endDate = '2010-06-30 00:00:00.000'

SELECT
[1.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.4 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PT' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.4 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PT' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.4 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PT' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.5 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'TM' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.5 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'TM' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.5 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'TM' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.6 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'DN' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[1.6 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'DN' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[1.6 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'DN' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.4 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.4 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.4 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'EP' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.5 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.5 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.5 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.6 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.6 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.6 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.7 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.7 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.7 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.8 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.8 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.8 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.9 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.9 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.9 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.10 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.10 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.10 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.11 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.11 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.11 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.12 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.12 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.12 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.13 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.COUNTRYCODE = 'EP' AND [-1036DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.13 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.COUNTRYCODE = 'EP' AND [-1036DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.13 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.COUNTRYCODE = 'EP' AND [-1036DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.14 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.COUNTRYCODE = 'EP' AND [165DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.14 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.COUNTRYCODE = 'EP' AND [165DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.14 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.COUNTRYCODE = 'EP' AND [165DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.15 - CURR] = COUNT(CASE WHEN NV.CASETYPE IN ('B', 'C') AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[2.15 - PREV] = COUNT(CASE WHEN NV.CASETYPE IN ('B', 'C') AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[2.15 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE IN ('B', 'C') AND NV.COUNTRYCODE = 'EP' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[3.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.COUNTRYCODE = 'EP' AND [-11402DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[3.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.COUNTRYCODE = 'EP' AND [-11402DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[3.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.COUNTRYCODE = 'EP' AND [-11402DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[3.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.CATEGORY = 'X' AND NV.SUBTYPE = 'AS' AND [-16DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[3.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.CATEGORY = 'X' AND NV.SUBTYPE = 'AS' AND [-16DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[3.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.CATEGORY = 'X' AND NV.SUBTYPE = 'AS' AND [-16DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.4 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.4 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.4 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.5 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'HK' AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.5 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'HK' AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.5 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND  NV.COUNTRYCODE = 'HK' AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.6 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'R' AND NV.COUNTRYCODE <> 'HK' AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.6 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'R' AND NV.COUNTRYCODE <> 'HK' AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.6 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'R' AND  NV.COUNTRYCODE <> 'HK' AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.7 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[4.7 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[4.7 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.4 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.4 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.4 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.5 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.5 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.5 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.6 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.6 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.6 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.7 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE NOT IN ('EM', 'GB') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.7 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE NOT IN ('EM', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.7 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE NOT IN ('EM', 'GB') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.8 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.8 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.8 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.9 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.9 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.9 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.10 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.10 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.10 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND [-1036DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.11 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.11 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.11 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.12 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[5.12 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[5.12 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND [-1036DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'GB' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.4 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.4 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.4 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.5 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.5 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.5 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND [-4DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.6 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.6 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.6 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'EM' AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.7 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE NOT IN ('GB', 'EM') AND [156DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[6.7 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE NOT IN ('GB', 'EM') AND [156DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[6.7 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE NOT IN ('GB', 'EM') AND [156DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -2, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.1 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[8.1 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.1 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.2 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[8.2 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.2 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.3 - CURR] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN @STARTDATE AND @ENDDATE THEN NV.IRN ELSE NULL END),
[8.3 - PREV] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -1, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END),
[8.3 - PREV-1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND [-8DATE] IS NOT NULL AND [-400DATE] BETWEEN DATEADD("YY", -2, @STARTDATE) AND DATEADD("YY", -1, @ENDDATE) THEN NV.IRN ELSE NULL END)
FROM
CR_NEWCASES_VIEW NV


I really hope there is another way for me to do this!
Go to Top of Page

chris_cs
Posting Yak Master

223 Posts
Posted - 2010-08-02 : 10:02:19
I've managed to make a bit of progress on this after using themethod suggested by Celko. My code is now as follows:

SELECT
RP.PERIODNAME,
[1.1] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PA' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[1.2] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TA' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[1.3] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DA' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[1.4] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'P' AND RIGHT(NV.IRN, 2) = 'PT' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[1.5] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'T' AND RIGHT(NV.IRN, 2) = 'TM' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[1.6] = COUNT(CASE WHEN NV.CASETYPE = 'F' AND NV.PROPERTYTYPE = 'D' AND RIGHT(NV.IRN, 2) = 'DN' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.3] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.4] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.5] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.6] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.7] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.8] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.9] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.10] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.11] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.12] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'PCT' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.13] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-1036DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.14] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[165DATE]) = 6 THEN NV.IRN ELSE NULL END),
[2.15] = COUNT(CASE WHEN NV.CASETYPE IN ('B', 'C') AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[3.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.COUNTRYCODE = 'EP' AND MONTH(NV.[-11402DATE]) = 6 THEN NV.IRN ELSE NULL END),
[3.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.CATEGORY = 'X' AND NV.SUBTYPE = 'AS' AND MONTH(NV.[-16DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'N' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'N' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.3] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'K' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.4] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'D' AND NV.COUNTRYCODE NOT IN ('EP', 'GB') AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.5] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE = 'HK' AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.6] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.CATEGORY = 'R' AND NV.COUNTRYCODE <> 'HK' AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[4.7] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND NV.COUNTRYCODE NOT IN ('EP', 'GB', 'PCT') AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.3] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.4] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.5] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.6] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.7] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE NOT IN ('EM', 'GB') AND MONTH(NV.[156DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.8] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.9] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-1036DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.10] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-1036DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.11] = COUNT(CASE WHEN NV.CASETYPE = 'C' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-1036DATE]) = 6 THEN NV.IRN ELSE NULL END),
[5.12] = COUNT(CASE WHEN NV.CASETYPE = 'B' AND NV.PROPERTYTYPE = 'T' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-1036DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.3] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.4] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'N' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.5] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.BASIS = 'Y' AND NV.COUNTRYCODE = 'GB' AND MONTH(NV.[-4DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.6] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE = 'EM' AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[6.7] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND NV.COUNTRYCODE NOT IN ('GB', 'EM') AND MONTH(NV.[156DATE])= 6 THEN NV.IRN ELSE NULL END),
[8.1] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'P' AND [-8DATE] IS NOT NULL AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[8.2] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'T' AND [-8DATE] IS NOT NULL AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END),
[8.3] = COUNT(CASE WHEN NV.CASETYPE = 'A' AND NV.PROPERTYTYPE = 'D' AND [-8DATE] IS NOT NULL AND MONTH(NV.[-400DATE]) = 6 THEN NV.IRN ELSE NULL END)
FROM
CR_NEWCASES_VIEW NV
LEFT OUTER JOIN CR_REPORTPERIODS RP ON NV.[-16DATE] BETWEEN RP.PERIODSTARTDATE AND RP.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP1 ON NV.[-4DATE] BETWEEN RP1.PERIODSTARTDATE AND RP1.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP2 ON NV.[-400DATE] BETWEEN RP2.PERIODSTARTDATE AND RP2.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP3 ON NV.[-1036DATE] BETWEEN RP3.PERIODSTARTDATE AND RP3.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP4 ON NV.[165DATE] BETWEEN RP4.PERIODSTARTDATE AND RP4.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP5 ON NV.[-11402DATE] BETWEEN RP5.PERIODSTARTDATE AND RP5.PERIODENDDATE
LEFT OUTER JOIN CR_REPORTPERIODS RP6 ON NV.[156DATE] BETWEEN RP6.PERIODSTARTDATE AND RP6.PERIODENDDATE
WHERE RP.PERIODNAME IS NOT NULL
GROUP BY RP.PERIODNAME


This seems to be giving me the desired results, but I'd like to pivot the results to have the report periods as column headings.

I'm struggling a little with this so wondered if someome could give me some help?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site