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 »

Gilg: Oct 10, 2002

I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it.

# ? Dec 17, 2010 01:33

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 20:31

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

gotly posted:

I am looking for a program like Microsoft Visio that can be used to visually mock-up data models. Something web based would be awesome.

I haven't tried it much (it looks really complex) but last time someone asked this yEd was suggested.

# ? Dec 17, 2010 01:42

8ender: Sep 24, 2003; clown is watching you sleep

Aturaten posted:

So my SQL is rusty as all gently caress, so do you think anyone could link me to a sample database that I could play around with and get back up to speed?

Heres a few good ones:
http://dev.mysql.com/doc/index-other.html

# ? Dec 17, 2010 07:40

slartibartfast: Nov 13, 2002

Gilg posted:

I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it.

Here's something I've used before that'll search within a given radius around a fixed center point. Shouldn't be too hard to adapt to your needs. This is MSSQL 2008, so you might need to tweak the variable declarations if you're using a different DBMS:

code:

--lat/lng of center point
  declare @tlat as float = 41.948200
  declare @tlng as float = -87.65572
  
   select companyName, companyLat, companyLng, dbo.fnGetDistance(@tlat,@tlng,companyLat,companyLng,'miles') as distance_in_miles
     from companies
    where dbo.fnGetDistance(@tlat,@tlng,companyLat,companyLng,'miles') <= 75

There's probably a faster way than using a scalar-value function here, but I'm only ever searching a few thousand records on a non-production server, so I haven't bothered to do any kind of query tuning.

Here's the function:

code:

Create Function [dbo].[fnGetDistance]
(
      @Lat1 Float(18), 
      @Long1 Float(18),
      @Lat2 Float(18),
      @Long2 Float(18),
      @ReturnType VarChar(10)
)

Returns Float(18)

AS

Begin

      Declare @R Float(8);
      Declare @dLat Float(18);
      Declare @dLon Float(18);
      Declare @a Float(18);
      Declare @c Float(18);
      Declare @d Float(18);

      Set @R = 
            Case @ReturnType 
            When 'Miles' Then 3956.55 
            When 'Kilometers' Then 6367.45
            When 'Feet' Then 20890584
            When 'Meters' Then 6367450
            Else 20890584 -- Default feet (Garmin rel elev)
            End

      Set @dLat = Radians(@lat2 - @lat1);

      Set @dLon = Radians(@long2 - @long1);

      Set @a = Sin(@dLat / 2) 
                 * Sin(@dLat / 2) 
                 + Cos(Radians(@lat1))
                 * Cos(Radians(@lat2)) 
                 * Sin(@dLon / 2) 
                 * Sin(@dLon / 2);
      Set @c = 2 * Asin(Min(Sqrt(@a)));

      Set @d = @R * @c;
      Return @d;

End 
GO

# ? Dec 17, 2010 16:04

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

Gilg posted:

I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it.

I made this. It's probably terrible, but it works. I also made a thing that allows the user to poll Google Maps for driving distance/time.

code:

SELECT entities.id, entities.name, entities.rate, entities.servicecharge, entities.status,
round(acos(sin(radians(entities.latitude)) * sin(radians(sites.latitude)) + 
cos(radians(entities.latitude)) * cos(radians(sites.latitude)) * cos(radians(sites.longitude-entities.longitude))) * 3956.6, 1) as distance,
                distance.drivemiles, distance.drivetime
                FROM sites, entities 
                LEFT JOIN distance on distance.entity = entities.id AND distance.site = '$site'
                WHERE sites.id = '$site' AND
                entities.status IN('Active', 'Unused')
                HAVING distance <= 120
                ORDER BY distance

You'll just want to join the table to itself with a different name and add something like "WHERE t1.id <> t2.id" so you don't match the record to itself.

So here, I guess. The 3956.6 gives you miles.

code:

SELECT round(acos(sin(radians(t2.latitude)) * 
sin(radians(t1.latitude))+cos(radians(t2.latitude)) * 
cos(radians(t1.latitude))*cos(radians(t1.longitude-t2.longitude))) * 3956.6, 1) as distance
FROM places AS t1, places AS t2 WHERE t1.id <> t2.id ORDER BY distance LIMIT 10

e: table breakery

