Unique Results Based On Popularity Voting

Jun 18, 2008

I've managed to get my query to this: Product | Color | Votes
======== ======= =======
Bus Red 10
Bus Blue 5
Train Blue 1
Car Red 1
Car Blue 1
 

I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results.

 

Any help would be greatly appreciated.

Thanks, Pete.

View 2 Replies


ADVERTISEMENT

VOTING/RATING SYSTEM: HOW TO ADD POINTS AND UPLOAD SQL VALUE?

Sep 2, 2006

I am trying to build a voting system. Website visitors will rate a pictures from 0-10.
This value should update the amount of points already stored is SQL.
I think the way to do it is passing the value of a field to a varible, performing the operation and updating the database, but I don't know how to capture a value in Sql and pass it to variable...
Does anybody there could post a piece of code or point to a link where I could find this information?

thanks

View 8 Replies View Related

How Do I Get Unique Rows Based On StudentID

Oct 4, 2006

I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID  First Name Last Name  Other Columns...............................................634565491 MARINA    BALDERAZ 640484566 TERE        BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID  First Name Last Name  Other Columns...............................................634565491 MARINA    BALDERAZ 634565491 MARINA    BALDERAZ 634565491 MARINA    BALDERAZ 640484566 TERE        BALDERAZ640484566 TERE        BALDERAZ640484566 TERE        BALDERAZ

View 1 Replies View Related

Unique Constraint Based On Value In A Column

Nov 23, 2007

I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?

Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A

only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..

are there other solutions to make a constraint like this ?

Aeneas.

View 1 Replies View Related

Generating Unique No Based On Certain Data

Oct 3, 2007

Hi,

I have the following tables :




Code Block
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #Call(Callid int,Calltype int,callDetailid int ) // CallType 1=New 2=Change 3=Delete

Create Table #CallDetail(callDetailId int,empid int)




The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?

SAMPLE DATA



Code Block
Insert into #CallDetail(12123,1)
Insert into #CallDetail(53423,1)
Insert into #CallDetail(6532,1)
Insert into #CallDetail(62323,1)
Insert into #CallDetail(124235,1)
Insert into #CallDetail(65423,2)
Insert into #CallDetail(56234,2)
Insert into #CallDetail(2364,2)
Insert into #CallDetail(34364,2)
Insert into #CallDetail(85434,2)

Insert Into #Call(111,1,12123)
Insert Into #Call(112,1,53423)
Insert Into #Call(114,1,6532)
Insert Into #Call(123,2,62323)
Insert Into #Call(134,1,124235)
Insert Into #Call(143,2,65423)
Insert Into #Call(145,1,56234)
Insert Into #Call(154,2,2364)
Insert Into #Call(185,1,34364)
Insert Into #Call(195,1,85434)

Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);
Insert Into #request Values('225652','Jim','SA055',67,143,0);
Insert Into #request Values('126756','Jasm','SA055',67,145,0);
Insert Into #request Values('786234','Chuck','SA055',67,154,0);
Insert Into #request Values('66234','Mutuk','SA059',72,185,0);
Insert Into #request Values('2232362','Buck','SA055',67,195,0);




EXPECTED OUTPUT will be (See the last column for unique nos). :



Code Block
Insert Into #request Values('324234','Jack','SA023',12,111,1);
Insert Into #request Values('223452','Tom','SA023',12,112,2);
Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1
Insert Into #request Values('22322362','Guck','SA024',44,114,1);
Insert Into #request Values('22654392','Luck','SA023',12,123,3);
Insert Into #request Values('225652','Jim','SA055',67,143,1);
Insert Into #request Values('126756','Jasm','SA023',69,134,1);
Insert Into #request Values('786234','Chuck','SA023',72,145,2);
Insert Into #request Values('66234','Mutuk','SA059',72,185,1);
Insert Into #request Values('2232362','Buck','SA055',67,195,2);





Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem

Plz help. I am stuck from 2 days on this :

Vidkshi

View 8 Replies View Related

Transact SQL :: Creating A View Using DISTINCT And Not Getting Unique Results?

Sep 21, 2015

I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:

SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index
FROM dbo.csr_standards_cmsars
ORDER BY ARSFamily

 but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?

View 2 Replies View Related

Selecting The Rows Based Off Of Unique Columns

