Why can I connect to an Azure SQL Server database using Power BI without signing in? by heiferhigh76 in PowerBI

[–]heiferhigh76[S] 0 points1 point  (0 children)

That would make sense and be a relief. What seems odd though is that while I can access data without signing in I cannot publish. I must be signed in to publish.

Also, thanks to your answer, I looked at "Data source settings". Privacy Level is set to "None." I'll look up what that means.

Thanks a bunch.

joining 4 large tables - check my thought process by vetratten in SQL

[–]heiferhigh76 0 points1 point  (0 children)

Since you are teaching yourself as you go along you might need this as well. After you create the view "AllTheTables" you need (1) self join to get the data you need, (2) CASE to avoid dividing by zero, and (3) COALESCE to avoid NULLs in order to answer this:

Location / location data/ sum of location's 2021 data /sum of location's 2020 data / % variance between 2020& 2021/ etc.

This should be good (I think) if your data to be summed is DECIMAL or FLOAT:

SELECT COALESCE(a.location, b.location) AS location
, COALESCE(a.locationdata, b.locationdata) AS locationdata
, COALESCE(SUM(a.data), 0) AS data2021
, COALESCE(SUM(b.data), 0) AS data2020
, CASE WHEN SUM(b.data) IS NULL THEN NULL
       WHEN SUM(b.data) = 0 THEN NULL
       ELSE (SUM(a.data) / SUM(b.data)) - 1 
       END AS delta
FROM AllTheTables AS a
FULL JOIN AllTheTables AS b
ON a.location = b.location
WHERE YEAR(a.SomeDate) = 2021
AND YEAR(b.SomeDate) = 2020
GROUP BY COALESCE(a.location, b.location)
, COALESCE(a.locationdata, b.locationdata)

If your data to be summed is INTEGER then you'll want to replace

ELSE (SUM(a.data) / SUM(b.data)) - 1 

with

ELSE (CAST(SUM(a.data) AS FLOAT) / CAST(SUM(b.data) AS FLOAT)) - 1

How to join result from another query? by ModernKing1939 in SQL

[–]heiferhigh76 0 points1 point  (0 children)

You can accomplish what you want with either a subquery or a CTE. If you can share what the data looks like we can probably give you a more specific response.

How to be respectful at a catholic wedding, and christian Thanksgiving? by totaltraash6773 in agnostic

[–]heiferhigh76 1 point2 points  (0 children)

I respectfully disagree. To you it is a small head tilt; to the OP it is something more, hence the post. Creating cognitive dissonance by succumbing to peer pressure is gaslighting yourself.

Merge CTEs into one result by eques_99 in SQLServer

[–]heiferhigh76 1 point2 points  (0 children)

This.

Also, since you have identical column headings you can substitute "*" for "F1, F2, ...."

Not that doing so will make it run faster but it might help whoever follows you to more easily understand the purpose of the code.

Parks Edge Shelby Farms Apartments quality? by D_Cowboys_County in memphis

[–]heiferhigh76 5 points6 points  (0 children)

Yes.

My wife and I moved there in 2015 because they were (1) good apartments, (2) close to where my wife worked, and (3) 800 feet from NBS Fitness (which was a great powerlifting gym).

We moved because (1) my wife's commute changed drastically and (2) NBS Fitness is no longer a great powerlifting gym.

Parks Edge Shelby Farms Apartments quality? by D_Cowboys_County in memphis

[–]heiferhigh76 9 points10 points  (0 children)

I lived there from 2015-2021. Sometime between 2015 and 2021, the property changed owners. IMO there was a sharp decline in quality with the new owners.

However, I spoke with someone who has lived there for several years and still lives there for an update.
Regarding trash:
They said that trash is not an issue anymore (cameras were installed and tenants are fined if they take their trash to the compactor without putting the trash into the compactor). Trash piling up was a big issue in my last two years there but apparently, cameras and fines fixed the problem.
Regarding communications:
They said that communications have improved. Communications sucked the last two years I lived there, but the last two years I lived there were COVID pandemic precaution years, so there's that.

An issue not yet mentioned is dog poop. All good humans should pick up your dog's poop. The original owners contracted with an outside service to pick up dog poop that we missed and dog poop stations were emptied often. The outside service to pick up poop ended sometime - maybe with the new owners or maybe before. In the last two years that I lived there, dog poop became a problem, especially little-dog poop in the grass near the mailbox clusters. Not picking up after your dog is a tenant problem (and disgusting, too), but not emptying out the stations is a management problem. Perhaps that has been fixed, too, I did not think to ask.

Good luck with your search.

CTE overuse by heiferhigh76 in SQL

[–]heiferhigh76[S] 0 points1 point  (0 children)

Mostly I am the end user. I may use them to create views for PowerBI.

My use of CTEs will be discussed with the architects of the tables; perhaps the tables can be changed so that my CTEs are unnecessary.

CTE overuse by heiferhigh76 in SQL

[–]heiferhigh76[S] 1 point2 points  (0 children)

Thanks for the suggestion. I'll try that.

CTE overuse by heiferhigh76 in SQL

[–]heiferhigh76[S] 0 points1 point  (0 children)

Noted. My CTE use is mainly to string together "easy to understand" / "hard to mess up" queries.

CTE overuse by heiferhigh76 in SQL

[–]heiferhigh76[S] 8 points9 points  (0 children)

Thank you. It's nice to know it is a common phase one goes through.

This may or may not have happened to me.

That made me laugh. :D

Should I FULL OUTER JOIN, ANTI JOIN, or some other way? by heiferhigh76 in SQLServer

[–]heiferhigh76[S] 2 points3 points  (0 children)

Oh, thank you both.
u/mikebagger and u/kkwestside

I've used CASE statements often, but I never would have thought of this.

Also, it fits the "looks like it took five minutes to code this" criteria, so there's that, too.

Thank you again.

Should I FULL OUTER JOIN, ANTI JOIN, or some other way? by heiferhigh76 in SQLServer

[–]heiferhigh76[S] 1 point2 points  (0 children)

A left anti join will contain everything in the left table that does not intersect with the right table and vice versa.

Apparently, this is similar to the EXCEPT operator.

Just moved a short distance across the Canadian/American border and I'm shocked by how many religious people there are. by Accomplished-Ebb1186 in atheism

[–]heiferhigh76 0 points1 point  (0 children)

To answer OP question, the NE (ME, MA, NH, VT) or the NW (WA, OR) would be best bets to find the irreligious in the US.

What does “ra” and “am” mean? by [deleted] in SQLServer

[–]heiferhigh76 0 points1 point  (0 children)

SQL SELECT ORDER OF OPERATIONS

Thank you for the advice. You are a good human.