Comparing Data In Table With Validation Table
Aug 27, 2007
Hi all
I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...
I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this
thanks
View 5 Replies
ADVERTISEMENT
Jul 20, 2005
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies
View Related
May 1, 2014
I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2
Example table
IDValue1Value 2
TEST1HouseTango
TEST2HouseTango
with test as (
select * from ExampleTable where ID= 'TEST'
),
[Code] ....
View 4 Replies
View Related
Nov 29, 2014
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
View 2 Replies
View Related
Mar 18, 2008
Hi guys, i'm sort of stuck.
I have a list of names, all i have to do is check to see if any of them are in a table, if they are they get deleted. The unique field of the table is title and it is what i have. I don't really know how i am meant to do this. I thought of creating a new table, placing my values into it and comparing to the already existing table, but surely their is a simple way?
View 3 Replies
View Related
Oct 18, 2006
Hi all,
Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?
Thanks
DBAnalyst
View 2 Replies
View Related
Jul 18, 2007
In my package, the first two tasks are as follows:
1)
IF EXISTS (SELECT name FROM sysobjects WHERE name = '<tablename>' AND type = 'U')
BEGIN
drop table dbo.<tablename>
END
2)
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = '<tablename>' AND type = 'U')
BEGIN
CREATE TABLE [dbo].[<tablename>](
col1,col2,col3......
)
end
so why am I getting the following error when trying to run the package?
Error Message
===================================
Package Validation Error (Package Validation Error)
===================================
Error at Get Fenics data to table [Load into FFFenics table [155]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.FFFenics'.".
Error at Get Fenics data to table [Load into FFFenics table [155]]: Failed to open a fastload rowset for "[dbo].[FFFenics]". Check that the object exists in the database.
Error at Get Fenics data to table [DTS.Pipeline]: "component "Load into FFFenics table" (155)" failed validation and returned validation status "VS_ISBROKEN".
Error at Get Fenics data to table [DTS.Pipeline]: One or more component failed validation.
Error at Get Fenics data to table: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)
View 4 Replies
View Related
Dec 14, 2007
Hi, I'm calling the cross validation SP like this
call SystemGetCrossValidationResults([V Product View Event],[V Product Event 1],4,500,'V Product View Event')
and I get this error:
Parsing the query ...
Error (Data mining): The attribute, 'V Product View Event', is not valid in the current procedure call because the attribute is not a PREDICT or a PREDICT_ONLY attribute for the model, 'V Product Event 1'.
Parsing complete
In my model 'V Product View Event' is a predictable attribute. It so happens that I'm using association rules. And 'V Product View Event' is a nested table (a TableMiningStructureColumn). Inside the 'V Product View Event' nested table I have a 'Product' ScalarMiningStructureColumn. Is there any way to call the SP with nested tables in models? I'm using SQL Server 2008 June CTP.
thanks,
Gustavo Frederico
View 1 Replies
View Related
Jun 14, 2007
HI everybody need help badly on the following
have to recordsets with the following with the following SQL
SELECT place, count(minus)as countminus
order by place
group by place
THE RESULT WOULD BE
PLACE countminus
ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0
MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus
THE RESULT WOULD BE:
PLUS COUNTPLUS
ABC 10
DEF 20
my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...
with this condition..
I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...
THE RESULT WOULD BE THIS.....
PLACE countminus COUNTPLUS
ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0
I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View 2 Replies
View Related
Jun 15, 2007
How do I only select rows with duplicate dates for each person (id)? (The actual table has approximately 13000 rows with approximately 3000 unique ids)
p_key id date pulse
--------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
3 32 4/26/2006 81
4 32 6/15/2006 82
5 15 1/20/2006 75
6 15 3/25/2006 80
7 15 3/25/2006 83
Result table I am looking for:
p_key id date pulse
------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
6 15 3/25/2006 80
7 15 3/25/2006 83
Thanks.
View 8 Replies
View Related
Aug 23, 2007
Hi all,
I have two tables in SQL Server 2005 and excel sheet ( Office 2003).
The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already)
Columns of table_one and table_two are: name ,ssn
I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1".
If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2".
Ex:
Table_one (input) ---- excelsheet_one ( Output)
ssn name ---- ssn name flagbit
11 NYC ---- 11 NYC T1
Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet.
Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet.
Does anybody how to do this.
Any help will be greatly appreciated.
Thanks
View 4 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Jun 16, 2014
How to resolve the below task
create table #temp ( idx int identity(1,1), col1 int, col2 int )
Here i want a flag success or fail on basis of below conditions.
I need to take all the col1 values and then i need to compare to each other.
if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".
if all the col1 values are ok , then we need to check Col2 values same as above.
--case 1
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522
--select * from #temp
--truncate table #temp
Because of difference in col1 values . the value of flag should be fail.
--case 2
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522
Here also the col1 is ok but col2 values have difference so it should be Fail.
Otherwise it should be success.
View 6 Replies
View Related
Nov 21, 2014
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
[Code] ....
I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:
[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]
Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline
update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
View 7 Replies
View Related
Jun 8, 2015
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
View 9 Replies
View Related
Jul 23, 2005
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo
View 3 Replies
View Related
May 26, 2007
I'm using SQL Server 2005 Express.
In my main database table I have many fields but the two following fields are my main concern.
1) email_address
2) unsubscribe
In my secondary database table I have one record only.
1) email_address
What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)
I'd really appreciate any help I can get.
Thanks,
Bill
View 3 Replies
View Related
Jul 31, 2007
Hi,
I've a bunch of records that may contain data that I'm after. For example:
This is a fake title [electronic resource]. 1997.
I have a very small table (~10 rows) of things like '[electronic resource]'
Is there any way to see if my record contains any of the 'target' items in the other table?
View 1 Replies
View Related
Jul 20, 2005
I have the following table structure.group1 group2 group1_result group2_result'One' 'Two' 3 2'One' 'Two' 3 1'One' 'Two' 2 5'One' 'Two' 4 1'One' 'Two' 0 5I need to sum up the number of times 'One' is greater than 'Two', andvice-versa. For example, the result I would like to achieve is asfollows.group1 group2 group1_total group2_total'One' 'Two' 3 2I'm using the following SQL statement, but I get 5 rows returned,giving me a '1' or '0' for each row.select group1, group2,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)FROM table1GROUP BY group1, group2Any help would be greatly appreciated.
View 1 Replies
View Related
Aug 8, 2006
Hi all,
I am new in SSIS. Anyone know how to valify number of record that I load from csv file to SQL database table?
For example, the source file call product.csv and target table in database named DSS table name PRODUCT. I load data from flat file to table then I need verification if count between source and target not match send e-mail to me.
Thanks.
Grace
View 5 Replies
View Related
Oct 24, 2006
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
View 9 Replies
View Related
Jan 31, 2014
how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,
create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
[code]....
i would like to get 4 columns output
how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,
MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key
so the request is
need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
for ex 2)
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
for ex 4) 2012 records there is not match acctno and name in 2013 recods
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
for ex 5) 2013 records there is no match of name and acct no with 2012 records
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
Expected Results (just for format)
AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877
View 9 Replies
View Related
Jun 2, 2015
I'm trying to avoid a large amount of manual data manipulation.
Here's the background: Legacy system that has (well let's call apples apples) pretty much no method of enforcing data integrity, which has caused a fairly decent amount of garbage data to be inserted in some tables. Pulling one of the [Individuals] table from within this Legacy system and inserting it into a production system, into the Table schema currently in place to track [Individuals] in this Production system.
Problem: Inserting the information is easy, how to deduplicate the records that exist within the staging table that the legacy [Individuals] table has been dumped into in production, prior to insertion. (Wanting to do this programmatically with SQL or SSIS preferably, so that I can alter it later to allow for updating existing/inserting new)
Staging Table Schema:
;
CREATE TABLE [dbo].[stage_Individuals](
[SysID] [int] NULL, --Unique, though it's not an index intended to identify the [Individuals]
[JJISID] [nvarchar](10) NULL,
[NameLast] [nvarchar](30) NULL,
[NameFirst] [nvarchar](30) NULL,
[NameMiddle] [nvarchar](30) NULL,
[code]....
Scenario: There are records that duplicate the JJISID, though this value is supposed to be unique for every individual. The SYSID is just a Clustered Index (I'm assuming) within the Legacy system and will be most likely dropped when inserted into the Production [Inviduals] table. There are records that are missing their JJISID, though this isn't supposed to happen either, but have valid information within SSN/DOB/Name/etc that can be merged into the correct record that has a JJISID assigned. There is really no data conformity, some records have NULLS for everything except JJISID, or some records will have all the [Individuals] information excluding the JJISID.
Currently I am running the following SQL just to get a list of the records that have a duplicate JJISID (I have other's that partition by Name/DOB/etc and will adapt whatever I come up with to be used for those as well):
;
select j.*
from (select ROW_NUMBER() OVER (PARTITION BY JJISID ORDER BY JJISID) as RowNum, stage_Individuals.*, COUNT(*) OVER (partition by jjisid) as cnt from stage_Individuals) as j
where cnt > 1 and j.JJISID is not nullNow, with SQL Server 2012 or later I could use LAG and LEAD w/ the RowNum value to do my data manipulation...but that won't work because we are on SQL Server 2008 in this environment.
[URL]
With, the following as a potential solution:
GSquared (3/16/2010)Here's a query that seems to do what you need. Try it, let me know if it works.
Performance on it will be a problem, but I can't fine tune that. You'll need to look at various method for getting this kind of data from the table and work out which variation will be best for your data. Without access to the actual table, I can't do that.
;
WITH CTE
AS (SELECT master_id,
MIN(ID) AS first_id,
MAX(Account_Expiry) AS latest_expiry
FROM #People
GROUP BY master_id)
SELECT P1.master_id,
[code].....
Unfortunately, I don't think that will accomplish what I'm looking for - I have some records that are duplicated 6 times, and I'm wanting to keep the values within these that aren't NULL.
Basically what I'm looking for, is to update any column with a NULL value to the corresponding Duplicate [Individuals] record value for that column.
**EDIT - Example, Record 1 has a JJISID with NULL NameFirst & NameLast BUT Record 2 has the same JJISID and values for NameFirst & NameLast. I'm wanting to propogate the NameFirst & NameLast from Record2 into Record1
View 6 Replies
View Related
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
View 6 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Aug 18, 2015
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies
View Related
May 31, 2007
I'm using SQL Server 2005 Express.
(Below are the three tables along with their fields)
1) tblEmailAddress Table
a) EmailAddress
b) Emailid (each EmailAddress has this corresponding Emailid)
2) tblEmailGroupLink Table
a) Emailid
b) Unsubscribed
3) Remove_List Table
a) EmailAddress
What I'd like to accomplish is...
A) To compare the EmailAddress field of all the records in the Remove_list Table to the EmailAddress field of all the records in the tblEmailAddress Table and if there is a match, get the Emailid field value.
B) Go to the tblEmailGroupLink Table and lookup the Emailid and change the value of the Unsubscribed field to 'True' for that record.
I'd appreciate any help I can get.
Thanks,
Bill
View 6 Replies
View Related
Jul 13, 2015
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
View 4 Replies
View Related
Sep 9, 2005
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies
View Related
Apr 1, 2014
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
View 7 Replies
View Related
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
May 18, 2006
Hi all,
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
View 7 Replies
View Related
