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 »

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

Victor posted:

Edit: moreover², CHECK constraints cannot contain subqueries in SQL2005; try this (from the stackoverflow thread):
code:
CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK (student_id NOT IN (SELECT student_id FROM musicians 
                            UNION SELECT student_id FROM slackers 
                            UNION ...)) 
);
I get this error:
SQL2005 posted:
Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Subqueries may not be allowed in check constraints, but UDFs are.

code:

CREATE FUNCTION [dbo].[CheckAthletes] 
(
	-- Add the parameters for the function here
	@student_id int
)
RETURNS bit
AS
BEGIN
	DECLARE @validAthlete tinyint

	-- Add the T-SQL statements to compute the return value here
	SELECT @validAthlete = (SELECT CASE WHEN student_id NOT IN (SELECT student_id FROM musicians 
                                UNION SELECT student_id FROM slackers 
                                UNION ...) THEN 1 ELSE 0 END)

	-- Return the result of the function
	RETURN @validAthlete 

END

code:

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK ([dbo].CheckAthletes(student_id) = 1) 
);

# ? Dec 2, 2008 19:09

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 14:29

Syano: Jul 13, 2005

Man I have one that is killing me. I have a table constructed thusly:

CompanyID | EmployeeID | Payrate | ChangeDate

Each row in the table records when the payrate for the employee changed and what it changed to and our payroll software uses the latest record for the current pay rate.

My boss wants me to construct a report that shows him current rates plus what the rate was previously for each employee. Basically so he can see what impact raises are having on the company. So essentially I would need for each companyID and employeeID to see the latest payrate plus the one just below that as relates to dates. What is killing me is figuring out how to return a result set that shows both the current rate and the rate just before that on the same row.

I have been farting around with top statements and subqueries for the last 4 hours and I am about ready to pull my eyes out. Any ideas?

Syano fucked around with this message at 18:24 on Dec 3, 2008

# ? Dec 3, 2008 18:07

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

Syano posted:

Man I have one that is killing me. I have a table constructed thusly:

CompanyID | EmployeeID | Payrate | ChangeDate

Each row in the table records when the payrate for the employee changed and what it changed to and our payroll software uses the latest record for the current pay rate.

My boss wants me to construct a report that shows him current rates plus what the rate was previously for each employee. Basically so he can see what impact raises are having on the company. So essentially I would need for each companyID and employeeID to see the latest payrate plus the one just below that as relates to dates. What is killing me is figuring out how to return a result set that shows both the current rate and the rate just before that on the same row.

I have been farting around with top statements and subqueries for the last 4 hours and I am about ready to pull my eyes out. Any ideas?

Assuming MS-SQL (given your mention of TOP), if you have 2005 or better you could do:

code:

select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate
  from (
select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       row_number() over (partition by CompanyID, EmployeeID order by ChangeDate desc()) pay_row
  from pay_table) ranked_pay
where pay_row in (1,2)

The sub-query ranks all of the pay rate changes for each CompanyID and EmployeeID by date from most recent to oldest. Then the outer query returns the two most recent for each.

EDIT: Oh, didn't see that you want them on the same row. Try using PIVOT. Give me a second to do something with that.

Jethro fucked around with this message at 18:36 on Dec 3, 2008

# ? Dec 3, 2008 18:33

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

code:

select CompanyID,
       EmployeeID,
       [1] [CurrentPayrate],
       [2] [PreviousPayrate]
  from (
select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       row_number() over (partition by CompanyID, EmployeeID order by ChangeDate desc()) pay_row
  from pay_table) as ranked_pay
pivot
(
sum(Payrate) FOR pay_row IN ([1],[2])
) as pvt

I think that would work.

# ? Dec 3, 2008 18:42

Syano: Jul 13, 2005

Brilliant. You have put me on the path. Specifically the row_number function was the kicker. Thank you very much

Edit: Hahaha I am going to shoot my software vendor. Their stupid software does not support compatibility level 90. No PIVOT for me!

Syano fucked around with this message at 19:57 on Dec 3, 2008

# ? Dec 3, 2008 19:15

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

Syano posted:

Brilliant. You have put me on the path. Specifically the row_number function was the kicker. Thank you very much

Edit: Hahaha I am going to shoot my software vendor. Their stupid software does not support compatibility level 90. No PIVOT for me!

For grins, here's the (untested) Oracle analytic function solution to your problem:

code:

select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       lag(Payrate, 1) over (order by ChangeDate) as last_payrate
  from pay_table

# ? Dec 3, 2008 23:40

tayl0r: Oct 3, 2002; This post brought to you by SOE

Markoff Chaney posted:

For grins, here's the (untested) Oracle analytic function solution to your problem:
code:
select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       lag(Payrate, 1) over (order by ChangeDate) as last_payrate
  from pay_table

i'm pretty sure you're using oracle since the row_number() analytic is oracle only, is it not?

i dunno what the pivot command is, is that something in ms-sql?

this should do what you want.

code:

select CompanyID,
EmployeeID,
Payrate,
ChangeDate,
pay_row,
lag(payrate, 1) over (partition by companyid, employeeid order by pay_row) previous_payrate
from (
   select CompanyID,
   EmployeeID,
   Payrate,
   ChangeDate,
   row_number() over (partition by CompanyID, EmployeeID order by ChangeDate desc()) pay_row
   from pay_table) ranked_pay
   where pay_row in (1,2)
)
where pay_row = 2;

# ? Dec 4, 2008 02:23

Victor: Jun 18, 2004

row_number() and pivot are new to SQL2005.

# ? Dec 4, 2008 06:36

Neutrino: Mar 8, 2006; Fallen Rib

I have a table that will be filled with information as follows:

code:

-----------------------------------
id    year_built   year_demolished
-----------------------------------
 1        0              9999
 2       1904              0
 3       1928            1979
-----------------------------------

I want to display it so that any null value will print as a "?" and values of 9999 will print as "present". This code will do one or the other for all of the results. I think I should probably redo it using CASE statements but am unsure of the correct way to do that. Any help?

code:

while( $row=mysql_fetch_assoc($recordset)){
 if ($row["year_demolished"] == 9999){
        $yr_demolished = "present";
    }else{
		$yr_demolished = $row["year_demolished"];
     }
 if ($row["year_built"] == 0){
        $yr_built = "?";
    }else{
		$yr_built = $row["year_built"];
     }
 if ($row["year_demolished"] == 0){
        $yr_demolished = "?";
    }else{
		$yr_demolished = $row["year_demolished"];
     }

# ? Dec 4, 2008 22:53

tayl0r: Oct 3, 2002; This post brought to you by SOE

the code you posted is PHP. you could do it in the php, or you could do it in your select statement.

if you want to do it in your select statement, just look at your DB's documentation for case statement, it should be really easy.

so you would just include that in your select, like:

(this is in oracle)

code:

select
id,
case when year_built = 9999 then 'present'
  when year_built is null then '?'
  else to_char(year_built)
end year_built,
case when year_demolished = 9999 then 'present'
  when year_demolished is null then '?'
  else to_char(year_demolished)
end year_demolished
from your_table
order by id;

this assumes year_built and year_demolished are numbers, so you have to convert them to chars in the else statement because your data types all need to be consistent, and 'present' and '?' are chars.
this might mess up your sorting or something else if you're using the years for that.

i dont think mysql handles checking for null values the same as oracle so you might have to change that.

# ? Dec 4, 2008 23:59

epswing: Nov 4, 2003; Soiled Meat

Also posted in the Java thread, but I might get more of a response here.

In an RDBMS, it's typical to normalize something like UserType (or PaymentMethod) into into its own table like 1=admin, 2=viewer (or 1=cash, 2=cheque, 3=creditcard), and other tables reference them by id. Such tables won't change, typically. In Java code, it makes sense to think of these as enums, so a Payment class would have a PaymentMethod field, where PaymentMethod is an enum: PaymentMethod.CASH, PaymentMethod.CHEQUE, etc.

How can I use Hibernate to represent this common use case?

The following links are what google has to say about the issue, but these solutions seem overly complex and/or are several years old.

http://www.hibernate.org/265.html
http://www.hibernate.org/265.html
http://appfuse.org/display/APF/Java+5+Enums+Persistence+with+Hibernate
http://www.hibernate.org/272.html
http://snipplr.com/view/5379/java-5-enum-hibernate-mapping/
http://hibernate.org/203.html

Does anyone have experience with this, and/or can vouch for one of the linked solutions?

Enlighten me!

(Don't get me wrong, I'm usually all for just trying things out, but the linked solutions look old, long, complicated and numerous, and this very well may be a case of someone here saying "oh just do X, it works great".)

# ? Dec 5, 2008 22:17

Greed is eternal: Jun 8, 2008

I have a table transactions:
id | account_a | account_b | amount
and want to calculate total balance for a given account

I'm wondering if I somehow can combine these queries to get the total with just one query.

code:

select sum(amount) as credit where account_a=account
select sum(amount) as debit  where account_b=account

This is postgresql. I am currently using a PL/pgSQL function for this.

# ? Dec 6, 2008 13:05

epswing: Nov 4, 2003; Soiled Meat

code:

select sum(amount) as sum where account_a=account or account_b=account

# ? Dec 6, 2008 17:28

Greed is eternal: Jun 8, 2008

epswing posted:

code:

select sum(amount) as sum where account_a=account or account_b=account

That's just ads up everything. What I need is debit-credit.

# ? Dec 7, 2008 02:42

xobofni: Mar 28, 2003

How about :

code:

select 
(select sum(amount) from transactions where account_b = account) - 
(select sum(amount) from transactions where account_a = account) 
as balance;

# ? Dec 7, 2008 04:35

epswing: Nov 4, 2003; Soiled Meat

trekdanne posted:

That's just ads up everything. What I need is debit-credit.

Ahh, for some reason I thought one of the columns contained only negative numbers.

Edit: Comedy case-when statement:

code:

select sum(case when account_a=10 then (-1*amount) else amount end) as balance
from transactions
where account_a=10 or account_b=10;

Works in mysql, not sure of postgresql syntax though I'm sure it's possible

epswing fucked around with this message at 06:16 on Dec 7, 2008

# ? Dec 7, 2008 06:03

tayl0r: Oct 3, 2002; This post brought to you by SOE

this is easy (once you know how to do it).
you can use the result set of each query, join them together, then select from it. this is pretty basis sub-select / nested select stuff.

code:

select credit.credit, debit.debit, debit.debit - credit.credit balance
from
( select sum(amount) as credit from transactions where account_a=account ) credit,
( select sum(amount) as debit from transactions where account_b=account ) debit;

i'm pretty sure this sql will work in any sql db.
notice i'm not joining the credit and debit tables together, this is because you're only going to get 1 row back per sub query. if you added accountID in there, you'd want to join by that:

code:

where credit.accountID = debit.accountID

if you're in oracle you can do a more graceful solution with analytic sums, showing you how the balance changes over time.

the previous solution will work fine too, and will be a little faster. if you need to do any more complicated things though, you might want to use sub-selects, like in my example.

tayl0r fucked around with this message at 09:15 on Dec 7, 2008

# ? Dec 7, 2008 09:10

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

Here is some ugly SQL code that I'm working on for a report.

code:

DECLARE @regionId INT
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @totalNumDays INT
DECLARE @dailyOptionTitle NVARCHAR(30)
DECLARE @remainingPercent INT
DECLARE @skipDays INT

SET @regionId = 1
SET @startDate = '10/1/2008'
SET @endDate = '10/31/2008'
SET @totalNumDays = DATEDIFF(day, @startDate, @endDate) + 1

SET @skipDays = (DATEDIFF(dd, @startDate, @endDate) + 1)
-(DATEDIFF(wk, @startDate, @endDate) * 2)
-(CASE WHEN DATENAME(dw, @startDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @endDate) = 'Saturday' THEN 1 ELSE 0 END)

SET @totalNumDays = @skipDays

SELECT tbl_SalesTool_Associates.AssociateName, tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime,

Abs(CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100 - 100, 0) AS INT)) AS RemainingPercent,

