SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›4 »

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm attempting to teach myself SQL using various books and internet tools to try and work my way through the ranks at the company I work for, and I've been using w3schools and sqlzoo to try and hone my knowledge. I have a question about question # 5 on this page, the database structure that they're using is here.

Question #5 states: Find the number of left racing socks ('Racing Socks, L') ordered by CompanyName 'Riding Cycles'.

The correct answer is: 3. I've found the answer using the following:

code:

SELECT orderqty 
FROM   salesorderdetail sod 
WHERE  ( productid IN (SELECT productid 
                       FROM   productaw 
                       WHERE  name = 'Racing Socks, L') 
         AND salesorderid IN (SELECT salesorderid 
                              FROM   salesorderheader soh 
                              WHERE  customerid IN (SELECT customerid 
                                                    FROM   customeraw 
                                                    WHERE 
                                     companyname = 'Riding Cycles') 
                             ) )

This gives the correct answer but I can't help but feel there's a much easier way to do it using joins.

# ¿ Dec 10, 2013 19:11

Adbot: ADBOT LOVES YOU

# ¿ May 5, 2024 21:49

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

NULL isn't a number, it's the inherent lack of knowledge of a value. Therefore, the expression 'NULL =' anything really doesn't make sense, as you don't know if it's 0, a billion, -3.14, undefined, etc.

# ¿ Mar 10, 2014 22:50

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

the posted:

How do I format something like this:

Select Name FROM List WHERE Gender = 'Man' AND Color = 'Blue' OR Color = 'Red' OR Color = 'Yellow'

I want it to choose the first option AND one of the others, how do I make sure that happens?

Also, is there any way I can write the second condition where I don't have to write each COLOR every time?

I'm an SQL novice but this should work:

Select Name FROM List WHERE Gender = 'Man' AND Color IN ('Blue', 'Red', 'Yellow')

# ¿ Jul 1, 2014 20:59

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm struggling to compare some rate records in our database and need a nudge in the right direction. I think it can be solved joining the table with itself but as I am a total novice (I've written a few nested selects and that's about the extent of my experience, so for this question Type is most certainly equal to Stupid) I'm at a loss how to proceed! Essentially I'm trying to pull a list of accounts where our customer has lapsed their contracted rate for some amount of time, so instead of receiving a contracted rate they instead receive a default. Rate table structure is (roughly) below:

code:


RateTypeKey (PK)  |  Account#  |  EffBeginDate  |  EffEndDate  | RateType
       1             12345        2013-01-01       2013-12-31    Fixed
       3             12345        2014-01-01       2050-12-31    Default
       5             12345        2014-02-01       2015-01-31    Fixed
       2             67890        2013-01-01       2013-12-31    Fixed
       4             67890        2014-01-01       2050-12-31    Default
       6             67890        2014-01-01       2014-12-31    Fixed

(obviously there's more but I don't much care about it)

Since 12345's newest fixed rate begins a full month after the previous expired, I'd like it to be pulled, and since 67890's started immediately after the previous one, it would need to be excluded, regardless of the fact that 67890 still has that default in there. My problem is I'm not sure how to set it up to compare the dates.

code:

SELECT * 
FROM   dbo.ratetype rt1 
JOIN   dbo.ratetype rt2 
ON     rt1.ratetypekey = rt2.ratetypekey 
WHERE  rt1.ratetype != 'Default' 
AND    ???

Am I even headed in the right direction or is there some much easier way to do this? Obviously I can't just do something like add an "and rt2.EffBeginDate > rt1.EffEndDate" as that's just comparing the record with itself but poo poo I feel like this shouldn't be this hard!! Thank you in advance for anyone who can shed some light!

# ¿ Oct 13, 2014 21:03

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Anaxandrides posted:

Try this:

code:

