Search For Missing Values In Advanced Query

Jan 15, 2013

KEYIDGROUP
1 1 a
2 1 b
3 2 a
4 2 b
5 3 a
6 3 b
7 4 a
8 5 a

This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.

query should reveal answer should be missing the group b
KEYID
7 4
8 5

I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B

View 3 Replies


ADVERTISEMENT

Advanced Search Query In SQL Server2000

Jul 25, 2006

Hi query-experts,

I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:

Table has the fields: id, fruit, color

User enters: apple pear green

The query should return all records that contain apple AND pear AND green in any of the fields.

Now I do it like this:

SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')

this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.

Question: is there an easier way to achieve this?



(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)

SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;

View 3 Replies View Related

Query To Get Range Of Values Missing

Mar 7, 2008

I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.

For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!

StartStop
---------
20452296
26173775
568936948
3727084237
84409178779
179013179995
180278259121
259292306409
307617366511

View 6 Replies View Related

Show Zero Values For Missing Data In Query Resultset

Jan 7, 2008

Hello,

I have the following query which grabs monthly usage data which is logged to a database table from a web page:


SELECT Button = CASE ButtonClicked

WHEN 1 THEN '1st Button'

WHEN 2 THEN '2nd Button'

WHEN 3 THEN '3rd Button'

WHEN 4 THEN '4th Button'

WHEN 5 THEN '5th Button'

WHEN 6 THEN '6th Button'

WHEN 7 THEN '7th Button'

WHEN 8 THEN '8th Button'

WHEN 9 THEN '9th Button'

ELSE 'TOTAL'

END,

COUNT(*) AS [Times Clicked]

FROM WebPageUsageLog (NOLOCK)

WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth

GROUP BY ButtonClicked WITH ROLLUP

ORDER BY ButtonClicked

The results look like this:

TOTAL 303
1st Button 53
2nd Button 177
3rd Button 10
4th Button 4
6th Button 18
7th Button 19
8th Button 21
9th Button 1

If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.

-Dave

View 3 Replies View Related

Advanced Search Box????

Aug 15, 2006

Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a
advanced search facility to my site. Problem is I do not know  how to begin.
The website is a business directory and displays company names and services that  they provide.
Here is where I am at....
So far I have made one query which allows users to search the database by "town" such as if they enter  "London" in the text box, it returns the relevant records into a gridview.
Rather than have a seperate text box for each query, I want to  provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc.
I have seen compact advanced search boxes on many sites!.
Replies greatly appreciated.
prontonet
 
 
 
 

View 8 Replies View Related

Install Replication Missing Sqlrun_sql.msi | SQL Server Express With Advanced Services

Jan 23, 2008

Hello,

on the server this version is running:

SQL Server Express with Advanced Services


Datenbankmodul
[Version: 9.2.3042.00 Edition: Express Edition Patchebene: 9.2.3042 Sprache: Deutsch (Deutschland)]

Reporting Services
[Version: 9.2.3042.00 Edition: Express Edition Patchebene: 9.2.3042 Sprache: Deutsch (Deutschland)]

Now I will install the "replication" modul, but when I select the option, install replication the system need the
"sqlrun_sql.msi.

I have select the folder (the install folder where the "sqlrun_sql.msi" is) but I become the message:

like this: the "sqlrun_sql.msi" is not the right.... for you SQLEXPRESS Version......



here I found the same .....error:

http://www.codeprof.com/dev-archive/71/19-95-712279.shtm


what can I do,

thanks for help, marc

View 1 Replies View Related

Stored Procedure - Advanced Search

Oct 9, 2006

HIi want to create a procedure, basically i'll have 4 input parametres (Title, Category, ReleaseClass and BuyPrice)title will come from a textbox, the rest from dropdownlistsif a user enters a title, selects a ReleaseClass and BuyPrice, But doesnt select a category, all categories should be returned - You know what i meanhow do i go about this - Any Ideas??Cheers!!!

View 7 Replies View Related

How To Upgrade SQL 2005 Express Instance To Advanced Services For Text Search

Apr 8, 2008

Have searched a good bit for a KB or other article but haven't been able to find one.

