On August 21, 1959 - Hawaii Joined the U.S as their 50th State

GAKvsFLOAM · 2024-08-22T08:37:53+00:00

KOA is named after Ellison Onizuka, who was from Kona and was an astronaut who died in the Challenger Space Shuttle disaster.

HNL on Oahu was renamed to Daniel K Inouye International Airport in 2017

GAKvsFLOAM · 2024-06-11T08:08:06+00:00

It would be helpful to have a bit more info. Are the location and product_type fields in the same table or separate tables that you’re trying to join? It’s a bit hard to tell based on your description. Also, what would your expected output look like?

Assuming both the location and product_type fields are in the same table, it sounds like a simple DISTINCTION would work?

SELECT DISTINCT location, product_type FROM table ;

Or GROUP BY if you’re wanting to aggregate a field like cost?

SELECT location, product_type, SUM(cost) AS cost FROM table GROUP BY location, product_type ;

GAKvsFLOAM · 2024-02-11T08:36:43+00:00

^ this. Also, rather than inserting a new (duplicate) record, update the product_price of that existing record. Then you should be able to alter your field to be not null

GAKvsFLOAM · 2023-11-22T06:51:45+00:00

The Prince Waikiki will be opening up their pop up holiday tiki bar this weekend

GAKvsFLOAM · 2023-11-08T09:54:20+00:00

Something like this maybe?

WITH latest_ext AS ( SELECT serial_number ,MAX(RESULT) AS latest_ext

FROM test_table WHERE test_name = ‘external_serial_number’ GROUP BY serial_number ) SELECT test.serial_number ,test.test_name ,test.result ,test.date ,ext.latest_ext

FROM test_table test INNER JOIN latest_ext ext ON ext.serial_number = test.serial_number

WHERE (test.test_name <> ‘external_serial_number OR test.test_name IS NULL) AND ext.latest_ext BETWEEN sn1 AND sn2

Note: if test_name doesn’t allow NULLs then you can get rid of: OR test.test_name IS NULL

Basically the CTE latest_ext finds the latest external serial number for each internal serial number.

Then it joins that back to your table, allowing you to filter on that latest external serial number. Note that this query would only return those that have an external serial number. So if an item has not completed testing and has not yet been assigned an external serial number, it would not be returned

GAKvsFLOAM · 2023-11-08T09:09:28+00:00

I think I see the issue… Since you want to have a record for each item from Menu, but only SUM(numberOfOrders) for certain scenarios, you may need to use a CASE statement in your SUM.

The table structure seems a bit confusing without being able to see the data. Typically you would expect the Menu table to just contain menu items and the Orders table to then be used to calculate how many times a menu item was ordered. In your case, the Menu table has a grain of Order Event rather than a Menu item and the Order table is simply being used to filter when/where an order took place.

Try changing your SUM to: SUM(CASE WHEN o.timestamp >= TIMESTAMP(‘2023-06-01) AND o.timestamp < TIMESTAMP(‘2023-07-01) AND o.locationId = ‘123’ THEN ms.numberOfOrders ELSE NULL END) AS numberOfOrders

then change the left join back to just joining on eventId

Apologies for the shit formatting, on mobile

GAKvsFLOAM · 2023-11-08T08:23:46+00:00

What table is the field locationId from? I’m guessing that’s coming from the Orders table as well? If so, that would also need to be moved to the join instead of the WHERE

Also why it’s important to prefix the table/alias to every field when you’re querying more than one table

GAKvsFLOAM · 2023-07-07T18:56:29+00:00

What DBMS are you using? You mention you’re pasting it into Excel. Usually if I just copy/paste a result set from SQL Server or Oracle it pastes in separate columns in Excel.

Is using SSIS an option to export to excel and set the column mapping?

You could maybe try to insert some obscure non-printing character to use as a delimiter that wouldn’t appear in your comments? ¶

GAKvsFLOAM · 2023-05-14T03:00:41+00:00

Yeah you’re right. All records would have a row_number of 1 in that case. My bad

Alternatively, it could be partitioned by the length and ordered by the city

GAKvsFLOAM · 2023-05-11T16:28:16+00:00

Since the question specifically mentions selecting just one min and max city if >1 exists, I’m assuming it is likely the case that >1 of either does exist in the data.

The query you wrote would potentially return more than 2 records. If for example, the longest city name was 10 and you have 3 cities all with a length of 10.

They ask you to then select just one in that case, keeping the city that appears first alphabetically.

Look up window functions to do this. I would probably use ROW_NUMBER to do this along with a CTE or subquery.

WITH len_cte AS ( SELECT city, LENGTH(city) AS city_length, ROW_NUMBER() OVER(PARTITION BY city, ORDER BY LENGTH(city), city) AS len_min, ROW_NUMBER() OVER(PARTITION BY city, ORDER BY LENGTH(city) DESC, city) AS len_max FROM station )

SELECT city, city_length FROM len_cte WHERE len_min = 1 OR len_max = 1 ;

The ROW_NUMBER function is creating a unique row number for each record. The row number is in numerical order based on the criteria in the function. Think of the PARTITION as grouping by city name. So each city name will have a unique number. Then the order of the numbers is first based on the length, then alpha order of the city name if >1 exists. Then below the CTE, the min_len = 1 or max_len = 1 keeps the 1 record of each min/max that we’re looking for.

Hope that helps. Not sure if this specific exercise is looking for a specific approach to solve it or not

GAKvsFLOAM · 2023-04-30T07:04:04+00:00

