Sample SQL Code

Sample code used to create some tables for the missing report.

CREATE TABLE ConstosoProduct<br>(ProductID INT IDENTITY(1,1) NOT NULL CONSTRAINT ContosoProductPK PRIMARY KEY<br>, ProductLevel1 VARCHAR(100)<br>, ProductLevel2 VARCHAR(100)<br>, ProductLevel3 VARCHAR(100)<br>, ProductName VARCHAR(100)<br>)<br>GO<br>INSERT dbo.ConstosoProduct<br>&nbsp;&nbsp;&nbsp; (ProductLevel1<br>&nbsp; , ProductLevel2<br>&nbsp; , ProductLevel3<br>&nbsp; , ProductName)<br>&nbsp; VALUES<br>&nbsp; ( 'Audio', 'MP4&MP3', '', ''),<br>&nbsp; ( 'Audio', 'Recorder', '', ''),<br>&nbsp; ( 'Audio', 'Radio', '', ''),<br>&nbsp; ( 'Audio', 'Recording Pen', '', ''),<br>&nbsp; ( 'TV and Video', 'Televisions', '', ''),<br>&nbsp; ( 'TV and Video', 'VCD & DVD', '', ''),<br>&nbsp; ( 'TV and Video', 'Home Theater System', '', ''),<br>&nbsp; ( 'Computers', 'Laptops', '', 'Fabrikam Laptop19W M9800 Black'),<br>&nbsp; ( 'Computers', 'Desktops', '', 'Adventure Works Desktop PC2.30 MD230 Silver')
<div><br></div>CREATE TABLE UserSample<br>(&nbsp;&nbsp; UserName&nbsp;&nbsp; VARCHAR(100)<br>&nbsp; , OrgLevel1 VARCHAR(20)<br>&nbsp; , OrgLevel2 VARCHAR(20)<br>&nbsp; , OrgLevel3 VARCHAR(20));<br>GO<br><br>INSERT dbo.UserSample<br>&nbsp;&nbsp;&nbsp; (UserName<br>&nbsp; , OrgLevel1<br>&nbsp; , OrgLevel2<br>&nbsp; , OrgLevel3)<br>VALUES<br>&nbsp;&nbsp;&nbsp; ('User g', 'someOrg', 'other Org', null),<br>&nbsp;&nbsp;&nbsp; ('User B', 'someOrg', 'other Org', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User C', 'someOrg', '', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User D', '', 'other Org', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User E', '', 'other Org', ''),<br>&nbsp;&nbsp;&nbsp; ('User F', '', 'other Org', '')

CREATE TABLE [dbo].[ProductSample](<br>&nbsp;&nbsp;&nbsp; [ProductName] [varchar](100) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel1] [varchar](20) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel2] [varchar](20) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel3] [varchar](20) NULL<br>) ON [PRIMARY]<br>GO<br>INSERT INTO dbo.ProductSample<br>&nbsp;&nbsp;&nbsp; (ProductName<br>&nbsp; , ProductLevel1<br>&nbsp; , ProductLevel2<br>&nbsp; , ProductLevel3)<br>VALUES<br>( 'Product A', 'somecat', 'other cat', 'thirdcat' ), <br>( 'Product B', 'somecat', 'other cat', 'thirdcat' ), <br>( 'Product C', 'somecat', '', 'thirdcat' ), <br>( 'Product D', '', 'other cat', 'thirdcat' ), <br>( 'Product E', '', 'other cat', '' ), <br>( 'Product F', '', 'other cat', '' )<br>



Sign In or Register to comment.