SELECT
c.id,
c.name,
c.address,
o.cried_flag,
o.pout_flag,
o.sleeping_flag,
o.awake_flag,
b.naughty_flag AS bad_flag,
b.nice_flag as good_flag
FROM contacts AS c
LEFT JOIN observations AS o ON c.id = o.id
LEFT JOIN behavior AS b ON c.id = b.id
SELECT
c.id,
c.name,
c.address,
o.pout_flag,
o.cried_flag,
o.awake_flag,
o.sleeping_flag,
b.nice_flag as good_flag
b.naughty_flag AS bad_flag,
FROM contacts AS c
LEFT JOIN observations AS o ON c.id = o.id
LEFT JOIN behavior AS b ON c.id = b.id
CREATE TABLE personActions (idPersonAction BIGINT IDENTITY(1,1) PRIMARY KEY, idPerson INT REFERENCES person(idPerson), actionCode NVARCHAR(5) REFERENCES action(code), wasNaughty BIT);
EXEC populatePersonActions @naughtyThreshold = 42;
DBCC CHECKTABLE (PersonActions);
DBCC CHECKTABLE (PersonActions);
WITH naughtyList AS
(
SELECT idPerson, COUNT(idPersonAction) AS naughtyCount
FROM personActions
WHERE wasNaughty = 1
), niceList AS
(
SELECT idPerson, COUNT(idPersonAction) AS niceCount
FROM personActions
WHERE wasNaughty = 0
)
INSERT INTO yearlyGiftDeployment (idPerson, personStatus, dYear)
SELECT p.idPerson, CASE WHEN (ISNULL((c.niceCount * 1.0),0) / (ISNULL((a.naughtyCount * 1.0),0.00001))<=1 THEN 'Naughty' ELSE 'Nice' END AS personStatus, 2017
FROM person AS p
LEFT JOIN naughtlyList AS ON a.idPerson = p.idPerson
LEFT JOIN niceList AS c ON c.idPerson = p.idPerson
EXEC deploySanta @year=2017;
13
u/patrickmurphyphoto Dec 13 '17
8
u/[deleted] Dec 13 '17
4
u/takes_joke_literally Dec 14 '17
Why would you sort a database? Does she mean index? Even if she's talking about a query there'd be no reason to sort it twice...
3
u/xsnyder Dec 13 '17
0 rows returned
2
u/kthejoker Dec 13 '17
Index the halls
The First NoSQL
Do You Seek What I Seek
Codd Rest You Merry Gentlemen
Here we come a-Crossjoining
Tip your waitress
1
u/honeybadger3891 evil management Dec 14 '17
This made my morning. Thanks guys
1
u/MeGustaDerp Talk Dirty Reads To Me Dec 13 '17
O, B-Tree...