;with cte (Account#, EffBeginDate, EffEndDate, DateRank)
AS (SELECT Account#, EffBeginDate, EffEndDate, DENSE_RANK() OVER(PARTITION BY Account# ORDER BY EffBeginDate)
    FROM dbo.RateType
    WHERE RateType = 'Fixed')
SELECT
FROM cte c
    JOIN cte c2
        ON c.Account# = c2.Account#
WHERE c.EffEndDate < c2.EffBeginDate
       AND c.DateRank = c2.DateRank -1

This was the nudge I needed in the right direction, thank you! What I ultimately ended up going with was (account# is ieinum and is actually in a different table hence the extra join):

code:

USE ny; 

WITH ratecomparison (ieinum, effbegindate, effenddate, daterank) 
     AS (SELECT DISTINCT sa.ieinum, 
                         rt.effbegindate, 
                         rt.effenddate, 
                         Dense_rank() 
                           OVER( 
                             partition BY sa.ieinum 
                             ORDER BY effbegindate) 
         FROM   dbo.ratetype rt 
                JOIN dbo.serviceaddr sa 
                  ON rt.serviceaddrid = sa.serviceaddrkey 
         WHERE  rt.ratetype NOT IN ( 'Default', 'Intro', 'VEREM2M', 'VEPTM2M' )) 
SELECT * 
FROM   ratecomparison rc1 
       JOIN ratecomparison rc2 
         ON rc1.ieinum = rc2.ieinum 
WHERE  rc1.effenddate + 1 < rc2.effbegindate 
       AND rc1.daterank = rc2.daterank - 1 
ORDER  BY rc2.effbegindate DESC, 
          rc2.ieinum

One other newbie question: the above query runs in 2 seconds if I omit the DESC in the order by clause, but with DESC it takes 22 seconds. Is there a better less intensive way to get the results like I want them? This is mostly an academic exercise as this isn't going to be a continually run query so I don't particularly care about the 20 seconds, but I'm interested why it's such a performance hit.

# ¿ Oct 14, 2014 21:30

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

What's the best way to extract a date out of the middle of a text string in MSSQL? Ex:

String = 'Text1 1/1/14 Text2'

Text1 is always the same length, but Text2 can be of varying length. I want to be able to order my query by the date pulled out of this string if at all possible, so I assume I need to somehow get rid of Text1 & Text2 while at the same time casting the date as datetime but I am unsure how to attack it. I tried using the TRIM function from this SQL reference book I have like so:

SELECT TRIM(LEADING 'Text1' FROM TRIM(TRAILING 'Text2' FROM String))

but MSSQL doesn't seem to have this function and I'm not entirely sure that function is even supposed to do what I'm trying to do with it. LTRIM and RTRIM only trim blanks so that's useless to me. What do I do here?

edit: Figured it out myself!

code:

SELECT CAST(substring(creditcheckexceptionnote,(patindex('%[/]%',creditcheckexceptionnote))-2,10) AS date) AS overridedate

Find the first '/' in the date, back up two positions to start at the first character in the month, grab 10 characters (MM/DD/YYYY). Sorry if this is trivial but as a novice I'm super happy I managed to figure this out myself!!

kumba fucked around with this message at 20:53 on Nov 6, 2014

# ¿ Nov 6, 2014 20:23

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Anaxandrides posted:

Do Text1 and Text2 ever have spaces? If not, XML PATH is almost always faster than substringing.

Both Text1 and Text2 always have spaces, so it sounds like that wouldn't work

# ¿ Nov 6, 2014 22:59

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

COOL CORN posted:

I'm curious if there's an easier way to do what I'm doing here.

I have two tables (we'll say T1 and T2). I'm looking for all the records in T1 that don't have matching IDs in T2.
code:
SELECT T1.ad_gisid      AS "T1 GIS_ID", 
       T1.ad_st_address AS "T1 Address" 
FROM   gis.dbo.table1 T1 
WHERE  T1.ad_gisid NOT IN (SELECT ad_gisid 
                           FROM   gis.dbo.table2) 
It looks like it should be so simple, but it's been running for half an hour, so I think I'm missing something dumb. Granted, there are ~200,000 records, but... still, should it be running that long?

I'd use a left join instead of a subselect. This:

code:

SELECT T1.ad_gisid      AS "T1 GIS_ID", 
       T1.ad_st_address AS "T1 Address" 
FROM   gis.dbo.table1 T1 
LEFT JOIN gis.dbo.table2 T2 ON T1.ad_gisid = T2.ad_gisid
WHERE  T2.ad_gisid is null

should do the trick I think

# ¿ Apr 16, 2015 19:41

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Roundboy posted:

While I can't offer you too many specifics without knowing your db setup, the answer is almost all universally powershell.

Its most likely not a direct table updates AD, but power she'll being the middle man to pull info and apply.

Someone on my team just wrote something like this in Powershell for our company last week, so I can confirm this is a good approach.

# ¿ Apr 17, 2015 13:57

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm looking to calculate some averages over various different date ranges all at once and trying to figure out the best approach. I have a way that works but it is ugly and involves multiple temp tables that I'm trying to clean up to make things more readable.

At the core, I am attempting to calculate Daily, Monthly, and Yearly averages for various payment methods that our customers use. I'm starting off with ACH & Credit/Debit Card Payments and will expand it to other methods once I figure out a better way to do this if there is one. We have a table PaymentQueue (PK is ID) that houses all ACH & Card payments made, and a separate table ScheduledPayment that references PaymentQueue that specifies payments that were either one-time payments scheduled ahead of time or are recurring every month. I want to break out the averages by One-Time payments (so anything that is in PaymentQueue but not in ScheduledPayment), Recurring Payments, and Scheduled One-Time Payments. Here's what I have so far:

code:

create table #temp (
Payment_Method varchar(50),
Payment_Type varchar(50),
Payment_Date datetime
)

create table #temp2 (
Payment_Method varchar(50),
Payment_Type varchar(50),
Monthly_Average int,
)

create table #temp3 (
Payment_Method varchar(50),
Payment_Type varchar(50),
Daily_Average int
)

