DECLARE @Teams AS TABLE(Team VARCHAR(3))
INSERT INTO @Teams
SELECT 'IND'
UNION
SELECT 'SA'
UNION
SELECT 'AUS'
select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
I'm trying to put an expression into a view query column. I've already created a column which contains the results of a calculation in the form of an expression. What I don't know how to do is use comparison operators to return a boolean value for display. I'll give some examples of what I'm trying to do, using literal values just to simplify things...
So say I want to display the result of an expression in a view query column, as a boolean value (or BIT), as True or False. I've tried putting the following expressions into the column and none work:
4 < 5: Access converts this to the string '4 < 5' and it's just displayed as a string.
CAST( '4 < 5' AS BIT ): When run, the query displays the error 'Syntax error converting the varchar value '4 < 5' to a column of data type BIT.
Although I'm using Access 2003, It's just a front end for a SQL Server 2000 database. The actual SQL statement containing the latter SQL function is:
SELECT dbo.Tapes.No, dbo.Tapes.Status, dbo.Tapes.LastWrittenDate, dbo.Tapes.Pool, dbo.Tapes.Location, dbo.TapeJobs.No AS Expr1, dbo.TapeJobs.JobNo,
dbo.TapeJobs.TapeNo, dbo.Jobs.No AS Expr2, dbo.Jobs.Date, dbo.Jobs.Successful, dbo.Jobs.RetentionRule, dbo.RetentionRules.No AS Expr3,
dbo.RetentionRules.Name, dbo.RetentionRules.Description, dbo.RetentionRules.RetentionPeriod, dbo.RetentionRules.RetentionCycles,
dbo.Jobs.Date + dbo.RetentionRules.RetentionPeriod AS AgedJob, CAST('4 < 5' AS BIT) AS Expr4
FROM dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No = dbo.Jobs.RetentionRule RIGHT OUTER JOIN
dbo.Tapes RIGHT OUTER JOIN
dbo.TapeJobs ON dbo.Tapes.No = dbo.TapeJobs.TapeNo ON dbo.Jobs.No = dbo.TapeJobs.JobNo
I'm new to SQL. I think the CAST function is the way forward, but I just don't know how to force it to resolve the expression '4 < 5'.
Can someone please help ?
--
Paul Anderson
Our dev team wants to introduce a complex key, which is made up of
EventDate datetime2(5) = ‘2015-10-22 10:19:59.12345’
ConsumerID bigint = 1234
SiteID tinyint = 15
EventDate will be converted to a bigint and then to a hex value
= 2015-10-22 10:19:59.12345 = 2015102210195912345 = 1BF714C8A0D4F699
ConsumerID will be converted to a hex value
= 1234 = 4D2
SiteID will be converted to a hex value
= 15 = F
The hex values will then be concatenated together and stored as a string (varchar). The application will handle the creation of the complex key
= ‘1BF714C8A0D4F699-4D2-F’
I am trying to argue against this approach and get them to store the values in their native form, in separate columns as a natural key.
To make matters worse, we need to use partitioning, where the partition boundary will be on a hexed datetime2(5) at weekly intervals. I was really hoping a proof of concept would show that hex string comparison could potentially put a row under a wrong partition, but so far, on an 8 million rowset, it is working fine.
For example, in the proof of concept, right partition boundary:
= '1BF398D53DFA1800'
(2014-12-15 00:00:00.00000)
contains rows
= '1BF3983242B9C000-1-2'
(2014-12-08 00:00:00.00000-1-2)
Through to
= '1BF398C373960580-2FAF3003-2'
(2014-12-14 23：59：00.00000-800010243-2）
The prior partition boundary is 1
= 'BF3983242B9C000'
(2014-12-08 00:00:00.00000)
How is this working, given that the string lengths (varchar) are different and the row value contains dashes and the partition boundaries are smaller in length without dashes? How varchar string comparison is working here?
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ;
GO
--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
Hi,
Assuming a table with a column defined char or varchar.
I have a SQL query like this :
Select * from table1 where column1='Building'
It returns the same result that
Select * from table1 where column1='BUILDING'
It is my understanding SQL Server (verison 7 or 2000, I tried on both) is lower/capital insensitive by default when it is installed. If I want SQL Server to be case-sensitive with my char or varchar columns, where can I set it?
Is it at database level or server level I can find this setting . What is the setting that control it?
Best Regards,
Alain Gagne, Lead DBA
gagnea@msagroup.com
i have 2 tables
table1
id name salary
2 sas 2000
3 jsh 2220
table2
id name
1 sas
2 jsh
how can we copmpare string like
name=name
Dear All:I encounter one problem when I want to implement my thought. My thoughtis that user want to search a record of someone but maybe user wouldtype wrong name or spell name wrong. I wish to compare the string whichuser inputed to the database column using "Socre Method". "ScoreMethod" has a variable "grade" to accumulate the score. I want toconvert the string to char array, and compare the char one by one. Ifthe string is more accurate , the grade is more high. At last, I choosethe most higher score record to show. How to do this thought with tsql?Could give me some tips or guide to learn? I will appreciate yourkindness, thanks.
View 6 Replies View RelatedI'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
Thanks
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
I'm trying to come up with a formula that will calculate the number of lines where two conditions are true.First, SLA must be either breached or achieved.And the second condition must be that the "country" and SLO group must be the same (these two values are located in different tables. So far I have only accomplished the first....
=CALCULATE(DISTINCTCOUNT([ID]),Data![SLA Result]="Breached")
I have tried adding FIND, EXACT or USERELATIONSHIP to the formula to no avail.... I keep running into the same error."The value for 'SLO Group' cannot be determined. Either 'SLO Group' doesn't exist, or there is no current row for a column named 'SLO Group'."
Country
SLO Group
SLA Achieved
SLA Breached
[code]....
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
View 9 Replies View RelatedFor Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
Tomy
I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
View 6 Replies View Relatedhow SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1
,b.table2
from table1 a, table2 b
where StringDistance (''a.table1,'b.table2') >90
HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT
)
ASSELECT * FROM
(
SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
I have a stored procedure with a where clause like this:
WHERE
Q.EffectiveDate >= @FromEffectiveDate AND Q.EffectiveDate <= @ToEffectiveDate AND I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' AND rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND Q.quoteID = isnull(@quoteID,Q.QuoteID) AND Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND ( isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks
i like to know how CTE works in sql server. i need to understand the flow of CTE. here i am pasting one example of CTE which i need to understand how works
DECLARE @StartTime DATETIME, @EndTime DATETIME
SELECT @StartTime = '09:00:00'
SELECT @EndTime = '17:30:00'
;WITH interval_cte(StartTime, EndTime) AS
[code]....
1) when CTE is declared then why fields name is used ;WITH interval_cte(StartTime, EndTime) AS ? what is the use of these field name.i do not understand how this line works
SELECT EndTime, DATEADD(mi, 30, EndTime) FROM interval_cte
WHERE EndTime < @EndTime
it has no alias define DATEADD(mi, 30, EndTime) ?how end time is increasing in loop ?which line increase end time value ?
Currently i have 2 type of data
A and a
But when i try to:
select * from tableA where col = 'a'
then all record with A and a will be selected, any way to avoid it and select only record with col ='a'?
I have to write a where condition where i need to compare string with date
I have 2 columns FROMDATE and TODATE with datatype varchar(9)
The strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM
i.e., 201401011 or 201401012
So I need to chop off last character before using them in WHERE condition.
Now I need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.
if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01
The query i wrote is
select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()
AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)
but it shows no data...
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
My expected result set should be like ....
We have two tables:
Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5
Table2
Servers|Databases|Users|Names
NULL |Database1|NULL |Name1
NULL |NULL |User1|Name2
NULL |NULL |NULL |Name3
Server5|Database5|NULL |NULL
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4
I used query like this:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
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.
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Note: Both col1 columns are of type nvarchar
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please????
Thank you!
I have a statement that works in compatibility mode 8 of SQL Server2000 while it fails in 7:declare @P3 intexec sp_prepexec @P3 output, N'@P1 float', N'select custname fromcustomer where custnr = @P1', 12600034custnr is a varchar(15). As long as the float p1 is less than1,000,000 the query returns correct results, but as soon as p1 isequal or greater than 1,000,000 the resultset is empty. I couldfigure, that above 1 million, sp_exec converts the float intoexponential format and compares this format to the string containingfixed format values. As said above, in mode 8 the statement returnscorrect results above 1,000,000.Please don't ask me, why we use float variables - I don't have anyinfluence on the statement - we isolated the statement with theprofiler.We are also currently dependent on the compatibility mode 7.I would be very grateful if you have any ideas, help or other issuesfor me.Thank you,Peter
View 1 Replies View RelatedI have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:--
CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVER
AFTER CREATE_DATABASE
AS
--execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.