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 »

LLJKSiLk: Jul 7, 2005; by Athanatos

Sprawl posted:

This is MSSQL server 2005/8 right?

2008. I figured it out though, had to tie the providerID in table_two.

# ? Apr 14, 2011 03:10

Adbot: ADBOT LOVES YOU

# ? Jun 11, 2024 03:26

Goat Bastard: Oct 20, 2004

Thel posted:

(MS SQL Server 2005, will be going to 2008 later this year and can wait until then if needed.)

So, I have a table with {staff_ID,start_datetime,end_datetime} tuples (there's a bunch of other stuff, those are the needed fields for this though). There's no restriction either in any of the applications or in the database to make sure times don't overlap and for one of our reports I need to amalgamate any overlapping appointments in order to get an actual time spent. i.e. a staff member might see one client from 10-12, then another client wanders in at 11 and stays until 1. Sum up the appointment times and it'd come to four hours, but the actual time spent by the staff is 10-1, so three hours.

I get the feeling this should be an already-solved problem, but my google-fu is ~~week~~weak.

Right, I've solved this for Oracle, and hopefully you can take it the rest of the way and adapt it for SQL Server.

I've made two assumptions: That there is a unique id field that identifies every row in your table, and that in the case of a staff member seeing a client from 11-12 and then another from 12-1 that these times should not be rolled up into 11-1.

So first, my test data:

code:

CREATE TABLE times
(id           NUMBER PRIMARY KEY
,staff_person NUMBER
,start_time   TIMESTAMP
,end_time     TIMESTAMP
);

--Person 1 - Control case, a single block. Expected return is 10:00 - 12:00
INSERT INTO times VALUES (1, 1, to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1200','dd/mm/rr hh24mi'));

--Person 2 - A single overlap. Expected return is 9:00 - 10:00 
INSERT INTO times VALUES (2, 2, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 0930','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (3, 2, to_timestamp('01/01/11 0915','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));

--Person 3 - Duplicate start + end times. Expected return is a single 9:00 - 10:00 block
INSERT INTO times VALUES (4, 3, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (5, 3, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));

--Person 4 - Multiple overlapping blocks, each only overlapping one other. Expected return is 9:00 - 11:30
INSERT INTO times VALUES (6, 4, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 0930','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (7, 4, to_timestamp('01/01/11 0915','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1015','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (8, 4, to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1030','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (9, 4, to_timestamp('01/01/11 1015','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1115','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (10, 4, to_timestamp('01/01/11 1100','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1130','dd/mm/rr hh24mi'));

--Person 5 - two distinct blocks. Expected return is 9:00 - 10:00, 11:00 - 12:00
INSERT INTO times VALUES (11, 5, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (12, 5, to_timestamp('01/01/11 1100','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1200','dd/mm/rr hh24mi'));

--Person 6 - two adjecent blocks. Expected return is 9:00 - 10:00, 10:00 - 11:00
INSERT INTO times VALUES (13, 6, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (14, 6, to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1100','dd/mm/rr hh24mi'));

--Person 7 - One block completely covering two distinct blocks. Expected return is 9:00 - 17:00
INSERT INTO times VALUES (15, 7, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1700','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (16, 7, to_timestamp('01/01/11 1100','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1200','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (17, 7, to_timestamp('01/01/11 1500','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1600','dd/mm/rr hh24mi'));

--Person 8 - Two blocks with the same start time and different end times. Expected return is 9:00 - 16:00
INSERT INTO times VALUES (18, 8, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (19, 8, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1600','dd/mm/rr hh24mi'));

--Person 9 - Two blocks with the same start time and different end times overlapping another block. Expected return is 9:00 - 14:00
INSERT INTO times VALUES (20, 9, to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1200','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (21, 9, to_timestamp('01/01/11 1000','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1100','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (22, 9, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1400','dd/mm/rr hh24mi'));

--Person 10 - Two instances of an overlapping block. Expected return is 9:00 - 12:00, 13:00 - 16:00
INSERT INTO times VALUES (23, 10, to_timestamp('01/01/11 0900','dd/mm/rr hh24mi'), to_timestamp('01/01/11 0930','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (24, 10, to_timestamp('01/01/11 0915','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1200','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (25, 10, to_timestamp('01/01/11 1300','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1530','dd/mm/rr hh24mi'));
INSERT INTO times VALUES (26, 10, to_timestamp('01/01/11 1500','dd/mm/rr hh24mi'), to_timestamp('01/01/11 1600','dd/mm/rr hh24mi'));

And my approach to solve it:
1) Select an inline view of the table with each row containing a foreign key to another row that it should be merged with, or null if such a row does not exist.
2) Select from that view every root row in the hierarchy (ie rows with null as their fk), and every row, including the root row itself, that "reports to" that root either directly or indirectly. That's the tricky part.
3) Now that we have a list of all the times that belong in a merged time block we can group by the root row for each block, and select the min and max times from the set of rows that "report to" that root.

Here's the Oracle query that I used to do this. I've tried to make it as database agnostic as possible, but there's no getting around the CONNECT BY for the hierarchy query (The ORDER BY is only there to make the results a bit more readable):

code:

WITH linked_times 
      AS (SELECT id
                ,parent
                ,staff_person
                ,start_time  
                ,end_time  
          FROM   (SELECT    child.id
                           ,child.staff_person
                           ,child.start_time  
                           ,child.end_time    
                           ,parent.id AS parent
                           ,row_number() OVER (PARTITION BY child.id, parent.id ORDER BY parent.id) AS parent_number
                  FROM      times child
                  LEFT JOIN times parent--Create a key to a block that this block should be merged into, if one exists
                            ON  child.staff_person = parent.staff_person
                            AND child.id != parent.id
                            AND(
                                   (    child.start_time > parent.start_time 
                                    AND child.start_time < parent.end_time
                                   ) --Should be merged into a block if the start time falls between the other block's start and end times
                                OR (    child.start_time = parent.start_time 
                                    AND child.id > parent.id
                                   )--If two or more rows have the same start time then exactly one must be chosen as the parent
                               )
                 )
          WHERE  parent_number = 1 -- only need one parent, doesn't matter which.
         )
    ,grouped_times --This is hopefully the only oracle specific bit.
     AS (SELECT     id                 AS child
                   ,CONNECT_BY_ROOT id AS parent
                   ,staff_person
                   ,start_time  
                   ,end_time  
         FROM       linked_times lt
         START WITH lt.parent IS NULL
         CONNECT BY PRIOR id = parent
        )
SELECT   gt.staff_person
        ,min(gt.start_time) AS start_time
        ,max(gt.end_time)   AS end_time
FROM     grouped_times gt
GROUP BY gt.staff_person
        ,gt.parent
ORDER BY gt.staff_person
        ,min(gt.start_time)

;

And the results from running it against the test data:

code:

STAFF_PERSON           START_TIME                END_TIME                  
---------------------- ------------------------- ------------------------- 
1                      01/01/11 10:00:00.000000000 01/01/11 12:00:00.000000000 
2                      01/01/11 09:00:00.000000000 01/01/11 10:00:00.000000000 
3                      01/01/11 09:00:00.000000000 01/01/11 10:00:00.000000000 
4                      01/01/11 09:00:00.000000000 01/01/11 11:30:00.000000000 
5                      01/01/11 09:00:00.000000000 01/01/11 10:00:00.000000000 
5                      01/01/11 11:00:00.000000000 01/01/11 12:00:00.000000000 
6                      01/01/11 09:00:00.000000000 01/01/11 10:00:00.000000000 
6                      01/01/11 10:00:00.000000000 01/01/11 11:00:00.000000000 
7                      01/01/11 09:00:00.000000000 01/01/11 17:00:00.000000000 
8                      01/01/11 09:00:00.000000000 01/01/11 16:00:00.000000000 
9                      01/01/11 09:00:00.000000000 01/01/11 14:00:00.000000000 
10                     01/01/11 09:00:00.000000000 01/01/11 12:00:00.000000000 
10                     01/01/11 13:00:00.000000000 01/01/11 16:00:00.000000000 

13 rows selected

So, to adapt it for SQL Server all you should need to do is convert the grouped_times view to SQL Server's hierarchical query syntax. I'm sure if you google around you'll find something explaining how (I found a brief overview here, but it looked a bit to complicated for me to just have a go and guess that I was right - I'd want to run it to have any confidence and I don't really want to install SQL Server

).

I'm sure I could have made that way more clear, so let me know if some part of it doesn't make sense or is flat out wrong. And thanks for posting the problem, it was quite a fun one to try and solve.

edit: cleaned up the query a bit

Goat Bastard fucked around with this message at 04:15 on Apr 20, 2011

# ? Apr 18, 2011 02:26

Necc0: Jun 30, 2005; by exmarx; Broken Cake

I've been teaching myself some SQL on the side and I can't figure out what the proper syntax for getting a count value from a sub-query. I'm using microsoft access for what it's worth.

So let's say I'm getting a username, and a count for the number of sales user has in another table in a certain category, lets say widgets.

code:

SELECT u.username
FROM userTable AS u
RIGHT JOIN salesTable as s ON u.userID = s.userID
WHERE s.type = "widgets";

How do I get it to display each sales guy's name only once with the associated row count for all the sales that fall under the widgets category? I can't put the count in the initial SELECT because I have non-aggregate fields in it. I know I have to put in a sub-query in there somehow but I know neither the syntax for them nor how they 'work' logically.

# ? Apr 20, 2011 23:19

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

I'm coming at this from an MSSQL standpoint so it'll be a bit different, but couldn't you just use a group by? e.g.

code:

SELECT u.username,count(*)
FROM userTable AS u
RIGHT JOIN salesTable as s ON u.userID = s.userID
WHERE s.type = "widgets"
GROUP BY u.username;

# ? Apr 20, 2011 23:33

Necc0: Jun 30, 2005; by exmarx; Broken Cake

Yeah I tried that but access is still bitching at me. It's really hating that count function in the first line. For what it's worth the error is: "You tried to execute a query that does not include the specified expression 'username' as part of an aggregate function."

edit: well drat I took out the count and I'm getting the same error. welp.

# ? Apr 20, 2011 23:42

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

Try this abortion

code:

SELECT
	username,
	countsales
FROM
	users
INNER JOIN(
	SELECT
		sales."userID",
		COUNT(*)AS countsales
	FROM
		sales
	WHERE
		sales."type" = 'widget'
	GROUP BY
		"userID"
)AS widgetsales ON widgetsales."userID" = users."userID"

# ? Apr 20, 2011 23:47

Necc0: Jun 30, 2005; by exmarx; Broken Cake

I guess I forgot to mention I'm using an intermediary (I have no idea what all the proper lingo is) table between the sales guy and the actual sales. So I have my salesGuys table, a table relating their ID to their multiple sales IDs, then another table that actually contains all of the sales data.

I'm looking more to understand this than just get some code, so bear with me. So we first start at the salesGuy table, and look at his ID number. We take that ID number, and doing an inner join we expand our table to (for now) all of his sales. We then do ANOTHER inner join to get to the actual table with data on it, then constrain it based on the widget thing. Right? So far we have our

SELECT
FROM
INNER JOIN
INNER JOIN
WHERE
GROUP BY

**RAN A TEST HALFWAY THROUGH TYPING THIS**

I just took the liberty of printing out more data to see what my query was actually doing, and it looks like I had the right idea but convinced myself it was wrong. Turns out the problem is in my comparator in the WHERE clause, which is a bit more complicated than just a string comparison.

Ok! So new question: Let's say I have two fields, a and b, where a is usually a subset of b but not the other way around. Right now I'm using a WHERE a NOT IN(b) and that's not working at all, pretty obvious why now that I think about it. So what should I be using?

I guess an example would be a = "FOO" and b = "FOO 505050" how do I go about comparing these two values? I would think a LIKE would work but then I don't know how to assign wildcards onto fields.

# ? Apr 21, 2011 00:10

Necc0: Jun 30, 2005; by exmarx; Broken Cake

The big problem is I have no idea what to google for this one. I can't search 'sql like access' because the only examples I get are the beginner 'compare a field to a string with wildcards' tutorials and not how to compare two fields together or how to concatenate a wildcard onto a field for comparing.

edit: Turns out I again had the right idea (fieldName+'*') but I was comparing the two fields backwards :doh:

Its working like I wanted it to. Sorry about all that.

Necc0 fucked around with this message at 00:54 on Apr 21, 2011

# ? Apr 21, 2011 00:32

Ibsen: Jun 20, 2006; I am Not.

A friend of mine's backend developer bailed on him, and so he has me looking at his web server, and I'm not too well versed in this. He wants me to take a database and export some of the data based on some criteria. I've got WAMP but I don't have much of the site's files running locally yet because everything is spread out.

Now, if this database is being updated pretty much daily, then the file details for it should show it as having been changed or modified very recently?

In var/lib/mysql I've found custom directories containing .frm .myd .myi files but I'm not sure if they're active or the "live" ones I want. The server has directories for several sites, both beta and active, but I'm not even sure about the latter because he has a few servers with a couple different hosting companies.

Obviously I've bitten off more than I can chew, but can anyone tell me what I should be looking for, in terms of the active databases?

# ? Apr 22, 2011 21:01

spiritual bypass: Feb 19, 2008; Grimey Drawer

Can you log into the database? Whatever user/pass you need should be stored in a config file for the web app. Then you could use mysqldump (or use MySQL workbench) to make a backup of the databases.

# ? Apr 22, 2011 22:25

Ibsen: Jun 20, 2006; I am Not.

When I ssh to the webserver I can immediately bring up a mysql prompt, but I'm unsure what to load or where from. I have root access; I haven't seen any config files that detail a user/pass.

Also, the previous developer had mentioned that the database was encrypted, and that a decrypt function was available in a php file, but I wasn't able to find the specific filename, if such a procedure is common/possible.

# ? Apr 22, 2011 23:01

John Matrix: Apr 27, 2004; GREAT AMERICAN DIPLOMAT

Necc0 posted:

I've been teaching myself some SQL on the side and I can't figure out what the proper syntax for getting a count value from a sub-query. I'm using microsoft access for what it's worth.

So let's say I'm getting a username, and a count for the number of sales user has in another table in a certain category, lets say widgets.
code:
SELECT u.username
FROM userTable AS u
RIGHT JOIN salesTable as s ON u.userID = s.userID
WHERE s.type = "widgets";
How do I get it to display each sales guy's name only once with the associated row count for all the sales that fall under the widgets category? I can't put the count in the initial SELECT because I have non-aggregate fields in it. I know I have to put in a sub-query in there somehow but I know neither the syntax for them nor how they 'work' logically.

What about this? Just replace "salesID" with your salesTable primary key... This should work...

code:

SELECT
  u.username,
  count(s.salesID) AS SalesCount
FROM
  userTable AS u
  LEFT JOIN salesTable AS s ON s.userID = u.userID AND s.type = "widgets"
GROUP BY
  u.userID,
  u.username

# ? Apr 23, 2011 17:59

LLJKSiLk: Jul 7, 2005; by Athanatos

Project:

I've got totals for each month of a year I'm pulling back in a query. Currently I have implemented all my calculations in Excel, but my boss wants me to have a completely query based approach to what I'm doing, ideally in SQL Server Reporting Services.

So, I want to pull in totals for each month of a 12 month period, i.e. Jan - Dec / Apr - Mar, whatever. I then want to build a moving average of the totals for the proceeding 13 months prior to each month, then build a confidence interval/standard deviation for acceptable variations in the data.

Right now in Excel, I just run an average of the period, then a standard deviation, then a confidence interval. For each month, for each separate "entity" in the data. Usually between 30-40 entities, so it is a lot of data and calculations.

Honestly my excel based approach takes me 30 seconds to build a spreadsheet report returning the data from a web service, so I don't see the necessity - but it is what it is, and he wants me to make it completely separate from excel.

So... any thoughts?

# ? Apr 25, 2011 16:18

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

I have a large XLSX file with multiple sheets and I was wondering how I can write a SQL statement against it.

I have seen it before where someone would write SQL against an excel spreadsheet, treating the "sheets" as tables and the top rows as "column" names and they were able to make it work by unioning all the sheets.

I searched Google and all over but can't find it. Anyone know how to get this done?

# ? Apr 26, 2011 22:47

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

cannibustacap posted:

I have a large XLSX file with multiple sheets and I was wondering how I can write a SQL statement against it.

I have seen it before where someone would write SQL against an excel spreadsheet, treating the "sheets" as tables and the top rows as "column" names and they were able to make it work by unioning all the sheets.

I searched Google and all over but can't find it. Anyone know how to get this done?

Never tried it but I've always been curious about this: http://www.sqldrill.com/

# ? Apr 26, 2011 22:55

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

cannibustacap posted:

I have a large XLSX file with multiple sheets and I was wondering how I can write a SQL statement against it.

I have seen it before where someone would write SQL against an excel spreadsheet, treating the "sheets" as tables and the top rows as "column" names and they were able to make it work by unioning all the sheets.

I searched Google and all over but can't find it. Anyone know how to get this done?

What rdbms are you using? If it's Oracle just use SQL*Loader to actually get the data in the db, I've quickly imported MASSIVE amounts of data into my database from xls/csv/txt files with it. TOAD even has a neat SQL*Loader wizard that lets you use it while staying away from scary command line interfaces. If you're using something other than Oracle I'm sure there's an equivalent for your db flavor.

EDIT: I'm assuming you do have a db of some flavor, which is silly but hey this is the SQL questions thread. If you don't you could always install postgres or MySQL

Vanadium Dame fucked around with this message at 04:35 on Apr 27, 2011

# ? Apr 27, 2011 04:32

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

Markoff Chaney posted:

What rdbms are you using? If it's Oracle just use SQL*Loader to actually get the data in the db, I've quickly imported MASSIVE amounts of data into my database from xls/csv/txt files with it. TOAD even has a neat SQL*Loader wizard that lets you use it while staying away from scary command line interfaces. If you're using something other than Oracle I'm sure there's an equivalent for your db flavor.

EDIT: I'm assuming you do have a db of some flavor, which is silly but hey this is the SQL questions thread. If you don't you could always install postgres or MySQL

Well, I was kind of looking for a way to treat my excel as the "db engine" and just run queries off of it...

# ? Apr 27, 2011 06:29

slartibartfast: Nov 13, 2002

cannibustacap posted:

Well, I was kind of looking for a way to treat my excel as the "db engine" and just run queries off of it...

Do you have SQL Server available? If so, you can connect to your Excel workbook as a 'Linked Server' and query it that way: http://support.microsoft.com/kb/306397

# ? Apr 27, 2011 13:54

Sub Par: Jul 18, 2001; Dinosaur Gum

Edit: You can do what ^^ he said with MS Access as well, but it might be a bear if there are a ton of sheets, as I recall that you'll have to link them one by one.

cannibustacap posted:

Well, I was kind of looking for a way to treat my excel as the "db engine" and just run queries off of it...

You can do this with VBA. You'll want to loop over the sheets and dump their contents into an ADO recordset and query against that. But there's almost certainly a less cumbersome and more direct way to accomplish whatever it is that you're doing.

# ? Apr 27, 2011 13:55

tiki-guru: Dec 25, 2005

I had some help in the php thread, and now I'm very much stuck on this bit of mysql.

A band can make a profile on the site, if they do their band.name is added to the master list . At the moment I LEFT JOIN and if the band name in the event listings matches the band in the master list, I use the unique id on the master list to make an html link to the band profile. . If band_id === null it just prints out the band name without a link on the line. This works for one band at one venue. But what if multiple bands (`mainlist`.`band2` `band3` etc), play the same show and more than 2 have profiles uploaded, how do I get their unique band_id as their link.

I hope that makes sense. Here is the code for the mysql so far

quote:

$get = queryMysql("SELECT `mainlist`.`sdate`, `mainlist`.`band`, `mainlist`.`venue`, `mainlist`.`price`, `bands`.`id` AS `band_id` FROM `mainlist` LEFT OUTER JOIN `bands` ON `mainlist`.`band` = `bands`.`name` WHERE `sdate` >= CURDATE() ORDER BY `sdate` LIMIT $start, $per_page ");

tiki-guru fucked around with this message at 14:33 on Apr 27, 2011

# ? Apr 27, 2011 14:30

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

tiki-guru posted:

I had some help in the php thread, and now I'm very much stuck on this bit of mysql.

A band can make a profile on the site, if they do their band.name is added to the master list . At the moment I LEFT JOIN and if the band name in the event listings matches the band in the master list, I use the unique id on the master list to make an html link to the band profile. . If band_id === null it just prints out the band name without a link on the line. This works for one band at one venue. But what if multiple bands (`mainlist`.`band2` `band3` etc), play the same show and more than 2 have profiles uploaded, how do I get their unique band_id as their link.

I hope that makes sense. Here is the code for the mysql so far

code:

SELECT
`mainlist`.`sdate`,
`mainlist`.`band`,
`mainlist`.`venue`,
`mainlist`.`price`,
`b1`.`id` AS `band_id`,
`b2`.`id` AS `band2_id`,
`b3`.`id` AS `band3_id`
FROM
`mainlist`
LEFT OUTER JOIN `bands` as `b1` ON `mainlist`.`band` = `b1`.`name`
LEFT OUTER JOIN `bands` as `b2` ON `mainlist`.`band` = `b2`.`name`
LEFT OUTER JOIN `bands` as `b3` ON `mainlist`.`band` = `b3`.`name`
WHERE `sdate` >= CURDATE()
ORDER BY `sdate`
LIMIT $start, $per_page

# ? Apr 27, 2011 15:12

tiki-guru: Dec 25, 2005

Jethro posted:

Thank you.

# ? Apr 27, 2011 17:59

KiloVictorDongs: Apr 12, 2007; SOME PIG

Quick question: I have a table with startDates and endDates, and Recommendations. Recommendations last from the startDate to the endDate (generally this is a 1 week period). I want to take the average recommendation for a month. I figure I can take the datediff(EndDate,Startdate), multiply that by the recommendation, and divide that value by the number of days in the month for middle weeks. However, beginning and end weeks pose more of a challenge. If a week has a startDate of 01/26 and an endDate of 02/01, I should weight it accordingly (that is, only (1*recommendation)/28). How can I get a count of how many days in a given time period are in a certain month?

# ? Apr 27, 2011 18:09

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

Just like to say I nominate Jethro for SQL Thread MVP.

# ? Apr 27, 2011 20:35

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Scaramouche posted:

Just like to say I nominate Jethro for SQL Thread MVP.

He's added three pages to this thread with his responses. Jeeez... Thanks Jethro!

I have a request where, depending on one of the three values of AccountType, I need to place the AccountNumber into one of three columns in the query output. In addition, if a userID is linked to multiple types of account, the account number information should be all in a single row of the output.

code:

Given this:

userID    AccountType    AccountNumber
001       Individual     11
002       Individual     22
001       Roth IRA       33
003       Roth IRA       44
003       Joint          55
004       Joint          66
003       Individual     77

code:

Produce This:

UserID    IndividualAccount    JointAccount    RothIRAAccount
001       11                   -               33
002       22                   -               -
003       77                   55              44
004       -                    66              -

Agrikk fucked around with this message at 05:18 on Apr 28, 2011

# ? Apr 28, 2011 05:14

baquerd: Jul 2, 2007; by FactsAreUseless

Agrikk posted:

code:

userID    AccountType    AccountNumber
001       Individual     11
002       Individual     22
001       Roth IRA       33
003       Roth IRA       44
003       Joint          55
004       Joint          66
003       Individual     77

code:

Produce This:

UserID    IndividualAccount    JointAccount    RothIRAAccount
001       11                   -               33
002       22                   -               -
003       77                   55              44
004       -                    66              -

code:

SELECT distinct UserId into dickbutt from MyTable;

ALTER TABLE dickbutt ADD IndividualAccount int null;
ALTER TABLE dickbutt ADD JointAccount int null;
ALTER TABLE dickbutt ADD RothIRAAccount int null;

update dickbutt d set d.IndividualAccount = 
    (select distinct AccountNumber from MyTable where AccountType = 'Individual' and userId = d.UserID)

You get the idea.

edit: added distinct to select into

baquerd fucked around with this message at 05:41 on Apr 28, 2011

# ? Apr 28, 2011 05:37

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Agrikk posted:

He's added three pages to this thread with his responses. Jeeez... Thanks Jethro!

I have a request where, depending on one of the three values of AccountType, I need to place the AccountNumber into one of three columns in the query output. In addition, if a userID is linked to multiple types of account, the account number information should be all in a single row of the output.
code:
Given this:

userID    AccountType    AccountNumber
001       Individual     11
002       Individual     22
001       Roth IRA       33
003       Roth IRA       44
003       Joint          55
004       Joint          66
003       Individual     77
code:
Produce This:

UserID    IndividualAccount    JointAccount    RothIRAAccount
001       11                   -               33
002       22                   -               -
003       77                   55              44
004       -                    66              -

What you're looking for is a PIVOT or crosstab query. How to do this depends on what RDBMS you are using. It's better to do this in a single query instead of using a temporary table.

# ? Apr 28, 2011 07:57

Bad Titty Puker: Nov 3, 2007; Soiled Meat

KiloVictorDongs posted:

Quick question: I have a table with startDates and endDates, and Recommendations. Recommendations last from the startDate to the endDate (generally this is a 1 week period). I want to take the average recommendation for a month. I figure I can take the datediff(EndDate,Startdate), multiply that by the recommendation, and divide that value by the number of days in the month for middle weeks. However, beginning and end weeks pose more of a challenge. If a week has a startDate of 01/26 and an endDate of 02/01, I should weight it accordingly (that is, only (1*recommendation)/28). How can I get a count of how many days in a given time period are in a certain month?

Use a calendar table, which is a lookup table you can join to. Here's an example article: http://searchoracle.techtarget.com/answer/Populate-a-calendar-table; google for more info. Basically you join the calendar table on the calendar date BETWEEN startDate and endDate and the calendar month = MONTH(endDate) and count the rows in the filtered calendar table. The calendar table has one row per day.

# ? Apr 28, 2011 08:02

Goat Bastard: Oct 20, 2004

Agrikk posted:

...
Given this:
...
Produce This:
...

Here's one that should be database agnostic as long as there's support for the max() function:

code:

SELECT   userid
        ,max(individualaccount) AS individualaccount
        ,max(jointaccount)      AS jointaccount
        ,max(rothiraaccount)    AS rothiraaccount
FROM     (SELECT userid
                ,CASE WHEN accounttype = 'Individual' THEN accountnumber END AS individualaccount
                ,CASE WHEN accounttype = 'Joint'      THEN accountnumber END AS jointaccount
                ,CASE WHEN accounttype = 'Roth IRA'   THEN accountnumber END AS rothiraaccount
          FROM   accounts
         )
GROUP BY userid
ORDER BY userid

The obvious drawback is that you need to know all the possible relevant values in the AccountType column at compile time, but in my experience you often do when writing this sort of query.

Goat Bastard fucked around with this message at 13:37 on Apr 28, 2011

# ? Apr 28, 2011 08: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!

Agrikk posted:

He's added three pages to this thread with his responses. Jeeez... Thanks Jethro!

I have a request where, depending on one of the three values of AccountType, I need to place the AccountNumber into one of three columns in the query output. In addition, if a userID is linked to multiple types of account, the account number information should be all in a single row of the output.
code:
Given this:

userID    AccountType    AccountNumber
001       Individual     11
002       Individual     22
001       Roth IRA       33
003       Roth IRA       44
003       Joint          55
004       Joint          66
003       Individual     77
code:
Produce This:

UserID    IndividualAccount    JointAccount    RothIRAAccount
001       11                   -               33
002       22                   -               -
003       77                   55              44
004       -                    66              -

Its pretty easy if you think about it in simple terms.

code:

CREATE TABLE `accounts` (
  `userid` int(11) DEFAULT NULL,
  `accounttype` varchar(25) DEFAULT NULL,
  `accountnumber` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



1	Individual	11
2	Individual	22
1	Roth IRA	33
3	Roth IRA	44
3	Joint	55
4	Joint	66
3	Individual	77



select 
userid,   
(Select t2.accountnumber from accounts as t2 where t1.userid = t2.userid and accounttype='Individual') as Individual,
(Select t3.accountnumber from accounts as t3 where t1.userid = t3.userid and accounttype='Joint') as Joint,
(Select t4.accountnumber from accounts as t4 where t1.userid = t4.userid and accounttype='Roth IRA') as RothIRA
from accounts as t1
group by t1.userid
order by t1.userid

A Pivot table or Crosstab would be best but if your using mysql you dont have those.

# ? Apr 28, 2011 21:42

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

camels posted:

What you're looking for is a PIVOT or crosstab query. How to do this depends on what RDBMS you are using. It's better to do this in a single query instead of using a temporary table.

Sprawl posted:

A Pivot table or Crosstab would be best but if your using mysql you dont have those.

I'm using SQL 2008 Enterprise. Can you explain further or point me to a good reference? I've never used PIVOT before...

# ? Apr 29, 2011 05:20

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Agrikk posted:

I'm using SQL 2008 Enterprise. Can you explain further or point me to a good reference? I've never used PIVOT before...

Search for PIVOT in Books Online, or "SQL Server" PIVOT in msdn.microsoft.com and you'll find this: http://msdn.microsoft.com/en-us/library/ms177410.aspx

# ? Apr 29, 2011 19:41

LLJKSiLk: Jul 7, 2005; by Athanatos

Moving Average
Using SQL Server 2008

sample data:

code:

ID    |  YYYY  |  MM  |  COUNT
1200  |  2008  |  12  |  537
1200  |  2009  |  1   |  580
1200  |  2009  |  2   |  480
1200  |  2009  |  3   |  530

What I want is to calculate a moving average starting 11 months before the current month. So for instance, 3/2009 here I want an average calculated on all counts from 3/2008 - 2/2009 in a column to the right named moving_average.

What I've tried is the following:

code:

select x.*, AVG(y.COUNT)moving_average from #temp x,#temp y
where x.YYYY>=y.YYYY-1 AND x.mm BETWEEN y.mm AND y.mm+11
group by x.*

There are about 200 - 300 unique IDs in the table, each one corresponds to a different "customer" being pulled back. I want to basically get a running average of the market share of said customer.

Any thoughts? The code doesn't work as-is, I was using a tutorial here: http://oreilly.com/catalog/transqlcook/chapter/ch08.html

# ? Apr 29, 2011 19:44

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

Agrikk posted:

I'm using SQL 2008 Enterprise. Can you explain further or point me to a good reference? I've never used PIVOT before...

If you find anything good that aren't the same dry MSDN examples post it here. I can do PIVOTs but I can't visualize one from scratch like I can with all kinds of other statements; it always ends up being a messy couple of minutes of add a statement, remove one, tinker here, tinker there, until it eventually works.

# ? Apr 29, 2011 20:35

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!

Agrikk posted:

I'm using SQL 2008 Enterprise. Can you explain further or point me to a good reference? I've never used PIVOT before...

code:


select userid, [Individual], [Joint], [Roth IRA]
from
(select userid, accounttype, accountnumber from dbo.accounts) as source
PIVOT
(
Max(accountnumber)
For AccountType in ([Individual], [Joint], [Roth IRA])
) as PivotTable;

it looks like when using a pivot table you have to use an analytical function on the result, but because there is only the one result per box it wont ever show the wrong one. You dont have to use max you could use a number of different ones like min, round etc.

code:

USE [tempdb]
GO
/****** Object:  Table [dbo].[accounts]    Script Date: 05/02/2011 10:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[accounts](
	[userid] [int] NOT NULL,
	[accounttype] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[accountnumber] [int] NULL
) ON [PRIMARY]


1,Individual          ,11
2,Individual          ,22
1,Roth IRA            ,33
3,Roth IRA            ,44
3,Joint               ,55
4,Joint               ,66
3,Individual          ,77

Sprawl fucked around with this message at 18:08 on May 2, 2011

# ? May 2, 2011 17:38

Swink: Apr 18, 2006; Left Side <--- Many Whelps

This should be fairly simple. I have 0 DB experience but need to have this query working by Monday.

code:

Select


Client.ClientID	as "Unique Client ID number. eg: SMI01234",
Client.ClientName as "Client Proper Name. eg: Mr Bob Smith",
Client.Client_Refer as "Referral ClientID. eg: GRA1111. Same data as ClientID"

FROM 

Client

I need to match up Client_refer with ClientName so that I can get a proper full name in the referer column, instead of an unreadable ID number.

Edit - There will be a lot of cases where Client_refer will have no data. These still need to be returned.

Swink fucked around with this message at 04:20 on May 7, 2011

# ? May 7, 2011 03:30

Aionic Duck: Apr 21, 2010

Swink posted:

This should be fairly simple. I have 0 DB experience but need to have this query working by Monday.
code:
Select


Client.ClientID	as "Unique Client ID number. eg: SMI01234",
Client.ClientName as "Client Proper Name. eg: Mr Bob Smith",
Client.Client_Refer as "Referral ClientID. eg: GRA1111. Same data as ClientID"

FROM 

Client
I need to match up Client_refer with ClientName so that I can get a proper full name in the referer column, instead of an unreadable ID number.

Edit - There will be a lot of cases where Client_refer will have no data. These still need to be returned.

Which particular SQL flavor are you using?

EDIT: In any case, you'll be looking at a left outer (also self) join. Something along the lines of:

SELECT Client.ClientID, Client.ClientName, Referral.ClientName
FROM ClientTable AS Client
LEFT OUTER JOIN ClientTable AS Referral ON Client.Client_Refer = Referral.ClientID

That is, if your referrals are from people also in the same table, if I gather right. This'll also show NULLs in the referral name if there's no matching person.

Aionic Duck fucked around with this message at 05:28 on May 7, 2011

# ? May 7, 2011 05:05

Swink: Apr 18, 2006; Left Side <--- Many Whelps

Aionic Duck posted:

if your referrals are from people also in the same table,

Yes they are. So that solution should work fine. Thanks!

Edit - You've got a "ClientTable" in there that bugs out my query. I'm on SQL2008.

Sorted it out. Working thing looks like this:

code:

SELECT
Client.cm_client_no, 
Client.cm_name,
Referral.cm_name as "Referer"

FROM Client 

LEFT OUTER JOIN Client AS Referral ON Client.cm_refer_by = Referral.cm_client_no

Swink fucked around with this message at 08:33 on May 7, 2011

# ? May 7, 2011 05:46

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Another SQL Server 2008 question:

I need to count the number of specific events that occur on a user account during a specific date range based on its creation date.

I'm confused on how to write a query that retreives UserID and ActiveDate, then counts occurences of EventX between ActiveDate and Activedate +42 days.

I keep bumping up against a "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." error and I don't understand how to dump a column record into a variable and then use that variable in a subquery of the original statement.

update:

I tried a different tack and tried to precalculate the range for each userid, returning a temp table #Dates that has:

UserID, ActiveDate, SixWeeksLater

Then running this query:

code:

select #Dates.AccountID,
	COUNT(1) as '42 Day Trade Count'
from EventData.dbo.EXT010 x10 

LEFT JOIN #Dates on #Dates.AccountID = x10.AccountNumber

where  #Dates.accountid = x10.Accountnumber 
and x10.EntryDate > #Dates.ActiveDate
and x10.EntryDate < #Dates.SixWeeksLater														 
group by #Dates.AccountID

but I get no results. What am I doing wrong here?

Agrikk fucked around with this message at 06:00 on May 9, 2011

# ? May 7, 2011 23:36

Adbot: ADBOT LOVES YOU

# ? Jun 11, 2024 03:26

benisntfunny: Dec 2, 2004; I'm Perfect.

Agrikk posted:

Another SQL Server 2008 question:

I need to count the number of specific events that occur on a user account during a specific date range based on its creation date.

I'm confused on how to write a query that retreives UserID and ActiveDate, then counts occurences of EventX between ActiveDate and Activedate +42 days.

I keep bumping up against a "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." error and I don't understand how to dump a column record into a variable and then use that variable in a subquery of the original statement.

Select userid, activedate, count(*)
from sometable
Where activedate between somedate and dateadd(dd,42,somedate)
And eventx = 'whatever'
Group by userid, activedate

At least I think that's what you're asking. If your dates include time then that's not going to be very fun. You migh want to do something like convert(varchar,activedate,101)

This sucks typing on iPad.

--- looking at your example in your edit....
It doesn't match up with what you've said. You don't need that first item in your where clause since it's handled by an ON statement and you don't have to use BETWEEN like I have but you might want to do something like <= >=

benisntfunny fucked around with this message at 06:09 on May 9, 2011

# ? May 9, 2011 06:02

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

«‹›303 »