create table #temp4 (
Payment_Method varchar(50),
Payment_Type varchar(50),
Yearly_Average int
)

insert into #temp (Payment_Method, Payment_Type, Payment_Date)
select
	'ACH or Credit/Debit Card Payments',
		(
		case 
			when ID in (select paymentqueueID from Payments.dbo.ScheduledPayment where IsRecurring = 1) then 'Recurring Payments'
			when ID in (select paymentqueueID from Payments.dbo.ScheduledPayment where IsRecurring = 0) then 'Scheduled One-Time Payments'
			else 'One-Time Payments'
		end
		) as Payment_Type,
		postdate as Payment_Date 
from Payments.dbo.PaymentQueue
where postdate >= '20120101'
	and postdate < '20150101'
and paymenttype <> 23 --23 is balance transfer; not a real payment, so get rid of it!
and ProcessStatus = 1 --Processed successfully locally
and PaymentResult = 1 --Was not rejected by bank/creditor

insert into #temp2 (Payment_Method, Payment_Type, Monthly_Average)

select x.Payment_Method, x.Payment_Type, avg(x.Number_of_Payments) as Monthly_Average from 
	(
	select Payment_Method, Payment_Type, count(Payment_Type) as Number_of_Payments, month(Payment_date) as Month, Year(Payment_date) as Year from #temp
	group by Payment_Method, Payment_Type, year(payment_date), month(payment_date)
	) x 
group by Payment_Method, Payment_Type

insert into #temp3 (Payment_Method, Payment_Type, Daily_Average)
select y.Payment_Method, y.Payment_Type, avg(y.Number_of_Payments)
from 
	(
	select Payment_Method, Payment_Type, count(Payment_Type) as Number_of_Payments, day(Payment_date) as Day, month(Payment_date) as Month, Year(Payment_date) as Year from #temp
	group by Payment_Method, Payment_Type, year(payment_date), month(payment_date), day(Payment_date)
	) y 
group by y.Payment_Method, y.Payment_Type

insert into #temp4 (Payment_Method, Payment_Type, Yearly_Average)
select z.Payment_Method, z.Payment_Type, avg(z.Number_of_Payments)
from 
	(
	select Payment_Method, Payment_Type, count(Payment_Type) as Number_of_Payments, Year(Payment_date) as Year from #temp
	group by Payment_Method, Payment_Type,  Year(Payment_date)
	) z 
group by z.Payment_Method, z.Payment_Type

select t2.Payment_Method, t2.Payment_Type, t3.Daily_Average, t2.Monthly_Average, t4.Yearly_Average
from #temp2 t2 
	join #temp3 t3 
	on t2.Payment_Method = t3.Payment_Method and t2.Payment_Type = t3.Payment_Type
	join #temp4 t4
	on t3.Payment_Method = t4.Payment_Method and t3.Payment_Type = t4.Payment_Type	

drop table #temp
drop table #temp2
drop table #temp3
drop table #temp4

There must be a better way but I don't know of one. I looked into 'WITH ROLLUP' but that appears only to work with SUM() so that won't work. I found COMPUTE & COMPUTE BY but that just seems like another way to just use a function with group by and not of any help to me. I feel like this is going to be something simple staring but in the face but I can't figure it out. Thanks in advance if anyone has any pointers - I know this could be done relatively easily using something like pivot tables in Excel but I'd like to be able to do it all in SQL if possible.

# ¿ May 8, 2015 21:56

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Those were just example dates thrown in there to see if the calculations work, which they do - I have no intention of leaving it like that. Eventually it will likely be parameterized so a user can input a range of years to get the averages over. Also this will never be run on anything other than SQL Server.

# ¿ May 11, 2015 17:55

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Gatac posted:

Okay, this feels like it should be easy, but I just can't figure out a smart way to do it. Oracle 11g, names changed for clarity, and I can't make any changes to the schema or the data, I'm just trying to query it the best way I can. My SQL knowledge is limited to what I've picked up over the last year, so if I'm being an idiot, feel free to call me out on it.

Say I have a table CONTRACT with a primary key of CON_ID that is also a foreign key of another table CON_ATTRIBUTE as CONATT_CONID. CON_ATTRIBUTE contains timesliced attributes for CONTRACT that need to be retained there when changes come in, so you can see "Ah, it these values at the beginning, then on April 1st these values, then on June 5th it changed again..." To do this, CON_ATTRIBUTE has a column CONATT_DATE. This column is null for the first timeslice belonging to each CON_ID, then subsequent rows for the same CON_ID contain the date of the change. There's a column in CON_ATTRIBUTE we'll call CONATT_VALUE which is what I actually want to find out for each CON_ID. The problems are:

1) I want to join these two tables so I can get the most recent timeslice belonging to a contract, i.e. the timeslice with the most recent date in CONATT_DATE, counting the one with the null in CONATT_DATE as oldest. Since Oracle doesn't do TOP, I've been looking at a way of getting around that, but my attempt looks super clunky.
code:
join
(
	select c.CONATT_CONID, c.CONATT_VALUE
	from CON_ATTRIBUTE c
	where c.CONATT_ID =
	(
		select b.CONATT_ID from
		(
			select a.CONATT_ID, nvl(c.CONATT_DATE,'01.01.1900')
			from CON_ATTRIBUTE a
			order by 2 desc
		) b
		where rownum = 1
	)
) d
on d.CONATT_CONID = CONTRACT.CON_ID
Please tell me there's an easier/prettier/saner way of doing this.

2) Both tables are pretty big. I'm looking at 100k+ rows in CONTRACT and CON_ATTRIBUTE is roughly twice that, so the prototype query runs pretty long. Now, there is an index on CON_ATTRIBUTE that combines the columns CONATT_CONID and CONATT_DATE. However, considering that roughly two thirds of all entries have null as CONATT_DATE, I'm not sure if it's doing any good for this at all. Is there a way to take advantage of it, or is this doomed to a full-table scan? (For what it's worth, I only need data for maybe 5 to 10 CON_IDs at a time, and without the join to CON_ATTRIBUTE the query is plenty fast.)

Though I've never used Oracle, a cursory google search seems to indicate Oracle can use CTEs, so I would go that route:

code:

USE whatever; 

WITH cte (id, value, valuerank) 
     AS (SELECT c.conatt_conid, 
                c.conatt_value, 
                Row_number() 
                  OVER ( 
                    partition BY c.conatt_conid 
                    ORDER BY c.conatt_date DESC)) 
SELECT * 
FROM   cte c 
WHERE  valuerank = 1

# ¿ May 22, 2015 18:30

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Fruit Smoothies posted:

I have part of a table that looks like this
code:
|	ID	|	Value		|
-------------------------------------
|	1	|	200		|
|	2	|	400		|
to save on post processing, I'd like to be able to modify the `value` within the query, on a row-by-row basis. Something conceptually like
code:
SELECT *, (value + 10 WHERE `id` = 1) from table
The reason I wanna do this using SQL is because the whole query is currently designed beautifully using Laravel's query builder. This means clauses, joins, and selects can be added depending on processing. Adding a SELECT or similar would enable to keep me using the query builder syntax, and not have to write a whole bunch of code just for this one case.

EDIT: I've done it with IF

If I'm understanding right you could also do this with a case statement

code:

SELECT id, 
       value, 
       ( CASE 
           WHEN id = 1 THEN value + 10 
           ELSE 'Whatever else??' 
         -- Don't know what you want here, put null or 0 or something whatever you want 
         END ) AS 'ValueAddedorWhatever'

# ¿ Jul 5, 2015 22:25

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

ljw1004 posted:

SELECT * FROM Posts WHERE Title LIKE '%resources%'

I've got as far as I can go with hacking my way through SQL, writing queries, googling and search stackoverflow for where I get stuck.

Now I want to sit down with an authoritative book and actually learn the language all-up in a more systematic and principled way -- so I no longer get surprised each time it tells me "can't have an aggregate inside a subquery" or whatever.

I couldn't find a "resources" post in this thread. Is there one? What's a good SQL book? I'm happy with language theory and computer science, and would prefer a thorough dry text over one that's practical and applied but partial.

