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 »

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Doing it in PHP would probably be the easiest, otherwise you'd have to do something retarded like

code:

SELECT min(Date) FROM statushistory WHERE Date > 
  (SELECT Date FROM statushistory WHERE Status <> 
    (SELECT Status FROM statushistory ORDER BY Date DESC LIMIT 1) LIMIT 1)

(this probably won't work)

# ? Oct 19, 2010 22:55

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 18:39

DholmbladRU: May 4, 2006

I created a website for my fraternity. I was wondering if there is a way to order database output in the greek alphabet, for example alpha, beta, gamma ect. Currently it is being output in alphabetic order.

# ? Oct 20, 2010 05:32

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Golbez posted:

Let's say I have a status history table...

Totally doable. I think. This will use MySQL's don't-have-to-GROUP BY-all-the-columns logic, which may not work in not-MySQL databases. You did not specify which SQL server you're using, but you're using PHP, and PHP and MySQL go together like Dumb and Dumber.

code:

SELECT `User ID`, `Status`, `Date`, Newest_Data.*
  FROM Status_History,
       (SELECT `User ID` AS nd_uid, `Status` AS nd_status, `Date` AS nd_date
          FROM Status_History
         GROUP BY `User ID`
         ORDER BY `Date` DESC
        ) AS Newest_Data
 WHERE Newest_Data.nd_uid = Status_History.`User ID`
   AND Newest_Data.nd_date > Status_History.`Date`
 GROUP BY `User ID`
 ORDER BY `Date` DESC

What this should give you is two sets of columns. The right-most set will be each user's newest status, and the date that status was given. The left-most set will be the user's immediate last status, and it's date. Once it's shown to work, you can add on a Status_History.`Status` = Newest_Data.nd_status in the WHERE clause to only catch those with two updates of the same status in a row.

# ? Oct 20, 2010 05:40

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

McGlockenshire posted:

Totally doable. I think. This will use MySQL's don't-have-to-GROUP BY-all-the-columns logic, which may not work in not-MySQL databases. You did not specify which SQL server you're using, but you're using PHP, and PHP and MySQL go together like Dumb and Dumber.
code:
SELECT `User ID`, `Status`, `Date`, Newest_Data.*
  FROM Status_History,
       (SELECT `User ID` AS nd_uid, `Status` AS nd_status, `Date` AS nd_date
          FROM Status_History
         GROUP BY `User ID`
         ORDER BY `Date` DESC
        ) AS Newest_Data
 WHERE Newest_Data.nd_uid = Status_History.`User ID`
   AND Newest_Data.nd_date > Status_History.`Date`
 GROUP BY `User ID`
 ORDER BY `Date` DESC
What this should give you is two sets of columns. The right-most set will be each user's newest status, and the date that status was given. The left-most set will be the user's immediate last status, and it's date. Once it's shown to work, you can add on a Status_History.`Status` = Newest_Data.nd_status in the WHERE clause to only catch those with two updates of the same status in a row.

Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? :v:

Yes, PHP/MySQL, I keep forgetting to mention my DB here.

# ? Oct 20, 2010 14:49

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Golbez posted:

Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row?

Uh, yeah, dunno about that one. Time to break out the PHP.

# ? Oct 20, 2010 17:50

InAndOutBrennan: Dec 11, 2008

Golbez posted:

Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Yes, PHP/MySQL, I keep forgetting to mention my DB here.

I haven't given it lots of thought but in SQL Server I'd start looking at breaking out the cursors or do it clientside.

Or look at the application that inputs the data and make it just update the date if the status is the same.

# ? Oct 20, 2010 18:14

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Golbez posted:

Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Yes, PHP/MySQL, I keep forgetting to mention my DB here.

I think if you do something like my last query it would work.
Innermost subquery:
SELECT Status FROM Status_History ORDER BY Date DESC LIMIT 1
this gives you the most current Status

Middle subquery:
SELECT Date FROM Status_History WHERE Status <> [Innermost subquery] ORDER BY Date DESC LIMIT 1
This gives you the date of the next most current Status

Outer query:
SELECT Date FROM Status_History WHERE Date > [Middle subquery] ORDER BY Date ASC LIMIT 1
This should give you the next most current date after the second Status, which I think is what you're looking for.

code:

SELECT Date
 FROM Status_History
 WHERE Date > 
  (SELECT Date 
  FROM Status_History
  WHERE Status <>
   (SELECT Status
    FROM Status_History
    ORDER BY Date DESC
    LIMIT 1)
  ORDER BY Date DESC
  LIMIT 1)
 ORDER BY Date ASC
 LIMIT 1

You'll have to add User Id in all the WHERE statements if you want to filter by that.

# ? Oct 20, 2010 18:33

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

DholmbladRU posted:

I created a website for my fraternity. I was wondering if there is a way to order database output in the greek alphabet, for example alpha, beta, gamma ect. Currently it is being output in alphabetic order.

You might be able to do this if you mess around with location/language encoding set but that would be a Bad Idea in my opinion.

The greek letters are basically extended characters, and probably go up incrementally anyway. You might be able to do something like convert the first letter(s) to their ascii value and sort that way. Otherwise it sounds like something you'd have to do on the client.

# ? Oct 20, 2010 18:44

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

You could make a table to hold greek letters, and have foreign keys to that table. Then ordering by would be a case of ordering by the foreign key columns.

If MySQL,

code:

CREATE TABLE GreekLetter (
    LetterID TINYINT UNSIGNED NOT NULL,
    LetterName CHAR(7) NOT NULL,
    LetterLowercase CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    LetterUppercase CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (LetterID)
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

INSERT INTO GreekLetter
    (LetterID, LetterName, LetterLowercase, LetterUppercase)
    VALUES
    (1, 'alpha', '&#945;', '&#913;'),
    ...,
    (24, 'omega', '&#937;', '&#969;')

CREATE TABLE Fraternity (
    FraternityID INT UNSIGNED NOT NULL,
    Letter1 TINYINT UNSIGNED NOT NULL,
    Letter2 TINYINT UNSIGNED NOT NULL,
    Letter3 TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (FraternityID),
    CONSTRAINT "Constr_Fraternity_Letter1_fk"
        FOREIGN KEY "Letter1_fk" ("Letter1") REFERENCES "GreekLetter" ("LetterID")
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT "Constr_Fraternity_Letter2_fk"
        FOREIGN KEY "Letter2_fk" ("Letter2") REFERENCES "GreekLetter" ("LetterID")
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT "Constr_Fraternity_Letter3_fk"
        FOREIGN KEY "Letter3_fk" ("Letter3") REFERENCES "GreekLetter" ("LetterID")
        ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

Or maybe the FraternityID column is unnecessary and you can use (Letter1, Letter2, Letter3) as the primary key, I don't know.

PS I can't actually use Greek letters in the above code because the forums are dumb. You might add extra columns for things like HTML entities and so on.

# ? Oct 20, 2010 19:57

DholmbladRU: May 4, 2006

I will take a look at that when I have time, thanks. I wont be using greek letters, but the words spelled out. so alpha, beta.. ect/.

# ? Oct 21, 2010 03:46

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

A couple of database design questions:

1) Phone numbers (American only, 10 digits) and Social Security Numbers. Should these be stored with or without dashes? Right now I'm doing it with dashes, validating input along the way.

2) Totals. Right now, we monitor transactions of adding or subtracting funds to a person's account. And right now, to get the total funds in an account, I sum up all of their transactions. And I was wondering if perhaps there was a better way of doing it. Maintaining it in a field in the user's record. But then I worry about when a transaction is made and, for whatever reason, the user's record isn't updated. MyISAM tables in a MySQL database. Any thoughts, or should I just keep it how it is?

Edit: In a similar vein, the status updates I wrote about earlier. Right now, to find someone's status, I simply grab the most recent status message from the status history table. Should that be stored as well, or keep it 'dynamic' like this?

Golbez fucked around with this message at 22:12 on Oct 21, 2010

# ? Oct 21, 2010 22:04

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Golbez posted:

A couple of database design questions:

1) Phone numbers (American only, 10 digits) and Social Security Numbers. Should these be stored with or without dashes? Right now I'm doing it with dashes, validating input along the way.

2) Totals. Right now, we monitor transactions of adding or subtracting funds to a person's account. And right now, to get the total funds in an account, I sum up all of their transactions. And I was wondering if perhaps there was a better way of doing it. Maintaining it in a field in the user's record. But then I worry about when a transaction is made and, for whatever reason, the user's record isn't updated. MyISAM tables in a MySQL database. Any thoughts, or should I just keep it how it is?

I don't store any formatting in the database, and phone numbers are a pain in the rear end especially because there are so many goddamn ways that people format them. If you just store digits, you can preg_replace('/[^\d]/','',$phone) and validate it that way. As for the transactions, if the calculation is trivial, you might as well do it every time. You could make a view that has the calculated total as one of the columns.

# ? Oct 21, 2010 22:14

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

I don't store any formatting in the database, and phone numbers are a pain in the rear end especially because there are so many goddamn ways that people format them. If you just store digits, you can preg_replace('/[^\d]/','',$phone) and validate it that way. As for the transactions, if the calculation is trivial, you might as well do it every time. You could make a view that has the calculated total as one of the columns.

Internal app, maybe 2 people ever using this system, and I validate numbers on a strict regex, so that's actually not too big an issue. Though, one might say the fact that I have to validate them that strictly is indicative of an issue.

# ? Oct 21, 2010 22:16

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

Golbez posted:

A couple of database design questions:

1) Phone numbers (American only, 10 digits) and Social Security Numbers. Should these be stored with or without dashes? Right now I'm doing it with dashes, validating input along the way.

In general, I tend to advocate "unless you use actual numeric properties of the data, or real profiling indicates that doing otherwise is a speed concern, you should store opaque data as text rather than numbers". Why? Consider postal codes: In some countries, postal codes contain letters. In some countries, they contain leading zeroes. You never need to do numeric operations like adding two postal codes or checking if one postal code is less than another; such operations are meaningless. So if you store them as text, you won't have to go change everything later when you decide to expand to international codes.

If you store your phone numbers as strings, your life will be easier when people want to start giving you "sip:example.com/John_Doe" as a phone number. This day may be many years in the future; it may never come at all. But the cost for planning ahead in this case is probably so low that it may as well be zero.

Golbez posted:

2) Totals. Right now, we monitor transactions of adding or subtracting funds to a person's account. And right now, to get the total funds in an account, I sum up all of their transactions. And I was wondering if perhaps there was a better way of doing it. Maintaining it in a field in the user's record. But then I worry about when a transaction is made and, for whatever reason, the user's record isn't updated. MyISAM tables in a MySQL database. Any thoughts, or should I just keep it how it is?

You probably shouldn't be tracking money in a MySQL database, especially one that isn't transactional.

In a real database, I'd suggest doing something like writing some stored procedures which simultaneously insert into the transaction table and update a cache of the balance in the user table. Then you only have one place you need to get the logic correct, and you don't have to worry about your application crashing midway through the process or something and leaving garbage data around.

# ? Oct 21, 2010 22:17

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

ShoulderDaemon posted:

You probably shouldn't be tracking money in a MySQL database, especially one that isn't transactional.

Does it make things better or worse that this is replacing an Access application?

# ? Oct 21, 2010 22:21

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

Golbez posted:

Does it make things better or worse that this is replacing an Access application?

Better. If you're writing a new application, is there any reason you have to be using MyISAM? Could you switch to postgres, or at least InnoDB?

# ? Oct 21, 2010 22:24

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

ShoulderDaemon posted:

Better. If you're writing a new application, is there any reason you have to be using MyISAM? Could you switch to postgres, or at least InnoDB?

MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM.

Fortunately, no money is moving through the system, so it doesn't have to be transactionally perfect; it's not like I'm debiting from one account and crediting another. I'm importing data from a weekly spreadsheet. It's record keeping and auditing.

# ? Oct 21, 2010 22:27

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Another question, probably simple but I'm having a mindfart.

Let's say I have...

pre:

User ID  | Status  | Date
--------------------------------
1        | AVAIL   | 1/1/2009
2        | AVAIL   | 1/5/2009
2        | NOT     | 2/2/2009
1        | NOT     | 3/3/2009
1        | AVAIL   | 4/4/2009
3        | NOT     | 5/5/2009
3        | AVAIL   | 6/6/2009

and I want to get the User IDs where AVAIL is the most recent status. So in this case, it would give me user IDs 1 and 3, but not 2.

# ? Oct 21, 2010 22:39

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!

Golbez posted:

MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM.

Fortunately, no money is moving through the system, so it doesn't have to be transactionally perfect; it's not like I'm debiting from one account and crediting another. I'm importing data from a weekly spreadsheet. It's record keeping and auditing.

You will likely want a "transaction" table that links to the user table that lists all of the balances etc thats and innodb and just set it up to show the newest kinda thing in your sql.

# ? Oct 21, 2010 22:42

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

Golbez posted:

MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM.

What's the reasoning behind that? AFAIK there are only a few special usage scenarios for which MyISAM is more appropriate than InnoDB. For something like what you are describing I really don't know why you would use MyISAM.

# ? Oct 21, 2010 22:55

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

What's the reasoning behind that? AFAIK there are only a few special usage scenarios for which MyISAM is more appropriate than InnoDB. For something like what you are describing I really don't know why you would use MyISAM.

Because that's how it's been done and I just got here a few months ago and am still technically a temp? v :shobon:

v

Though, since this is a project that is 99% independent from the rest of the site, I could probably get away with it without anyone knowing.

# ? Oct 21, 2010 22:56

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!

Golbez posted:

Another question, probably simple but I'm having a mindfart.

Let's say I have...
pre:
User ID  | Status  | Date
--------------------------------
1        | AVAIL   | 1/1/2009
2        | AVAIL   | 1/5/2009
2        | NOT     | 2/2/2009
1        | NOT     | 3/3/2009
1        | AVAIL   | 4/4/2009
3        | NOT     | 5/5/2009
3        | AVAIL   | 6/6/2009
and I want to get the User IDs where AVAIL is the most recent status. So in this case, it would give me user IDs 1 and 3, but not 2.

This is the easiest thing i could come up with.

code:

CREATE TABLE `golbez` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


1	1	AVAIL	2009-01-01
4	1	NOT	2009-03-03
5	1	AVAIL	2009-04-04
2	2	AVAIL	2009-01-05
3	2	NOT	2009-02-02
6	3	NOT	2009-05-05
7	3	AVAIL	2009-06-06


select 
t1.userid,
t1.status,
if(t1.Date = (select Max(date) from golbez as t2 where t1.userid = t2.userid) and t1.status='AVAIL',1,NULL) as test,
Max(t1.date) as temp,
t1.date
from golbez as t1
group by id

output
1	AVAIL	NULL	2009-01-01	2009-01-01
2	AVAIL	NULL	2009-01-05	2009-01-05
2	NOT	NULL	2009-02-02	2009-02-02
1	NOT	NULL	2009-03-03	2009-03-03
1	AVAIL	1	2009-04-04	2009-04-04
3	NOT	NULL	2009-05-05	2009-05-05
3	AVAIL	1	2009-06-06	2009-06-06

Finalized sql

select 
t1.userid,
t1.status,
if(t1.Date = (select Max(date) from golbez as t2 where t1.userid = t2.userid) and t1.status='AVAIL',1,NULL) as test,
Max(t1.date) as temp,
t1.date
from golbez as t1
group by id
having test = 1

1	AVAIL	1	2009-04-04	2009-04-04
3	AVAIL	1	2009-06-06	2009-06-06

# ? Oct 21, 2010 23:12

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Golbez posted:

MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM.

Fortunately, no money is moving through the system, so it doesn't have to be transactionally perfect; it's not like I'm debiting from one account and crediting another. I'm importing data from a weekly spreadsheet. It's record keeping and auditing.

If you do switch to InnoDB, make sure you look at the settings. It makes some kind of rollback that eats up a retarded amount of space with the default settings.

# ? Oct 21, 2010 23:20

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Sprawl, I have a question based on something from what you did, which looks like it might work but this bit is confusing me. Here's a query:

code:

select status_id, Max(status_date) from table where user_id = 1326

Now, looking at the data, I can see that the most recent status id is 12, and date is 5/30/2007. However, whenever I run this, or any variation of it I can think of, the status id returned is 4 - the earliest one in the table, so the first one that comes up. I've tried various groupings, order bys, havings... I simply cannot get it to say 12. What am I doing wrong?

Edit: And your query isn't working because of said issue. Could it be MySQL's fault?

Edit 2: OK, I swapped the inner query for a simple "select ... order by desc limit 1", and added a "where status = 12" to the outer query, and it's working now. Thanks

Golbez fucked around with this message at 15:47 on Oct 22, 2010

# ? Oct 22, 2010 14:53

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!

Golbez posted:

Sprawl, I have a question based on something from what you did, which looks like it might work but this bit is confusing me. Here's a query:
code:
select status_id, Max(status_date) from table where user_id = 1326
Now, looking at the data, I can see that the most recent status id is 12, and date is 5/30/2007. However, whenever I run this, or any variation of it I can think of, the status id returned is 4 - the earliest one in the table, so the first one that comes up. I've tried various groupings, order bys, havings... I simply cannot get it to say 12. What am I doing wrong?

Edit: And your query isn't working because of said issue. Could it be MySQL's fault?

Edit 2: OK, I swapped the inner query for a simple "select ... order by desc limit 1", and added a "where status = 12" to the outer query, and it's working now. Thanks

If it wasn't working you were doing it wrong and without a full explanation of what you were doing with exact query and table layout i can't do anything to fix your broken work.

# ? Oct 22, 2010 15:48

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Sprawl posted:

If it wasn't working you were doing it wrong and without a full explanation of what you were doing with exact query and table layout i can't do anything to fix your broken work.

Literally copying your query and changing the field names. That was it. Oho, wait - I was misreading your final 'group by id'; I was taking that to mean employee_id, you meant row id. OK, running that... oh hey it works :saddowns:

Sorry. So yes, your work was actually perfect and I thank you very much for it.

# ? Oct 22, 2010 15:58

MoNsTeR: Jun 29, 2002

Golbez posted:

A couple of database design questions:
<snip>
Edit: In a similar vein, the status updates I wrote about earlier. Right now, to find someone's status, I simply grab the most recent status message from the status history table. Should that be stored as well, or keep it 'dynamic' like this?

The current status should be stored on the entity to which it pertains. The history of that status should be stored in a separate table, typically trigger-populated (though you could use a procedure if you are allergic to triggers).

# ? Oct 22, 2010 16:32

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

MoNsTeR posted:

The current status should be stored on the entity to which it pertains. The history of that status should be stored in a separate table, typically trigger-populated (though you could use a procedure if you are allergic to triggers).

Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?)

# ? Oct 22, 2010 16:46

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!

Golbez posted:

Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?)

Storing calculated values if its a complex calculation, ie involving taxes, burdens, discounts etc. should likely be stored so you don't run into rounding issues.

# ? Oct 22, 2010 16:50

MoNsTeR: Jun 29, 2002

Golbez posted:

Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?)

You're not storing every change? That doesn't make any sense.

"Current state" is by no means a calculated value, it only seems that way because you're starting from a model of storing state changes as events rather than just... storing state. Now, sometimes an event model is appropriate. But if a thing has a state, just store it! It is "history of the record" because state is part of the record and that's what you're keeping history of.

9 times out of 10, in the actual transactional system (viz. reporting), you care about the current state. And when you do want the history, you just need to display it, not analyze or interact with it. Optimize for those use cases.

# ? Oct 23, 2010 00:22

Benson Cunningham: Dec 9, 2006; Chief of J.U.N.K.E.R. H.Q.

Ok, I'm beating my head against a wall here. I'm trying to make an sql update that will check the recipients table to see how many times a given message has been sent, and update the num_recipients value in the messages table with that number.

pre:

Table 1: messages
msgID          num_recipients
1              ?
2              ?
3              ?


Table 2: recipients
msgID    recipient
1          frank
1          ben
2          jesse

I keep doing things like this;

code:

UPDATE messages 
SET    num_recipients = (SELECT t 
                         FROM   (SELECT COUNT(recipients.msgid) AS t, 
                                        recipients.msgid 
                                 FROM   recipients, 
                                        messages 
                                 WHERE  messages.msgid = recipients.msgid 
                                 GROUP  BY recipients.msgid), 
                                recipients 
                         WHERE  messages.msgid = recipients.msgid);

Just not sure how to get the count of a given msgID back into the matching row on the messages table.

# ? Oct 23, 2010 01:52

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!

Like this?

code:

update messages m1 
set num_recipients = (select count(msgid) from recipiants r1 where m1.msgid = r1.msgid);

# ? Oct 23, 2010 02:14

Benson Cunningham: Dec 9, 2006; Chief of J.U.N.K.E.R. H.Q.

I didn't know you could alias the table you were working on in the update and then reference it within the statement. Thanks so much! (clearly I was over thinking the issue)

# ? Oct 23, 2010 02:40

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

MoNsTeR posted:

You're not storing every change? That doesn't make any sense.

"Current state" is by no means a calculated value, it only seems that way because you're starting from a model of storing state changes as events rather than just... storing state. Now, sometimes an event model is appropriate. But if a thing has a state, just store it! It is "history of the record" because state is part of the record and that's what you're keeping history of.

9 times out of 10, in the actual transactional system (viz. reporting), you care about the current state. And when you do want the history, you just need to display it, not analyze or interact with it. Optimize for those use cases.

By every change, I meant along the lines of, "On timestamp X, they changed the address from this to this. On timestamp Y, they changed this minor field in this minor table to this", etc. It would make no sense to generate a record from that. But generate a sum from a list of sums, or generate "current status" from the most recent status update, where past statuses do matter?

However, because of other new requirements, I have to end up generating dates from the most recent status, so the whole 'not storing a calculated value' thing is out the window.

# ? Oct 23, 2010 08:30

Gimpalimpa: Jun 27, 2004; Title text?

Roughly speaking I have data that looks like this:
userID,username,hits,date
1,bob,100,01/01/2010
2,bob,200,01/01/2010
3,jim,300,01/01/2010
4,bill,150,01/01/2010

But it's over 40 userid, 20 usernames and the last 4 years. I want to see a progression of the average hits per month that's based on the average hits per day of usernames that have a total of over 200 hits in a day.

Since I have so much raw data, I figured using a database would be the best way to crunch it. However, I'm pretty stuck. Am I using the wrong tool, or could someone explain how I would use a database to get what I want? I basically want to examine if people's stats are declining.

# ? Oct 25, 2010 01:49

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!

Gimpalimpa posted:

Roughly speaking I have data that looks like this:
userID,username,hits,date
1,bob,100,01/01/2010
2,bob,200,01/01/2010
3,jim,300,01/01/2010
4,bill,150,01/01/2010

But it's over 40 userid, 20 usernames and the last 4 years. I want to see a progression of the average hits per month that's based on the average hits per day of usernames that have a total of over 200 hits in a day.

Since I have so much raw data, I figured using a database would be the best way to crunch it. However, I'm pretty stuck. Am I using the wrong tool, or could someone explain how I would use a database to get what I want? I basically want to examine if people's stats are declining.

Thats mysql? and that date field is a date type? if so that's easy.

code:

select 
userid,
username,
Date_Format(date,'%Y-%m') as yearmonth,
count(hits) as hits
from YourTableHere
group by 
userid,
Date_Format(date,'%Y-%m')
order by 
userid,
Date_Format(date,'%Y-%m') asc

This will count the hits by year-month per userid

# ? Oct 25, 2010 07:16

baquerd: Jul 2, 2007; by FactsAreUseless

edit: nevermind, didn't see your table structure correctly

# ? Oct 25, 2010 07:19

Avarkx: Feb 11, 2003; :):):):):)

Gimpalimpa posted:

Roughly speaking I have data that looks like this:
userID,username,hits,date
1,bob,100,01/01/2010
2,bob,200,01/01/2010
3,jim,300,01/01/2010
4,bill,150,01/01/2010

But it's over 40 userid, 20 usernames and the last 4 years. I want to see a progression of the average hits per month that's based on the average hits per day of usernames that have a total of over 200 hits in a day.

Since I have so much raw data, I figured using a database would be the best way to crunch it. However, I'm pretty stuck. Am I using the wrong tool, or could someone explain how I would use a database to get what I want? I basically want to examine if people's stats are declining.

When I read what you're saying, I think this:

code:

SELECT	4					UserID,
	'Bill'					UserName,
	150					Hits,
	CAST( '01/01/2010' AS DATETIME )	AuditDate
INTO	t_WebStats
GO

INSERT INTO t_WebStats
		SELECT	1, 'Bob', 100, '01/01/2010'
UNION ALL	SELECT  2, 'Bob', 200, '01/01/2010'
UNION ALL	SELECT	3, 'Jim', 300, '01/01/2010'
UNION ALL	SELECT	1, 'Bob', 150, '01/15/2010'
UNION ALL	SELECT	2, 'Bob', 199, '01/15/2010'
UNION ALL	SELECT	3, 'Jim', 350, '01/15/2010'
UNION ALL	SELECT	4, 'Bill', 50, '01/15/2010'
UNION ALL	SELECT	1, 'Bob', 100, '02/01/2010'
UNION ALL	SELECT	2, 'Bob', 199, '02/01/2010'
UNION ALL	SELECT	3, 'Jim', 350, '02/01/2010'
UNION ALL	SELECT	4, 'Bill', 50, '02/01/2010'
UNION ALL	SELECT	1, 'Bob', 150, '02/15/2010'
UNION ALL	SELECT	2, 'Bob', 199, '02/15/2010'
UNION ALL	SELECT	3, 'Jim', 350, '02/15/2010'
UNION ALL	SELECT	4, 'Bill', 50, '02/15/2010'
UNION ALL	SELECT	1, 'Bob', 150, '03/01/2010'
UNION ALL	SELECT	2, 'Bob', 150, '03/01/2010'
UNION ALL	SELECT	3, 'Jim', 400, '03/01/2010'
UNION ALL	SELECT	4, 'Bill', 99, '03/01/2010'
UNION ALL	SELECT	1, 'Bob', 150, '03/15/2010'
UNION ALL	SELECT	2, 'Bob', 150, '03/15/2010'
UNION ALL	SELECT	3, 'Jim', 400, '03/15/2010'
UNION ALL	SELECT	4, 'Bill', 99, '03/15/2010'
GO

CREATE FUNCTION dbo.tusf_OrderID
(
	@userName	VARCHAR( 100 )
) 	RETURNS 	VARCHAR( 100 )
BEGIN
	DECLARE	@UIDs VARCHAR( 100 );
	SET	@UIDs = '';
	
	SELECT	@UIDs = @UIDs + ', ' + CAST( ws.UserID AS VARCHAR )
	FROM	t_WebStats ws
	WHERE	ws.UserName = @userName
	GROUP BY ws.UserID
	ORDER BY ws.UserID
	
	RETURN	CASE
		    WHEN LEN( @UIDs ) <= 2
		      THEN NULL
		    ELSE RIGHT( @UIDs, LEN( @UIDs ) - 2 )
		END
END
GO

SELECT	dbo.tusf_OrderID( hc.UserName )	UserIDs,
	hc.UserName,
	hc.DlyHitAvg,
	hc.MonthID
FROM (	SELECT	UserName,
		LEFT( CONVERT( VARCHAR, AuditDate, 112 ), 6 )	MonthID,
		CAST( SUM( Hits ) AS FLOAT ) 
		    / COUNT( DISTINCT AuditDate )		DlyHitAvg
	FROM	t_WebStats 
	GROUP BY UserName, LEFT( CONVERT( VARCHAR, AuditDate, 112 ), 6 ) ) hc
RIGHT JOIN (	SELECT	DISTINCT UserName
		FROM	t_WebStats
		GROUP BY UserName, AuditDate
		HAVING	SUM( Hits ) > 200 ) u
	ON	hc.UserName = u.UserName
ORDER BY UserIDs, hc.MonthID
GO

DROP FUNCTION dbo.tusf_OrderID
GO

DROP TABLE t_WebStats
GO

--------------------------------------------------------

 UserIDs	UserName	DlyHitAvg	MonthID
 1, 2		Bob		324.5		201001
 1, 2		Bob		324		201002
 1, 2		Bob		300		201003
 3		Jim		325		201001
 3		Jim		350		201002
 3		Jim		400		201003

Avarkx fucked around with this message at 14:43 on Oct 28, 2010

# ? Oct 28, 2010 13:37

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Best practices: Storing names. Right now, we store last name and first name. But I'm seeing some problems and inconsistencies. For example, sometimes there's a middle initial tacked to first name, and sometimes it even has a period after it. Sometimes "Jr." is after the last name, sometimes the first name. This obviously is not good.

What's the best way of storing this stuff? I don't need too much, and only ever search on first and last name; middle, etc. is irrelevant for everything but reports. I was wondering if this seemed reasonable:
LastName
FirstName
FullName

It seems a little off to demand people enter or change a name twice, but on the other hand, a name is not something that changes often. People may still enter middle initials without a period but that's less a worry. And there may be inconsistency with how they handle Jr. (with or without a comma? with or without a period?) But, at least the searchable data itself is clean. Another option is:
LastName
FirstName
MiddleName
Suffix

The middle name could enforce a period if there's only one letter in it, and the suffix could be populated from a dropdown which would ensure the proper formatting.

Then I wonder if I'm making a big deal out of nothing. As stated, the name outside of the basics is for display only, but I'd like our display to be nice.

# ? Oct 28, 2010 15:54

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 18:39

Avarkx: Feb 11, 2003; :):):):):)

In the camp of "things I believe in," I would say that's application layer stuff. If you're as utterly misanthropic as I am, you do something like this:

code:

CREATE TABLE #t_InputNames
(
  InputLast	VARCHAR( 100 ),
  InputFirst	VARCHAR( 100 )
)

INSERT INTO #t_InputNames
		SELECT	'Smith',	'Will Chris'
UNION ALL	SELECT	'John',		'Doe'
UNION ALL	SELECT	'Downey Jr.',	'Robert'
UNION ALL	SELECT	'Von Beethoven','Ludwig'
UNION ALL	SELECT	'Williams Jr',	'Billy'
UNION ALL	SELECT	'King, Jr',	'Martin Luther'
UNION ALL	SELECT	'Jones Junior',	'Jim Junior'
GO

SELECT	n.InputFirst,
	n.InputLast,
	n.InputFirst + ' ' + n.InputLast 	InputFull,
	n.FirstName,
	n.LastName,
	n.LastName + ', ' + n.FirstName		FullName
FROM (	SELECT	InputFirst,
		InputLast,
		COALESCE( LEFT( InputFirst,
			CASE
			    WHEN CHARINDEX( ' ', InputFirst, 1 ) <> 0
			      THEN CHARINDEX( ' ', InputFirst, 1 )
			    ELSE NULL
			END - 1 ),
			InputFirst )		FirstName,
		COALESCE( LEFT( InputLast, 
			CASE
			    WHEN CHARINDEX( ',', InputLast, 1 ) <> 0
			      THEN CHARINDEX( ',', InputLast, 1 )
			    ELSE NULL
			END - 1 ),
			CASE
			    WHEN InputLast LIKE '% [j]%'
			      THEN LEFT( InputLast, CHARINDEX( ' ', InputLast, 1 ) - 1 )
			    ELSE InputLast
			END )			LastName
	FROM	#t_InputNames ) n
GO

DROP TABLE #t_InputNames
GO

=============================================================================================================
InputFirst	InputLast	InputFull		FirstName	LastName	FullName
-------------------------------------------------------------------------------------------------------------
Will Chris	Smith		Will Chris Smith	Will		Smith		Smith, Will
Doe		John		Doe John		Doe		John		John, Doe
Robert		Downey Jr.	Robert Downey Jr.	Robert		Downey 		Downey, Robert
Ludwig		Von Beethoven	Ludwig Von Beethoven	Ludwig		Von Beethoven	Von Beethoven, Ludwig
Billy		Williams Jr	Billy Williams Jr	Billy		Williams 	Williams, Billy
Martin Luther	King, Jr	Martin Luther King, Jr	Martin		King		King, Martin
Jim Junior	Jones Junior	Jim Junior Jones Junior	Jim		Jones 		Jones, Jim

Just, you know, for MySQL, however that's written.

That particular query will fail hard for last names like "Van J%" and is culturally insensitive to hillbilly names like "Billy Bob," so I would say setting up the application layer to take prefix, firstname, middlename, lastname and/or suffix is the best way to allow users to tell you all about their marital statuses, Ph.Ds, knighthoods, military ranks and esquire-isms that make them more different and special than the next processing number.

Avarkx fucked around with this message at 17:41 on Oct 28, 2010

# ? Oct 28, 2010 17:01

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

«‹›303 »