exercise, Sql Server

exercise

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

CREATE TABLE dbo.company  
(
    company_id INT NOT NULL PRIMARY KEY,  
    company_name varchar(200),
	account_id INT NOT NULL, 
	account_name varchar(200)
);

CREATE TABLE dbo.nexus  
(
    company_id INT NOT NULL,
	JurisTypeId varchar(3) NULL,  
    state varchar(2) NULL,  
    country varchar(2) NULL,
	createdate DATETIME NOT NULL DEFAULT(GETDATE() ),
	constraint fk_company_id_N foreign key (company_id) references company (company_id)
);

CREATE TABLE dbo.document  
(
    createdate DATETIME NOT NULL DEFAULT(GETDATE() ),
	company_id INT NOT NULL, 
	--company_name varchar(200),
	state varchar(2) NULL, 
	country varchar(2) NULL,
	--JurisTypeId varchar(3) NULL,
	 constraint fk_company_id_D foreign key (company_id) references company (company_id)
);

INSERT INTO dbo.company VALUES
(1001, 'Google', 1, 'Alphabet'),
(1002, 'Fiber', 1, 'Alphabet'),
(1003, 'Verily', 1, 'Alphabet'),
(1004, 'Calico', 1, 'Alphabet'),
(1005, 'Bing', 2, 'Microsoft'),
(1006, 'Xbox', 2, 'Microsoft'),
(1007, 'MSN TV', 2, 'Microsoft'),
(1008, 'Nokia', 2, 'Microsoft'),
(1009, 'Skype', 2, 'Microsoft');

INSERT INTO dbo.nexus VALUES
(1001, 'CTY', 'MN', 'US', getdate()),
(1002, 'STA', 'IL', 'US', getdate()),
(1003, 'STA', 'NY', 'US', getdate()),
(1004, 'CIT', 'NC', 'US', getdate());

INSERT INTO dbo.document VALUES
(getdate(), 1001, 'OR', 'US'),
(getdate(), 1002, 'IL', 'US'),
(getdate(), 1003, 'NY', 'US'),
(getdate(), 1006, 'IL', 'US'),
(getdate(), 1008, 'IA', 'US'),
(getdate(), 1002, 'PA', 'US');

-- 1)	Companies who have recently (within last 180 days) have added a new nexus state (indicated by ‘STA’ in JurisTypeId)
SELECT C.company_name AS "Company Name"
FROM dbo.nexus N,
     dbo.company C
WHERE N.company_id = C.company_id
  AND JurisTypeId = 'STA'
  AND createdate >= DATEADD(DAY,-180, GETDATE());
  
-- 2)	AvaTax customers (by account_id) who have sales in states in the last 180 days where they do NOT have nexus  
SELECT DISTINCT C.account_id AS "AccountId"
FROM dbo.document D
JOIN dbo.company C ON C.company_id = D.company_id
WHERE EXISTS
    (SELECT *
     FROM dbo.nexus N
     WHERE JurisTypeId = 'STA'
       AND N.company_id = D.company_id
       AND N.state = D.state)
  AND D.createdate >= DATEADD(DAY,-180, GETDATE());


run \| edit \| history \| help	0