Each location with at least 3 different start dates.

SELECT loc
FROM (VALUES ('SAMSCLUB.COM','2021-12-05',42110)
,('SAMSCLUB.COM','2021-12-12',11731)
,('SAMSCLUB.COM','2021-12-19',9886)) d(loc,StartDate,Forecast)
GROUP BY loc
HAVING COUNT(DISTINCT(d.StartDate)) >= 3;

loc column will contain all values for which the predicate...

You'd evaluate "3 in a row" rule with something like

ThreeInARow = CASE WHEN columName = LAG([columnName],1) AND columName = LAG([columnName],2) THEN 1 ELSE 0 END

CREATE TABLE scripts are the scripts to create the tables that you have in your database. If you right-click on the table in the SQL Browser window, one of the...

• This reply was modified 3 years, 10 months ago by pietlinden.

You need to post CREATE TABLE and INSERT scripts so people who are trying to help you can work on your question and not guess about your table structures etc....

Look up STRING_AGG() function in Books Online.  It was added in SQL Server 2017.

So you're stuck with STUFF. Sorry.

You would have to use two separate queries to accomplish it if you replace the LEFT JOIN.

UPDATE r
SET r.HasChemical=CASE WHEN cm.partid IS NOT NULL THEN 'HasChemical' ELSE...

• This reply was modified 3 years, 10 months ago by pietlinden.

You would have to filter those out in the WHERE clause in the inner query.

SELECT FIRSTVALUE(…)

FROM (Select…WHERE elementID !=‘C’)

Disassemble the CTE into single queries and run them one at a time?

insert the intermediate results into a temporary table once you fix them and then query tempdb?

I would just create the proper tables (not munging all the data into a single table) and then put in fake data to test it. So maybe 3-4 records in...

I have a DB full of RACES and RUNNERS, COURSES, STATS etc etc. One horse could appear in the table dozens of times

I think you may be missing a table....

That's the weird thing about DAX / PowerBI... the reports are fully interactive. You can get data from a stored procedure or view that has no aggregates at all, and...

• This reply was modified 3 years, 10 months ago by pietlinden.
• This reply was modified 3 years, 10 months ago by pietlinden.

Except I'm not talking about normal SQL Server/T-SQL, except to get data out of SQL Server and into the data model. I'm talking about DAX. =)

Here are the measures for...

• This reply was modified 3 years, 10 months ago by pietlinden.

I was pointing that out to the OP... Maybe read a little of The Data Warehouse Toolkit to learn how to build star schemas might be in order. I didn't...

I think writing summary queries in T-SQL is the wrong way to go if you're consuming the data in PowerBI. It's fine to create views using T-SQL to populate your...

This is super handy!

I should use this to create a super simple PowerBI file and write all the measures there, so you can see how it works. The reason I...

• This reply was modified 3 years, 10 months ago by pietlinden.