FWIW:

I forced the CAST on the text date, so in the outer query, the date values are real dates, so I can order and filter them.

CREATE TABLE...

If you look up CURSOR on the MSFT site, you'll find examples.

Once you open a cursor (a recordset in Access-speak), you have to loop through it, and once inside the...

Could you not use SSIS to do the import and then delete the files afterward? (or move them to some other directory)

Odd, CONVERT didn't work for me. So I tried it with CAST

CREATE TABLE textDates(textDate char(10) not null);
go

INSERT INTO textDates(textdate) values ('10/01/2022'),('09/02/2022'),('01/03/2024');

SELECT *
FROM (
SELECT textDate, testDate = CAST(textDate...

• This reply was modified 1 years, 8 months ago by pietlinden.

You can download any of the MSFT databases (AdventureWorks etc) from Github.

See this article: https://learn.microsoft.com/en-us/sql/samples/sql-samples-where-are?view=sql-server-ver16

I'd go the backup file (.bak) route. Super easy.

A thousand years ago, I tried doing this in SSIS, but the PowerQuery source works like... well, not at all. =(

Here's the beginning of the conversation: SSIS task that...

• This reply was modified 1 years, 8 months ago by pietlinden.

I was basically just playing with DAX Studio when I discovered it could export the result of my PowerQuery.

I suppose I could just copy and paste my PowerQuery into Excel...

Okay, just a quick update... I got the PowerQuery working (Text.Combine in PowerQuery is my new best friend!!!) Here's the PowerQuery:

let
    Source = Folder.Files("C:\Users\User\OneDrive\Documents"),
...

Sounds like a hideous design.

Why not just put all the data in the same database with a foreign key column to the server name? Then you don't need any dynamic...

Okay. The other way I was thinking of doing it was to write a query to find changed prices each week (since he only sells cnce a week) and then...

To quote Gene Wilder in "Charlie and the Chocolate Factory", "Wait a minute, Strike that! Reverse it! Thank you."

The trick is to avoid SSIS altogether. If you have DAX Studio...

• This reply was modified 1 years, 9 months ago by pietlinden.
• This reply was modified 1 years, 9 months ago by pietlinden.

Martin,

I'm trying to work out options. Would you use something like a system-versioned table to handle the ingredient slowly changing dimension? Or can I just use a few fairly simple...

Sounds like you're missing the other table, Student.

Student(StudentID (PK),...)

StudentTest(StudentID (PK1), TestID (PK2), TestScore)

Test(TestID (PK), TestDate...)

if you allow nulls in StudentTest(Score), then you can just cross join a filtered view of...

I'm trying to simulate price changes of some of my ingredients over time. (Not really necessary to change the prices of more than just some of the most commonly used...

Thanks Steve! Like I said, I felt like I was missing something that should have been blatantly obvious! Thanks for clarifying!

So this is my plan (after staring at this for...