My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)
Id StdId TeacherName Day subject 1 1 Archana Monday English 2 1 Archana Tue Marathi 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi 6 2 Archana Mon english 7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid 1 Shalini 1 2 Monika 1 3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject from StudentTeacherRelation inner join Student Student.id = StudentTeacherRelation.StuId where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show Id StdId TeacherName Day subject where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
I want BetId 500 and 502 to be returned as result if i give select criteria where game id = 108,109. Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110). Similarly if i give, select criteria where game id =(108,109,110) it should return BetId 501.not the 500 and 502..which is different combination..
Hope i clarified my problem..pls help me in this regard.Thanks a lot...
I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:
SELECT * FROM TABLEA WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)
But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:
SELECT * FROM TABLEA WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN (SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)
I wonder what might be wrong, but also whether this is the correct approach to begin with.
how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.
Hosting_Site (Source File) YEAR MONTH HOSTING CLASS REGION HOSTING COST 2015 1 A UK 75 2015 1 A IN 80 2015 1 B IN 60 2015 1 C US 100
BENCHMARK (Lookup Table) Row Year Month Hosting class Region BENCHMARK COST 1 2015 1 A US 100 2 2015 1 B US 200 3 2015 1 A UK 50 4 2015 1 A null 70
STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)
Row Year Hosting class BENCHMARK COST 1 2015 A 22 2 2015 B 33
Then result should be:
FACT LOAD:
YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST 2015 1 A UK 75 50 2015 1 A IN 80 70 2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used) 2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)
create table #temp1 (name varchar(5), id int) insert into #temp1 (name, id) ( select 'a', 5 union select 'a', 8
[code]....
As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...
Selectdistinct b.name from( Selectdistinct name , id from#temp1 wherename = 'a' ) as a cross join( Selectdistinct name , id from#temp2 ) as b wherea.id = b.id
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
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 have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then
* First we delete last 3 month data from staging tables. * Get last 3 months data from source table. * Load that 3 months data from source to staging table.
We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys.
* Should I implement the same method of deleting last 3 months records and loading them again.
need help how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute) i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from ID fname new_date val_holiday ----------------------------------------------------
this is the table 2 i insert into ---------------------------------- ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
I have a little problem with DB, I'm using MS SQL 2005 Express edition.
I have a few tables in my DB, every table contains a list of objects and I need unique ID for every object, I can do it for one table, but not for more then one.
I've set a primary key and unique of a field.also I set it to auto numbering. Everytime I insert a record , that field will increase 1 (type bigint , start from 1).
After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?
I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int Or uniqueIdentifier? Or is it all the same??
I would like to store some frequently asked questions in a database. As those questions/answers could be translated in several languages, I was thinking of using two tables :
The first table would contain a unique field : the key wich is an id for each faq.
The second table would contain those fields : FAQ_ID : foreign key LANG : language QUESTION : the question translated in the good language. ANSWER : the answer translated in the good language.
What I find embarrassing, is that first table just contains a unique field : the id. But it allows : - to have a table in which a line correspond to an object. It is convenient for object relational mapping. - it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.
But, one more time, I find a bit embarrassing having a table with a unique field...
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates. The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table? Thanks in advance.SMc
I need to to have a table that has a counter field (used to generate an id code for a record). This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event. This is a sequential counter that will start 1 and go up on each new record inserted into other tables. What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time. This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled? A transaction to query and update the field?
As Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)
set @TableUniqueIdentifier = newid() set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7') set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))' exec @SQLString
Return Go Set Quoted_Identifier Off Go Set Ansi_Nulls On Go
------------------------------------------- the error is: Server: Msg 2812, Level 16, State 62, Line 12 Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.
So may'be a format issue or something, im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00
I am getting problem in unique row in database table, but not getting unique row because I have used Distinct keyword but not getting unique row so how can we do?
I have two table one table information another table Id. But second table in two code same but I am using distinct keyword i get some row unique but second table in two row in same code but when i am fetching row same auto_id render same id create duplicate row. But I am getting only unique row.
Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.
My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.
I would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER( [CUSTNO] varchar(5) NOT NULL, [ID] CHAR(9) NOT NULL, [NAME] VARCHAR(128) NOT NULL, [ADDRESS] VARCHAR(128) NOT NULL, [DATEOFBIRTH] DATE NOT NULL,
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table. MemberID + Month + Year should ne unique in Temp table