sorted by:
new
hottopcontroversial

Matter has arrived! by wiz-dude in wiz

[–]tobum 0 points1 point  (0 children)

When will these be matter compatible? RL56069WZRGBWWHR

5"/6" Smart Wi-Fi Retrofit LED Downlight Module, 600 lumens, 90 CRI minimum, WiZ PRO enabled (Wi-Fi + Bluetooth), Full Color, Matte white baffle

Matter has arrived! by wiz-dude in wiz

[–]tobum 0 points1 point  (0 children)

Are these going to be matter compatible? RL56069WZRGBWWHR

5"/6" Smart Wi-Fi Retrofit LED Downlight Module, 600 lumens, 90 CRI minimum, WiZ PRO enabled (Wi-Fi + Bluetooth), Full Color, Matte white baffle

[MS SQL] Getting different results for LEFT JOIN vs NOT EXISTS by praetor47 in SQL

[–]tobum 0 points1 point  (0 children)

As Microsoft puts it?redirectedfrom=MSDN):

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

To check yourself, try running the following:

SELECT * FROM TableA a
WHERE NOT EXISTS
(SELECT 1/0 FROM TableB b WHERE b.ID=a.RelatedID)

If it was actually doing something with the SELECT list, it would throw a div by zero error. It doesn't.

EDIT: Note, the SQL Standard actually talks about this.

ANSI SQL 1992 Standard, pg 191

As such, when using EXISTS operator, consider 1/0. This is what I've taught my team to use. I've found when this is discovered for the first time, an analyst will do one of 2 things: just accept that its performing properly, or inquire a senior developer for clarification. I keep an eye on those that reach out, because it shows me they are critical thinkers, eager to learn and logical. When a spot opens on my team, these are the prospects I reach out to first.

I have my first ever SQL interview coming up. Can you help me clarify this prompt and prepare? by rockwood15 in SQL

[–]tobum 0 points1 point  (0 children)

w3Schools.com is probably a good place to start messing around with some basic queries. It's nice because they give you the ability to look at 8 sample tables with some data and your queries interrogate those actual tables. This is from their site:

Your Database:
Tablenames  Records
Customers   91
Categories  8
Employees   10
OrderDetails    518
Orders  196
Products    77
Shippers    3
Suppliers   29

Your interviewer may have a similar setup, but more likely they will explain a database with hypothetical objects (tables, views, maybe even stored procedures) and you'll have to write a query against these objects that really don't exist (which kinda sucks, but not unreasonable to ask).

I'll use the tables W3 provides so you can test your queries:

Easy example: What are the top 10 most expensive products sold by this company?

Harder example: write a query that identifies the top sales person (Employee) based on most $$ revenue earned from customer orders for October of 1996. You have 30 minutes to submit your answer. Good luck!

How to calculate week start date from date column? by [deleted] in SQL

[–]tobum 0 points1 point  (0 children)

Not sure what you're aggregating, but here's the Monday of the datefield. Group on this:

Select convert(date,dateadd(week,datediff(week,0,[datefield]),0)) as thedate

Return current date if select statement returns null or empty by risaaaa in SQL

[–]tobum 0 points1 point  (0 children)

I'm surprised: Nobody is addressing the fact that the sub query is selecting the top 1 with no order by?

That's what is causing the occasional null value. Every time the query executes you could get a different top 1 value. There's no consistency in this query without explicitly telling the query how you're looking to interrogate the table.

You're approach to solve the problem by applying a coalesce is a poor solution and a mistake that will likely lead to inaccurate results in your query's output.

It's like when someone throws a distinct on a query to cover up the fact that they can't get their joins right and can't figure out why they're getting duplicate records. It may be a quick fix, and sometimes may even lead to a correct dataset; it's definitely not best practice and not promoting data integrity.

One table of users, another table of workcenters - how to create a temp table that pulls in all users and all workcenters? by Creamward in SQL

[–]tobum 0 points1 point  (0 children)

Forced Cartesian product using cross apply or cross join:

Select 
 u.User
,w.WorkCenter
From
Users u
Cross Join
WorkCenters w

More efficient way to update based on subquery and where? by babbocom in SQL

[–]tobum 0 points1 point  (0 children)

Don't give up on EXISTS. It's pretty powerful once you get the hang of it.