If you're learning SQL Server, the 70-461 is, afaik, the industry standard for starting out: http://www.amazon.com/Training-70-461-Querying-Microsoft-Server/dp/0735666059. At least it is around these parts. There's also the MTA which is way more dumbed down and somehow more expensive than the 70-461: http://www.amazon.com/Exam-98-364-Database-Administration-Fundamentals/dp/0470889160

# ¿ Jul 21, 2015 20:09

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm trying to write a sproc that takes a tablename as a parameter as well as a recordid, which will use the tablename in an update statement to update that record. I am having no luck. Here's what I have:

code:

USE [Database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].MarkRecordAsCompleted
@TableName varchar(250),
@RecordID int,
as

Declare @UpdateCommand varchar(max)

set @UpdateCommand = 'update ' + @TableName + ' SET isShown = 0, 
MarkedCompletedTime = getdate(), 
MarkedCompletedUser = substring(SYSTEM_USER,charindex(CHAR(39)\CHAR(39),SYSTEM_USER)+1,len(SYSTEM_USER)-charindex(CHAR(39)\CHAR(39),SYSTEM_USER)) 
WHERE [ID]=' + @RecordID
EXEC (@UpdateCommand)

I get the following error every time:

Msg 245, Level 16, State 1, Procedure MarkRecordAsCompleted, Line 9
Conversion failed when converting the varchar value 'update NestRatesWithoutTPSCRecordsResults SET isShown = 0,
MarkedCompletedTime = getdate(),
WHERE [ID]=' to data type int.

I'm not sure how to stop the sproc from attempting to return an int like this. I thought maybe it was my roundabout way of grabbing the username, but I deleted that and tried without it and no dice. I tried just adding a line at the end to RETURN 'Success!' instead of the integer value but that doesn't seem to work either. I tried adding an output variable to the proc and that was no good. Not sure where to go from here!

# ¿ Jul 21, 2015 20:20

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Nth Doctor posted:

Do a cast of @RecordID:

You're a gentleman and a scholar. Thank you!

# ¿ Jul 21, 2015 20:58

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Whenever you connect to the server, your connection defaults to the "master" database and you have to either include a use statement to let your query know which database you want to talk to, or you need to just change it in the menu at the top:

To avoid doing either of these steps, you should get used to using 3 (or even 4 if you deal with multiple servers) part naming in your queries instead. So, if you have a server named Server1, and a database within that called DBMaster, and a table called Customers in the dbo schema within that database, after connecting to Server1 you could write:

code:

USE dbmaster 

SELECT * 
FROM   customers

Or, you can write this instead:

code:

SELECT * 
FROM   dbmaster.dbo.customers

You can use 4-part naming including the server name to join databases across servers (assuming they can see each other / are linked), like so:

code:

SELECT * 
FROM   server1.dbmaster.dbo.customers c1 
       INNER JOIN server2.dbmaster.dbo.customers c2 
               ON c1.id = c2.id

# ¿ Jul 29, 2015 15:57

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

No Safe Word posted:

Just be aware that like 99% of the time this is the wrong way to do such a thing. Other than occasional one-off things.

This is the first I've heard this - how else can you accomplish the same thing? We have several reporting servers that do various things and we have nightly processes where batches of monitoring queries are run and we've never run into a problem with the servers talking to each other.

e: This post may sound snarky but it's not meant to, if we could do this in a better way I'm totally open to it!

kumba fucked around with this message at 17:20 on Jul 29, 2015

# ¿ Jul 29, 2015 17:16

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

You have to separate them, yeah. There's no way to combine LIKE and IN (without including every possibility something could be LIKE inside the IN).

# ¿ Aug 25, 2015 21:54

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

That is correct. The order they're evaluated in is:

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

# ¿ Sep 1, 2015 21:39

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Knifegrab posted:

Awesome thanks, this helps a ton! I will keep all of this in mind when building my application.

I've got another question regarding CTE's.

I am using CTE's to insert into a table rows that are being deleted from another. My basic code is like this:
code:
WITH tmp AS (DELETE FROM table_a WHERE col_a = 'someValue' RETURNING col_b) INSERT INTO table_b (col_b) SELECT col_b from tmp RETURNING col_b
This works pretty well but what I really want is the count of rows that the delete/insert does but I don't think there is a way to get an aggregate in the return Any suggestions?

If I'm understanding your question right you can use the output clause, here's an example:

code:

declare @mytable table (colA int, colB int, colC bit)
insert into @mytable output inserted.colA as ColAInsert, inserted.colB as ColBInsert, inserted.colC as ColCInsert 
values (1,1,0),(1,50,0),(1,9,0) 

update @mytable set colC = 1
OUTPUT inserted.colA as ColAUpdate1, inserted.colB as ColBUpdate1, inserted.colC as ColCUpdate1, 
		deleted.colA as ColAUpdate2, deleted.colB as ColBUpdate2, deleted.colC as ColCUpdate2
where colA = 1

delete from @mytable
OUTPUT deleted.ColA as ColADelete, deleted.ColB as ColBDelete, deleted.ColC as ColCDelete
where colC = 1

https://msdn.microsoft.com/en-us/library/ms177564.aspx

# ¿ Oct 19, 2015 14:48

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

My first thought was to put NULL where it's undefined and use coalesce from order of most specific to least specific in order to get the best hit, so:

select coalesce(City.speedLimit, State.speedLimit, Federal.speedLimit);

Should always give you the right answer

# ¿ Dec 8, 2015 15:13

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Are the fields before and after it consistently the same? My first thought is to use the various substring/charindex/patindex built-in functions to find the position of the "LastName" & "ProxyOnly" fields, find the MRN in between it, and use commas as delimiters

If they're not, you're going to probably have to make an ugly custom function to count occurrences of MRN, then find the starting position of the final occurrence and work from there

Something like:

code:

CREATE FUNCTION dbo.CountOccurancesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END;
GO

declare @mrnCount int,
		@stringDataLength int,
		@patternDataLength int,
		@inputString nvarchar(max),
		@searchPattern nvarchar(30)
set @searchPattern = 'MRN'
set @inputString = 'your long rear end string here'

set @mrnCount = (select dbo.CountOccurancesOfString(@inputString,@searchPattern))
set @stringDataLength = DATALENGTH(@inputstring)
set @patternDataLength = DATALENGTH(@searchPattern)

You could then use a while loop that subtracts 1 from @mrnCount each time it iterates through using charindex and incrementing the starting position using the @patternDataLength and the result of the charindex, so then your final starting position will be the MRN you're looking for, and then you just need to figure out how to determine the length to extract using @stringDataLength and the last MRN and.. something else I'm not quite sure. This is ugly and someone might have a better idea. Hopefully LastName and ProxyOnly are reliably there so you can just do it that way

# ¿ Dec 15, 2015 20:20

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

As long as you know that there is only one occurrence of MRN in between LastName and ProxyOnly (seems like it?), then it doesn't actually matter how long anything is cause you can figure it all out

code:

DECLARE @inputString       NVARCHAR(max), 
        @modifiedString    NVARCHAR(max), 
        @searchPattern     NVARCHAR(50), 
        @lastNamePosition  INT, 
        @proxyOnlyPosition INT 

SET @inputString = 'long rear end string' 
SET @searchPattern = 'MRN' 
SET @lastNamePosition = (SELECT Charindex('"LastName":', @inputString)) 
SET @proxyOnlyPosition = (SELECT Charindex('"ProxyOnly":', @inputString)) 
SET @modifiedString = Substring(@inputString, @lastNamePosition, 
                      @proxyOnlyPosition - @lastNamePosition) 

SELECT @modifiedString -- This should now be everything from "LastName" to the , after the MRN value 
                          you want followed by some bullshit empty characters

SET @modifiedString = Substring(@modifiedString, 
                      Charindex('"MRN"', @modifiedString), 
                                            Datalength(@modifiedString) - 
                                            Charindex('"MRN"', @modifiedString)) 

SELECT @modifiedString -- Now it's just "MRN":####, and the bullshit characters  

SELECT Substring(@modifiedString, Charindex(':', @modifiedString) + 1, 
              Charindex(',', @modifiedString) - ( 
              Charindex(':', @modifiedString) + 1 ) 
       )

If you can have multiple MRNs in between LastName and ProxyOnly then that sounds.. awful

# ¿ Dec 15, 2015 22:49

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

This would be significantly easier if you A.) didn't have to use SQL or B.) could convert the data to XML easily somehow

I don't know much about non-SQL stuff since I'm a data monkey and not a dev, but if you can convert whatever data this is to XML then you can use the built-in XML stuff to yank out the value you need

code:

CREATE FUNCTION dbo.CountOccurancesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END;
GO

declare @convertedString xml = 'your bullshit'
declare @MRNCount int

set @MRNCount = (select dbo.CountOccurancesOfString(cast(@convertedString as nvarchar(max))))

select @convertedString.value('(/XML/Identifiers/@MRN)[@MRNCount]','int')

Something like that

# ¿ Dec 15, 2015 23:08

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Gatac posted:

Note that this will only give you a product and the highest revision, not the price of that revision. You will need a ranking function for that.

Which is also relatively easy, just adds a few lines:

code:

;WITH cte (product, price, revision, revrank) 
     AS (SELECT product, 
                price, 
                revision, 
                Row_number() 
                  OVER ( 
                    partition BY product 
                    ORDER BY revision DESC) 
         FROM   YourTable) 
SELECT product, 
       price, 
       Max(revision) AS HIGHESTREVISION 
FROM   cte 
WHERE  product IN ( 'SHIRT', 'PANTS' ) 
       AND revrank = 1 
GROUP  BY product, 
          price

# ¿ Feb 15, 2016 22:47

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Huh. Never thought to use an inline-view like that instead of ranking. Interesting approach. I am a sucker for CTEs so that's where my mind always goes first :v:

# ¿ Feb 16, 2016 17:12

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

From personal experience option b has always worked out easiest for me, but I'm pretty sure it's just going to boil down to checking your execution plan and seeing what works best with the tables you're working with.

# ¿ Feb 19, 2016 19:03

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Do we have an SSRS thread anywhere or would this be a decent place to ask a question?

I'm trying to figure out if there is anyway in SSRS to load up the front page of a report that has parameters without the parameters being supplied yet. Essentially I'm trying to populate an instructions page of sorts before the user actually inputs the information and hits the 'View Report' button, but for the life of me I cannot figure out how to do it or if it's even possible. I would think something like this would be very common and was surprised to find how obtuse this software can be.

# ¿ Feb 23, 2016 19:56

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I left out a very important detail that will likely impact the solution:

I'm using SSRS for something that is definitely not what it was designed for. There are reasons I'm doing this (mostly because development time is not really available due to other things taking far higher priority than this), but suffice to say that I'm not using this report to actually report on anything, but rather as a way for a user to be able to add information to a database without an actual UI. Yes, that means I'm using Insert statements in my SSRS Dataset. It's ugly and stupid but really I don't have any other way to do what I need to do.

The problem is that if I just populate defaults, it's going to insert that default information into the database whenever someone opens it. I would love if I could just have the database table have non-nullable fields and default all the SSRS parameters to NULL and fire away, but it throws an error in that case instead of doing what I want it to.

This is going to be a two part solution, as the "Report" I'm building is just inserting into a dummy sandbox table on a non-production environment, and a daily job will run to crawl through what's inserted into my sandbox and put what is necessary into production.

What I'm thinking is to have the defaults set to something clearly erroneous, and then part of the daily job would be to remove anything it recognizes as a default and process the rest. This sounds dangerous but I'm not sure I have any other options.

# ¿ Feb 23, 2016 21:16

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

The sproc method you suggested set off a lightbulb in my head that I don't know why I didn't think of earlier: I can just filter out the dummy placeholder data in the insert itself.

The whole purpose of this tool was to allow a select few members of our organization to manually put in orders for a specific product. There's a quantity variable dictating how many units they're trying to order, so I can just set the placeholder data to have a quantity of 0 and do a simple

INSERT INTO MyTable (Quantity) SELECT @Quantity WHERE @Quantity > 0

(since I care not about orders with 0 or less quantity)

Obviously there's more columns than that but the logic works out the same. Can't believe I never thought to do that. Thanks for the nudge!

# ¿ Feb 24, 2016 15:30

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Indeed. Necessary evils and all that. :v:

# ¿ Feb 24, 2016 16:53

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Can you select top in oracle? If so this should work

code:

DECLARE @today DATE = Getdate() 

SELECT TOP 1 * 
FROM   (SELECT TOP 10 * 
        FROM   MyTable 
        WHERE  is_holiday = 0 
               AND datum >= @today 
        ORDER  BY datum) x 
ORDER  BY datum DESC

# ¿ Mar 1, 2016 18:36

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

A sproc could be using the built-in SYSTEM_USER function to generate the username in which case whoever fires it off will be listed as the user

# ¿ Apr 20, 2016 20:11

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

HoboMan posted:

Ok so this is breaking my brain: I got a table that has a FK to itself (PK is ID, FK is LinkID). I need a query that gives me everything where an item is not linked to another item. In other words I need all rows where its ID does not appear in another row's LinkID column.

If I understand your problem correctly:

code:

select ID from SomeTable t1 where not exists (select 1 from SomeTable t2 where t1.id = t2.LinkID)

