Select Last 20 Records By Date Then Sum Lowest 10 Scores???

Jan 21, 2004

I have a table and am usuing ASP to query the database, the connection is to a MS Access table at the moment but am working to convert to SQL Server.

Question:

I need to select the last 20 records, by a date field, then from those 20 records select the 10 lowest scores.

Example is a member logs on an that member has say 80 total records in the table. Then I need to select the last 20 records entered by the date field then select the lowest 10 scores out of those 20.

I am new to more compex SQL Statements any help would be mostly appreciated!

table = HC_ID
date field = date
member_id = member
score = ScoreHC

View 14 Replies


ADVERTISEMENT

Fuzzy Lookup Transform Row Scores 'inconsistent' With Individual Column Scores

Sep 1, 2006

I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.

The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.

For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.

Match 1 Match 2
----------------- -----------------
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0

_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332

In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.

Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?


Fernando Tubio

View 2 Replies View Related

Get Lowest Date

Jul 23, 2005

Is there a more elegant way to do the following?...declare @d1 datetime, @d2 datetimeset @d1 = '2005-01-01'set @d2 = '2005-02-01'selectcasewhen datediff(dd,@d2,@d1)is nullthen coalesce(@d1, @d2)when (datediff(dd,@d2,@d1)> 0)then @d2else @d1endCheers,..N

View 2 Replies View Related

Select Row With The Lowest Value In Column

Dec 20, 2007

I'm using sql express 2005. I need help with buliding a view.



I'm trying to join two tables and then, if duplicate ID's exist, retrieve the row with the lowest value in a specific column.



For instance:



TableHousehold:

ID Name Status

001 The Smiths A

002 The Jones A

003 The Adams A

004 The Grants A



TableResidents

Pk HouseholdID ResidentID Name Gender

1 001 1 Mary Smith F

2 001 2 Jason Smith M

3 001 3 Rachel Smith F

4 002 1 Mike Jones M

5 002 2 Sara Jones F

6 003 2 Erik Adams M



The resident ID is auto increment and assigned by the server. It is possible for resident 1 for each household to have been deleted. It is also possible that a household does not contain any residents. Therefore, I wish to join these tables and return this:



ID HouseHoldName Status ResidentName Gender

001 The Smiths A Mary Smith F

002 The Jones A Mike Jones M

003 The Adams A Erik Adams M

004 The Grants A null null



There are other columns in both tables that I will want to join, but for simplicity, the above example illustrates my need.



I was able to accomplish this by creating two views. But Id like to try and do this in one.

View 3 Replies View Related

Problem Displaying Lowest Date From Two Fields

Nov 19, 2007

ID

Original_Date

Date1

Date2

Lowest_Date


1

12/2/07

14/02/07

18/02/2007

14/02/2007


2

01/03/07

23/3/2007

11/3/2007

11/03/2007


3

15/4/2007

14/05/2007

16/05/2007

14/5/2007


4

6/6/07

8/6/07

9/6/06

8/6/07


I have a select query that looks like this

Select table1.id, table1.original_date,
(select ..from ..) as Date1,
(select ..from..) as Date2
from table1


Date 1 and Date 2 are both complex subqueries that retrieve a date from other tables. I would like another column displaying the oldest date between date1 and date2. Can anyone help?

View 3 Replies View Related

Transact SQL :: Select Lowest Level From Hierarchical Data

Jun 22, 2015

I have hierarchical data such as:

Id    Level    ParentId
1     0         1
2     1         1
3     2         2
4     0         4
5     1         4
6     0         6
7     1         6
8     2         7
9     3         8        
10   4         9
11   0         11

As you can see even the parent element has parentId(in this case id = parentid)

How can I select the lowest level data in the hierarchy and get this result:

Id    Level    ParentId
3     2         2
5     1         4
10   4         9
11   0         11

View 4 Replies View Related

Select All Records Where Date Column Equals To Today

Nov 13, 2014

I have a table with a column of type date. I want to select all records where the date column = today. I tried

Select * from myTable where DateSent = GetDate()