I have a 2003 server with SQL Server 2005 Express installed, but not the Advanced Services version. Recently installed an application that needs the Full Text search capability. Would prefer not to remove SQL Server Express and reinstall the Advanced version because I'm also using Symantec Backup Exec 11d which has its own instance on the SSExpress installation.

Is it possible to upgrade an instance to Advanced Services? If not, is it possible to upgrade the entire install?

There's got to be a how-to article somewhere on how to do this. Just can't seem to locate it at Microsoft or with Google.

Thanks for any help.

View 1 Replies View Related

Starting Full-text Search On SQL Server 2005 Express Advanced Edition

Aug 10, 2006

I have upgraded a SBS 2003 MSDE database (instance MSSQL$SHAREPOINT) to SQL Server 2005 Express Advanced Edition. This worked without a problem even when I enabled all the options for the upgrade including Full-text search.

I now want to have Full-text search on this instance of SQL 2005 with database name of STS_EVEREST_1.

I first tried to use the T-SQL command of "CREATE FULLTEXT CATALOG BBVisionCatalog AS DEFAULT;"
I now know that the original database was created under SQL 2000 and therefore I needed to use SQL 2000 commands. So I used the following script:

USE STS_EVEREST_1
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'BBVisionCatalog', 'create';

It produced the following ERROR messages:

(1 row(s) affected)
Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.

I checked to see if the Microsoft Search Service was running. It was running.

Anyone know how I can fix this?


Regards

Rolf Pfotenhauer

email: rolfpf@yahoo.com.au

View 9 Replies View Related

Full-Text Search Problem When I Upgrade From SQL 2005 Express To Express Advanced Services

Mar 1, 2007

Hi. I'm trying to get full-text search working on my SQL 2005 Express with Advanced Services and am having problems. I thought that I installed it correctly, and when I look in the services running, I see it SQL Server Fulltext Search(MSSQLSERVER). Also note that I have SQL Server 2005 Standard installed on this same laptop. I don't know if that Full text Search applies to the Standard, Express or both.

What I do see is that if I connect to the database engine named <mylaptop>, I can see the version is 9.0.1399 and I can see the check box to enable full-text indexing in a particular databases properties. When I run SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'), I get a 1.

However, when I connect to the engine name <mylaptop>SQLExpress, I se the version is 9.0.3042, and I don't see the same check-box to enable full-text indexing. When I run SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'), I get a 0.

I've never tried this before so I know I'm probably missing something basic. But, I have searched quite a bit and not found my answer. So, I'm looking to you for help.

Thanks in advance.

John.

View 3 Replies View Related

SQL 2012 :: Create A Script To Search For Missing Word Or Phrase

Jan 16, 2015

We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.

Creating a script to search for a missing word or a phrase in SQL Server script.

Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>

but generally developers write only <table> <alias>

View 4 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

Filling In Missing Values

Oct 29, 2004

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9

View 1 Replies View Related

Clusters With Missing Values

Jan 7, 2007

Hello all and a happy new year!

I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)

Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?

Thank you in advance.

View 4 Replies View Related

Transact SQL :: Get Missing Values From A Column?

Nov 16, 2015

I have table with column having values 1,2,3,5,6,10.

I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.

I want to get the values 4,7,8,9.

View 8 Replies View Related

SQL And ASP.NET Advanced Query

Jun 19, 2008

I want to query a database through a ASP.NET page   and I want to set the page up like an advanced search page would look.  My question is,    how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values.    For instance, if someone wants to query by TRIP id and not vehicle ID   then how do I write the where claue? 

View 10 Replies View Related

Advanced Query

Feb 7, 2006

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName
so in my select I want to add something like ev.approved which then that brings back either Null or 0.  Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.  I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

View 14 Replies View Related

Stored Procedure - Swapping Out Missing Values

May 16, 2008

I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field). 
I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image
I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string:
  select product_code,