# ¿ May 12, 2016 18:41

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Subqueries used properly are not gross, but that left join method will work just fine yeah.

Explanation without venn diagram: left joins keep all data from the table on the left side of the join regardless of whether the key being evaluated has a match in the table on the right. If you remove the where clause, this will return every ID in t1 (regardless of if there is a corresponding LinkID in t2), as well as the match in t2 if it exists. It will return NULL if it does not find a match, instead of a normal join which will filter out the row entirely.

Thus, when you add the where clause back in it is returning all IDs in t1 that do not have a corresponding match in t2, which is exactly the condition you're looking for.

# ¿ May 12, 2016 19:36

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I would do something like this instead:

code:

SELECT col, 
       Count(col) AS Number 
FROM   my_table 
GROUP  BY col 
HAVING Count(col) > 1 

UNION 

SELECT 'Other', 
       Sum(x.number) 
FROM   (SELECT col, 
               Count(col) AS Number 
        FROM   my_table 
        GROUP  BY col 
        HAVING Count(col) = 1) x 
ORDER  BY col

# ¿ May 12, 2016 20:37

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Moreau posted:

So, I have two databases sitting on an MSSQL server. Database A has a table, Table A, that a user updates arbitrarily, 4 or 5 times a year. Database B has a view of Table A, View A.

I would like to fire off some sprocs in Database B when certain columns in Table A change, but I dont want to create any additional code in Database A, if at all possible (its a monumental pain in the rear end). Is it possible to apply a trigger to View A in Database B? Or is there an alternative that would allow this functionality? If necessary, Ill create a simple trigger on Table A in Database A - but I'd really prefer to just work with Database B.

How quickly do you need to respond to changes in Table A? Is some delay acceptable or does it need to be immediate? If you're okay with some delay, you could setup a simple (hourly/daily/whatever increment is appropriate) job that would crawl through table A looking for changes. I'm assuming table A has some sort of LastUpdatedOn column or something of that nature, so you could store the current LastUpdatedOn in some table in Database B and do something like

code:

DECLARE @compareDateTime DATETIME 
DECLARE @newUpdateDateTime DATETIME 

SET @compareDateTime = (SELECT lastupdatedon 
                        FROM   database_b..some_table) 

IF EXISTS (SELECT 1 
           FROM   database_a..table_a a 
           WHERE  a.updatedon > @compareDateTime) 
  BEGIN 
      SET @newUpdateDateTime = (SELECT TOP 1 a.updatedon 
                                FROM   database_a..table_a a 
                                ORDER  BY a.updatedon DESC) 

      EXEC Some_stuff 

      UPDATE database_b..some_table 
      SET    lastupdatedon = @newUpdateDateTime 
  END

# ¿ May 27, 2016 14:15

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm sure there's a better way to do this but this is the first thing I thought of (temp table is just for demonstration, obviously you don't need that part in your case):

code:

declare @temp table (
	ID1 nvarchar(10)
,	ID2 nvarchar(10)
,	ID3 nvarchar(10)
,	ID4 nvarchar(10)
,	ID5 nvarchar(10)
	)

insert into @temp (ID1, ID2, ID3, ID4, ID5)
select '123', 'bla', 'bla', 'bla', 'bla'
union
select 'abc', null, null, null, null

select 'before update:',* from @temp 

update t2
set ID1 = t1.ID1, ID2 = t1.ID2, ID3 = t1.ID3, ID4 = t1.ID4, ID5 = t1.ID5
from @temp t1 join @temp t2 on t2.ID1 = 'abc'

select 'after update:',* from @temp

# ¿ Aug 12, 2016 18:47

Adbot: ADBOT LOVES YOU

# ¿ May 5, 2024 21:49

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

COOL CORN posted:

stuff

You should be able just use that query as an inline view and group by it:

code:

SELECT   x.worktype, 
         x.workmonth, 
         Sum(x.numberofworkorders) 
FROM     ( 
                  SELECT   <logic> AS worktype <aggregate logic> AS workmonth <count logic> AS numberofworkorders
                  FROM     linkedservera 
                  WHERE    <logic> 
                  GROUP BY <aggregate month logic> 
                  UNION 
                  SELECT   <logic> AS worktype <aggregate logic> AS workmonth <count logic> AS numberofworkorders
                  FROM     linkedserverb 
                  WHERE    <logic> 
                  GROUP BY <aggregate month logic> ) x 
GROUP BY x.worktype, 
         x.workmonth

# ¿ Sep 12, 2016 17:16

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›4 »