Mar 18, 2007

Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ...  There will be entries where there can be duplicate customerFirst and customerLast names.  I would like to just return a single entry of the duplicate names and all associated row information.  IE: [customerID], [customerFirst], [customerLast],            [ email],             [program]         01               Bill                 Smith             bill.smith@hotmail.com    ymca        02               Bill                 Smith             bill.smith@hotmail.com    Sports        03               jon                   doe                 jon.doe@hotmail.com    AAA        04               jon                   doe                 jon.doe@hotmail.com    Ebay          05               Paul                 Sprite             paul.sprite@hotmail.com    Rec Desired Returned result:        01               Bill                 Smith             bill.smith@hotmail.com    ymca        03               jon                   doe                 jon.doe@hotmail.com    AAA
         05               Paul                 Sprite             paul.sprite@hotmail.com    Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr);         dAdapter.Fill(pocDS, "Data Set");        However this is throwing up an error when i build the app:  An expression of non-boolean type specified in a context where a condition is expected, near ')'.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.

Source Error:




Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison  1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry  

View 12 Replies View Related

Count Up Based On Unique Value -- Brain Freeze

Apr 17, 2008



Ok... I know this is something that I've seen and probably done before....

I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.

The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.








Value1
Value2
LineNumber

1
Hey
1

1
Can
2

1
Someone
3

1
Help
4

1
With
5

1
This
6

2
Very
1

2
Simple
2

2
Issue
3

2
As
4

2
I
5

3
Have
1

3
A
2

3
Brain
3

3
Freeze
4



--Thanks--

View 4 Replies View Related

Transact SQL :: Create Unique ID Based On Existing Fields

Sep 1, 2015

I have the following table (Table does not have unique key id )

Last Name     First Name         DATE        Total-Chrg

Jaime               KRiSH           5/1/2015         -4150.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015           4150.66
Jaime               KRiSH           5/3/2015           4150.66
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/23/2015         123.02

I want the results to be in following way

Uniq ID    Last Name    First Name          DATE         Total-Chrg

1                Jaime            KRiSH                 5/1/2015     -4150.66
2                Jaime            KRiSH                 5/1/2015     1043.66
2                Jaime            KRiSH                 5/1/2015     1043.66
3                Jaime            KRiSH                 5/1/2015      4150.66
4               Jaime             KRiSH                 5/3/2015      4150.66
5               Peter              Jason                5/1/2015       321.02
6               Peter              Jason                5/1/2015       321.02
7               Peter              Jason               5/23/2015     123.02

May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values.  There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates). 

View 4 Replies View Related

Transact SQL :: Finding Unique Counts Based On Consecutive Days?

Jul 9, 2015

I have a scenario here where the data looks like -

ID        Date
100      07/01
100     07/02
100    07/03
100   08/01
100   08/02
100   08/15

Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?

View 5 Replies View Related

SQL Server 2014 :: Query To Display Unique Comments Based On Certain Conditions

Jul 1, 2015

TABLE DEFINITION

Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)

CUSTOMER TABLE

CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

OUTPUT

CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

Records should be filtered by

1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo

View 2 Replies View Related

DISTINCT Results Based On The Value Of One Col?

Jun 19, 2008

Here's one thats had me and a coworker puzzled. Hopefully it's something simple:

TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)

Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message


TABLE B: (List of users)
userid, int
name, varchar(100)

Table B Data:
1, John
2, Mike

I want to extract the most recent message logged per user, i.e.:

name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.

I have been unable to come up with a query that can return just the one value. I've tried variants of:

SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid

including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?

TIA

View 4 Replies View Related

UPDATE Based On Results?

Feb 6, 2008

Hello,

I'm trying to update FOR_SORTING to 1, for all instances of a RESPONSE_ID when QUESTION_ID = YESNO and RESPONSE = Yes.

So, for below I'm trying to update FOR_SORTING to 1, for RESPONSE_ID that has Question_ID that is equal to YESNO and Response equals Yes.

No. RESPONSE_ID QUESTION_ID RESPONSE FOR_SORTING
1. 1 RATING Incredible
2. 1 YESNO Yes 1
3. 2 RATING Incredible
4. 2 YESNO No

The Table is called LSN_RESPONSE_DETAILS

I tried the following, but it only updates the one row.

UPDATE LSN_RESPONSE_DETAIL
SET FOR_SORTING = '1'
WHERE QUESTION_ID = 'YESNO' and 'RESPONSE = 'Yes'

Any idea's?

View 3 Replies View Related

Display Results Based On Query

Mar 3, 2005

Hi,

I don't know if anyone has encountered this before but here goes:

I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"

queryLocalName is an input field that allows the user to search for non-English characters in the database.

What I'm wondering is what kind of effect is the N in the where clause is having?

I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.

View 2 Replies View Related

Getting The Average Results Based On Time.

Nov 19, 2006

Hello~,
The table has columns like this.
________________________________
time smalldatetime
value1 int
value2 int
----------------------------------------------------------

for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....

The record is inserted at every hour.

I want get daily,monthly,yearly average and display the result ordered by time.

View 1 Replies View Related

Returning Results Based On Dates

Oct 19, 2007



I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this

Select InformationDate From thisTAble

Where

If @dateValue IsNot @Null
Informationdate = @dateValue
Else

Where

In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go

InfomationDate IsBettween @daterangeFrom @dateRangeTwo


Any help on this i hope i was clear...thank you!

View 7 Replies View Related

Sql Query Results Based On Column Value?

Mar 18, 2008



I have a stored procedure which selects results based on some date calculations.

In my table I have a status column and two date fields (Approval Date and Signature Date)
If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.

If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.

How can i achieve this?

View 3 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

Assign A Variable Based Upon Query Results...how To Do It?

Feb 23, 2007

I have the following code which is incomplete.  Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code.  What do I need to add there?
 
Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()

View 2 Replies View Related

Display Results Based On Percentage Match

May 5, 2008

Dear Pals,

I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.

I mean the result set should be displayed based on percentage match.

For example i have the below table.

create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)

insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'

I need the output something similar as below

REG
Reg
Regsxysn
psReg

I have tried out with full text indexing but i could'nt get the required output.

Any suggestions would be appreciated.

Thanks in Advance.

View 1 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD

As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.

I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.

View 9 Replies View Related

Display Results Based On Percentage Match

May 5, 2008

Dear Pals,

I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.

I mean the result set should be displayed based on percentage match.

For example i have the below table.

create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)

insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'

I need the output something similar as below

REG
Reg
Regsxysn
psReg

I have tried out with full text indexing but i could'nt get the required output.

Any suggestions would be appreciated.

Thanks in Advance.

View 3 Replies View Related

Restrict Results Based On Top 10 People With Enquiries?

Sep 26, 2013

I am trying to write a report that gives me a list of all enquiries based on top 10 people who received it the most.

The fields are:

enquiry.enquiry_number
enquiry.enquiry_time
officer.officer_name

What I want to do is run the report which details each enquiry each officer has received but stop running once top 10 officers have the most enquiries are in the results.

The officers are identified using the officer.officer_name field, enquiry.enquiry_number field shows each enquiry record.

View 5 Replies View Related

Lookup Based On Results Of Previous Lookups

Feb 17, 2008

i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that.

so, i have a dataflow whose first component extracts certain kinds of data from an xml document.

each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update.

So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.

View 7 Replies View Related

Listing Results Based On The Starting Alphabet

Mar 27, 2007

Hi,



I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated.



Thanks in advance,

Sonu

View 4 Replies View Related

Transact SQL :: Update Based On Results From Select

Jul 24, 2015

Update statement based on the results from this SELECT:

SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)

[Code] ....

I need to update all records where the 

RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'

I imagine that the UPDATE statement will be something like:

UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,

[Code] .....

View 4 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

SQL Server 2012 :: Getting Related Records Set Based Results

Sep 29, 2015

I have a table that has multiple transactions for stock items.

This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.

I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:

DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME

Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)

[Code] ....

View 9 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Fixing My Table Based On Dbcc Showcontig Results

Jul 20, 2005

Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.

View 2 Replies View Related

Transact SQL :: Query Distinct Results Based On Date Range

May 21, 2015

I have a data structure like this

UID , Name, amount, start date                               End Date
     1      A         10         2015-05-01 00:00:00             2015-05-01 23:59:59
     2      A         10         2015-05-02 00:00:00             2015-05-02 23:59:59
     3      A         10         2015-05-03 00:00:00             2015-05-03 23:59:59
     4      A         10         2015-05-04 00:00:00             2015-05-04 23:59:59
      5      B         10         2015-05-05 00:00:00             2015-05-05 23:59:59

[code]...

View 5 Replies View Related

Transact SQL :: Commit And Rollback Transaction Based Upon Results Of A Calculation

Oct 5, 2015

In t-sql 2012, I have the following sql that I would like the following to occur:

1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.

DECLARE @TransactionName varchar(20) = 'Transaction1';
        @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID  int NOT NULL PRIMARY KEY,
                       schoolID  int NOT NULL,                    
                       UNIQUE(schoolID,lockID)
 )

[Code] ....

Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?

View 5 Replies View Related







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