butt dickus fucked around with this message at 16:10 on Dec 17, 2010

# ? Dec 17, 2010 16:07

Gilg: Oct 10, 2002

Thanks slartibartfast and Doctor rear end in a top hat, your posts were helpful.

# ? Dec 17, 2010 23:47

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

Gilg posted:

Thanks slartibartfast and Doctor rear end in a top hat, your posts were helpful.

I guess the degree of helpfulness depends on what database you're using.

# ? Dec 18, 2010 00:16

8ender: Sep 24, 2003; clown is watching you sleep

Doctor rear end in a top hat posted:

I guess the degree of helpfulness depends on what database you're using.

If he's using Oracle I wrote a PL/SQL stored procedure to do the same thing and could dig it up. I ended up doing the same as you though and making something to poll Google because as the crow flys isn't really useful unless you're calculating distances for your scud missile launcher.

# ? Dec 18, 2010 06:37

Primpod: Dec 25, 2007; jamming on crusty white

Gilg posted:

I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it.

You could do something like this in MSSQL Server 2008 and it uses it's inbuilt function for figuring out the distance between geography datatypes, just use whatever you're using instead.

code:

DECLARE @GETTOP INT
SET @GETTOP = 10

SELECT 
	PS1.Code,
	Closest.Code
FROM 
	POSTCODES PS1 
CROSS APPLY 
(
	SELECT Top(@GETTOP)
		PS2.CODE
	FROM
		POSTCODES PS2
	ORDER BY 
		PS2.longlat.STDistance(PS1.LongLat)
)	Closest
Where 
	PS1.Code <> Closest.Code

Primpod fucked around with this message at 18:53 on Dec 18, 2010

# ? Dec 18, 2010 18:46

Rocko Bonaparte: Mar 12, 2002; Every day is Friday!

I am looking for some advice on creating a database out of some XML data. I expect to have to coddle it a little bit, but I also expect it to be easier and more robust than having to create a table myself. I need to take a huge pile of XML files and turn them into a tabular report. Each file has a little bit of the data for this table. Each row in the table would be determined by a name and a number. Each XML file will have that information. Any extra information then I would need to insert on the fly, potentially creating new columns as I go. I am aware of the nature of this data and know the best way to represent that data when I need to create new columns.

I don't expect to be able to just give the XML files raw and hope for the best. Still, I think it will be a lot cleaner to have a database keeping everything in a table rather than having, say, many parallel hash maps or lists involved.

# ? Dec 20, 2010 23:34

Namlemez: Jul 9, 2003

Most DBMSs (e.g. Oracle, MySQL) have varying degrees of support for importing XML and stuffing it into tables without having to write your own parser. Which are you looking at?

# ? Dec 21, 2010 00:50

Rocko Bonaparte: Mar 12, 2002; Every day is Friday!

Namlemez posted:

Most DBMSs (e.g. Oracle, MySQL) have varying degrees of support for importing XML and stuffing it into tables without having to write your own parser. Which are you looking at?

MySQL, or even sqlite. I want to use the database as an intermediary for producing the table I want, after which I generate a report from a big query on it and purge it.

# ? Dec 21, 2010 04:12

Jo: Jan 24, 2005; Soiled Meat

General database question: Each object in my database is described by it's position in 64-dimensional space. This translates to each object having 64 floating point numbers between one and zero. If I wanted to do nearest neighbor search inside of a database, is using MySQL (or any database of the sort) recommended? Where can I learn more about this sort of transaction?

EDIT: Think I found a solution. Still open to suggestions, but will shuffle through, "K Nearest Neighbor Queries and KNN-Joins in Large Relational Databases (Almost) for Free" by Yao, Li, and Kuman.

Jo fucked around with this message at 20:36 on Dec 21, 2010

# ? Dec 21, 2010 06:28

TildeATH: Oct 21, 2010; by Lowtax

Rocko Bonaparte posted:

MySQL, or even sqlite. I want to use the database as an intermediary for producing the table I want, after which I generate a report from a big query on it and purge it.

If the xml has any real level of complexity you should give some serious thought to writing the ingest script in PHP. You can do a lot in MySQL, but part of learning how to use it is learning when to use other, more efficient tools, like scripting languages.

# ? Dec 21, 2010 07:38

Rocko Bonaparte: Mar 12, 2002; Every day is Friday!

TildeATH posted:

If the xml has any real level of complexity you should give some serious thought to writing the ingest script in PHP. You can do a lot in MySQL, but part of learning how to use it is learning when to use other, more efficient tools, like scripting languages.

I was figuring I'd have to use a scripting language to do some of the heavy lifting, but I was hoping there was a technology that was like a database where I could plug all this stuff in. Otherwise, I am creating my own arbitrary-column table and trying to fit everything in the right column manually. That was the bullet I hoped to dodge.

# ? Dec 21, 2010 17:47

Thel: Apr 28, 2010

Been trying to write some SQL to take caption codes piled into a string (from a multiselect, e.g. ',Ch,Ca,'), split out the codes, go looking for the language captions and pile them all back into a string (i.e. 'Calm, Cheerful' or 'Alegre, Tranquilo'*). Got it all working, aside from piling the finished string back together.

* This isn't an actual Spanish translation, we actually only support English at the moment :v:

. However, the application is built with support for internationalisation blah blah blah.

Anyway, issue as follows:

code:

DECLARE @stuff varchar(5)
SET @stuff = ', '

SELECT len(@stuff) AS length
----
length
----
 1

Is this just because len() strips trailing blanks, even from varchars?

Also, is there any way to avoid this behaviour (do I gently caress around with ANSI padding or something)? I'm working around it by using DATALENGTH, but no prizes for spotting the problem there.

DATALENGTH measures the number of bytes. Unicode characters - welp.

Thel fucked around with this message at 01:22 on Dec 30, 2010

# ? Dec 30, 2010 01:15

Thel: Apr 28, 2010

(double posting because it's two separate problems.)

If I ever meet the person who put this advertisement-for-cutting-losses-and-starting-again of a system together, there will be blood, tears and a fair amount of descriptive verbiage.

We have Clients, Clients have goals (multiple). Clients also have a dream (singular) - something they want to do, somewhere they want to go, whatever.

No prizes for guessing where the dream is stored.

There's a dreams field in each goal.

No prizes for guessing whether dreams are kept up to date.

We have clients with five different dreams, but if you edit the dream now they all get updated to the new one. :psyduck:

~~How can I fix this to get a single line of output?~~ I am literally retarded. If I'm redesigning a reporting template for this, I can just SELECT DISTINCT and stick it in a subreport.

Thel fucked around with this message at 03:57 on Dec 30, 2010

# ? Dec 30, 2010 03:54

McGlockenshire: Dec 16, 2005; GOLLOCKS!

e: n/m, contradictory data.

McGlockenshire fucked around with this message at 07:51 on Dec 30, 2010

# ? Dec 30, 2010 07:47

Akrabbim: Aug 10, 2003; It means 'scorpion' in Hebrew, and rhymes with 'bakrabbim'. Don't even ask...

I'm trying to get specific values out of a SQL field which contains XML. The problem is, no matter what I do I keep getting nothing but NULL. I can't for the life of me figure out what sort of mystic sorcery I'm supposed to use to get the value.

Basically, the value SHOULD be three levels down, but I only get nulls back. I have found one thing, though. If I take the XML field and declare it as a variable, I can SOMETIMES get a value back. But if it has a property in the tag, it stops working.

Here's what I have that will work:

declare @XML XML

set @XML = '<ApplicationInfo>
<ApplicationId>0</ApplicationId>
</ApplicationInfo>'

SELECT
@XML.value('/ApplicationInfo[1]/ApplicationId[1]', 'nvarchar(max)') as FirstName

That gives me an actual value back.

declare @XML XML

set @XML = '<ApplicationInfo xmlns="http://sungardhe.com/PowerCAMPUS/Application/">
<ApplicationId>0</ApplicationId>
</ApplicationInfo>'

SELECT
@XML.value('/ApplicationInfo[1]/ApplicationId[1]', 'nvarchar(max)') as FirstName

That gives me NULL. How do I make it ignore the xmlns?

Again, though, this is what I've been able to cobble together. The actual thing I want to get back is within a field of a table, not in a variable. Is there an easier way to get a value back from XML in a field?

# ? Jan 4, 2011 15:56

slartibartfast: Nov 13, 2002

Are you doing something with PowerCampus's Self-Service product? PM me with more details and I can try it on a test box at my school. :ninja:

# ? Jan 4, 2011 21:32

Akrabbim: Aug 10, 2003; It means 'scorpion' in Hebrew, and rhymes with 'bakrabbim'. Don't even ask...

slartibartfast posted:

Are you doing something with PowerCampus's Self-Service product? PM me with more details and I can try it on a test box at my school.

I am, but the thing I'm needing to do has nothing to do with the product at all. I'm just trying to get something out of a field populated with XML.

EDIT: As an additional bit of info, I've found that the existence of "xmlns" as an attribute screws up any attempt to use XML queries. Is there a reason for this, or a way around it?

Akrabbim fucked around with this message at 21:49 on Jan 4, 2011

# ? Jan 4, 2011 21:47

Primpod: Dec 25, 2007; jamming on crusty white

Akrabbim posted:

I am, but the thing I'm needing to do has nothing to do with the product at all. I'm just trying to get something out of a field populated with XML.

EDIT: As an additional bit of info, I've found that the existence of "xmlns" as an attribute screws up any attempt to use XML queries. Is there a reason for this, or a way around it?

[Not an XML Expert]

xmlns is a namespace declaration and declaring it in the way you are doing specifies it as the default namespace, which is why you are getting nulls, as you're querying the 'global' namespace and your fields are hidden away in the "http://sungardhe.com/PowerCAMPUS/Application/" namespace.

Pretty sure thats what it's doing, as if you use /*[1]/*[1] (wildcards) you'll get the right results, which implies that they are there, they just aren't called what you think they are.

I did some googling and couldn't see a way of specifying an XPath statement to execute within a specified namespace. There's probably some simple way of doing it but I don't know what it is, sorry.

EDIT:

Akrabbim posted:

The actual thing I want to get back is within a field of a table, not in a variable. Is there an easier way to get a value back from XML in a field?

You could use something like this in SQL Server. This may not work, I don't have anything to test it with. It should give you the value for each row as an additional column if you run it against the table.

code:

SELECT 
        XMLInnerValues.Value,
        XMLTable .OriginalXML 

FROM 
	XMLTable  
CROSS APPLY 
(
	SELECT OriginalXML.value('/*[1]/*[1]', 'nvarchar(max)') as Value
) XMLInnerValues

Primpod fucked around with this message at 23:08 on Jan 4, 2011

# ? Jan 4, 2011 22:54

Akrabbim: Aug 10, 2003; It means 'scorpion' in Hebrew, and rhymes with 'bakrabbim'. Don't even ask...

That appeared to do it. I got it to work in the actual query, but it's still kinda annoying that that happens. Is there any way you know of to figure what name SQL is giving to the fields?

# ? Jan 4, 2011 23:17

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I've heard of this as a new interview question, has anyone else?

When shouldn't you use SQL?

Are they talking specifically when not to use a certain version, or in general, like don't use a database instead of XML/spreadsheet/zip file to store stuff.

# ? Jan 6, 2011 22:25

TildeATH: Oct 21, 2010; by Lowtax

Bob Morales posted:

I've heard of this as a new interview question, has anyone else?

When shouldn't you use SQL?

Are they talking specifically when not to use a certain version, or in general, like don't use a database instead of XML/spreadsheet/zip file to store stuff.

There's two cases I can think of from a general perspective:

1) You can do a lot of processing in SQL, but there's diminishing returns, and so know when to pass the results to/from scripting.

2) For extremely large graph datasets, there are better DBMS options, such as Neo4J, that are optimized for graph queries.

# ? Jan 6, 2011 22:30

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

I guess theoretically it could also refer to data that simply can't be normalized or doesn't make sense in a relational database but most industries that require that kind of thing have their own solutions.

# ? Jan 6, 2011 23:13

McGlockenshire: Dec 16, 2005; GOLLOCKS!

They could also use "shouldn't use SQL" to mean "don't need ACID," referring to one of the new shard-based/distributed "NoSQL" key-value stores that sacrifice one or more of the ACID principals in the name of performance.

# ? Jan 7, 2011 00:49

TildeATH: Oct 21, 2010; by Lowtax

McGlockenshire posted:

