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'

«‹›303 »

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

wigga please posted:

So what if I implement half of this and make 10 logins (9 + service login to fetch user), and make separate Connect methods in the application?

I now have:

code:

    public static class Connect
    {
        public static SqlConnection GetConnection()
        {
            SqlConnection cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
                + "Persist Security Info=True;User ID=POSUser;Password=******");
            return cnc;
        }
    }

and maybe could replace that with

code:

        public static SqlConnection GetConnectionForLevel()
        {
            SqlConnection cnc;
            switch (User.LoggedInUser.Level)
            {
                case 0:
                    cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
               + "Persist Security Info=True;User ID=POSAdmin;Password=********");
                break;
                case 1:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSOwnerr;Password=********");
                break;
                case 2:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSManager;Password=********");
                break;
                case 3:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSAccountant;Password=********");
                break;
                case 5:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSWarehouse;Password=********");
                break;
                case 6:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSSales;Password=********");
                break;
                case 7:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSService;Password=********");
                break;
                case 8:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSBasicSales;Password=********");
                break;
                default:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSGuest;Password=********");
                break;
            }

            return cnc;
        }

Yea that's one way to do a very basic security level check.

# ? Oct 23, 2009 17:18

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 09:16

wigga please: Nov 1, 2006; by mons all madden

Here's another question that's probably SQL 101 but:

I have a stored procedure that gets all details from a product. Half of these details are referenced from another table. I currently have

code:

	SELECT @brand_id = Products.brand, @category_id = Products.category,
		@subcategory_id = Products.subcategory, @extra_tax_id = Products.extra_tax,
		@taxrate_id = Products.taxrate, @supplier_id = Products.supplier 
	FROM Products WHERE Products.id = @id
	
	SELECT Products.code, Products.description, Products.price_in,
		Products.price_out, Products.barcode, Products.EAN
	FROM Products WHERE Products.id=@id
	
	SELECT Brands.name FROM Brands WHERE Brands.id=@brand_id
	SELECT Category.name FROM Category WHERE Category.id=@category_id
	SELECT SubCategory.name FROM SubCategory WHERE SubCategory.id=@subcategory_id
	SELECT Extra_Taxes.name FROM Extra_Taxes WHERE Extra_Taxes.id=@extra_tax_id
	SELECT Tax_Rates.rate FROM Tax_Rates WHERE Tax_Rates.id=@taxrate_id
	SELECT Supplier.name FROM Supplier WHERE Supplier.id=@supplier_id

but this returns several rows, one with all the data from the first statement, and a single record from every other table. Should I use a JOIN of sorts to combine these into a single object that can be read by a .NET System.Data.SQLClient.Datareader?

e: I know I could do this in my application as well but I'd like to do it inside the database

wigga please fucked around with this message at 11:34 on Oct 26, 2009

# ? Oct 26, 2009 11:00

Dietrich: Sep 11, 2001

wigga please posted:

Here's another question that's probably SQL 101 but:

I have a stored procedure that gets all details from a product. Half of these details are referenced from another table. I currently have
code:
	SELECT @brand_id = Products.brand, @category_id = Products.category,
		@subcategory_id = Products.subcategory, @extra_tax_id = Products.extra_tax,
		@taxrate_id = Products.taxrate, @supplier_id = Products.supplier 
	FROM Products WHERE Products.id = @id
	
	SELECT Products.code, Products.description, Products.price_in,
		Products.price_out, Products.barcode, Products.EAN
	FROM Products WHERE Products.id=@id
	
	SELECT Brands.name FROM Brands WHERE Brands.id=@brand_id
	SELECT Category.name FROM Category WHERE Category.id=@category_id
	SELECT SubCategory.name FROM SubCategory WHERE SubCategory.id=@subcategory_id
	SELECT Extra_Taxes.name FROM Extra_Taxes WHERE Extra_Taxes.id=@extra_tax_id
	SELECT Tax_Rates.rate FROM Tax_Rates WHERE Tax_Rates.id=@taxrate_id
	SELECT Supplier.name FROM Supplier WHERE Supplier.id=@supplier_id
but this returns several rows, one with all the data from the first statement, and a single record from every other table. Should I use a JOIN of sorts to combine these into a single object that can be read by a .NET System.Data.SQLClient.Datareader?

e: I know I could do this in my application as well but I'd like to do it inside the database

Holy poo poo, YES for the love of god use JOINS. This is what joins are for.

# ? Oct 26, 2009 14:06

wigga please: Nov 1, 2006; by mons all madden

crap, quote!= edit, see below

wigga please fucked around with this message at 15:03 on Oct 26, 2009

# ? Oct 26, 2009 14:57

wigga please: Nov 1, 2006; by mons all madden

Dietrich posted:

Holy poo poo, YES for the love of god use JOINS. This is what joins are for.

Yeah that's what I figured, but then I read some comments (also itt) on how joins are too expensive in some cases because they would always scan the whole table (or something?) so I was thinking UNION and subqueries were the better solution. Ofcourse UNION wasn't an option in this case for obvious reasons.

Would you mind giving me an example of how I would use multiple joins in this case?

e: could I also leave out

code:

	SELECT @brand_id = Products.brand, @category_id = Products.category,
		@subcategory_id = Products.subcategory, @extra_tax_id = Products.extra_tax,
		@taxrate_id = Products.taxrate, @supplier_id = Products.supplier 
	FROM Products WHERE Products.id = @id

in this case?

# ? Oct 26, 2009 15:02

MoNsTeR: Jun 29, 2002

Steps for writing SQL:
1. get the syntax correct
2. get the output correct
3. get the performance correct

IN. THIS. ORDER.

The only way you'll know if the join is slow is to try it!

# ? Oct 26, 2009 15:30

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Thinking that it is possible to replicate a JOIN's output without replicating it's performance cost is a symptom of not knowing how queries work.

In almost all cases, one query with many (necessary) joins is better than any set of smaller queries that attempt to skirt around using it. One rationale here is that it is easier (in most if not all cases) for the query engine to analyze the big query as one operation and optimize it down to something small, versus the many queries where it can't possibly know of any semantic link between them.

The long and short of it is use joins appropriately.

# ? Oct 26, 2009 16:02

Dietrich: Sep 11, 2001

This is your basic setup.

code:

SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table3.Column1 
FROM Table1 INNER JOIN Table2 ON Table1.FK1 = Table2.FK1 
INNER JOIN Table3 ON Table1.FK2 = Table3.FK2

If a foreign key may not always be present on Table1, then you'll want to use a LEFT OUTER join instead of an INNER join

# ? Oct 26, 2009 16:03

wigga please: Nov 1, 2006; by mons all madden

I'm sorry people, I had a horrible teacher for Databases 101.

Still, would someone mind providing me with the right query for the mess I've posted?

e: tx Dietrich

wigga please fucked around with this message at 16:07 on Oct 26, 2009

# ? Oct 26, 2009 16:03

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

For the love of Relational Jesus, please write your queries like this:

code:

SELECT
  Table1.Column1,
  Table1.Column2,
  Table2.Column1,
  Table3.Column1 
FROM Table1

INNER JOIN Table2
ON Table1.FK1 = Table2.FK1 

INNER JOIN Table3
ON Table1.FK2 = Table3.FK2

# ? Oct 26, 2009 16:23

Dietrich: Sep 11, 2001

Triple Tech posted:

For the love of Relational Jesus, please write your queries like this:

code:

SELECT
  Table1.Column1,
  Table1.Column2,
  Table2.Column1,
  Table3.Column1 
FROM Table1

INNER JOIN Table2
ON Table1.FK1 = Table2.FK1 

INNER JOIN Table3
ON Table1.FK2 = Table3.FK2

Yes I too enjoy having to scroll though even simple queries by putting every individual concept on a different line.

# ? Oct 26, 2009 17:48

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Dietrich posted:

Yes I too enjoy having to scroll though even simple queries by putting every individual concept on a different line.

I guess there has to be a happy medium between compact formatting that's hard to read and near-vertical formatting that's easy to read but takes up a lot of space. But I'd prefer easy to read most times. My preferred formatting for that query would be the following:

code:

SELECT
    Table1.Column1,
    Table1.Column2,
    Table2.Column1,
    Table3.Column1 
FROM
    Table1
    INNER JOIN Table2 ON Table1.FK1 = Table2.FK1 
    INNER JOIN Table3 ON Table1.FK2 = Table3.FK2

# ? Oct 27, 2009 00:16

wigga please: Nov 1, 2006; by mons all madden

If you don't mind I got another question:

I'm trying to so a search with optional parameters (search term, specific brand/category/supplier) on the same database, but I'm unclear on how to pass some sort of ANY or '*' value when say, I want all products from department x but I need all brands and all suppliers. I could just load in everything and apply a filter in my application, but it seems like unnecessary traffic to the server.

Here's what I got so far:

code:

USE [POSDB]
GO
/****** Object:  StoredProcedure [dbo].[spFindProducts]    Script Date: 10/28/2009 10:20:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spFindProducts]
@brand INT,
@category INT,
@subcategory INT,
@supplier INT,
@description NVARCHAR
AS
BEGIN
	SELECT Products.code, Products.description, Products.price_in, Products.price_out,
		Products.barcode, Products.EAN, Brands.name AS brand, Category.name AS Category, 
		SubCategory.name AS Subcategory, Extra_Taxes.name AS Extrataxes, Extra_Taxes.amount, 
		Tax_Rates.name AS Taxrate, Tax_Rates.rate, Supplier.name 
	FROM Products 
	LEFT OUTER JOIN Brands ON Products.brand = Brands.id 
	LEFT OUTER JOIN Category ON Products.category = Category.id
	LEFT OUTER JOIN SubCategory ON Products.subcategory = Subcategory.id
	LEFT OUTER JOIN Extra_Taxes ON Products.extra_tax = Extra_Taxes.id
	LEFT OUTER JOIN Tax_Rates ON Products.taxrate = Tax_Rates.id
	LEFT OUTER JOIN Supplier ON Products.supplier = Supplier.id
	WHERE Products.description LIKE '%' + @description + '%'
		AND (Products.brand = @brand)
		AND (Products.category = @category)
		AND (Products.subcategory = @subcategory)
		AND (Products.supplier = @supplier)
END

Can I somehow solve this by using "IS NULL" on the other fields?

# ? Oct 28, 2009 12:08

Kaiho: Dec 2, 2004

Hi. I really hope you guy don't mind an Access question from a complete and utter newbie...

I've got two tables in Access, "old" and "new". These tables contain information in exactly the same format. Some of the entries in the new table are identical to entries in the old one. I am looking to find these entries and replace three fields in the new table with the corresponding entries

It looks a little like this (there are more fields than these but the citation field is the most relevant):

Table "Old"

code:

Citation          Notes         Other

blah blah         hello         mike check one two
doop doop

Table "New"

code:

Citation          Notes         Other

new stuff
other things
blah blah

Where the citation entries match (in the above example, at blah blah) I'd like to place the contents of the "Notes" and "Other" fields from "Old" into the new table. (So that in "New", blah blah will have hello and mike check one two appended to the other fields) I assume this is doable in Access (and quite easily at that). What sort of query should I run?

Thanks. I am trying to figure out access and am starting literally from scratch.

# ? Oct 28, 2009 13:33

Sub Par: Jul 18, 2001; Dinosaur Gum

Kaiho posted:

Where the citation entries match (in the above example, at blah blah) I'd like to place the contents of the "Notes" and "Other" fields from "Old" into the new table. (So that in "New", blah blah will have hello and mike check one two appended to the other fields) I assume this is doable in Access (and quite easily at that). What sort of query should I run?

Thanks. I am trying to figure out access and am starting literally from scratch.

You want to do an update query. When updating multiple fields at once, I strongly recommend you steer clear of the Access query building drag-and-drop thing. It doesn't do what you think it does. Here's what you need:

code:

UPDATE Old, New
SET New.Notes = Old.Notes, New.Other = Old.Other
WHERE New.Citation = Old.Citation;

Sub Par fucked around with this message at 14:38 on Oct 28, 2009

# ? Oct 28, 2009 14:26

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

wigga please posted:

If you don't mind I got another question:

I'm trying to so a search with optional parameters (search term, specific brand/category/supplier) on the same database, but I'm unclear on how to pass some sort of ANY or '*' value when say, I want all products from department x but I need all brands and all suppliers. I could just load in everything and apply a filter in my application, but it seems like unnecessary traffic to the server.

Can I somehow solve this by using "IS NULL" on the other fields?

I might be misunderstanding what you're doing, but can't you assign default values when assigning the variables before the select, and then responding based on what gets put into the proc?

psuedo example:

code:

@category int = 0

(other stuff)

IF @category = 0 
-- do something for default/no category
ELSE
-- do something for provided category

SELECT etc.

In the above case category will be 0 if nothing is provided to the proc in the first place. Are you trying to identify the case of when a category is not supplied, or figure out how to select any category when it's not supplied? If the latter you can do NULL/NOT NULL checking, but it'll depend on your constraints. For example, does every brand have a category or vice versa. Given that you're using outer joins that's probably not the case.

In the past I've faked this out with multiple procs/switch statements depending on what's provided, but I'm pretty sure that's not the most efficient way. You should probably identify all of your iterations and it'll make more sense depending on how many cases there are (e.g. sp_FindProductsByCategory,FindProductsByBrand, etc.). The multi-proc approach works better for me when you make them functions and call them from your 'master' search proc, and, for example, UNION them.

Please note: I'm responding from a developer standpoint and not an efficiency guru standpoint.

# ? Oct 28, 2009 21:26

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

MySQL question here:

Let's say I've got a table with 52 columns, named COLUMN1, COLUMN2, etc. Each row of this table has data for each column, but for some rows COLUMN1 could correspond to 12/1/2009 and for other rows COLUMN1 could correspond to 10/15/2009.

For each row, the amount of time between COLUMN1 and COLUMN2 could be 1 week or 1 month.

First of all, is this type of thing commonly done? Does it have a name where I can read about working with data like this?

I'm trying to do things like viewing weekly data at a monthly or quarterly scale, and I'm running into all kinds of weird situations and need some direction on this.

# ? Oct 28, 2009 23:43

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

MySQL question here:

Let's say I've got a table with 52 columns, named COLUMN1, COLUMN2, etc. Each row of this table has data for each column, but for some rows COLUMN1 could correspond to 12/1/2009 and for other rows COLUMN1 could correspond to 10/15/2009.

For each row, the amount of time between COLUMN1 and COLUMN2 could be 1 week or 1 month.

First of all, is this type of thing commonly done? Does it have a name where I can read about working with data like this?

I'm trying to do things like viewing weekly data at a monthly or quarterly scale, and I'm running into all kinds of weird situations and need some direction on this.

This sounds insane hope this helps.

# ? Oct 29, 2009 00:22

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

fletcher posted:

MySQL question here:

Let's say I've got a table with 52 columns, named COLUMN1, COLUMN2, etc. Each row of this table has data for each column, but for some rows COLUMN1 could correspond to 12/1/2009 and for other rows COLUMN1 could correspond to 10/15/2009.

For each row, the amount of time between COLUMN1 and COLUMN2 could be 1 week or 1 month.

First of all, is this type of thing commonly done? Does it have a name where I can read about working with data like this?

I'm trying to do things like viewing weekly data at a monthly or quarterly scale, and I'm running into all kinds of weird situations and need some direction on this.

What Sprawl said. So your rows look like DATE1, DATE2 (which is later than DATE1), DATA_1, DATA_2, DATA_N? Or is this something more sinister and insane?

# ? Oct 29, 2009 02:52

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

fletcher posted:

MySQL question here:

Let's say I've got a table with 52 columns, named COLUMN1, COLUMN2, etc. Each row of this table has data for each column, but for some rows COLUMN1 could correspond to 12/1/2009 and for other rows COLUMN1 could correspond to 10/15/2009.

For each row, the amount of time between COLUMN1 and COLUMN2 could be 1 week or 1 month.

First of all, is this type of thing commonly done? Does it have a name where I can read about working with data like this?

I'm trying to do things like viewing weekly data at a monthly or quarterly scale, and I'm running into all kinds of weird situations and need some direction on this.

Tell the people in charge they don't want a database, they want a calendar.

I think I get what they're trying to do, which is have an accessible interval based system, that has flexible intervals. But unless there's some other compelling reason I don't see why they did it with columns instead of rows and various calculate options.

How many rows are involved? Is each column time stamped, or does column2-column=1 week | 1 month occur randomly?

# ? Oct 29, 2009 03:57

Kaiho: Dec 2, 2004

Sub Par posted:

You want to do an update query. When updating multiple fields at once, I strongly recommend you steer clear of the Access query building drag-and-drop thing. It doesn't do what you think it does. Here's what you need:

Holy wow, thanks!
I knew it must be a simple thing but didn't realise it'd be quite that simple. Thanks again!

# ? Oct 29, 2009 10:32

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Markoff Chaney posted:

What Sprawl said. So your rows look like DATE1, DATE2 (which is later than DATE1), DATA_1, DATA_2, DATA_N? Or is this something more sinister and insane?

Kinda. Each row has a date assigned to the first DATA_1 column, and a scale (weekly or monthly) that indicates how far away DATA_2 is from it.

Scaramouche posted:

How many rows are involved? Is each column time stamped, or does column2-column=1 week | 1 month occur randomly?

100s of millions

# ? Oct 29, 2009 16:17

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

Kinda. Each row has a date assigned to the first DATA_1 column, and a scale (weekly or monthly) that indicates how far away DATA_2 is from it.

100s of millions

This sounds like it was bad data planning from the beginning. I would refactor the whole thing hth.

So you have Date1, Value1, scale1 as seperate columns? and then the same for date2,3 etc.?

# ? Oct 29, 2009 17:01

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Sprawl posted:

This sounds like it was bad data planning from the beginning. I would refactor the whole thing hth.

So you have Date1, Value1, scale1 as seperate columns? and then the same for date2,3 etc.?

No, one date and one scale for each row.

# ? Oct 29, 2009 17:08

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

fletcher posted:

MySQL question here:

Let's say I've got a table with 52 columns, named COLUMN1, COLUMN2, etc. Each row of this table has data for each column, but for some rows COLUMN1 could correspond to 12/1/2009 and for other rows COLUMN1 could correspond to 10/15/2009.

For each row, the amount of time between COLUMN1 and COLUMN2 could be 1 week or 1 month.

First of all, is this type of thing commonly done? Does it have a name where I can read about working with data like this?

I'm trying to do things like viewing weekly data at a monthly or quarterly scale, and I'm running into all kinds of weird situations and need some direction on this.

fletcher posted:

Kinda. Each row has a date assigned to the first DATA_1 column, and a scale (weekly or monthly) that indicates how far away DATA_2 is from it.

100s of millions

What on earth does "Each row has a date assigned to the first DATA_1 column" mean? When you say assigned, that sounds like it means DATA_1 is where that date is stored. But from the context of your explanation, I think you mean that DATA_1 has actual data, and the "start date" and scale (and presumably other identifying information) are stored in other columns in the row that you didn't mention.

So to clarify:

Each row has a start date, a scale, and then the 52 data columns. These 52 data columns hold data, like an account balance or something like that, for 52 weeks or months, where the DATA_1 column corresponds to the balance on the start date, DATA_2 is the balance for the next period, etc. Is this correct?

That sounds kinda like a pivot table or query.

# ? Oct 29, 2009 17:08

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Jethro posted:

What on earth does "Each row has a date assigned to the first DATA_1 column" mean? When you say assigned, that sounds like it means DATA_1 is where that date is stored. But from the context of your explanation, I think you mean that DATA_1 has actual data, and the "start date" and scale (and presumably other identifying information) are stored in other columns in the row that you didn't mention.

So to clarify:

Each row has a start date, a scale, and then the 52 data columns. These 52 data columns hold data, like an account balance or something like that, for 52 weeks or months, where the DATA_1 column corresponds to the balance on the start date, DATA_2 is the balance for the next period, etc. Is this correct?

That sounds kinda like a pivot table or query.

Ya! That sounds exactly right.

# ? Oct 29, 2009 17:31

unixbeard: Dec 29, 2004

hello, im trying to aggregate daily share price data to weekly periods using mysql

the data looks like this

code:

+----+--------+---------------------+--------+--------+--------+--------+------+----------+
| id | code   | date                | open   | high   | low    | close  | vol  | adjclose |
+----+--------+---------------------+--------+--------+--------+--------+------+----------+
|  1 | ASX200 | 2001-10-17 00:00:00 | 3210.7 | 3237.9 | 3209.3 | 3237.9 |    0 |   3237.9 | 
|  2 | ASX200 | 2001-10-18 00:00:00 | 3237.9 | 3237.9 | 3183.1 | 3199.6 |    0 |   3199.6 | 
|  3 | ASX200 | 2001-10-19 00:00:00 | 3199.6 | 3199.6 | 3172.4 | 3175.4 |    0 |   3175.4 | 
|  4 | ASX200 | 2001-10-23 00:00:00 |   3180 |   3218 |   3180 | 3213.3 |    0 |   3213.3 | 
|  5 | ASX200 | 2001-10-24 00:00:00 | 3213.3 | 3251.1 | 3202.8 |   3246 |    0 |     3246 | 
|  6 | ASX200 | 2001-10-25 00:00:00 |   3246 | 3258.6 | 3241.6 | 3254.7 |    0 |   3254.7 | 
|  7 | ASX200 | 2001-10-26 00:00:00 | 3254.7 | 3285.9 | 3254.7 | 3276.2 |    0 |   3276.2 | 
|  8 | ASX200 | 2001-10-29 00:00:00 | 3276.2 | 3284.1 | 3256.2 | 3256.2 |    0 |   3256.2 | 
|  9 | ASX200 | 2001-10-30 00:00:00 | 3256.2 | 3256.2 | 3232.1 | 3252.8 |    0 |   3252.8 | 
| 10 | ASX200 | 2001-10-31 00:00:00 | 3252.8 | 3252.8 | 3228.9 | 3249.6 |    0 |   3249.6 | 
+----+--------+---------------------+--------+--------+--------+--------+------+----------+

The query so far is this

code:

select date, open, MAX(high), MIN(low), close from period_data where code='ASX200' group by YEAR(date), WEEK(date) limit 3;
+---------------------+--------+-----------+----------+--------+
| date                | open   | MAX(high) | MIN(low) | close  |
+---------------------+--------+-----------+----------+--------+
| 2001-10-17 00:00:00 | 3210.7 |    3237.9 |   3172.4 | 3237.9 | 
| 2001-10-23 00:00:00 |   3180 |    3285.9 |     3180 | 3213.3 | 
| 2001-10-29 00:00:00 | 3276.2 |    3284.1 |   3228.9 | 3256.2 | 
+---------------------+--------+-----------+----------+--------+
3 rows in set (0.14 sec)

The problem is I need the first open and the last close for the week. MySQL doesn't seem to have first or last functions.

I could wrap the open/close columns in something like CASE(DAYOFWEEK(date) WHEN 2 THEN open ELSE 0 END) but, it's not a given that the first day of trading will be a monday (dayofweek == 2) or that the last day is a friday for the close.

any ideas?

[edit] thinking about it, i really just need it for the close. i still have the problem that it might be a wednesday/thursday or friday

unixbeard fucked around with this message at 12:10 on Nov 1, 2009

# ? Nov 1, 2009 11:02

Vince McMahon: Dec 18, 2003

unixbeard posted:

hello, im trying to aggregate daily share price data to weekly periods using mysql

the data looks like this

code:

+----+--------+---------------------+--------+--------+--------+--------+------+----------+
| id | code   | date                | open   | high   | low    | close  | vol  | adjclose |
+----+--------+---------------------+--------+--------+--------+--------+------+----------+
|  1 | ASX200 | 2001-10-17 00:00:00 | 3210.7 | 3237.9 | 3209.3 | 3237.9 |    0 |   3237.9 | 
|  2 | ASX200 | 2001-10-18 00:00:00 | 3237.9 | 3237.9 | 3183.1 | 3199.6 |    0 |   3199.6 | 
|  3 | ASX200 | 2001-10-19 00:00:00 | 3199.6 | 3199.6 | 3172.4 | 3175.4 |    0 |   3175.4 | 
|  4 | ASX200 | 2001-10-23 00:00:00 |   3180 |   3218 |   3180 | 3213.3 |    0 |   3213.3 | 
|  5 | ASX200 | 2001-10-24 00:00:00 | 3213.3 | 3251.1 | 3202.8 |   3246 |    0 |     3246 | 
|  6 | ASX200 | 2001-10-25 00:00:00 |   3246 | 3258.6 | 3241.6 | 3254.7 |    0 |   3254.7 | 
|  7 | ASX200 | 2001-10-26 00:00:00 | 3254.7 | 3285.9 | 3254.7 | 3276.2 |    0 |   3276.2 | 
|  8 | ASX200 | 2001-10-29 00:00:00 | 3276.2 | 3284.1 | 3256.2 | 3256.2 |    0 |   3256.2 | 
|  9 | ASX200 | 2001-10-30 00:00:00 | 3256.2 | 3256.2 | 3232.1 | 3252.8 |    0 |   3252.8 | 
| 10 | ASX200 | 2001-10-31 00:00:00 | 3252.8 | 3252.8 | 3228.9 | 3249.6 |    0 |   3249.6 | 
+----+--------+---------------------+--------+--------+--------+--------+------+----------+

The query so far is this

code:

select date, open, MAX(high), MIN(low), close from period_data where code='ASX200' group by YEAR(date), WEEK(date) limit 3;
+---------------------+--------+-----------+----------+--------+
| date                | open   | MAX(high) | MIN(low) | close  |
+---------------------+--------+-----------+----------+--------+
| 2001-10-17 00:00:00 | 3210.7 |    3237.9 |   3172.4 | 3237.9 | 
| 2001-10-23 00:00:00 |   3180 |    3285.9 |     3180 | 3213.3 | 
| 2001-10-29 00:00:00 | 3276.2 |    3284.1 |   3228.9 | 3256.2 | 
+---------------------+--------+-----------+----------+--------+
3 rows in set (0.14 sec)

code:

SELECT
t1.date as week_start,
t1.start_open as week_open,
t2.end_open as week_close,
t2.date as week_end
FROM period_data
INNER JOIN (SELECT open as start_open, WEEK(date), date FROM period_data) AS t1 ON WEEK(t1.date) = WEEK(period_data.date)
INNER JOIN (SELECT open as end_open, WEEK(date), date FROM period_data ORDER BY date DESC) AS t2 ON WEEK(t2.date) = WEEK(period_data.date)
where code= 'ASX200' 
GROUP BY YEAR(period_data.date), WEEK(period_data.date)
LIMIT 3;

This is a bit of an ugly way to do it, but it seems to work so you can try simplifying it from there. It feels like you don't need two joins but I'm having trouble thinking a way around it. I imagine it would be horribly slow too.

# ? Nov 1, 2009 13:19

unixbeard: Dec 29, 2004

hmm yeah, thanks. do you know if theres anything like this for mysql? http://wiki.postgresql.org/wiki/Last_(aggregate)

i might even switch to postgresql, as i also need something akin to pivot tables which mysql doesn't really have.

unixbeard fucked around with this message at 16:54 on Nov 2, 2009

# ? Nov 2, 2009 16:44

ray2k: Feb 7, 2004; Puppet scum beware!

Need some help with Sql Server 2005 clustering.

At work I'm trying to deploy an app that is migrating from Sql 2000 to a 3-node clustered Sql 2005 setup. My cursory understanding is that there is a 'virtual name' that the cluster listens on and intermediates connections between the available nodes in the cluster. Is that correct? If so, how does that affect my connection string settings at the application level. Let's say the virtual name is CLUSTER and the nodes are CLUSTER01\INST01, CLUSTER02\INST01, CLUSTER03\INST01, would my connection string just have Data Source=CLUSTER ? Would specifying a specific node\instance defeat the point of clustering to begin with or does it work regardless?

Also I'm getting pushback from stupid dba team but I have server login + sysadmin rights so what nonintuitive management utility do I have to bring up to actually determine the cluster's virtual name?

EDIT: It appears my understanding of clustering was flawed, it's for high availablity/failover only and no real balancing so i should just be using SERVER\INSTANCE that was given to me. :doh:

ray2k fucked around with this message at 00:23 on Nov 4, 2009

# ? Nov 3, 2009 23:24

badatshess4ever: May 21, 2005

I apologize if this has been asked in some form, I read through a few pages but can't go through all 71. I took a database class in college and have some experience with querying, but none actually building databases..

Anyways, I want to build a database that includes lots of information that is easy to access on the web. This includes how the stock market did each day, the weather in a certain city each day, average gas prices, etc. My question is, is there a way to have this information automatically added to the database, and not manually added? Thank you.

# ? Nov 4, 2009 01:51

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

badatshess4ever posted:

I apologize if this has been asked in some form, I read through a few pages but can't go through all 71. I took a database class in college and have some experience with querying, but none actually building databases..

Anyways, I want to build a database that includes lots of information that is easy to access on the web. This includes how the stock market did each day, the weather in a certain city each day, average gas prices, etc. My question is, is there a way to have this information automatically added to the database, and not manually added? Thank you.

Nice troll

# ? Nov 4, 2009 01:58

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

badatshess4ever posted:

I apologize if this has been asked in some form, I read through a few pages but can't go through all 71. I took a database class in college and have some experience with querying, but none actually building databases..

Anyways, I want to build a database that includes lots of information that is easy to access on the web. This includes how the stock market did each day, the weather in a certain city each day, average gas prices, etc. My question is, is there a way to have this information automatically added to the database, and not manually added? Thank you.

On the off chance that you're not trolling, someone already did this.

# ? Nov 4, 2009 10:46

Little Brittle: Nov 1, 2004; Come visit me dawg

How can I run a MySQL select that searches a column for names that do not start with an alphanumeric character? Here is a terrible example of what I'm trying to do:

code:

SELECT * FROM things WHERE name !LIKE '%(a-zA-Z0-9)'

# ? Nov 6, 2009 20:37

InAndOutBrennan: Dec 11, 2008

It's MySQL, not my forte, but you could try REGEXP and see if it's faster?

Something like:

code:

SELECT * FROM things WHERE name REGEXP '^[^A-Za-z0-9]'

It's a query for which SQL is not very well suited.

Is performance an issue?

# ? Nov 6, 2009 20:44

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Little Brittle posted:

How can I run a MySQL select that searches a column for names that do not start with an alphanumeric character? Here is a terrible example of what I'm trying to do:
code:
SELECT * FROM things WHERE name !LIKE '%(a-zA-Z0-9)'

IIRC numbers and letters go after them so a

code:

SELECT * FROM things WHERE name < '1%'

should work

# ? Nov 6, 2009 20:46

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Little Brittle posted:

How can I run a MySQL select that searches a column for names that do not start with an alphanumeric character? Here is a terrible example of what I'm trying to do:
code:
SELECT * FROM things WHERE name !LIKE '%(a-zA-Z0-9)'

SELECT * FROM things WHERE LEFT(name, 1) NOT REGEXP '[a-z0-9]'

(Maybe this isn't the most efficient way of doing what you want to do, mind you)

# ? Nov 6, 2009 20:50

Little Brittle: Nov 1, 2004; Come visit me dawg

All of the above solutions work, thanks. I'll do some benchmarking to see which one is quickest.

# ? Nov 6, 2009 21:12

nbv4: Aug 21, 2002; by Duchess Gummybuns

How do you copy a postgis database from one server to another? One the old server, I'm using postgres 8.3 and postgis 1.3.x. On the new server I'm using 8.4 and postgis 1.4.0.

Is there a way to do pg_dump without including the postgis stuff?

# ? Nov 7, 2009 19:50

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 09:16

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

I'm doing this to an InnoDB table:

code:

INSERT INTO logins 
  (lo_cookie, lo_created, lo_ip)
  VALUES ("something","whatever","eyepee")
  ON DUPLICATE KEY UPDATE 
    lo_created = VALUES(lo_created), lo_ip = VALUES(lo_ip)

where lo_cookie is a unique key. However, on the first duplicate, the update clause is causing an insert with a blank lo_cookie. Every subsequent update causes that new second row to be updated.

It doesn't matter if I tell it to update the lo_cookie in the 'on duplicate' clause, it still creates and updates a new row with a blank lo_cookie.

That not what's supposed to happen, right?

# ? Nov 9, 2009 22:20

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

«‹›303 »