August 3, 2010 at 2:19 am
I have the following query:
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
I'm trying to figure out how I can PIVOT this to get the values for each report period in a column. I'm struggling a bit so wondered if someone could help?
Thanks in advance!
August 3, 2010 at 2:39 am
Hi Chris
Since a report type can depend on the values of up to six other columns, and not necessarily the same six between report types, I reckon pivot would make this way overcomplicated and would probably put a choke on the performance too. Even a preaggregate, in this case, might make things worse. However, there are a couple of pointers if performance is an issue:
RP1 to RP6 don't appear to be referenced in the output, are they neccessary? Comment them out as shown below and check the rowcount.
The LEFT OUTER JOIN to RP is converted to an INNER JOIN by the WHERE clause, so you might as well write it as such:
FROM
CR_NEWCASES_VIEW NV
INNER 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
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 3, 2010 at 3:08 am
Thanks for your reply.
I see what you mean so I think I may have to thinkabout how I'm doing this. The view I'm querying to get the data has multiple dates which I need to make sure fall into the reporting periods.
Can you suggest an easier way for me to do this?
August 3, 2010 at 3:13 am
Please post table def and some sample data in a ready to use format. There might be an easier way to do it...
August 3, 2010 at 3:22 am
Here is the query which makes up my view:
SELECT
C.IRN AS [IRN],
C.CASETYPE AS [CASETYPE],
C.PROPERTYTYPE AS [PROPERTYTYPE],
C.CASECATEGORY AS [CATEGORY],
C.COUNTRYCODE AS [COUNTRYCODE],
C.SUBTYPE AS [SUBTYPE],
P.BASIS AS [BASIS],
CE.EVENTDATE AS [-16DATE],
CE1.EVENTDATE AS [-4DATE],
CE2.EVENTDATE AS [-400DATE],
CE3.EVENTDATE AS [-1036DATE],
CE4.EVENTDATE AS [165DATE],
CE5.EVENTDATE AS [-11402DATE],
CE6.EVENTDATE AS [156DATE],
CE7.EVENTDATE AS [-8DATE]
FROM CASES C
LEFT OUTER JOIN [PROPERTY] P ON P.CASEID = C.CASEID
LEFT OUTER JOIN CASEEVENT CE ON CE.CASEID = C.CASEID AND CE.EVENTNO = -16
LEFT OUTER JOIN CASEEVENT CE1 ON CE1.CASEID = C.CASEID AND CE1.EVENTNO = -4
LEFT OUTER JOIN CASEEVENT CE2 ON CE2.CASEID = C.CASEID AND CE2.EVENTNO = -400
LEFT OUTER JOIN CASEEVENT CE3 ON CE3.CASEID = C.CASEID AND CE3.EVENTNO = -1036
LEFT OUTER JOIN CASEEVENT CE4 ON CE4.CASEID = C.CASEID AND CE4.EVENTNO = 165
LEFT OUTER JOIN CASEEVENT CE5 ON CE5.CASEID = C.CASEID AND CE5.EVENTNO = -11402
LEFT OUTER JOIN CASEEVENT CE6 ON CE6.CASEID = C.CASEID AND CE6.EVENTNO = 156
LEFT OUTER JOIN CASEEVENT CE7 ON CE7.CASEID = C.CASEID AND CE7.EVENTNO = -8
WHERE C.CASETYPE IN ('A', 'B', 'C', 'F')
AND C.PROPERTYTYPE IN ('P', 'T', 'D')
A sample line of data from the view is:
P027069DK|A|P|XA|DK|NULL|N|2006-05-17 00:00:00.000|NULL|NULL|NULL|NULL|NULL|2006-06-08-00:00:00.000|NULL
Thanks for looking at this
August 3, 2010 at 3:57 am
Please post table def and some sample data in a ready to use format for the following tables:
CASES, PROPERTY, CASEEVENT, and CR_REPORTPERIODS.
Also, please post your expected result based on those sample.
For a guideline on how to post data please see the first link in my signature.
I see quite some room for improvement here... 😉
August 3, 2010 at 11:36 am
It might also help to give an overview of what the tables represent and what you are trying to accomplish.
I also notice that you are joining the CASEEVENT table a total of EIGHT times. Since you are only pulling one field from each of those tables, you can definitely benefit from a pivot. Depending on your data and the rest of your process, you may want to pivot before or after joining to the rest of you tables. Here is a sample doing it before joining to the other tables.
SELECT
CASEID
, [-16] AS [-16DATE]
, [-4] AS [-4DATE]
, [-400] AS [-400DATE]
, [-1036] AS [-1036DATE]
, [165] AS [165DATE]
, [-11402] AS [-11402DATE]
, [156] AS [156DATE]
, [-8] AS [-8DATE]
FROM (
SELECT CASEID, EVENTNO, EVENTDATE
FROM CASEEVENT
) AS p
PIVOT (
Max(EVENTDATE)
FOR EVENTNO IN ( [-16] , [-4] , [-400] , [-1036] , [165] , [-11402] , [156] , [-8] )
) AS pvt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2010 at 1:26 am
I'm little reluctant to post some actual data on here because its pretty sensitive. But here is the definitions of the tables i'm using.
Cases table:
CREATE TABLE [dbo].[CASES](
[CASEID] [int] NOT NULL,
[IRN] [nvarchar](30) NOT NULL,
[FAMILY] [nvarchar](20) NULL,
[STEM] [nvarchar](30) NULL,
[STATUSCODE] [smallint] NULL,
[CASETYPE] [nchar](1) NULL,
[PROPERTYTYPE] [nchar](1) NULL,
[COUNTRYCODE] [nvarchar](3) NULL,
[CASECATEGORY] [nvarchar](2) NULL,
[SUBTYPE] [nvarchar](2) NULL,
[TYPEOFMARK] [int] NULL,
[TITLE] [nvarchar](254) NULL,
[NOINSERIES] [smallint] NULL,
[NOOFCLASSES] [smallint] NULL,
[LOCALCLASSES] [nvarchar](254) NULL,
[INTCLASSES] [nvarchar](254) NULL,
[LOCALCLIENTFLAG] [decimal](1, 0) NULL,
[ENTITYSIZE] [int] NULL,
[PREDECESSORID] [int] NULL,
[FILECOVER] [int] NULL,
[PURCHASEORDERNO] [nvarchar](80) NULL,
[REPORTTOTHIRDPARTY] [decimal](1, 0) NULL,
[CURRENTOFFICIALNO] [nvarchar](36) NULL,
[TAXCODE] [nvarchar](3) NULL,
[STOPPAYREASON] [nchar](1) NULL,
[EXTENDEDRENEWALS] [int] NULL,
[TITLE_TID] [int] NULL,
[BUDGETAMOUNT] [decimal](11, 2) NULL,
[IPODELAY] [int] NULL,
[APPLICANTDELAY] [int] NULL,
[IPOPTA] [int] NULL,
[OFFICEID] [int] NULL,
[BUDGETREVISEDAMT] [decimal](11, 2) NULL,
[STATETAXCODE] [nvarchar](3) NULL,
[SERVPERFORMEDIN] [nvarchar](20) NULL,
[LOGUSERID] [nvarchar](50) NULL,
[LOGIDENTITYID] [int] NULL,
[LOGTRANSACTIONNO] [int] NULL,
[LOGDATETIMESTAMP] [datetime] NULL,
[LOGAPPLICATION] [nvarchar](128) NULL,
[LOGOFFICEID] [int] NULL,
[NUMBEROFRESPONSES] [int] NULL,
[CAMPAIGNCOST] [decimal](11, 2) NULL,
[LEADCOUNT] [int] NULL,
[OPPORTUNITYCOUNT] [int] NULL,
[PROFITCENTRECODE] [nvarchar](6) NULL,
CONSTRAINT [XPKCASES] PRIMARY KEY CLUSTERED
(
[CASEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Case event table:
CREATE TABLE [dbo].[CASEEVENT](
[CASEID] [int] NOT NULL,
[EVENTNO] [int] NOT NULL,
[CYCLE] [smallint] NOT NULL,
[EVENTDATE] [datetime] NULL,
[EVENTDUEDATE] [datetime] NULL,
[DATEREMIND] [datetime] NULL,
[DATEDUESAVED] [decimal](1, 0) NULL DEFAULT ((0)),
[OCCURREDFLAG] [decimal](1, 0) NULL,
[CREATEDBYACTION] [nvarchar](2) NULL,
[CREATEDBYCRITERIA] [int] NULL,
[ENTEREDDEADLINE] [int] NULL,
[PERIODTYPE] [nchar](1) NULL,
[DOCUMENTNO] [smallint] NULL,
[DOCSREQUIRED] [smallint] NULL,
[DOCSRECEIVED] [smallint] NULL,
[USEMESSAGE2FLAG] [decimal](1, 0) NULL,
[GOVERNINGEVENTNO] [int] NULL,
[EVENTTEXT] [nvarchar](254) NULL,
[LONGFLAG] [decimal](1, 0) NULL,
[EVENTLONGTEXT] [ntext] NULL,
[JOURNALNO] [nvarchar](20) NULL,
[IMPORTBATCHNO] [int] NULL,
[EVENTTEXT_TID] [int] NULL,
[EMPLOYEENO] [int] NULL,
[SENDMETHOD] [int] NULL,
[SENTDATE] [datetime] NULL,
[RECEIPTDATE] [datetime] NULL,
[RECEIPTREFERENCE] [nvarchar](50) NULL,
[DISPLAYORDER] [smallint] NULL,
[FROMCASEID] [int] NULL,
[DUEDATERESPNAMETYPE] [nvarchar](3) NULL,
[LOGUSERID] [nvarchar](50) NULL,
[LOGIDENTITYID] [int] NULL,
[LOGTRANSACTIONNO] [int] NULL,
[LOGDATETIMESTAMP] [datetime] NULL,
[LOGAPPLICATION] [nvarchar](128) NULL,
[LOGOFFICEID] [int] NULL,
CONSTRAINT [XPKCASEEVENT] PRIMARY KEY CLUSTERED
(
[CASEID] ASC,
[EVENTNO] ASC,
[CYCLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Property table:
CREATE TABLE [dbo].[PROPERTY](
[CASEID] [int] NOT NULL,
[BASIS] [nvarchar](2) NULL,
[REGISTEREDUSERS] [nchar](1) NULL,
[DEBTORTYPE] [int] NULL,
[EXAMTYPE] [int] NULL,
[NOOFCLAIMS] [smallint] NULL,
[GOODSSERVICES] [nchar](1) NULL,
[RENEWALSTATUS] [smallint] NULL,
[RENEWALTYPE] [int] NULL,
[RENEWALCYCLE] [smallint] NULL,
[RENEWALNOTES] [nvarchar](254) NULL,
[PAYMENTFLAG] [decimal](1, 0) NULL,
[DEBITRAISEDFLAG] [decimal](1, 0) NULL,
[WORKINGINSTR] [decimal](1, 0) NULL,
[PLACEFIRSTUSED] [nvarchar](254) NULL,
[PROPOSEDUSE] [nvarchar](254) NULL,
[PLACEFIRSTUSED_TID] [int] NULL,
[PROPOSEDUSE_TID] [int] NULL,
[RENEWALNOTES_TID] [int] NULL,
[LOGUSERID] [nvarchar](50) NULL,
[LOGIDENTITYID] [int] NULL,
[LOGTRANSACTIONNO] [int] NULL,
[LOGDATETIMESTAMP] [datetime] NULL,
[LOGAPPLICATION] [nvarchar](128) NULL,
[LOGOFFICEID] [int] NULL,
CONSTRAINT [XPKPROPERTY] PRIMARY KEY CLUSTERED
(
[CASEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Report Periods table:
CREATE TABLE [dbo].[CR_REPORTPERIODS](
[PERIODID] [int] IDENTITY(1,1) NOT NULL,
[PERIODNAME] [varchar](20) NOT NULL,
[PERIODSTARTDATE] [datetime] NOT NULL,
[PERIODENDDATE] [datetime] NOT NULL
) ON [PRIMARY]
What I'm trying to do is count the amount of cases which have a certain event that fall in a certain date range. The month will be provided by a report parameter and I'll use that to count the cases in a certain month.
I hope this helps explain what I'm trying to do.
August 4, 2010 at 2:57 am
I think we need some sample data. You could also reduce the table def to the columns required. This would also reduce the amount of sample data to be generated and will help to "hide the total picture". Replace all data you consider being sensitive with made-up values (but keep it consistent when using it as foreign keys...) To obfuscate caseids's you could use CHECKSUM(NEWID()) to generate random numbers. If you reduce the columns and use fake data there shouldn't be anything to worry about. The next "level of obfuscation" would be3 using arbitrary column and table names. But it's (almost) too late for that 😉 (unless you edit all of your previous posts and ask ChrisM to remove his sample code...
In returnyou'll get tested code.
August 4, 2010 at 6:47 am
Chris-475469 (8/4/2010)
I'm little reluctant to post some actual data on here because its pretty sensitive. But here is the definitions of the tables i'm using.
Then make some up 😛
-- set up some sample data
DROP TABLE #CR_REPORTPERIODS
CREATE TABLE #CR_REPORTPERIODS (PERIODNAME VARCHAR(10), PERIODSTARTDATE DATETIME, PERIODENDDATE DATETIME)
INSERT INTO #CR_REPORTPERIODS (PERIODNAME, PERIODSTARTDATE, PERIODENDDATE)
SELECT 'August', GETDATE()-1, GETDATE() + 1
DROP TABLE #CASES
CREATE TABLE #CASES (CASEID INT, IRN CHAR(4), CASETYPE CHAR(1),
CASECATEGORY CHAR(1), COUNTRYCODE CHAR(3), SUBTYPE CHAR(2))
INSERT INTO #CASES (CASEID, IRN, CASETYPE,
CASECATEGORY, COUNTRYCODE, SUBTYPE)
SELECT 1, 'XXPA', 'A', 'N', 'GB ', 'AS' UNION ALL
SELECT 2, 'XXTA', 'A', 'K', 'GB ', 'AS'
DROP TABLE #PROPERTY
CREATE TABLE #PROPERTY (CASEID INT, BASIS CHAR(1), PROPERTYTYPE CHAR(1))
INSERT INTO #PROPERTY (CASEID, BASIS, PROPERTYTYPE)
SELECT 1, 'Y', 'P' UNION ALL
SELECT 2, 'N', 'P'
DROP TABLE #CASEEVENT
CREATE TABLE #CASEEVENT (CASEID INT, EVENTNO INT, EVENTDATE DATETIME)
INSERT INTO #CASEEVENT (CASEID, EVENTNO, EVENTDATE)
SELECT 1,-16,GETDATE() UNION ALL
SELECT 1,-4,GETDATE() UNION ALL
SELECT 1,-400,GETDATE() UNION ALL
SELECT 1,-1036,GETDATE() UNION ALL
SELECT 1,165,GETDATE() UNION ALL
SELECT 1,-11402,GETDATE() UNION ALL
SELECT 1,156,GETDATE() UNION ALL
SELECT 2,1,GETDATE() UNION ALL
SELECT 2,-4,GETDATE() UNION ALL
SELECT 2,-400,GETDATE() UNION ALL
SELECT 2,-1036,GETDATE() UNION ALL
SELECT 2,165,GETDATE() UNION ALL
SELECT 2,-11402,GETDATE() UNION ALL
SELECT 2,7,GETDATE()
-------------------------------------------------------
SELECT
NV.PERIODNAME,
[1.1] = SUM(CASE WHEN NV.GroupCode LIKE 'FP_____' AND NV.IRN2 = 'PA' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[1.2] = SUM(CASE WHEN NV.GroupCode LIKE 'FT_____' AND NV.IRN2 = 'TA' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[1.3] = SUM(CASE WHEN NV.GroupCode LIKE 'FD_____' AND NV.IRN2 = 'DA' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[1.4] = SUM(CASE WHEN NV.GroupCode LIKE 'FP_____' AND NV.IRN2 = 'PT' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[1.5] = SUM(CASE WHEN NV.GroupCode LIKE 'FT_____' AND NV.IRN2 = 'TM' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[1.6] = SUM(CASE WHEN NV.GroupCode LIKE 'FD_____' AND NV.IRN2 = 'DN' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[2.1] = SUM(CASE WHEN NV.GroupCode = 'APNNEP ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.2] = SUM(CASE WHEN NV.GroupCode = 'APNYEP ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.3] = SUM(CASE WHEN NV.GroupCode LIKE 'APK_EP ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.4] = SUM(CASE WHEN NV.GroupCode LIKE 'APD_EP ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.5] = SUM(CASE WHEN NV.GroupCode LIKE 'AP__EP ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[2.6] = SUM(CASE WHEN NV.GroupCode = 'APNNGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.7] = SUM(CASE WHEN NV.GroupCode = 'APNYGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.8] = SUM(CASE WHEN NV.GroupCode LIKE 'APK_GB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.9] = SUM(CASE WHEN NV.GroupCode LIKE 'APD_GB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.10] = SUM(CASE WHEN NV.GroupCode LIKE 'AP__GB ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[2.11] = SUM(CASE WHEN NV.GroupCode LIKE 'AP__PCT' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[2.12] = SUM(CASE WHEN NV.GroupCode LIKE 'AP__PCT' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[2.13] = SUM(CASE WHEN NV.GroupCode LIKE 'C___EP ' AND nv.[EVENT-1036] > 0 THEN 1 ELSE 0 END),
[2.14] = SUM(CASE WHEN NV.GroupCode LIKE 'B___EP ' AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[2.15] = SUM(CASE WHEN NV.GroupCode LIKE '[BC]___EP ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[3.1] = SUM(CASE WHEN NV.GroupCode LIKE 'A___EP ' AND nv.[EVENT-11402] > 0 THEN 1 ELSE 0 END),
[3.2] = SUM(CASE WHEN NV.GroupCode LIKE 'A_X____' AND NV.SUBTYPE = 'AS' AND nv.[EVENT-16] > 0 THEN 1 ELSE 0 END),
[4.1] = SUM(CASE WHEN NV.GroupCode LIKE 'APNN___' AND RIGHT(NV.GroupCode,3) NOT IN ('EP ', 'GB ', 'PCT') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.2] = SUM(CASE WHEN NV.GroupCode LIKE 'APNY___' AND RIGHT(NV.GroupCode,3) NOT IN ('EP ', 'GB ', 'PCT') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.3] = SUM(CASE WHEN NV.GroupCode LIKE 'APK____' AND RIGHT(NV.GroupCode,3) NOT IN ('EP ', 'GB ') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.4] = SUM(CASE WHEN NV.GroupCode LIKE 'APD____' AND RIGHT(NV.GroupCode,3) NOT IN ('EP ', 'GB ') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.5] = SUM(CASE WHEN NV.GroupCode LIKE 'AP__HK ' AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.6] = SUM(CASE WHEN NV.GroupCode LIKE 'APR____' AND RIGHT(NV.GroupCode,3) <> 'HK ' AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[4.7] = SUM(CASE WHEN NV.GroupCode LIKE 'AP_____' AND RIGHT(NV.GroupCode,3) NOT IN ('EP ', 'GB ', 'PCT') AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[5.1] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_NGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[5.2] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_YGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[5.3] = SUM(CASE WHEN NV.GroupCode LIKE 'AT__GB ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[5.4] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_NEM ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[5.5] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_YEM ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[5.6] = SUM(CASE WHEN NV.GroupCode LIKE 'AT__EM ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[5.7] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_____' AND RIGHT(NV.GroupCode,3) NOT IN ('EM ', 'GB ') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[5.8] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_____' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[5.9] = SUM(CASE WHEN NV.GroupCode LIKE 'CT__GB ' AND nv.[EVENT-1036] > 0 THEN 1 ELSE 0 END),
[5.10] = SUM(CASE WHEN NV.GroupCode LIKE 'BT__GB ' AND nv.[EVENT-1036] > 0 THEN 1 ELSE 0 END),
[5.11] = SUM(CASE WHEN NV.GroupCode LIKE 'CT__EM ' AND nv.[EVENT-1036] > 0 THEN 1 ELSE 0 END),
[5.12] = SUM(CASE WHEN NV.GroupCode LIKE 'BT__EM ' AND nv.[EVENT-1036] > 0 THEN 1 ELSE 0 END),
[6.1] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_NGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[6.2] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_YGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[6.3] = SUM(CASE WHEN NV.GroupCode LIKE 'AD__GB ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[6.4] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_NEM ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END),
[6.5] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_YGB ' AND nv.[EVENT-4] > 0 THEN 1 ELSE 0 END), -- check ##### 'AD_YEM '?
[6.6] = SUM(CASE WHEN NV.GroupCode LIKE 'AD__EM ' AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[6.7] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_____' AND RIGHT(NV.GroupCode,3) NOT IN ('GB ', 'EM ') AND nv.[EVENT156] > 0 THEN 1 ELSE 0 END),
[8.1] = SUM(CASE WHEN NV.GroupCode LIKE 'AP_____' AND nv.[EVENT-8] > 0 AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[8.2] = SUM(CASE WHEN NV.GroupCode LIKE 'AT_____' AND nv.[EVENT-8] > 0 AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END),
[8.3] = SUM(CASE WHEN NV.GroupCode LIKE 'AD_____' AND nv.[EVENT-8] > 0 AND nv.[EVENT-400] > 0 THEN 1 ELSE 0 END)
FROM (
-- could do grouping at this level
SELECT ce.PERIODNAME,
c.CASEID,
RIGHT(c.IRN, 2) AS [IRN2],
GroupCode = CAST(c.CASETYPE + ISNULL(p.PROPERTYTYPE, ' ') + c.CASECATEGORY + p.BASIS + LEFT(C.COUNTRYCODE+' ',3) AS CHAR(7)), -- check types & lengths
c.SUBTYPE,
ce.[EVENT-16],
ce.[EVENT-8],
ce.[EVENT-4],
ce.[EVENT-400],
ce.[EVENT-1036],
ce.[EVENT165],
ce.[EVENT-11402],
ce.[EVENT156]
FROM #CASES c
LEFT JOIN (
SELECT rp.PERIODNAME, CASEID,
[EVENT-16] = SUM(CASE WHEN EVENTNO = -16 THEN 1 ELSE 0 END),
[EVENT-8] = SUM(CASE WHEN EVENTNO = -8 THEN 1 ELSE 0 END),
[EVENT-4] = SUM(CASE WHEN EVENTNO = -4 THEN 1 ELSE 0 END),
[EVENT-400] = SUM(CASE WHEN EVENTNO = -400 THEN 1 ELSE 0 END),
[EVENT-1036] = SUM(CASE WHEN EVENTNO = -1036 THEN 1 ELSE 0 END),
[EVENT165] = SUM(CASE WHEN EVENTNO = 165 THEN 1 ELSE 0 END),
[EVENT-11402] = SUM(CASE WHEN EVENTNO = -11402 THEN 1 ELSE 0 END),
[EVENT156] = SUM(CASE WHEN EVENTNO = 156 THEN 1 ELSE 0 END)
FROM #CASEEVENT ce
INNER JOIN #CR_REPORTPERIODS RP ON ce.EVENTDATE BETWEEN RP.PERIODSTARTDATE AND RP.PERIODENDDATE
WHERE ce.EVENTNO IN (-16, -8, -4, -400, -1036, 165, -11402, 156)
GROUP BY PERIODNAME, CASEID
) ce ON ce.CASEID = c.CASEID
LEFT OUTER JOIN #PROPERTY P ON P.CASEID = C.CASEID
WHERE C.CASETYPE IN ('A', 'B', 'C', 'F')
AND p.PROPERTYTYPE IN ('P', 'T', 'D')
-- /could do grouping at this level
) nv
GROUP BY nv.PERIODNAME
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2010 at 1:18 am
Sorry about the delay in replying but here is some sample data and the tables with only the fields I'm referencing:
CREATE TABLE #CASES
(
CASEID INT,
IRN CHAR(20),
CASETYPE CHAR(1),
PROPERTYTYPE CHAR(1),
COUNTRYCODE CHAR(2),
CASECATEGORY CHAR(1),
SUBTYPE CHAR(2)
)
INSERT INTO #CASES
SELECT 10, 'S123456PA', 'F', 'P', 'PA', NULL, 'AS' UNION ALL
SELECT 11, 'S123456TA', 'F', 'T', 'PA', NULL, 'AS' UNION ALL
SELECT 12, 'S123456DA', 'F', 'D', 'PA', NULL, 'AS' UNION ALL
SELECT 13, 'S898989PA', 'A', 'P', 'EP', 'N', 'AS' UNION ALL
SELECT 14, 'S444444PA', 'F', 'P', 'EP', 'K', 'AS' UNION ALL
SELECT 15, 'S232323EP', 'C', 'P', 'EP', NULL, 'AS' UNION ALL
SELECT 16, 'S123456PA', 'B', 'P', 'EP', NULL, 'AS' UNION ALL
SELECT 17, 'S123456TT', 'A', 'P', 'EP', 'X', 'AS' UNION ALL
SELECT 18, 'S123456GB', 'A', 'P', 'GB', 'N', 'AS' UNION ALL
SELECT 19, 'S123456HK', 'A', 'P', 'HK', NULL, 'AS' UNION ALL
CREATE TABLE #CASEEVENT
(
CASEID INT,
EVENTNO INT,
EVENTDATE DATETIME
)
INSERT INTO #CASEEVENT
SELECT 10, -16, '2010-06-11 00:00:00.000' UNION ALL
SELECT 11, -16, '2010-06-11 00:00:00.000' UNION ALL
SELECT 12, -16, '2010-06-11 00:00:00.000' UNION ALL
SELECT 13, -4, '2010-06-04 00:00:00.000' UNION ALL
SELECT 14, -400, '2010-06-04 00:00:00.000' UNION ALL
SELECT 15, -1036, '2010-06-04 00:00:00.000' UNION ALL
SELECT 16, -165, '2010-06-04 00:00:00.000' UNION ALL
SELECT 17, -16, '2010-06-04 00:00:00.000' UNION ALL
SELECT 18, -4, '2010-06-04 00:00:00.000' UNION ALL
SELECT 19, 156, '2010-06-04 00:00:00.000' UNION ALL
CREATE TABLE #PROPERTY
(
CASEID INT,
BASIS VARCHAR(5)
)
INSERT INTO #PROPERTY
SELECT 13, 'N' UNION ALL
SELECT 18, 'Y' UNION ALL
CREATE TABLE #CR_REPORTPERIODS(
PERIODID INT IDENTITY,
PERIODNAME VARCHAR(20),
PERIODSTARTDATE] DATETIME,
PERIODENDDATE DATETIME
)
INSERT INTO #CR_REPORTPERIODS
SELECT 1, 2007-2008, '2007-07-01 00:00:00.000', '2008-06-30 00:00:00.000' UNION ALL
SELECT 2, 2008-2009, '2008-07-01 00:00:00.000', '2009-06-30 00:00:00.000' UNION ALL
SELECT 3, 2009-2010, '2009-07-01 00:00:00.000', '2010-06-30 00:00:00.000' UNION ALL
Hopefully this is sufficient as I wasn't sure how crazy to go with the data. I can always include some more though if required.
Thanks again for taking the time to help me with this.
August 5, 2010 at 10:13 am
Ok, here's what I came up with so far:
SELECT
RP.PERIODNAME,
[1.1] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.2] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'T' AND RIGHT(C.IRN, 2) = 'TA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.3] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'D' AND RIGHT(C.IRN, 2) = 'DA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.4] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PT' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.5] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'T' AND RIGHT(C.IRN, 2) = 'TM' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.6] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'D' AND RIGHT(C.IRN, 2) = 'DN' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.1] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'N' AND P.BASIS = 'N' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.2] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'N' AND P.BASIS = 'Y' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.3] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'K' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.4] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'D' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.5] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -400 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.6] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'N' AND P.BASIS = 'N' AND C.COUNTRYCODE = 'GB' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.7] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'N' AND P.BASIS = 'Y' AND C.COUNTRYCODE = 'GB' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.8] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'K' AND C.COUNTRYCODE = 'GB' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.9] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.CASECATEGORY = 'D' AND C.COUNTRYCODE = 'GB' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.10] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.COUNTRYCODE = 'GB' AND CE.EVENTNO = -400 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.11] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.COUNTRYCODE = 'PCT' AND CE.EVENTNO = -4 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.12] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.PROPERTYTYPE = 'P' AND C.COUNTRYCODE = 'PCT' AND CE.EVENTNO = -400 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.13] = COUNT(CASE WHEN C.CASETYPE = 'C' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -1036 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.14] = COUNT(CASE WHEN C.CASETYPE = 'B' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = 165 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[2.15] = COUNT(CASE WHEN C.CASETYPE IN ('B', 'C') AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -400 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[3.1] = COUNT(CASE WHEN C.CASETYPE = 'A' AND C.COUNTRYCODE = 'EP' AND CE.EVENTNO = -11402 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END)--,
FROM #CASES C
LEFT OUTER JOIN [#PROPERTY] P ON P.CASEID = C.CASEID
LEFT OUTER JOIN #CASEEVENT CE ON CE.CASEID = C.CASEID
INNER JOIN #CR_REPORTPERIODS RP ON CE.EVENTDATE >= RP.PERIODSTARTDATE AND CE.EVENTDATE < RP.PERIODENDDATE
WHERE C.CASETYPE IN ('A', 'B', 'C', 'F')
AND C.PROPERTYTYPE IN ('P', 'T', 'D')
AND RP.PERIODNAME IS NOT NULL
GROUP BY RP.PERIODNAME
I didn't code it all the way down to 8.3 but I'm sure you get the concept... 😉
Basically, I didn't use the view and also didn't do any pivoting in between. This allows to call a table just once instead of eight times. When you used a column like
MONTH(NV.[156DATE]), I changed it to CE.EVENTNO = 156 AND MONTH(CE.EVENTDATE).
I'm not sure what your expected output will look like based on your sample data, so please let me know if I'm clse enough. 😉
August 6, 2010 at 3:16 am
Chris-475469 (8/5/2010)
Sorry about the delay in replying but here is some sample data and the tables with only the fields I'm referencing:
...bunch of statements which runs with errors...Hopefully this is sufficient as I wasn't sure how crazy to go with the data. I can always include some more though if required.
Thanks again for taking the time to help me with this.
Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match those in your real tables, not those I made up. If you can't be bothered then it's unlikely you will get much more help. Show some effort and enthusiasm and folks will fall over themselves to help you. Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2010 at 3:41 am
Chris Morris-439714 (8/6/2010)
...Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match those in your real tables, not those I made up. If you can't be bothered then it's unlikely you will get much more help. Show some effort and enthusiasm and folks will fall over themselves to help you. Thanks.
The only error I found was the UNION ALL statement at the end of each last insert. Easy to fix. I think the solution I provided earlier is at least something to start with...
But in general you're right, sample data usually should be tested before posting.
August 6, 2010 at 3:50 am
LutzM (8/6/2010)
Chris Morris-439714 (8/6/2010)
...Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match those in your real tables, not those I made up. If you can't be bothered then it's unlikely you will get much more help. Show some effort and enthusiasm and folks will fall over themselves to help you. Thanks.
The only error I found was the UNION ALL statement at the end of each last insert. Easy to fix. I think the solution I provided earlier is at least something to start with...
But in general you're right, sample data usually should be tested before posting.
Hi Lutz, the column COUNTRYCODE is copied - erroneously - from my sample data, one of the values it should take is "PCT". It's not much work to correct the columntypes in the sample code I posted (and Chris copied) so that they match the actual column types, and it will in this case make a difference. I just don't have time right now to do it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply