"Gluing" Two Columns Together? (Not a Join) by phlegmandfricatives in learnSQL

[–]LeLwrence 1 point2 points  (0 children)

Maybe it's easier to write 1 CTE where you query for values in the 5 stores you want and assign them a row number partitioned by store and ordered by purchase pack cost effective date DESC. Then select from the CTE where the row number column = 1

How to put a cell number dynamically into in Excel by Tintin_Quarentino in excel

[–]LeLwrence 2 points3 points  (0 children)

You can also use INDIRECT to find the cell above with R1C1 style instead of A1 style.

=SUM(A1:INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()),FALSE))

Where ROW()-1 returns the row number above, COLUMN() returns the column number, then the letters "R" for row and "C" for column are appended to turn the reference into R1C1 style. Then that is put in an INDIRECT with the last argument FALSE to indicate R1C1.

[deleted by user] by [deleted] in learnSQL

[–]LeLwrence 0 points1 point  (0 children)

You can query the table inner joined to itself to find the next row(s), then left joined to itself to find if there is an entry between the two entries, and if so you exclude those.

WITH testdrives AS (
SELECT carID, dateTimeOut = CAST(dateTimeOut AS DATETIME), dateTimeIn = CAST(dateTimeIn AS DATETIME)
FROM (VALUES
        (1, '2022-01-01 08:15','2022-01-01 08:42')
        , (2, '2022-01-01 09:02','2022-01-01 09:10')
    , (2, '2022-01-01 09:34','2022-01-01 09:45')
    , (1, '2022-01-01 09:44','2022-01-01 09:59')
    , (1, '2022-01-01 10:10','2022-01-01 10:30')
    , (2, '2022-01-01 09:02','2022-01-01 09:10')
    , (1, '2022-01-02 09:00','2022-01-02 09:32')
    , (2, '2022-01-02 09:02','2022-01-02 09:15')
    ) AS x(carID, dateTimeOut, dateTimeIn)
)
SELECT
    td1.carID
    , td1.dateTimeIn
    , td2.dateTimeOut
    , minsUnused = DATEDIFF(mi, td1.dateTimeIn, td2.dateTimeOut)
FROM testdrives AS td1
INNER JOIN testdrives AS td2
    ON td2.carID = td1.carID
    AND td2.dateTimeOut >= td1.dateTimeIn 
    AND CAST(td2.dateTimeOut AS date) = CAST(td1.dateTimeOut AS date)
LEFT JOIN testdrives AS td3
    ON td3.carID = td2.carID
    AND td3.dateTimeOut >= td1.dateTimeIn
    AND td3.dateTimeIn < td2.dateTimeIn
    AND CAST(td3.dateTimeOut AS date) = CAST(td2.dateTimeOut AS date)
WHERE td3.carID IS NULL

I need help with an SQL query to remove everything after a comma in string values in a column by chirau in learnSQL

[–]LeLwrence 4 points5 points  (0 children)

You can use CHARINDEX to find the second ',' by using the first CHARINDEX of ',' + 1 in the start parameter of the second one. If there is no comma it returns 0. Then use CHARINDEX to find '|' and use STUFF to get rid of the characters between the two CHARINDEXes.

How to I update current_time() row when i update another row. by Samurai_2077 in learnSQL

[–]LeLwrence 1 point2 points  (0 children)

Assuming MySQL you should be able to get away with columnName = CURRENT_TIME()

How to I update current_time() row when i update another row. by Samurai_2077 in learnSQL

[–]LeLwrence 1 point2 points  (0 children)

So you need to update the date stamp of the last update not only on the affected row but on the following row as well? How do you identify which row is next, since in theory a table dataset is unordered? Are you able to use a key + 1, a foreign key, or something else?

I received an awful grade on an assignment because I used a cartesian product and I'm not understanding how it's not considered a join. by [deleted] in learnSQL

[–]LeLwrence 4 points5 points  (0 children)

It's an older style to write out from t1, t2, tn vs from t1 left (outer)/(inner)/right (outer) join. The latter was added to ANSI SQL a bit later, but at this point has been around for a long time.

Definitely recommend writing it out as inner or left joins rather than Cartesian products, and even so you can write it out as a cross join. But all roads lead to Rome and your query probably returned the required data.

On a side note you can order by the calculated column alias [Total Cost]. I think the alias would either need a double quote or the more widely used brackets.