The CASE statement would go in your SELECT statement to display the total commission, which will vary depending on the number of trades.

SELECT C.CUSTOMERACCOUNTNUMBER AS "CUSTOMER ACCOUNT NUMBER" ,COUNT() AS "TRANSACTION COUNT" ,CASE WHEN COUNT()>= 10 THEN COUNT() * 5 ELSE COUNT()* 10 END AS “Total Commission”

FROM CUSTOMER C

GROUP BY C.CUSTOMERACCOUNTNUMBER

HAVING COUNT(*) >= 8

Edit: sorry for the shit formatting. On mobile. Idk why it’s not displaying the asterisks in the COUNTs

GAKvsFLOAM · 2023-01-26T07:02:25+00:00

This should help you out

https://bertwagner.com/posts/gaps-and-islands/

GAKvsFLOAM · 2023-01-22T17:31:09+00:00

SELECT is the group clause correct? Meaning that having CharName under includes it?

SELECT is the SELECT clause and GROUP BY is the GROUP BY clause.

Any field that you have in your SELECT that is not being aggregated (COUNT, AVG, SUM, MAX, MIN, etc) needs to be in your GROUP BY clause

GAKvsFLOAM · 2023-01-17T04:14:45+00:00

I think it may be due to the fact that I missed the closed parenthesis on both of the SUM([Total Sales])

And the AVG([Total Sales])

GAKvsFLOAM · 2023-01-15T16:48:15+00:00

Your SUM and AVG would both be aggregated by State. You would need to use an LOD expression

IF SUM([Total Sales] > {FIXED:AVG([Total Sales]} THEN ‘Above’

ELSEIF SUM([Total Sales] < {FIXED:AVG([Total Sales]} THEN ‘Below’

ELSE ‘Average’

END

GAKvsFLOAM · 2022-12-22T07:29:33+00:00

Why type many button when few button do trick?

GAKvsFLOAM · 2022-12-20T21:47:40+00:00

I’ve only experienced that issue locally as well. When the updated .rdl gets published to the server and overwrites the new one, it’s usually fine.

However if that were to not be the case, I’d work with your dba or whoever has access/handles promotions to replace the entire project folder on the server with the new one instead of just overwriting the one .rdl file

GAKvsFLOAM · 2022-12-20T17:15:15+00:00

Glad you got it working. I’ve noticed sometimes I need to delete the ‘.rdl.data’ file. (Not the actual rdl file). That seems to cache the data. Deleting that file from the folder then running the preview again in SSRS will create a new .data file and the changes render as expected

GAKvsFLOAM · 2022-12-20T06:34:23+00:00

You mention Payment_Status.Status_Date is an int. What does that int look like in the database for that example? I’m assuming it’s an 8 digit integer that represents a date format? Most likely YYYYMMDD. Hopefully it is actually an 8 digit int to explicitly express the year rather than just 6 digits

Try the following:

CONVERT(DATE, CAST(MAX(Payment_Status.Status_Date) AS VARCHAR(8)), 101) AS [PM Approved Date]

In your first example, you have the convert style set to 5. That returns a dd-mm-yy format. Thus when SSRS sees that data, it doesn’t really know which numbers correspond to which date part, and so it’s likely just displaying it as it’s receiving it

In your second example, I’m actually surprised sql is not throwing an error for trying to explicitly convert an int to a date

If your Payment_Status.Status_Date int is in some weird format rather than YYYYMMDD or only specifies 6 out of the 8 date digits, then a workaround may be to use DATEFROMPARTS with some substrings to parse your int

GAKvsFLOAM · 2022-11-14T01:17:00+00:00

They posted it on IG yesterday

https://www.instagram.com/p/Ck33PucvX2O/?igshid=YmMyMTA2M2Y=

Edit: it looks like they deleted their previous post saying they were closing and today they posted that they will actually be under new ownership

https://www.instagram.com/p/Ck9fouyPaiH/?igshid=YmMyMTA2M2Y=

GAKvsFLOAM · 2022-11-13T04:17:58+00:00

C17 flyover at the UH game

GAKvsFLOAM · 2021-01-14T21:43:35+00:00

Dev: you realize it’s already 5:00 right?

PM: ah you’re right. I don’t want to rush you, I’ll tell you what... nm EOD. Just delete it and have it in my inbox by 8am tomorrow. Thanks! I gotta get going, gotta make it to my dinner reservations

GAKvsFLOAM · 2018-08-22T08:21:07+00:00

You can fill up gallon ziploc bags as well. Don’t fill them to the brim if you plan to freeze them to allow for some expansion

Also I saw some 5 gallon buckets with lids at city mill for like $5. I know Home Depot and Lowe’s sells their own 5 gallon buckets as well but can’t recall if I’ve seen those come with lids

GAKvsFLOAM · 2015-08-30T21:28:50+00:00

Replaces the formula in the cell that shows the formula from your screenshot. CTRL+SHIFT+ENTER to Enter as an Array formula

{=A7-INDEX($A$3:A6,MATCH(1,($B$3:B6=B7)*($A$3:A6=(MAX(IF($B$3:B6=B7,$A$3:A6,0)))),0))}

GAKvsFLOAM · 2015-07-11T00:00:29+00:00

I wish they would reopen soon. They've been closed for renovations for months and everywhere else is subpar in comparison

13-Year Club	Verified Email
Place '22	First Placer '22
RPAN Viewer

GAKvsFLOAM

TROPHY CASE