but it returns no records.

Do I really have to use this slow method?

select datesent from Messages where convert(varchar,DateSent,103) = CONVERT(varchar, GetDate(), 103)

View 2 Replies View Related

SQL Server 2012 :: Using Select Query To Get 2014-08-09 11:13:03 From Original Date Records?

Aug 18, 2014

I have date field in table and data contain 2014-08-09 11:13:03.340

when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03

Why the 03 is trimming in smalldatefield.

how do I use select query to get 2014-08-09 11:13:03 from original Date records

View 1 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Top 10 Scores - Row Query

Jul 12, 2004

I have a database that holds student details in one table and their scores in particular subjects in another. The tables are linked through ID. The subjects table looks like this. (example)

ID Sub1 Sub2 Sub3 Sub4 etc
1 2 4 2 6
2 5 5 1 3
3 7 4 2 5
etc

What I need to do is return a students details followed by their top 10 subjects in ascending order.
Student 1's results would look like this...

Student 1
details

Sub1 2
Sub3 2
Sub2 4
Sub4 6

This is driving me insane. Please help.

View 1 Replies View Related

Calculating Survey Scores

Oct 16, 2007

I have surveys that I need to add weights to and was wondering if there was a way to convert the contents of a column.

select empid, ans_for_ql,
(if ans_for_ql = A then 0, B then 3, C then 5) as weightscore,
ans_for_q2,
(if ans_for_q2 = A then 8, B then 4, C then 2, D then 1, E then 0) as weightscore

Here is what the table looks like:

empid | Ans_for_Q1 | Ans_for_Q2
1001 A C
1002 B E

And these are the possible answers and what they need to be converted to:

Weights for answers to Question1
Q1_A = 0
Q1_B = 3
Q1_C = 5

Weights for answers to Question2
Q2_A = 8
Q2_B = 4
Q2_C = 2
Q2_D = 1
Q2_E = 0

View 3 Replies View Related

Cluster Discrimination Scores

Aug 4, 2006



Dear all here:

I have a question about the cluster mining result.

In the discrimination tab shows the different between group 1 and group 2.

I just wonder how to calculate the discremination scores.

Do any expertise can answer the question?



Regards!



Jerry

View 1 Replies View Related

SQL 2012 :: Get Percentage Over Sum Of All Scores Over Last 90 Days

Sep 30, 2014

I am trying to build a query where I want to extract the sum of the scores for each code MCC and get the percentage over the sum of all the scores over the last 90 days

select MCC, sum(score) as total from scores
(select Datediff(day, creationdate, getdate()) as Q from scores
where Datediff(day, creationdate, getdate()) <90)
group by MCC

TABLE
ID creationdate score MCC
1 2014-08-02 30 7422
. . . .
. . . .
. . . .

View 4 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

Find 90th Percentile Scores For Each Student

Sep 15, 2006

I am stumped on a set-based approach for this one.

A cursor approach is straightforward enough, but i want to avoid that.

Here's my table:

create table StudentScores
(
id int primary key identity(1,1),
student_id int not null,
score int not null
)

with some sample data:

insert into StudentScores (student_id, score)
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 88 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 98


What I want is, for each student, what is their 90th percentile score?

For a given single student, one possibility would be:

declare @studentid int
set @studentid = 2
select top 1 @studentid as student_id, a.score as [90th percentile score]
from
(
select top 90 percent score from StudentScores
where student_id = @studentid order by score asc
) as a
order by a.score desc

But I want this for all students, and not use a cursor.

Any ideas?

Thanks!

View 6 Replies View Related

Comparing Date Records In One Column To Know Missing Date Interval?

Jun 6, 2012

I need to compare the contents of a column.

I have a table with 1 column. Below are the sample contents..

DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000

[code]....

as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:

DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000

if it is not doable, we can get the nearest record of all missing records like this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000

or this:

DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000

or this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000

View 6 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

How To Select All Dates Upto Todays Date And Include The First Next Future Date

Jan 11, 2006

hello
how can i select all dates upto todays date and include the first next future date in a given data base