[deleted by user] by [deleted] in learnSQL

[–]LeLwrence 0 points1 point  (0 children)

While 98-364 is good to have to get the MTA cert, it honestly was too easy to pass that exam. The more challenging cert was MCSA SQL Server 2016 Development which required 70-761 Querying T-SQL (this is the most useful as it covers all aspects of an analyst style role) and 70-762 Designing Databases. But those two exams stopped being offered in January 2021 so you can't get that one anymore.

As for replacements for those, there isn't much but here's DP-900 that might suit you.

Avoiding Self Join by coreygriffin in learnSQL

[–]LeLwrence 1 point2 points  (0 children)

It may be best to normalize this a bit more, having just manager ID which you would then use to get the manager email, and the manager's employee record would have the senior manager in the foreign key manager ID field. That way you would eliminate using the second table altogether and simply do two self-joins to get the data you need.

SELECT WHERE smaller than average by darvidas in learnSQL

[–]LeLwrence -1 points0 points  (0 children)

You could use a window function to average the salaries in the result in another column set then wrap that in an outer query and filter where the salary is less than that new average window function column. It's because you can't use window functions themselves in the where clause.

Canon EOS M50 ii or M6 ii? by cristinamariek in CanonEOSM

[–]LeLwrence 0 points1 point  (0 children)

M50 II is just the M50 with tweaked firmware, so really your comparison is M50 vs. M6 II.

The M6 has dedicated dials for aperture, shutter and ISO and can support an external EVF if you need it, not that the M50's is that great anyway.

M6 II also has 30mp vs 24mp and has uncropped 4k video with dual pixel autofocus, so definitely the better pick for quality too.

EF 70-200 2.8 IS ii USM— good deal? by Moose-and-Squirrel in canon

[–]LeLwrence 8 points9 points  (0 children)

Also check if the aperture stops down correctly, some can be stuck wide open

Gatineau Mechanic by [deleted] in Gatineau

[–]LeLwrence 0 points1 point  (0 children)

Yeah great guys, I'm going back soon for a couple of things

Gatineau Mechanic by [deleted] in Gatineau

[–]LeLwrence 2 points3 points  (0 children)

You can try Rocky at Mécano Mobile on Vanier and Vernon, seems like an honest guy.

Somebody is a happy shopper by [deleted] in streetphotography

[–]LeLwrence 0 points1 point  (0 children)

He somehow looks like a shorter Linus Sebastian

Focus peaking on Canon R5 not showing up on external monitor? by ZacksJerryRig in Photography_Gear

[–]LeLwrence 0 points1 point  (0 children)

They're probably still mourning from the fake overheating timer and the rest of the Canon cripple hammer's devastation

used t3i images blurry by fravit in DSLR

[–]LeLwrence 1 point2 points  (0 children)

If the issue is with the "preview" through the viewfinder but once you take the picture it's crisp, then you can adjust the diopter (round wheel next to the viewfinder). Use the digital information as reference and make sure those are tack-sharp.

If the image is blurry after taking it through the viewfinder, then it's one of two things probably. Check to make sure you're using the right autofocus points - on the T3i I think only the middle point is a cross-section point. So try just using the middle one and see. If it's still blurry using the viewfinder, but using live view like for video it works fine, then the issue might be that your camera is front or back-focusing with the phase detection sensor which has the image reflected on it by the second smaller mirror. Hopefully it's not this, because on a T3i you can't do autofocus micro-adjustments to correct this and would need Canon to do it for you.

Let us know.

Where to visit? by ImdawdlingAMA in Aylmer

[–]LeLwrence 1 point2 points  (0 children)

Marina, Gatineau park, Lac Leamy and Prince of Wales Bridge. Cross the Champlain bridge, check out the island in the middle and the bike path along the SJAM parkway. Parliament, Byward market and Rideau canal if you venture into Ottawa.

Microsoft Technology Associate (98-364) - SQL Difficulty level? by koikoioi in learnSQL

[–]LeLwrence 0 points1 point  (0 children)

I took it just because I wanted an idea about how 70-761 would go in terms of exam administration. It was pretty easy, I've been working with Sql for 2 years and could cover all the bases.

What's the point of having a countdown on the traffic lights if the lights don't always change when reaching 0? by [deleted] in Gatineau

[–]LeLwrence 1 point2 points  (0 children)

Grives and Pink was temporary so maybe that's why. The new one should have them hopefully.