Hello and welcome again to SQL Poetry Friday. I sadly do not have a code poem today, and it turns out that the poetry is suitable to be sung to America's classic hit "A Horse with No Name". It goes like this:
Well I've built me a table
With no column names,
where results never
came out the same.
In the sandbox, they won't
remember your name
for you're not in production
to cause them all pain...
Please come back next week for another exciting parlance into prose.
Woof woof!
Jason
I'm building a "dashboard" which counts up objects in a time period. It happens that the objects have parts (that is to say 'part A', 'part B', etc.) that reside in a separate table. When I would join the parts table into the object table, all of the sudden my count for each object would increase, totally blowing my mind! Using Count(DISTINCT object) brought me back to my original counts and my brain was saved from the pounding of frustration.
Here’s to you, Count(DISTINCT), a real database hero!
Woof woof!
Jason
SELECT
c.name AS 'When I Am'
FROM sys.tables t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECTID
WHERE c.name LIKE 'l%'
CREATE TABLE #TMP (ID INT, ADAY VARCHAR(20))
INSERT INTO #TMP(ID, ADAY)
VALUES (1, 'Sunday'),
(2, 'Monday'),
(3, 'Tuesday'),
(4, 'Wednesday'),
(5, 'Thursday'),
(6, 'Friday'),
(7, 'Saturday')
SELECT
ADAY
FROM #TMP
ORDER BY ID
DROP TABLE #TMP
Mondays are my day to try and expound or extol or ex-something to shed more light on SQL and it’s pros and cons. This may be a tougher situation to tackle on a Monday than a sum up of the week, as my weekends are usually free of SQL. I will say this, however. I’ve been listening to a LOT of books (I listen to audiobooks as I hardly ever set any time apart to read, so listening is how I have to feed the rest of my head), and many of the business books I’ve been pushing through have talked about forming strategies and then sticking to them. I’ve been trying to apply this to query and report building, as I have always tended to more of a shotgun approach rather than a rifle; a scattered, hope something sticks pursuit rather than a narrow, step by step. It’s a detriment that I’ve been working on probably for the last 12 months. Agile development practices have helped me get started, especially giving myself a 3 point to do list every workday to push towards, but I really need to put something into templates or systemization of reports. I’ll keep you posted on how this continues to develop, as it is a process I think we all work towards (or at least us hounds who doggedly pursue bigger better things). Keep digging until that bone is firmly in teeth, my fellow hounds!
Woof woof,
Jason
CREATE TABLE #poem
(ID int, WORDS VARCHAR(20))
GO
INSERT INTO #poem
(ID, WORDS)
VALUES
(1, 'Chase'),
(2, 'Fall'),
(3, 'Crumple'),
(4, 'Blow'),
(5, 'Pile'),
(6, 'Whirl'),
(7, 'Dance'),
(8, 'Drift'),
(9, 'Scatter'),
(10, 'Play'),
(11, 'Burn'),
(12, 'Roll')
GO
DECLARE @Random INT;
DECLARE @Random2 INT;
DECLARE @Random3 INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 1
SET @Upper = 12
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT
WORDS AS LeafActions
FROM #poem
WHERE ID = @Random
UNION
SELECT
WORDS
FROM #poem
WHERE ID = @Random2
UNION
SELECT
WORDS
FROM #poem
WHERE ID = @Random3
DROP TABLE #poem
="Total Zip Codes: " & RunningValue(Fields!PKUPZIP.Value, countdistinct, "table1_County")
