I have been trying to limit the table rows in the following situation,
any suggestions will be appreciated.
we have table called tempTb has columns id, c_id, c_name, rating, date
columns.
id is an identity column.
date is a datetime column, the rest are varchar datatype.
Here is the table structure with sample data,
idc_idc_nameratingdate
1aoamer onli11/1/2002
2aoamer onli13/1/2002
3aoamer onli16/1/2002
4aoamer onli39/1/2002
5aoamer onli312/1/2002
6aoamer onli33/1/2003
7aoamer onli36/1/2003
8aoamer onli39/1/2003
9aoamer onli212/1/2003
10aoamer onli16/1/2004
11aoamer onli112/1/2004
12xyxabs yasd11/1/2002
13xyxabs yasd23/1/2002
14xyxabs yasd26/1/2002
15xyxabs yasd29/1/2002
16xyxabs yasd112/1/2002
17xyxabs yasd13/1/2003
18xyxabs yasd36/1/2003
19xyxabs yasd39/1/2003
20xyxabs yasd212/1/2003
21xyxabs yasd16/1/2004
22xyxabs yasd112/1/2004
[color=blue]
>From this table I need to select the rows with rating changes only,[/color]
i.e if two or three consecutive rows have same rating only the first
row should be selected.
I was trying to do this by self-joining the table like....
select t1.* from tempTb t1, tempTb t2
where t1.id!=t2.id,
t1.c_id=t2.c_id,
t1.c_name=t2.c_name,
t1.rating!=t2.rating.
But this is generating cartesian products,
I have tried some other combinations after where clause with date colmn
wtc,
but none seems to give the required result.
so if anybody can guide me in the right direction I would appreciate
it.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID ) and the newly inserted row with a insert time stamp so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 NULL 14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
I am trying to create a stored proc that will update exactly one row. Simple. For insurance purposes, I want to create some logic that will rollback the entire transaction if more than one row is updated.
I know that I could force the primary key into the WHERE clause, but I was looking for some logic that will allow me to bypass that.
I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price
Example: Cheetos Zone A: $2.79 Zone B: $2.89
The pricing data in the tables is listed by zone. However, the new product uses pricing by store.
Zone A contains stores 1,2,4,6,7.... Zone B contains stores 10,11,12,14.....
I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.
So I'm looking to go from:
Zone UPC Price A 1234500000 2.79 B 1234500000 2.89
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day) -- Primary key is on L4Ident (clustered) -- nonclustered index on ProcessDate, LinkRL4 DROP TABLE dbo.#Events; DROP TABLE dbo.#Results; CREATE TABLE dbo.#Events ( L4Ident int IDENTITY(1,1) NOT NULL,
I created a simple type 1 slowly changing dimension, setting all the columns to "Changing Attibute". The first time I run the package it sees all rows as new and imports them into the dim as it should. Next time I run it put 100% of the rows into the "Changing Attribute Updates" and runs an update on all 90,000 rows - updating the rows to exactly what they were before
If I take the DIM and the Source in SQL and join on every row the join succeeds (meaning the rows match perfectly).
Shouldn't the SCD object just ignore the rows if they match? Or does it assume that ALL incoming rows are either new or changed? (if so why is there an output called "Unchanged Output"?). Is there some "gotcha" I am missing??
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length. Any ideas people?
Hi, I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's. can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows. select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company] ,a.[ProductID] ,a.[ColourSize] ,a.[StockDate] ,a.[Quantity] ,b.[Ticket Price] FROM[Stock_Ledger] a LEFT OUTER JOIN [Product_Prices] b ON a.[Company] = b.[Company] AND a.[ProductID] = b.[ProductID] AND a.[ColourSize] = b.[ColourSize] AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate] ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
I have a table stock, on the table I have company_name, stock_value, sector. Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL. I will appreciate if you help me on this
I know I can use "First" to specify a number of rows to return from a query but is it possible for the number of rows returned to be based on a parameter, something like this:
SELECT FIRST @someNumber name, age FROM friends WHERE age > @ageInput
Hello All,I have this table:CREATE TABLE [dbo].[tbl_TESTING] ([ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,[FNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[MOVEDINYR] [int] NULL ,[MOVEDOUTYR] [int] NULL) ON [PRIMARY]GOwith these records:INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1995', '2000')INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1994', '2005')What I would like to do is be able to select FIRSTNAME, LASTNAME,MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.FIRSTNAME=JAMESLASTNAME=TAYLORMOVEDINYR=1994MOVEDOUTYR=2005Some sql syntax help appreciated,thanks in advance!
hi!I have two tables with same structure. I need to run a select query thatwill return only the rowsfrom one table that are not in the other.In MySQL it would beselect * from table1 MINUS select * from table2.but in MsSql I can not find the apropriate way to do this.Any Help?Zvonko
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM gf_game_ext AS ext INNER JOIN gf_game ON gf_game.gameID = ext.gameIDWHERE (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot!
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE when (substring(check_date,1,4) not between '1997' and '2000' or substring(check_date,5,2) not between '01' and '12' or substring(check_date,7,2) not between '01' and '31') THEN null ELSE cast(check_date as datetime) END AS check_date
I have to select rows based on if the transaction date = todays date.The column is defined as numeric 8 with 0 decimal.how to code for todays date with such a column?
I would like to filter the second table taking one row per ID of Field A (first table) and selecting the rows whose sum of E is equal to the value in field C; in this example the resulting table should be:
SELECT RN_TEST_ID AS 'Test ID', MAX(RN_EXECUTION_DATE) AS 'Last Execution Date', MAX(RN_EXECUTION_TIME) AS 'Execution Time', RN_DURATION AS 'Run Duration' FROM RUN
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
As a beginner i am having trouble with this. i have two different tables , both have a name column, nvarchar datatype. I would like to select from table B all the rows which contain a name which is not in table A. Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
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
