|
OK, How can I make a SUM statement evaluate to NULL if one of the SUM'd records is NULL? Or an equivalent of course...
I have a table with four fields, we'll call them id, date, time, readvalue. Id is a foreign key to a table holding meter information to which the reading pertains. Each meter belongs to a site, and a site can have between 1 and 10 meters. Using SUM and GROUP BY I can create aggregate SUM information. The problem is that a missing read value is represented by a NULL, (as 0 could be a valid read value, as can both negative and positive values), and for each date and time period if one meter belonging to a site has a NULL read value, the site level date/period read value should be derived as NULL. In case you want to know, there are 96 read periods in a day, (1/15 minutes)
Table Structure
MeterId ReadDate TimeIndex ReadValue
int datetime int decimal(19,10)
Aggregating Query
<br />
select<br />
m.SiteLevelId,<br />
mr.ReadDate,<br />
mr.TimeIndex,<br />
SUM(mr.ReadValue) as 'SiteReadValue'<br />
from<br />
MeterReadingTable mr INNER JOIN MeterTable m ON mr.Id = m.Id<br />
group by<br />
m.SiteLevelId,<br />
mr.ReadDate,<br />
mr.TimeIndex<br />
GO<br />
SiteLevelId is a foreign key on the meter table, referencing the site to which it belongs, (one to many as a site can have many meters)
Any idea's?
I'd like to avoid using a cursor, which I'm sure I could to get around this, as it's going to be pretty convoluted and probably very innefficient if I go that route.
Rhys
A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation...
Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die.
|
|
|
|
|
No worries - solved it 
Rhys
A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation...
Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die.
|
|
|
|
|
Try to use case :
SUM( case when len(mr.ReadValue)=0 then 0 else mr.ReadValue end )
hope it helpful.
James
|
|
|
|
|
Doesn't quite do what I want. for info. this works...
<br />
select <br />
case when count(mr.ReadValue) = count(*) then <br />
sum(mr.ReadValue) <br />
else <br />
null <br />
end <br />
as 'SiteReadValue'<br />
|
|
|
|
|
put isnull( mr.ReadValue,0)...tobe as follow;)
select
m.SiteLevelId,
mr.ReadDate,
mr.TimeIndex,
SUM(isnull( mr.ReadValue,0)) as 'SiteReadValue'
from
MeterReadingTable mr INNER JOIN MeterTable m ON mr.Id = m.Id
group by
m.SiteLevelId,
mr.ReadDate,
mr.TimeIndex
GO
keepSmile
|
|
|
|
|
I would like to create a SQL database with password and username, but when i do this i get the message "User not associated with a trusted SQL server connection". I read some FAQ:s on the net and all of them said something of mixed mode. So i changed the SQL server to mixed mode from the registry according the instructions on MSDN. But i still get this message (everything works fine with windows integrated security).
Can someone please advice?
|
|
|
|
|
Hi, I wrote an SP and select command of this SP is written to a parameter (@SqlString). there is a lot of if statement that can change the @SqlString.. how can I execute this select command in SP? (exec @SqlString doesn't work
|
|
|
|
|
|
|
I came across one article on data access layer design pattern. It was to show how to separate data access into layers such that one can easily switch between different:
1. OR Mapping tools, for instance: from NHibernate to ObjectSpace
2. Database vendor
I didn't read the article in detail, but I can't remember where to find the article now....
Norman Fung
|
|
|
|
|
Hello,
I've got two databases and need to copy one table to the another database. The table doesn't exist in the target DB, so I need a table with all columns etc.
Are there any fast solutions or must I get all values and create the sql command?
|
|
|
|
|
What DB are you using. You can use the DTS in SQL Server.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I have a mdb database.It has a password (123456), i found out password with a password cracker program. I exactly dont know why this password exist . I try to connect this database via VB.Net, OLEDB Connection. My Connection string is "Provider=Microsoft.Jet.OLEDB.4.0;Password=123456;Data Source=C:\Documents and Settings\creek\Desktop\AC115DataBase.mdb; Mode=Read;Persist Security Info=True"
i cant connect to database with this connection string. I get this error "Application cannot be started. Work File is missed, or any user opened file excluesive mode"
i dont know user name or any extra information about mdb file. I am so confused.
Question is What is wrong,
|
|
|
|
|
Where did you get the database?
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
at the end i solved my problem myself  Line i missed is Bold.
Dim oOleDbConnection As New OleDb.OleDbConnection<br />
Dim strPassword As String = "123456"<br />
Dim strDataBasePath As String = "C:\Documents and Settings\creek\Desktop\AC115DataBase.mdb"<br />
<br />
Dim sConnString As String = _<br />
"Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
"Data Source=" & strDataBasePath & ";" & _<br />
"Jet OLEDB:Database Password=" & strPassword & ";"
|
|
|
|
|
I have two tables Animal and AnimalClassification joined by a forgein key in the AnimalClassification table called HerdBookNumber. I want to count animals that have a given sire (paramSire) in each of six class types (AnimalClassification.FinalClass). The problem is that each animal could have several enteries in the AnimalClassification table and I only want the entry with the highest Lactation Number (AnimalClassification.LactationNumber) for each animal. The query below counts each entry in the AnimalClassification table not just the one with the highest lactation nubmer.
NOTE: It is not the highest LactationNumber in the AnimalClassification table but the highest LactationNumber in the AnimalClassification table for the associated animal.
SELECT COUNT(Animal.HerdBookNumber) AS Daughters, AnimalClassification.FinalClass
FROM Animal INNER JOIN AnimalClassification ON Animal.HerdBookNumber = AnimalClassification.HerdBookNumber
WHERE (((Animal.Sex)=1) AND Animal.Sire = paramSire)
GROUP BY Animal.Sire, AnimalClassification.FinalClass;
Could you please suggest how to get the information I require?
Thanks
|
|
|
|
|
Something like this might work:
SELECT
COUNT(a.*) AS Daughters,
ac.FinalClass
FROM
Animal a
INNER JOIN
AnimalClassification ac
ON (a.HerbBookNumber = ac.HerbBookNumber)
WHERE
(ac.LactationNumber =
(SELECT
MAX(LactationNumber)
FROM
AnimalClassification
WHERE
HerbBookNumber = a.HerbBookNumber)) AND
(Animal.Sex = 1) AND
(Animal.Sire = paramSire)
GROUP BY
ac.FinalClass
|
|
|
|
|
Hi,
I found this note from the download page of ODP.Net
- Do not install ODP.NET over a previous Oracle Home version (e.g. Oracle8i) if you have applications dependent on the older Oracle Home and are not certified with an Oracle9i Release 2 Home. For example, the Microsoft OLE DB provider for Oracle (MSDAORA) may not work with Oracle9i Release 2 client because the provider has not been tested for it.
According to it, my understanding is:
(1) ODP.NET only work with oracle client of Oracle9i Release 2 or high version
(2) if there's an application using oracle8i client and connect to oracle by odbc, then it's not recommended to install oracle 9i client and ODP.Net on it
(3) if there's an application using oracle9i client and connect to oracle by odbc, then it's ok to install odp.net and my .net application on it
Correct me if I am wrong. Thanks in advance.
|
|
|
|
|
i want ot import data from excel .xls file to sql server 2000 into an existing table.
should i use some stored procedures or else
softpioneer
|
|
|
|
|
You should be able to use the DTS Wizard to accomplish this task. Right-click your database in Enterprise Manager and select All Tasks > Import Data. For the data source pick the Microsoft Excel 97-2000 then select the file to import and click Next. The destination should already be the database you selected; click Next.
If you just want a copy of the data in a particular sheet, select 'Copy table(s) and view(s) from the source database' and click Next. Now check the sheet(s) you want to copy. Under Destination, select the target table. You can customise the transformation by clicking the [...] button in the Transform column.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
As you know, the =, like operators for string comparison in MS SQL are just for not-case sensitive string comparison. So my concern is how to compare two strings in case sensitive mode?
Thank in advance!
|
|
|
|
|
|
Cast the string to varbinarys and then compare - here is a small script you can run in Query Analyzer to test it if you would like.
DECLARE @Str1 VARCHAR(20)
DECLARE @Str2 VARCHAR(20)
DECLARE @Str3 VARCHAR(20)
SET @Str1 = 'abcdefg'
SET @str2 = 'ABCDEFG'
SET @Str3 = 'abcdefg'
IF @Str1 = @str2
Print 'Case Insentative: Equal'
ELSE
Print 'Case Insentative: Not Equal'
IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str2 AS VARBINARY(50))
Print 'Case Sensitive 1: Equal'
ELSE
Print 'Case Sensitive 1: Not Equal'
IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str3 AS VARBINARY(50))
Print 'Case Sensitive 2: Equal'
ELSE
Print 'Case Sensitive 2: Not Equal'
|
|
|
|
|
That's great! Thanks all of you!
|
|
|
|
|
How can i create a new database by PL/SQL statements ?
Thanks!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
