You don't really need to store the duration at all. Well, unless you really want to for some reason. (I guess it's okay, because once a duration is calculated, it's...

Ouch! Not exactly a rookie question! Since you're brand new, I created a simple table...

use tempdb;
go

CREATE TABLE deviceActivity (
deviceID int not null,
EventName CHAR(3) not null,
EventTime datetime not...

Some consumable data (CREATE TABLE and INSERT scripts) would be really nice.

If the On/Off events are in pairs (so you can't two "On" events in a row for the same...

How are you using your data warehouse data? If you're using PowerBI or Excel, there's a USERELATIONSHIP() function, where you can have multiple relationships between two tables (like Sales fact...

For example? maybe check out Jeff Moden's article on Crosstabs

Your case statement is a bit off... try this:

use tempdb;
go

CREATE TABLE tibbleTransactions(TransactionID INT IDENTITY, TransactionDescription VARCHAR(10), Amount INT);
GO
INSERT INTO tibbleTransactions(TransactionDescription, Amount) VALUES ('Positive',10),('Negative',20),('Positive',20);

SELECT TransactionID, TransactionDescription, Amount
FROM tibbleTransactions;

SELECT...

Use a running aggregate before you pivot?

SELECT n
, rt = SUM(t.n) OVER (order by n rows between unbounded preceding and current row)
FROM Testdb.dbo.tally t;

tally is just a...

Understanding what the different tables describe helps a lot. You don't necessarily have to understand the entire diagram, just the part(s) you're going to use.

https://www.biinsight.com/how-to-share-a-sql-server-database-diagram-with-others/#:~:text=First%20of%20all%20you%20should%20install%20diagram%20support,Now%20you%20are%20able%20to%20create%20your%20diagrams.

Look at the database diagram?

Just saw a video from Chandoo (Excel MVP), right here.  Watch the next to last section - the biggest game changer in Excel in the last year is PowerQuery,...

Use sp_ForEachDB to loop over the DBs, and @@ROWCOUNT to collect the number of records affected/inserted?

I'd just create a counter variable and increment it after each insert. Then if you...

LEFT(SourceString,4) & FORMAT(RIGHT(SourceString,2),"000")

?

Thanks for the update. It's pretty much what I expected to have to do... use something (PowerBI, Excel) to generate the PowerQuery and then copy and paste.

What was the solution? I'd be interested to know, because I haven't used PowerQuery inside SSIS yet.

(Do you still have to write the PowerQuery in Excel or PowerBI and then...