They could also use "shouldn't use SQL" to mean "don't need ACID," referring to one of the new shard-based/distributed "NoSQL" key-value stores that sacrifice one or more of the ACID principals in the name of performance.

Yeah, that would be a smart answer.

I think the question has a few such smart answers, and it's probably also going to tell them what part of the data ecosystem you consider yourself to live in.

# ? Jan 7, 2011 01:21

bamhand: Apr 15, 2010

Ok I think I know the answer to my question is that Access is a piece of poo poo but just to be sure:

I have a query that's basically:

SELECT 'a bunch of random poo poo'
from table_a, table_b
WHERE left(table_a.sgl_account, 4) = table_b.sgl

This query runs fine. I then try:

SELECT 'a bunch of random poo poo'
from table_a, table_b
WHERE left(table_a.sgl_account, 4) = left(table_b.sgl, 4)

This causes Access to poo poo itself and freeze when running the query.

As a bonus, the following query works perfectly fine too:

SELECT 'a bunch of random poo poo'
from table_a, table_b
WHERE left(table_a.sgl_account, 4) = sgl and left(table_a.sgl_account, 4) = left(sgl, 4)

So am I retarded in not understanding why this happens or is Access retarded? I'm a bit frustrated at the moment if you can't tell. I feel like I'm trying random voodoo at thist point in trying to get this thing to work.

# ? Jan 7, 2011 20:20

TildeATH: Oct 21, 2010; by Lowtax

bamhand posted:

So am I retarded in not understanding why this happens or is Access retarded?

Access can be so absurdly bad that even SQLServer has support articles bemoaning it.

I don't have any particular experience with what you're dealing with, except to say that when I was forced to work on Access for a week last year, I found any complex queries were best built with their crappy query construction tool and not by writing the SQL by hand. I don't know if that helps.

# ? Jan 7, 2011 20:35

bamhand: Apr 15, 2010

The query is built by a vb script based off some choices that you make on a menu. I also have no idea how the query builder works.

*edit* I fixed the issue by just adding another column to table_b that was equal to left(sgl, 4) and now everything is fine and dandy. If anyone has insight on why the previous query doesn't work I would still appreciate it. My guess is that for the third query once it runs through that first condition the second condition using left(sgl_account, 4) = left(sgl, 4) doesn't have many comparisons to do? I really no nothing about efficiency when it comes to coding, all my SQL I pretty much learned on the job with a lot of googling.

bamhand fucked around with this message at 20:46 on Jan 7, 2011

# ? Jan 7, 2011 20:40

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

bamhand posted:

The query is built by a vb script based off some choices that you make on a menu. I also have no idea how the query builder works.

*edit* I fixed the issue by just adding another column to table_b that was equal to left(sgl, 4) and now everything is fine and dandy. If anyone has insight on why the previous query doesn't work I would still appreciate it. My guess is that for the third query once it runs through that first condition the second condition using left(sgl_account, 4) = left(sgl, 4) doesn't have many comparisons to do? I really no nothing about efficiency when it comes to coding, all my SQL I pretty much learned on the job with a lot of googling.

My guess would be something about access makes it calculate left(sgl, 4) for every record in table_b once for every record in table_a.

# ? Jan 7, 2011 21:11

Just-In-Timeberlake: Aug 18, 2003

Ugh, I don't know why I have such an absurdly hard time wrapping my head around PIVOT/UNPIVOT, but before I bang my head bloody against my desk anymore, I figured I'd ask for help.

I have a table with a customer ID and a bunch of columns with rules, so it looks like this:

code:

CustomerID    [101]   [102]   [103]
--------------------------------------
123             1       0       0

Where [101], [102], etc correspond to another table with the description for each code:

code:

[CategoryID]    [CatCode]    [CatDescription]
--------------------------------------------------
1                  101           Some Description

Now I need some way of joining these tables so I get a row for each category and the value with the description. I know how to get the column names from the first table, and I managed to figure out how to unpivot the first table, but it only has the CustomerID and bit value so I can't join against the second table on [CatCode].

Now, I think the solution is to somehow unpivot the first table with the CatCode in 1 column and the bit value in another, but I'm not seeing a way to do it. This is part of a development project and it's in the beginning stages so I can re-do the tables if necessary, I just figured it'd be better this way instead of a row for each value.

# ? Jan 10, 2011 17:51

Aredna: Mar 17, 2007; Nap Ghost

You really should change the first table so that you can join without worrying about the pivot/unpivot. There are many reasons, but one big one is that you don't want values as part of your column headers in case that changes.

I'd probably change the first table to be something like:

code:

CustomerID    CatCode   Value
--------------------------------------
123             101       1
123             102       0
123             103       0

# ? Jan 11, 2011 02:05

Just-In-Timeberlake: Aug 18, 2003

Aredna posted:

You really should change the first table so that you can join without worrying about the pivot/unpivot. There are many reasons, but one big one is that you don't want values as part of your column headers in case that changes.

I'd probably change the first table to be something like:
code:
CustomerID    CatCode   Value
--------------------------------------
123             101       1
123             102       0
123             103       0

Yeah, I'll probably end up doing this, I was just hoping to avoid it. The other way just seemed more efficient to me for some reason.

# ? Jan 11, 2011 06:53

Aredna: Mar 17, 2007; Nap Ghost

I've got a table with current inventory levels by dimension and no history tracking other than summing up everything in a transaction table.

I'd like to start keeping a daily snapshot of this table and have my own database on the server (SQL Server 2008) where I can store the data. The current table has ~1.2m records, however only ~45k records have a non-zero on hand value and only ~10k records will change on any given day.

Rather than store the entire table each day I'm thinking of storing a full snapshot on the first day and then each day only storing records that have changed.

Here's my current update query from testing that should give an idea of how I'm currently going about it. The table is unique on (sku, dimension)

code:

insert into inventoryhistory
select cast(getdate() as date) as DataDate,
       sku,
       dimension,
       onhand
from currentinventory
except
select cast(getdate() as date),
       sku,
       dimension,
       onhand
from   (   select sku,
                  dimension,
                  onhand,
                  rank() over (partition by sku, dimension order by datadate desc) as r
           from inventoryhistory
       ) as s
where r = 1

Is there a better way to go about storing the data? I thought about storing all non-zero records daily, but the table would grow so much faster that it would quickly eclipse the current table.

My update script takes about 7s currently to run and I have to wonder if that will get out of hand in a year so any suggestions for a better way to do this are appreciated.

# ? Jan 12, 2011 00:41

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Have you considered adding triggers on the inventory table instead? You can use this to capture before-and-after data into another table, which may prove more useful than a daily snapshot. You can even build the daily snapshot from that data by effectively re-playing the log.

(Any sane inventory system should already be doing this, either at the table level or at the application level...)

# ? Jan 12, 2011 07:25

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

McGlockenshire posted:

Have you considered adding triggers on the inventory table instead? You can use this to capture before-and-after data into another table, which may prove more useful than a daily snapshot. You can even build the daily snapshot from that data by effectively re-playing the log.

(Any sane inventory system should already be doing this, either at the table level or at the application level...)

This man speaks truth, trigger populated history tables are immensely useful.

# ? Jan 15, 2011 07:34

three: Aug 9, 2007; i fantasize about ndamukong suh licking my doodoo hole

If I have an excel spreadsheet that I need to have automatically imported every night into SQL Server 2005 by a scheduled task, what would be the best way to achieve this?

# ? Jan 16, 2011 04:59

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 20:31

slartibartfast: Nov 13, 2002

three posted:

If I have an excel spreadsheet that I need to have automatically imported every night into SQL Server 2005 by a scheduled task, what would be the best way to achieve this?

Do you have access to SQL Server Integration Services? If so, that's a very easy thing to pull off with SSIS. You'll need SSIS installed on the server, and access to a machine with the Business Intelligence Development Studio (BIDS) installed. This can be found as part of the Management tools on the SQL Server install. You'll use BIDS to create the SSIS package, and then create a maintenance task or SQL Agent job on the target server that can fire the SSIS package on a specified schedule.

In SSIS, it's just a Data Flow task: Excel as the source, and SQL Server as the destination. You can find plenty of tutorials online for how to do a basic Data Flow task in SSIS 2005.

If SSIS isn't an option, you could probably write a VBScript or a PowerShell script that could do this, and then schedule that via the Scheduled Tasks in the Control Panel.

# ? Jan 16, 2011 20:27

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

«‹›303 »