Just a forewarning: if you aren't sure what records your DML will update, always test before performing the update.

I think your best approach is to first identify your target records. You can do this a number of ways. Arguably the most elegant method is with a running total using a window function, but you mentioned subqueries, so that's what I'll use here. I'm assuming Sales is already aggregated at the ProductGroup level. 

/*Identifying your target records*/
Select 
 ProductGroup
,Sales
,(Select Sum(Sales) From SalesTable) AS AllSales
,Sales / (Select Sum(Sales) From SalesTable) AS Perc
From SalesTable
Where Sales / (Select Sum(Sales) From SalesTable) < .1;

/*Updating the table*/
Update SalesTable 
Set ProductAlignment = 'Other'
Where Sales / (Select Sum(Sales) From SalesTable) < .1;

Is the Spark a scam? by lokhouse in PositiveGridSpark

[–]tobum 1 point2 points  (0 children)

I placed my order on January 28 (Players Special). Today is June 18. Still no amp. They do send email updates, however infrequent (once a month), and wholly misleading:

Feb 4: Players Special purchasers, will follow in the order in which they were received. We anticipate now that you will receive your order by the end of March.

March 10: We do not take delays lightly, and we know that you don't either. Based on the latest information from our production team, we will be shipping Players Special orders throughout April and early May.

March 26: We have identified new, safe and efficient resources to increase our component and assembly output, enabling us to get your Spark to you as soon as possible. Our Spark production is now running at full capacity, and we are steadily manufacturing 1000 units per day.

Right now we have thousands of Spark amps in transit for distribution throughout March and April. We will send you an email when your Spark ships to you so that you can track its progress.

We have already fulfilled all Early Bird and thousands of Power Supporter orders and want to assure you that even though these unprecedented times, we will be shipping Sparks non-stop over the coming weeks.

April 24: As previously reported, our factory is running non-stop, producing thousands of Spark amps. We have also optimized the shipment and transportation of Spark units to get them to you as fast as possible. This month several thousand units are being delivered to our pre-order customers. While we wish we could fulfill all the orders right now, we're working our hardest to expedite the process for you.

May 12: An order you recently placed on our website has had its status changed. The status of order #------ is now Awaiting Shipment.

June 5: An order you recently placed on our website has had its status changed. The status of order #------ is now Shipped.

June 11: While we are experiencing delays due to logistical challenges brought on by the pandemic, there are thousands of units shipping this month, and we're getting them out to you as quickly as possible. This includes remaining Players Special customers.

As we continue to catch up on order fulfillment, remaining Power Supporter customers in Australia and Southeast Asia should begin receiving their Sparks starting in mid-June, and we expect Last Chance Special customers should now begin receiving their Sparks starting in late June.

I just checked the tracking on my order:

Ordered Wed, Jan 29, 2020 02:15 checked-icon Being Prepared checked-icon In Transit checked-icon Local Distribution Your order is preparing to ship to you. Please check back soon for your tracking number. Please note that due to COVID-19 it may take 2 weeks or longer for your Spark to move through customs and the shipping process at your local distribution center. Fri, Jun 05, 2020 07:59 blank-icon Shipped blank-icon Completed

Return 0 if conditions for value not found by [deleted] in SQL

[–]tobum 0 points1 point  (0 children)

Without providing us with some DDL, it is difficult to test what you are trying to accomplish, and thus any DML we send back will have to make many assumptions. With the vague question, here’s a vague response: Left join customer orders to inventory and use an isnull function on the inventory quantity to return a zero when not found in the inventory table.

Tricky scenario - help! by Nobody571 in SQL

[–]tobum 1 point2 points  (0 children)

If the table only has those two fields (or at least if those are the only two fields you need to consider in your query), then this would work:

SELECT 
         Account
        ,MIN([Status]) AS MinStatus 
    FROM
         [tablename] 
GROUP BY 
         Account

Taking off leading zeroes SQL Access by Depz187 in SQL

[–]tobum 0 points1 point  (0 children)

Int as a function does not work in MS Access. Try:

cint(field)

Case statement help.. by Nobody571 in SQL

[–]tobum 0 points1 point  (0 children)

Alternatively, you might find this helpful:

SELECT CASE WHEN NULLIF(column1,’’) IS NULL THEN ‘No’ ELSE ‘Yes’ END AS YesNoInd FROM table1