say todays date was the 01/06/2006 (MM,DD,YYYY)

below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006

i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006

so the results would return

1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006

View 2 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Date Format - Column Which Select First Day Of Given Month Of Converted Date

Oct 21, 2013

Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,

Also create a new column called “BegMonth” which selects first day of the given month of the converted date column

The table is ;

SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]

Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521

Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01

View 3 Replies View Related

Select * From Table Does Not Select All Records

Aug 17, 2005

I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.

Any suggestions?

Thanks!

GordonG

View 13 Replies View Related

Select A Date Minus Whatever Todays Date Is?

Feb 15, 2008

At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

View 8 Replies View Related

Lowest Cost

Nov 30, 2004

OK, this should be an easy one but my brain isn't quite working right now.

I have a table, we'll call Table1 like so:

ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00


And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:

SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID

and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.

Thanks,

View 7 Replies View Related

The Lowest Value Between Two Columns?

Sep 14, 2012

SELECT

@AppId AS Application_Id
, MB.POLICY_ID AS Policy_Id
, MH.BFN_CHG_DATEAS Date_Of_Disinvestment
, AD.X_INVEST_AMT AS Original_Investment_Value
, MB.TOTAL_AMOUNT AS Encashment_Value
, WH.TOT_COMMISSION AS Top-Up_Commission

FROM: I select from lot of table, as specified from my select statement

My output:
AppId PolicyId Date_Of_DisIn Original_Inv Encashment_Value Top-Up_Commision ???
1-1-1; 1000000; Jan-01-12; 2882.07; 3312.00; 2306; ??

My Aliases returns just one values, of which that's what I am expecting, so I need to make a comparison between Original_Investment_Value and (Encashment_Value + Top-Up_Commision), and return the lowest value

View 2 Replies View Related

How To Insert A Row With Lowest Possible Key?

Aug 29, 2007

Hi, this seems to be an easy question but I can't find a quick answer to it.
I have an asp page that communicates with a database and I want the following example to work...

Insert 5 records into the DB with primary keys 1-5.
Remove record with key number 2.


Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.

Does auto increment work like this?
If not, how do I do it?


Thanks for any replies!
Niklas

View 5 Replies View Related

Date Select Query - Select Between Two Dates

Aug 22, 2006

have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View 6 Replies View Related

Bp Server With The Lowest Price

May 25, 2004

bp server with the lowest price

Our company is a proffesional deticated bullet proof server and general server provider in china. If you're looking for a reliable and long term cooperation bp server provider, it deservers you to read our company file to know more about us.

we have the lowest price for bp server.

http://www.serverinchina.com/bpserverservice/index.htm


ICQ:226745581 MSN:dqkj#hotmail.com

ICQ:329211498 MSN:jiemie11#hotmail.com

Email: serverinchina#yahoo.com.cn
Website: www.mailinchina.com www.serverinchina.com

View 5 Replies View Related

Find Lowest Value In 3 Columns

Jun 2, 2008

Im having trouble finding a solution to comparing columns to find the lowest value.

Example

Row 1

COL 1 = 10
COL 2 = 20
COL 3 = 60
COL 4 = 5
COL 5 = 35

I would want to return 5.

I tried to write a case statement such as below but am unable to use Logical Operators.

WHEN C1 <= C2 and C1<= C3 THEN C1
WHEN C2 <= C3 THEN C2
ELSE C3



any help would be great.

Thanks,
Nick

View 1 Replies View Related

Best Bp Servers And Lowest Price

Feb 11, 2007

(Spam Removed)

View 1 Replies View Related

Stored Procedure Lowest Priority

Jul 13, 2006

Hello,
 
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
 

View 2 Replies View Related

Display Only The Listing With The Lowest Price (was Need A Little Help Here.....)

Mar 13, 2006

I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:

I have a database with the following values:
Model Make Price
DA1100 GTN$88.00
DA1100 GTN $100.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00

In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is:
Model Make Price
DA1100 GTN$88.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00

View 1 Replies View Related







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