none
Creating Closure rates By Months

    Question

  • Hi

    I have created two queries, One is for total Open cases and other is total closed cases. I want to get the closure rate by month and the formula is TotalClosed/TotalOpen*100

    TotalClosed

    SELECT 
    				Count(CaseReference) as TotalClosed,
    				DateName( month , DateAdd( month , mONTH(ch.CaseCompletionDate) , -1 ) ) AS 'Month Name',
    				Month(ch.CaseCompletionDate) as MonthNo
    			From CaseHeader CH
    					JOIN LookupItem LI2
    						ON LI2.LookupItemId = CH.CaseStateLKId
    						AND LI2.IsDeleted = 0
    					JOIN LookupItem LI3
    						ON LI3.LookupItemId = CH.CaseStageLKId	
    						AND LI3.IsDeleted = 0
    					JOIN CaseUser CU
    							ON CH.CaseHeaderId = CU.CaseHeaderId
    							AND CU.AssignedDate =
    												(SELECT		Max(CU.AssignedDate)
    												 FROM		CaseUser CU		
    												 WHERE		CU.IsDeleted = 0
    												 AND		CU.CaseHeaderId = CH.CaseHeaderId
    												 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    					JOIN UserDetail 
    							ON CU.UserDetailId = UserDetail.UserDetailId
    							AND CU.IsDeleted = 0	
    
    					where YEAR(ch.CaseCompletionDate) = 2013 -- selection by the user 
    					and ch.CaseCompletionDate is not null
    					and ch.isdeleted = 0
    					and LI3.ItemName in ('Screening', 'In the form required') Group by Month(CaseCompletionDate)

    Total Open

    SELECT 
    				COUNT(CaseReference) AS TotalOpen,
    				DateName( month , DateAdd( month , mONTH(ch.CaseCreationDate) , -1 ) ) AS 'Month Name',
    				Month(ch.CaseCreationDate) as MonthNo
    			From CaseHeader CH
    					JOIN LookupItem LI2
    						ON LI2.LookupItemId = CH.CaseStateLKId
    						AND LI2.IsDeleted = 0
    					JOIN LookupItem LI3
    						ON LI3.LookupItemId = CH.CaseStageLKId	
    						AND LI3.IsDeleted = 0
    					JOIN CaseUser CU
    							ON CH.CaseHeaderId = CU.CaseHeaderId
    							AND CU.AssignedDate =
    												(SELECT		Max(CU.AssignedDate)
    												 FROM		CaseUser CU		
    												 WHERE		CU.IsDeleted = 0
    												 AND		CU.CaseHeaderId = CH.CaseHeaderId
    												 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    					JOIN UserDetail 
    							ON CU.UserDetailId = UserDetail.UserDetailId
    							AND CU.IsDeleted = 0	
    
    					where YEAR(ch.CaseCreationDate) = 2013 -- selection by the user 
    					and ch.CaseCompletionDate is null
    					and ch.isdeleted = 0
    					and LI3.ItemName not like 'Restor%' Group by Month(ch.CaseCreationDate)

    Results

    How can I achive the following result using PIVOT?

    MonthName ClosureRate

    Jan 10%

    so on.

    Many thanks,

    MH


    MH

    Tuesday, November 26, 2013 12:19 PM

Answers

  •  Latheesh, I am getting the following result from your query, but why the closure rate is zero for all month


    MH

    You may try the below:

    ;With cte
    as
    (
    SELECT 
    				COUNT(Case when ch.CaseCompletionDate is null and LI3.ItemName not like 'Restor%' then CaseReference 
    				else NULL End ) AS TotalOpen,
    				Count(Case when ch.CaseCompletionDate is not null andLI3.ItemName in ('Screening', 'In the form required') then CaseReference 
    				else NULL End) as TotalClosed,
    				DateName( month , DateAdd( month , mONTH(ch.CaseCreationDate) , -1 ) ) AS 'Month Name',
    				Month(ch.CaseCreationDate) as MonthNo
    			From CaseHeader CH
    					JOIN LookupItem LI2
    						ON LI2.LookupItemId = CH.CaseStateLKId
    						AND LI2.IsDeleted = 0
    					JOIN LookupItem LI3
    						ON LI3.LookupItemId = CH.CaseStageLKId	
    						AND LI3.IsDeleted = 0
    					JOIN CaseUser CU
    							ON CH.CaseHeaderId = CU.CaseHeaderId
    							AND CU.AssignedDate =
    												(SELECT		Max(CU.AssignedDate)
    												 FROM		CaseUser CU		
    												 WHERE		CU.IsDeleted = 0
    												 AND		CU.CaseHeaderId = CH.CaseHeaderId
    												 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    					JOIN UserDetail 
    							ON CU.UserDetailId = UserDetail.UserDetailId
    							AND CU.IsDeleted = 0	
    
    					where YEAR(ch.CaseCreationDate) = 2013 -- selection by the user 
    					and ch.isdeleted = 0
    					Group by Month(ch.CaseCreationDate)
    )
    Select *,Cast(TotalClosed as float)/Cast(TotalOpen as float)*100 'ClosureRate' From cte Order by monthno asc


    • Edited by Latheesh NKMVP Tuesday, November 26, 2013 1:22 PM
    • Marked as answer by MustafaH Tuesday, November 26, 2013 1:44 PM
    Tuesday, November 26, 2013 1:21 PM

All replies

  • Try the below:(Not tested) Once you think the below is fetching records, you can use PIVOT on the CTE, but as per your desired output, you do not need PIVOT query.

    ;With cte
    as
    (
    SELECT 
    				COUNT(Case when ch.CaseCompletionDate is null and LI3.ItemName not like 'Restor%' then CaseReference 
    				else NULL End ) AS TotalOpen,
    				Count(Case when ch.CaseCompletionDate is not null andLI3.ItemName in ('Screening', 'In the form required') then CaseReference 
    				else NULL End) as TotalClosed,
    				DateName( month , DateAdd( month , mONTH(ch.CaseCreationDate) , -1 ) ) AS 'Month Name',
    				Month(ch.CaseCreationDate) as MonthNo
    			From CaseHeader CH
    					JOIN LookupItem LI2
    						ON LI2.LookupItemId = CH.CaseStateLKId
    						AND LI2.IsDeleted = 0
    					JOIN LookupItem LI3
    						ON LI3.LookupItemId = CH.CaseStageLKId	
    						AND LI3.IsDeleted = 0
    					JOIN CaseUser CU
    							ON CH.CaseHeaderId = CU.CaseHeaderId
    							AND CU.AssignedDate =
    												(SELECT		Max(CU.AssignedDate)
    												 FROM		CaseUser CU		
    												 WHERE		CU.IsDeleted = 0
    												 AND		CU.CaseHeaderId = CH.CaseHeaderId
    												 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    					JOIN UserDetail 
    							ON CU.UserDetailId = UserDetail.UserDetailId
    							AND CU.IsDeleted = 0	
    
    					where YEAR(ch.CaseCreationDate) = 2013 -- selection by the user 
    					and ch.isdeleted = 0
    					Group by Month(ch.CaseCreationDate)
    )
    Select *,TotalClosed/TotalOpen*100 'ClosureRate' From cte


    Tuesday, November 26, 2013 12:32 PM
  • Try this one (one query)

    SELECT 
    Count(case when LI3.ItemName in ('Screening', 'In the form required')   then CaseReference end) as TotalClosed,
    Count(case when LI3.ItemName not like 'Restor%'  then CaseReference end) as Totalopen,
    Count(case when LI3.ItemName in ('Screening', 'In the form required')   then CaseReference end)
    / Count(case when LI3.ItemName not like 'Restor%'  then CaseReference end) *100.0

    DateName( month , DateAdd( month , mONTH(ch.CaseCompletionDate) , -1 ) ) AS 'Month Name',
    Month(ch.CaseCompletionDate) as MonthNo
    From CaseHeader CH
    JOIN LookupItem LI2
    ON LI2.LookupItemId = CH.CaseStateLKId
    AND LI2.IsDeleted = 0
    JOIN LookupItem LI3
    ON LI3.LookupItemId = CH.CaseStageLKId
    AND LI3.IsDeleted = 0
    JOIN CaseUser CU
    ON CH.CaseHeaderId = CU.CaseHeaderId
    AND CU.AssignedDate =
    (SELECT Max(CU.AssignedDate)
    FROM CaseUser CU
    WHERE CU.IsDeleted = 0
    AND CU.CaseHeaderId = CH.CaseHeaderId
    AND CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    JOIN UserDetail 
    ON CU.UserDetailId = UserDetail.UserDetailId
    AND CU.IsDeleted = 0

    where YEAR(ch.CaseCompletionDate) = 2013 -- selection by the user 
    and ch.CaseCompletionDate is not null
    and ch.isdeleted = 0

    Group by Month(ch.CaseCreationDate)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, November 26, 2013 12:34 PM
  • Thanks for your prompt reply, the results are not correct By Uri, suggestions. Latheesh, I am getting the following result from your query, but why the closure rate is zero for all months? 


    MH

    Tuesday, November 26, 2013 1:17 PM
  • If you want us to provide a useful suggestion please provide a table structure + sample data + desired result. Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 26, 2013 1:21 PM
  •  Latheesh, I am getting the following result from your query, but why the closure rate is zero for all month


    MH

    You may try the below:

    ;With cte
    as
    (
    SELECT 
    				COUNT(Case when ch.CaseCompletionDate is null and LI3.ItemName not like 'Restor%' then CaseReference 
    				else NULL End ) AS TotalOpen,
    				Count(Case when ch.CaseCompletionDate is not null andLI3.ItemName in ('Screening', 'In the form required') then CaseReference 
    				else NULL End) as TotalClosed,
    				DateName( month , DateAdd( month , mONTH(ch.CaseCreationDate) , -1 ) ) AS 'Month Name',
    				Month(ch.CaseCreationDate) as MonthNo
    			From CaseHeader CH
    					JOIN LookupItem LI2
    						ON LI2.LookupItemId = CH.CaseStateLKId
    						AND LI2.IsDeleted = 0
    					JOIN LookupItem LI3
    						ON LI3.LookupItemId = CH.CaseStageLKId	
    						AND LI3.IsDeleted = 0
    					JOIN CaseUser CU
    							ON CH.CaseHeaderId = CU.CaseHeaderId
    							AND CU.AssignedDate =
    												(SELECT		Max(CU.AssignedDate)
    												 FROM		CaseUser CU		
    												 WHERE		CU.IsDeleted = 0
    												 AND		CU.CaseHeaderId = CH.CaseHeaderId
    												 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    					JOIN UserDetail 
    							ON CU.UserDetailId = UserDetail.UserDetailId
    							AND CU.IsDeleted = 0	
    
    					where YEAR(ch.CaseCreationDate) = 2013 -- selection by the user 
    					and ch.isdeleted = 0
    					Group by Month(ch.CaseCreationDate)
    )
    Select *,Cast(TotalClosed as float)/Cast(TotalOpen as float)*100 'ClosureRate' From cte Order by monthno asc


    • Edited by Latheesh NKMVP Tuesday, November 26, 2013 1:22 PM
    • Marked as answer by MustafaH Tuesday, November 26, 2013 1:44 PM
    Tuesday, November 26, 2013 1:21 PM
  • Thanks latheesh for your prompt reply it is fine now, If I want to convert to the pivot how would i do that?

    Month Name in Columns, Status (Open ,close ) in rows

    Jan Feb March

    Open 10 30 60

    Closed 20 50 30

    How can I achiev this result using pivot?

    Many thanks for your help.


    MH

    Tuesday, November 26, 2013 1:27 PM
  • Thanks Uri, I will do that. I am using SQL Server 2012.

    MH

    Tuesday, November 26, 2013 1:29 PM
  • Thanks latheesh for your prompt reply it is fine now, If I want to convert to the pivot how would i do that?

    Month Name in Columns, Status (Open ,close ) in rows

    Jan Feb March

    Open 10 30 60

    Closed 20 50 30

    How can I achiev this result using pivot?

    Many thanks for your help.


    MH

    10,30,60 looks like your computed value as Closed/Open*100. This is common for a month right? Then why do you need to PIVOT the result?

    BTB, if you want to PIVOT, replace the select with the below:

    Select [Jan],[Feb],[Mar],[Apr],[May],[Jun],
    [Jul],[Aug],[Sep],[Oct],[Nov],[Dec] From cte
    pivot (MAX (ClosureRate) for [month name] in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],
    [Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) as RateinMonths

    Tuesday, November 26, 2013 1:38 PM
  • Thanks Latheesh, I think Pivot option is not correct in this scenario. Many thanks for all your help. 

    MH

    Tuesday, November 26, 2013 1:44 PM
  • Hi Lateesh,

    I am just checking the data and found out that If I run my individual queries above I get different number for TotalClosed Cases. If I use CTE then I get totalclosed = 93 and if I run individual query i get 117 closed cases. 

    The reason I found because may be We are using casecreationdate in the where clause, am I right or there may be anyother reason?

     


    MH

    Tuesday, November 26, 2013 3:15 PM
  • Yeap. As we do not have DDL and DML with us, we will not completely test from our side. You may check the data and do the relevant changes. But you can use the approach to come up with a solution.
    Tuesday, November 26, 2013 4:04 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you have no idea and this is full of basic errors). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as  much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    The use of flags is assembly language programming and has no place in RDBMS. We have temporal data types in SQL, so we do not use string functions like Year() and Month(). A month by itself is useless; it is part of a date or a duration. 

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
      CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
      CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    It looks like you used a GUID for an identifier! That would be so awful; they are for external objects only. 

    The weirdest one is “case_role_type_lk_id” since it crammed so many attribute properties into one data element name. What is it? An identifier? A type? A role? The “_lk_” is completely wrong. A proper data element name tells uswhat it is by its nature, not how it is used in one particular table (i.e. a look-up value). Also, tables model sets, so they have to be plural or collective nouns. 

    When a table name appears twice or more in one query, it is a pretty good sign of bad SQL. In your code, we have that silly “LookupItem” (only one row according to your data element name!) table having both states and stages in it. Look at the code: 

     INNER JOIN Lookup_Item AS LI2 
     ON LI2.Lookup_Item_Id = CH.Case_State_LK_Id 
       AND LI2.Is_Deleted_flg = 0 --assembly language !
     INNER JOIN Lookup_Item AS LI3
       ON LI3.Lookup_Item_Id = CH.Case_Stage_LK_Id 
        AND LI3.Is_Deleted_flg = 0

    This is called a “Automobiles, Squids and Lady Gaga” table. It is not how to write SQL. A table is a set. A set is made up of one and only one kind of entity. This is usually covered in grade school when they teach naive set theory to kids. 

    If you want to really get help, then please post the DDL so we can re-do this correctly. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 26, 2013 8:30 PM
  • Hi Celko,

    Thanks for your comments. It sounds very harsh. Here is DDL for case header.

    CREATE TABLE [dbo].[CaseHeader](
    	[AcceptedInFormRequiredDate] [datetime] NULL,
    	[AllegationSummaryDraft] [nvarchar](max) NULL,
    	[CaseCompletionDate] [datetime] NULL,
    	[CaseCreationDate] [datetime] NOT NULL,
    	[CaseDescription] [nvarchar](max) NULL,
    	[CaseHeaderId] [uniqueidentifier] NOT NULL,
    	[CaseMetaDataXml] [xml] NULL,
    	[CaseName] [nvarchar](100) NOT NULL,
    	[CasePriorityLKId] [uniqueidentifier] NULL,
    	[CaseReference] [nvarchar](128) NOT NULL,
    	[CaseRestrictionLKId] [uniqueidentifier] NULL,
    	[CaseSpecialityLKSId] [uniqueidentifier] NOT NULL,
    	[CaseStageLKId] [uniqueidentifier] NULL,
    	[CaseStateLKId] [uniqueidentifier] NOT NULL,
    	[CaseTypeId] [uniqueidentifier] NOT NULL,
    	[ComplexityLKId] [uniqueidentifier] NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[CreatedUser] [nvarchar](50) NOT NULL,
    	[ExternalReference] [nvarchar](128) NOT NULL,
    	[IncidentEndDate] [datetime] NULL,
    	[IncidentStartDate] [datetime] NULL,
    	[IsAppliedLastTwelveMonths] [bit] NULL,
    	[IsConsentToDisclosure] [bit] NULL,
    	[IsDeleted] [bit] NOT NULL,
    	[IsFirstReferee] [bit] NULL,
    	[IsHasAppealed] [bit] NULL,
    	[IsInFormRequired] [bit] NULL,
    	[IsNewRulesApplication] [bit] NULL,
    	[IsPNCCheck] [bit] NULL,
    	[IsSecondReferee] [bit] NULL,
    	[IsStruckOffInLastFiveYears] [bit] NULL,
    	[ModifiedDate] [datetime] NOT NULL,
    	[ModifiedUser] [nvarchar](50) NOT NULL,
    	[ReceivedDate] [datetime] NULL,
    	[Recommendation] [nvarchar](256) NULL,
    	[ReferralCountryLKId] [uniqueidentifier] NULL,
    	[ReferralTypeLKSId] [uniqueidentifier] NOT NULL,
    	[ReferrerTypeLKSId] [uniqueidentifier] NOT NULL,
    	[ResponsibleTeamId] [uniqueidentifier] NOT NULL,
    	[RowVer] [int] NOT NULL,
    	[IsCaseClosing] [bit] NULL,
    	[IsReadOnly] [bit] NULL,
    	[IsAutoBarOffence] [bit] NULL,
    	[IsHighProfile] [bit] NULL,
     CONSTRAINT [PK_CaseHeader] PRIMARY KEY CLUSTERED 
    (
    	[CaseHeaderId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
     CONSTRAINT [IX_CaseHeader_CaseReference] UNIQUE NONCLUSTERED 
    (
    	[CaseReference] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[CaseHeader] ADD  DEFAULT (newid()) FOR [CaseHeaderId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_CoreCaseHeader_CasePriorityLKId] FOREIGN KEY([CasePriorityLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_CoreCaseHeader_CasePriorityLKId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_CoreCaseHeader_CaseRestrictionLKId] FOREIGN KEY([CaseRestrictionLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_CoreCaseHeader_CaseRestrictionLKId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_CoreCaseHeader_CaseStateLKId] FOREIGN KEY([CaseStateLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_CoreCaseHeader_CaseStateLKId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_CoreCaseHeader_CaseTypeId] FOREIGN KEY([CaseTypeId])
    REFERENCES [dbo].[CaseType] ([CaseTypeId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_CoreCaseHeader_CaseTypeId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_CoreCaseHeader_ResponsibleTeamId] FOREIGN KEY([ResponsibleTeamId])
    REFERENCES [dbo].[Team] ([TeamId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_CoreCaseHeader_ResponsibleTeamId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_NMCCaseHeader_CaseStageLKId] FOREIGN KEY([CaseStageLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_NMCCaseHeader_CaseStageLKId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_NMCCaseHeader_ComplexityLKId] FOREIGN KEY([ComplexityLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_NMCCaseHeader_ComplexityLKId]
    GO
    
    ALTER TABLE [dbo].[CaseHeader]  WITH CHECK ADD  CONSTRAINT [FK_NMCCaseHeader_ReferralCountryLKId] FOREIGN KEY([ReferralCountryLKId])
    REFERENCES [dbo].[LookupItem] ([LookupItemId])
    GO
    
    ALTER TABLE [dbo].[CaseHeader] CHECK CONSTRAINT [FK_NMCCaseHeader_ReferralCountryLKId]
    GO
    

    Here is a Lookup Item table

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[LookupItem](
    	[CategoryId] [uniqueidentifier] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL,
    	[CreatedUser] [nvarchar](50) NOT NULL,
    	[IsDeleted] [bit] NOT NULL,
    	[IsObsolete] [bit] NOT NULL,
    	[IsSystemDefined] [bit] NOT NULL,
    	[ItemCode] [nvarchar](4) NOT NULL,
    	[ItemDesc] [nvarchar](256) NULL,
    	[ItemName] [nvarchar](100) NOT NULL,
    	[ItemOrder] [bigint] NOT NULL,
    	[LookupItemId] [uniqueidentifier] NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
    	[ModifiedUser] [nvarchar](50) NOT NULL,
    	[RowVer] [int] NOT NULL,
     CONSTRAINT [PK_LookupItem] PRIMARY KEY CLUSTERED 
    (
    	[LookupItemId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
     CONSTRAINT [IX_LookupItem_ItemCode] UNIQUE NONCLUSTERED 
    (
    	[ItemCode] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[LookupItem] ADD  DEFAULT (newid()) FOR [LookupItemId]
    GO
    
    ALTER TABLE [dbo].[LookupItem]  WITH CHECK ADD  CONSTRAINT [FK_LookupItem_CategoryId] FOREIGN KEY([CategoryId])
    REFERENCES [dbo].[LookupCategory] ([LookupCategoryId])
    GO
    
    ALTER TABLE [dbo].[LookupItem] CHECK CONSTRAINT [FK_LookupItem_CategoryId]
    GO
    
    


    MH

    Wednesday, November 27, 2013 9:59 AM