CASE tbl_SalesTool_DailyOptions.Title
WHEN 'Travel - Rep' THEN tbl_SalesTool_RepOffices.Title
ELSE tbl_SalesTool_DailyOptions.Title
END as DailyOption,

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
ORDER BY AssociateName

This code produces this output:

code:

AssociateName   SchedulingOption  PercentOfTime  RemainingPercent  
John Doe	1	          80     	 20
John Doe        2                 10             90
Jane Lol	1	          15	         85
Jane Lol        2                 5              95

Except I want RemainingPercent to be added after it has totaled up the PercentOfTime for each user. So it should look like this:

code:

AssociateName   SchedulingOption  PercentOfTime  RemainingPercent  
John Doe	1	          80     	 10
John Doe        2                 10             10
Jane Lol	1	          15	         80
Jane Lol        2                 5              80

I've been picking at it for a day or so but have yet to come across a good solution. I imagine I need to use a sub-query but haven't been able to get it right. Any help would be greatly appreciated.

# ? Dec 8, 2008 22:51

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

Gary the Llama posted:

stuff

Assuming SQL2005, change

code:

SELECT tbl_SalesTool_Associates.AssociateName, tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime,

Abs(CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100 - 100, 0) AS INT)) AS RemainingPercent,

CASE tbl_SalesTool_DailyOptions.Title
WHEN 'Travel - Rep' THEN tbl_SalesTool_RepOffices.Title
ELSE tbl_SalesTool_DailyOptions.Title
END as DailyOption,

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
ORDER BY AssociateName

code:

SELECT AssociateName,
       SchedulingOption,
       PercentTime,

100 - sum(PercentTime) over (partition by AssociateName) AS RemainingPercent,

       DailyOption
FROM (
SELECT tbl_SalesTool_Associates.AssociateName,
       tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime,

CASE tbl_SalesTool_DailyOptions.Title
WHEN 'Travel - Rep' THEN tbl_SalesTool_RepOffices.Title
ELSE tbl_SalesTool_DailyOptions.Title
END as DailyOption

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title) sales_stats
ORDER BY AssociateName

This won't work on SQL 2000.

# ? Dec 8, 2008 23:09

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

Jethro posted:

Assuming SQL2005, change

100 - sum(PercentTime) over (partition by AssociateName) AS RemainingPercent,

That did the trick. Never seen that before, that's pretty awesome. Now to look it up so I can understand what it does. Thanks a million!

# ? Dec 8, 2008 23:20

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

Gary the Llama posted:

That did the trick. Never seen that before, that's pretty awesome. Now to look it up so I can understand what it does. Thanks a million!

http://msdn.microsoft.com/en-us/library/ms189461.aspx

# ? Dec 8, 2008 23:55

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

Gary the Llama posted:

code:
AssociateName   SchedulingOption  PercentOfTime  RemainingPercent  
John Doe	1	          80     	 10
John Doe        2                 10             10
Jane Lol	1	          15	         80
Jane Lol        2                 5              80
I've been picking at it for a day or so but have yet to come across a good solution. I imagine I need to use a sub-query but haven't been able to get it right. Any help would be greatly appreciated.

Okay, instead of showing the data like that, is there any way to show the RemainingPercent on a new row with SchedulingOption = 0?

code:

AssociateName   SchedulingOption  PercentOfTime
John Doe	1	          80
John Doe        2                 10
John Doe        0                 10

Hope that makes sense.

# ? Dec 9, 2008 22:24

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

A simple union could probably do the trick.

code:

SELECT tbl_SalesTool_Associates.AssociateName,
       tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
UNION ALL
SELECT tbl_SalesTool_Associates.AssociateName,
       0 SchedulingOption,
100 - CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
ORDER BY AssociateName

EDIT: That problem is due to an extra something in the GROUP BY part of the second half of the query. I don't have any idea about what your data means, so I don't know what it is, but I'd guess it's the tbl_SalesTool_DailyOptions.Title, so try taking that out of the second GROUP BY.

Jethro fucked around with this message at 22:50 on Dec 9, 2008

# ? Dec 9, 2008 22:28

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

Jethro posted:

A simple union could probably do the trick.

I have to run to a mid-wife appointment, but that didn't have the intended affect. It produced this:

code:

AssociateName   SchedulingOption  PercentOfTime
John Doe	1	          80
John Doe        2                 10
John Doe        0                 20
John Doe        0                 90

But instead I'm looking for only one result with SchedulingOption=0 and PercentOfTime=10. I'll take a closer look when I get home.

# ? Dec 9, 2008 22:46

milkaxor: Mar 5, 2003; I choose you!

I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database?

# ? Dec 9, 2008 23:46

Halo_4am: Sep 25, 2003; Code Zombie

milkaxor posted:

I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database?

1. You never know when you might need the data regardless of how stupid it sounds. Questions come up eventually of 'can you cross post how many people ordered every 4 weeks, but only on Tuesdays?' and it turns out for some reason that would actually be excellent info to have.

2. Delete 1 record paves the way for the eventual delete statement with no 'where' clause.
- 2a. On this subject, make sure you're running transactional backups every 30 minutes or less.
- 2b. Try to make sure all things web are accessing your data via stored procedures and not direct sql statements. This makes performance better via a lot of nifty automatic metrics SQL uses, and it also prevents a lot of that nasty SQL injection you may have heard about. Oh, and it means an easier time of upgrading and future data maintenance, because you can change the layout of tables, databases and the application won't care as long as the stored procedure responds with the same info it always has.

3. Depending on the kind of volume you expect, the amount of disk space saved by actually deleting records vs archiving records is negligible. However since you are archiving data, it will be trivial at a later point to just split it off onto some sort of report database running the cheapest storage you can find to maintain functionality at low cost.

4. Databases are more difficult to restore than standard files. If for any reason at some point down the line something is deleted by mistake, you'll have to restore a 2nd instance of the whole database and run a compare, or identify and rollback the trans log (also undoing everything that wasn't deleted by mistake). This will be an endeavor and the whole time you'll be kicking yourself for not building some sort of undelete feature.

In short... it's simply a future proofing practice. The purpose of this database as it is now may change in the future for something totally unknown. You may find yourself needing features that never seemed needed before.

Halo_4am fucked around with this message at 23:54 on Dec 9, 2008

# ? Dec 9, 2008 23:52

Operation Atlas: Dec 17, 2003; Bliss Can Be Bought

milkaxor posted:

I am working with a DBA that is refusing to allow me to delete information from a transactional database. His recommended approach is to consume the data and set a "active" field to zero. Once I consume the data it is no longer useful, not even for historical purposes. I am wondering why he would want a bloated database. It seems silly to just leave sometimes-redundant and no-longer-useful information in the database. We are using sql server 2005 if that makes any difference. Would replication cause any problems with deleting items form the database?

The DBA is probably just nervous about you leaving off/screwing up a WHERE clause and clobbering too much stuff. Much easier to just flat-restrict your DELETE privileges and delete the rows in bulk manually every now and again.

# ? Dec 9, 2008 23:53

tayl0r: Oct 3, 2002; This post brought to you by SOE

Gary the Llama posted:

I have to run to a mid-wife appointment, but that didn't have the intended affect. It produced this:
code:
AssociateName   SchedulingOption  PercentOfTime
John Doe	1	          80
John Doe        2                 10
John Doe        0                 20
John Doe        0                 90
But instead I'm looking for only one result with SchedulingOption=0 and PercentOfTime=10. I'll take a closer look when I get home.

You basically need to get 1 query that gives you the rows for scheduling options 1 & 2, and then another query that gives you 1 row for scheduling option 0.

Once you have those 2 queries, just union them together, as previously mentioned.

code:

select associatename, schedulingoption, percentoftime
from blah where blah
UNION ALL
select associatename, 0, percentoftime
from blah where blah

So to make query 2, you'd just need to get the sum of the percentoftime from query 1, then subtract it from 100.

One of the concepts with SQL that a lot of programmers have a hard time grasping is that you can't just arbitrarily add rows to a result set. The rows have to come from somewhere.

# ? Dec 10, 2008 00:35

tayl0r: Oct 3, 2002; This post brought to you by SOE

Praetorian42 posted:

The DBA is probably just nervous about you leaving off/screwing up a WHERE clause and clobbering too much stuff. Much easier to just flat-restrict your DELETE privileges and delete the rows in bulk manually every now and again.

That is a good point, but you could easily use write stored procedures for everything the application developers want to do to the database (inserts, updates, deletes), and then only give them access to select + run the stored procedures.

The DBAs / database developers could own the stored procedures, or at least look over the code first to ensure the application developers weren't doing anything stupid with them.

Plus, it's a lot more secure this way since less people will have insert / update / delete access to the database.

# ? Dec 10, 2008 08:27

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

Jethro posted:

EDIT: That problem is due to an extra something in the GROUP BY part of the second half of the query. I don't have any idea about what your data means, so I don't know what it is, but I'd guess it's the tbl_SalesTool_DailyOptions.Title, so try taking that out of the second GROUP BY.

That did the trick. Unfortunately, when I remove DailyOptions.title from the second group by, I no longer get DailyOptions.title in my result set, which I need. When I put it back in, I get two entries per user where SchedulingOption=0, when there should only be one.

And yeah, SQL is my biggest weakest as a programmer. So frustrating.

# ? Dec 10, 2008 16:02

Xae: Jan 19, 2005

tayl0r posted:

That is a good point, but you could easily use write stored procedures for everything the application developers want to do to the database (inserts, updates, deletes), and then only give them access to select + run the stored procedures.

The DBAs / database developers could own the stored procedures, or at least look over the code first to ensure the application developers weren't doing anything stupid with them.

Plus, it's a lot more secure this way since less people will have insert / update / delete access to the database.

Logical deletes make a lot of sense, particularly if you are dealing with anything involving money. You really don't want to ever lose a record involving cash. It has less to do with worrying that you will screw up and more to do with keeping a history.

# ? Dec 10, 2008 17:13

Gary the Llama: Mar 16, 2007; SHIGERU MIYAMOTO IS MY ILLEGITIMATE FATHER!!!

tayl0r posted:

You basically need to get 1 query that gives you the rows for scheduling options 1 & 2, and then another query that gives you 1 row for scheduling option 0.

Once you have those 2 queries, just union them together, as previously mentioned.
code:
select associatename, schedulingoption, percentoftime
from blah where blah
UNION ALL
select associatename, 0, percentoftime
from blah where blah
So to make query 2, you'd just need to get the sum of the percentoftime from query 1, then subtract it from 100.

One of the concepts with SQL that a lot of programmers have a hard time grasping is that you can't just arbitrarily add rows to a result set. The rows have to come from somewhere.

Okay, here's a stupid question - though I guess that's what this thread is for. How can I make that second query's results be added to my first query? When I run two separate queries I get two separate result sets, but that's not what I want.

# ? Dec 10, 2008 19:06

tayl0r: Oct 3, 2002; This post brought to you by SOE

Gary the Llama posted:

Okay, here's a stupid question - though I guess that's what this thread is for. How can I make that second query's results be added to my first query? When I run two separate queries I get two separate result sets, but that's not what I want.

UNION =P

if you do this:
select A, B, C from foo
UNION ALL
select X, Y, Z from bar

you get:
A B C
X Y Z

The number of columns and the datatypes need to match up for a UNION to work.
There is also a difference between UNION and UNION ALL.
UNION will check your final result set and remove duplicates while UNION ALL will not.

There's also MINUS, which removes any rows from the first result set that also appear in the second.

Does that answer your question?

# ? Dec 10, 2008 19:41

Victor: Jun 18, 2004

If tayl0r's union vs. union all doesn't answer your question, Gary the Llama, I suggest you give us a reproducible, simplified example that exhibits your problem. Make it complete with data, either with create table and insert, or by using CTEs to fake tables, like this:

code:

with Person as (
    select id = 1, name = 'Charles' union all
    select id = 2, name = 'Matthew' union all
    select id = 3, name = 'Harold'
), Pet as (
    select id = 1, person_id = 1, name = 'Spot' union all
    select id = 2, person_id = 1, name = 'Tasha' union all
    select id = 3, person_id = 3, name = 'Mutface'
)
select  *
from    Person p
left join Pet on person_id = p.id

# ? Dec 10, 2008 20:18

tayl0r: Oct 3, 2002; This post brought to you by SOE

The "left join" "inner join" "outer join" syntax always freaks me out. I much prefer using (+) for when I want to use an outer join, and leaving out any of the join keywords. Maybe that's an Oracle thing.

# ? Dec 10, 2008 20:25

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

tayl0r posted:

The "left join" "inner join" "outer join" syntax always freaks me out. I much prefer using (+) for when I want to use an outer join, and leaving out any of the join keywords. Maybe that's an Oracle thing.

It is.

# ? Dec 10, 2008 22:17

Victor: Jun 18, 2004

Keep in mind that if you use the join keyword, you can effectively separate the projection you're making (e.g. all columns from all included tables) from the criteria being applied to that projection. Otherwise, it's all in one big mess, not necessarily sorted, and harder to parse. I suggest you have some patience with join -- I think you will start to get used to and then appreciate it.

# ? Dec 11, 2008 01:00

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

In SQL Server 2005, how do you check if a temporary object (table or procedure) already exists? They seem to lack object id's (rather, object_id returns null).

Edit: It seems tempdb..sysobjects has them listed... Sort of.
Edit^2: And it seems that their fully qualified names for object_id to work are tempdb..#table

Triple Tech fucked around with this message at 00:32 on Dec 13, 2008

# ? Dec 13, 2008 00:28

joojoo2915: Jun 3, 2006

MSSql - I have a database with soybean prices over the course of the year from several different elevators. Not all of these elevators had prices updated every day so I need a list of all the elevators with more than 100 days worth of data. How can I accomplish this? The columns pertinent to this problem are cashid (unique identifier for each elevator), and tdate (the date the price was entered). Any help would be super awesome. Thanks!

# ? Dec 14, 2008 02:20

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 14:29

Victor: Jun 18, 2004

joojoo2915 posted:

MSSql - I have a database with soybean prices over the course of the year from several different elevators. Not all of these elevators had prices updated every day so I need a list of all the elevators with more than 100 days worth of data. How can I accomplish this? The columns pertinent to this problem are cashid (unique identifier for each elevator), and tdate (the date the price was entered). Any help would be super awesome. Thanks!

code:

select  cashid
from    (
    -- guarantee one row per elevator/date combination
    select  cashid
    from    the_table
    group by cashid, tdate
)t -- derived table syntax requires an alias
group by cashid
having count(*) > 100 -- only pull elevators with more than 100 days worth of data

# ? Dec 14, 2008 02:28

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

«‹›303 »