CASE can_image_en
?? When (can_image_en LIKE '%.jpg') then can_image_en
Else 'no_image_EN.jpg'
End as can_image_en,
none of these do the trick either (some are bad syntax obviously):
? When (can_image_en LIKE '%.jpg') then can_image_en
? When LIKE '.jpg' then can_image_en
? When '%.jpg' then can_image_en
? When right(can_image_en,4) = '%.jpg' then can_image_en  This is the one that has correct syntax, though it seems to return false in ALL cases  CASE can_image_en
When '%.jpg%' then can_image_en
Else 'no_image_EN.jpg'
 

 

View 5 Replies View Related

Missing Identity Values In Primary Keys

May 29, 2012

We are facing the following issue, several machines/users that are executing very often a command similar to :

INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;

Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables

These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.

View 4 Replies View Related

Line Chart With Breaks For Missing Values

Dec 13, 2007

I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.

Thanks!
sash

View 1 Replies View Related

SQL Query Advanced Option

Apr 18, 2001

I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..

View 1 Replies View Related

Advanced Union Query

Sep 6, 2004

Hi!

I'm trying to get the following query to work:

SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer
FROM item, t15
WHERE item.row_id = t15.id AND t15.f167 > getDate()
UNION ALL
SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id
+ '> KAN < / a > '
FROM item, t30
WHERE item.row_id = t30.id AND t30.f221 > getDate()
ORDER BY start DESC

The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.

So the question is basically, how do I get the following to work:

'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'


Vidar

View 6 Replies View Related

Advanced Sql-Shape Query - Help

Jan 12, 2006

Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !

View 3 Replies View Related

Advanced SQL Query Problem ...

Jul 20, 2005

Dear Expert,I have 2 tables as the following,Table : ProductColumn : ID, Code, NameTable : TrxColumn : Trx_ID, Trx_Date, Product_IDHow can perform the following query in MSSQL or is there any other wayto perform the same query ??? For you info I can do this in Sybase andit's work fine. Thank you.================================================== ==========Select Trx.Product_ID,(Select Produc.CodeFrom ProductWhere Product.ID = Trx.Product_ID) as Product_CodeFrom TrxWhere Product_Code = '0001'================================================== ==========Regards,Chen Fock Siong2004-05-27

View 4 Replies View Related

SQL Server 2012 :: Date Sequence Missing Values?

Apr 27, 2015

Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.

-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'

-- test data

;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL

[code]....

View 2 Replies View Related

Ug! Replicated Tables Missing Default Values And Identities.

Feb 8, 2007

Ok, so I must have screwed something up.

I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:

1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.

2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €œIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.

Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.

So, what did I do to cause this situation, and how to I fix it?

Thanks a bunch!

md

View 9 Replies View Related

Question On Dealing With Missing Values For Training Models

Jul 19, 2007

Hi, all,



Just really wonder what is the good idea to deal with missing values? Should we leave the missing values there in the traning data set ? Or replace it with other values?



What I am really concerned is that if we simply replace those missing values with other values, then how will it really affect the correctness of the training models?



I am looking forward to hearing from you for the above issue and it will be really great if we have any kind of best practices of dealing with this issue.



Thanks.



With best regards,



Yours sincerely,

View 4 Replies View Related

SQL Query JOIN Needed? Too Advanced For Me

Sep 5, 2006

THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME.  I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp          -   DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00       123         125             1289                      121 2006/8/12 12:05       124         128             1291                      1232006/8/15 12:00        130        128              1190                     1242006/8/15 1:00          131        127             1196                      123  The columns labeled DATA do not really matter.  The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY.  Example Output- 2006/8/12      124   128   1291   1232006/8/15      131   127   1196   123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is:  SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C,
CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0)
FROM Table_1 T1
WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp)
ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days.  Can anyone help me correct this?  Thanks in advance for your help. 

View 34 Replies View Related

Help With A Confusing And Advanced Query - If Exsists?

Nov 14, 2003

Ok,

I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.


Dbo.Products – This table holds product names and information.

Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID

Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.

Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID

Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.

Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are “required� (no product will be returned that does not have what is in the list).

I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.

If any of you know how to do the above with a subquery of some kind, please let me know.

Dave Larson

View 1 Replies View Related

Values For Field Missing When Attempting To Modify Filegroup To Readwrite

Jun 2, 2015

I'm trying to swap out old partitions and getting "An error occurred while processing 'AltFile' metadata for database id 12 file id 605" 605 is missing from sys.sysfiles. I've tried adding new file groups since it seemed to be assigning them in that range to allow the command to find a match. Once created and I issue the alter command the file id of the target file changes to something else in the missing range.

The file id values seem to be managed solely by sqlserver so I'm not sure what to try. There are hundreds of files with millions of rows and the method has been used problem free for years. I do occasionally get "unable to remove file because it's not empty" once in a while which may be related. I wind up having to shrink those and leave them in existence.

The target file group has an existing file id value when you join sys.sysfiles using the filegroup name.

I partition data in 2 tables on one filegroup per day. I swap out parition 1 each day which makes the new earliest day's partition the new partition 1. Different databases have different day ranges depending on requirements.

View 0 Replies View Related

How Does Linear Regression Handle Missing Values For Prediction And For Training?

Sep 18, 2006

Q1. Model Prediction -- Suppose we already have a trained Microsoft Linear Regression Mining Model, say, target y regressed on two variables:

x1 and x2, where y, x1, x2 are of datatype Float. We try to perform Model Prediction with an Input Table in which some records consist of NULL x2 values. How are the resulting predicted y values calculated?

My guess:

The resulting linear regression formula is in the form:

y = constant + coeff1 * (x1 - avg_x1) + coeff2 * (x2 - avg_x2)

where avg_x1 is the average of x1 in the training set, and avg_x2 is the average of x2 in the training set (Correct?).

I guess that for some variable being NULL in the Input Table, Microsoft Linear Regression just treat it as the average of that variable in the training set.

So for x2 being NULL, the whole term coeff2 * (x2 - avg_x2) just disappear, as it is zero if we substitute x2 with its average value.

Is this correct?



Q2. Model Training -- Using the above example that y regressed on x1 and x2, if we have a train set that, say, consist of 100 records in which

y: no NULL value

x1: no NULL value

x2: 70 records out of 100 records are NULL

Can someone help explain the mathematical procedure or algorithm that produce coeff1 and coeff2?

In particular, how is the information in the "partial records" used in the regression to contribute to coeff1 and the constant, etc ?

View 1 Replies View Related

How Does Linear Regression Handle Missing Values For Prediction And For Training?

Sep 18, 2006

Q1. Model Prediction -- Suppose we already have a trained Microsoft Linear Regression Mining Model, say, target y regressed on two variables:

x1 and x2, where y, x1, x2 are of datatype Float. We try to perform Model Prediction with an Input Table in which some records consist of NULL x2 values. How are the resulting predicted y values calculated?

My guess:

The resulting linear regression formula is in the form:

y = constant + coeff1 * (x1 - avg_x1) + coeff2 * (x2 - avg_x2)

where avg_x1 is the average of x1 in the training set, and avg_x2 is the average of x2 in the training set (Correct?).

I guess that for some variable being NULL in the Input Table, Microsoft Linear Regression just treat it as the average of that variable in the training set.

So for x2 being NULL, the whole term coeff2 * (x2 - avg_x2) just disappear, as it is zero if we substitute x2 with its average value.

Is this correct?



Q2. Model Training -- Using the above example that y regressed on x1 and x2, if we have a train set that, say, consist of 100 records in which

y: no NULL value

x1: no NULL value

x2: 70 records out of 100 records are NULL

Can soemone help explain the mathematical procedure or algorithm that produce coeff1 and coeff2?

In particular, how is the information in the "partial records" used in the regression to contribute to coeff1 and the constant, etc ?

View 3 Replies View Related

Transact SQL :: Advanced Query With Windows Function?

Jun 16, 2015

SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date,  @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:

CREATE TABLE MyTable
(
  IDTable INT IDENTITY NOT NULL,
  DateLog  DATE NOT NULL,
  CountLog INT NOT NULL
)

[code]....

In this case I need to apply a filter with the Dates:

@dateFrom ='2014-01-01',
@dateTo     ='2014-04-28'

But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:

Id        MonthYear      Diff
1            Jan2014       5
2            Feb2014       3
3            March2014   0
4            April2014      5

With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range  was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.  

[URL]

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved