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)
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.
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 an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents, However I know this will get quite messy at the application level.
I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess?
How can I set an on insert constraint, or what its called for this in SQL Server?
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem ( FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY ,ParentFSIDINTEGER NULL ,NameVARCHAR(100) ,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
ID Flag TestDate Value Comment 111 2 12/15/2014 7.5 null 222 2 Null 10 received
Matrix_Current table could have 1 or multiple rows as below.
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 111 2 02/23/2015 7.9 111 2 04/07/2015 6.8 222 1 null 8 test comment 1 222 3 null 9 test comment 2
When I run below update
UPDATE AM SET M.Flag = MC.Flag, M.TestDate = MC.TestDate, M.Value = MC.Value, M.comment = MC.Comment FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 222 1 Null 8 test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID Flag TestDate Value Comment 111 2 04/07/2015 6.8 222 3 Null 9 test comment 2
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
Hi,i m using sqlexpress 2005 and sql management express studio. I want to know how could i insert multiple records on a single query in a table?i also want to whats wrong with this insert query?DROP TABLE IF EXISTS `tblcountry`;CREATE TABLE `tblcountry` ( `ID` int(3) NOT NULL auto_increment, `LCID` int(4) unsigned default '0', `CountryCode` char(2) default NULL, `Country` varchar(50) default NULL, `CountryInt` varchar(50) default NULL, `Language` varchar(50) default NULL, `Standard` tinyint(1) unsigned default '0', `Active` tinyint(1) unsigned default '0', PRIMARY KEY (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;INSERT INTO `tblcountry` (`ID`,`LCID`,`CountryCode`,`Country`,`CountryInt`,`Language`,`Standard`,`Active`) VALUES (1,1030,'DK','Danmark','Denmark','Dansk',0,1), (2,2057,'GB','England','England','English',1,1), (3,1031,'DE','Deutschland','Germany','Deutsch',0,1);finally how could i extract the database structure and data from the sql express management studio?so that i can copy it and re use it some other computer.Thanks.Jack.
I am trying to insert multiple rows into a table getting data from a web form.
I have 2 fields being passed from a web form to the below stored procedure @FormBidlistID Sample Data --> 500 @CheckBoxListContractors Sample Data --> 124,125,145,154,156,
The below DELETE function is working great. However for some reason or another the INSERT INTO sql command is not putting seperating the string and adding rows to the database? I am not sure where the error is occuring.
In the end I need the data to go into the database columns like so
CREATE PROCEDURE dbo.UpdateBidlistContractors @FormBidlistID int, @CheckBoxListContractors varchar(3999) AS DELETE FROM BidlistContractors WHERE BidlistID = @FormBidlistID DECLARE @ContractorID nvarchar(10) DECLARE @BidlistID nvarchar(10) DECLARE @startPosition int DECLARE @commaPosition int SET @startPosition =1 SET @commaPosition = 0 WHILE (@startPosition < LEN(@ContractorID)) BEGIN SET @commaPosition = CHARINDEX(' , ' , @ContractorID, @startPosition) SET @ContractorID= SUBSTRING(@ContractorID, @startPosition, @commaPosition - @startPosition) INSERT INTO BidlistContractors (BidlistID, ContractorID) VALUES (@BidlistID, @ContractorID) SET @startPosition = @startPosition + LEN(@ContractorID) + 1 END GO
Where am I going wrong? Thank you in advance for any help. :-)
CREATE TABLE [dbo].[appl]( [app_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL) CREATE TABLE [dbo].[appl_party]( [prty_id] [numeric](9, 0) NOT NULL, [app_id] [numeric](9, 0) NOT NULL) CREATE TABLE [dbo].[party]( [prty_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL, [lockbyid] [char](8)
I want to update muliple rows in table "party" for column "lockbyid"
below is the update query with which i can only update one row but i need to update multiple rows in party table
update party set LOCKBYID ='abcd' where prty_id in (select distinct prty_id from sappl_party where app_id in (Select appl.app_id FROM appl INNER JOIN appl_party ON appl.app_id = appl_party.app_id where appl_party.prty_id=1234)) and LOCKBY_USR_ID is null
Is it possible to insert multiple rows in a table using one INSERT statment. If yes, how can I do that ? I tried doing this using the substitution method.
Using Substitution Method - This is how,I proceeded.
In our organization there is a call center where operators entry call details and custormer qruery in a table using front end application. For these queries there are multiple no of executive who will reply the query using a front end application. using the application the exectives retrive specific no of records from call center table(if there are unreplied call exists) and reply them. When a excutive retrives specfic no of rows, i want to update a flag in that rows, so that others do not get them.
I use the following procedure which is creating dead lock when multiple users execute it simultaneously
create procedure get_from_maintab @cnt int as declare @slno int,@curcnt int select @curcnt=1 set rowcount 1 while @curcnt<=@cnt begin begin tran select @slno=serial_no from maintab where getflag='' if @@rowcount=0 begin rollback return end update maintab set getflag='Y' where serial_no=@slno and getflag='' if @@rowcount = 0 begin rollback return end insert into detab select fld1,fld2,fld3 from maintab where serial_no=@slno if @@rowcount = 0 begin rollback return end commit tran select @curcnt=@curcnt+1 end set rowcount 0 return
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID' And szElementDescr='Slideshow' if @@error <> 0 begin goto ErrPos end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC' And szElementDescr='CategorySlideshow' if @@error <> 0 begin goto ErrPos end
As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
create table #prints(id int IDENTITY(1,1) NOT NULL, Printermarkersupplyid varchar(36),PrinterID varchar(10),Description varchar(10),SupplyLevel int,ModifiedDate datetime) insert into #prints(Printermarkersupplyid,PrinterID,Description,SupplyLevel,ModifiedDate) select newid(),'P1','D1',100,'2013-08-1 03:28:38.203' union all
[code]....
my requirement is to get the difference between adjacent rows.ie difference between 2nd and 3rd or 6th and 7th but not 6th and 8th.if difference between 2nd and 3rd is less than zero and 3rd modified date > 2nd modified date,then i should get count as 1 against 3rd row.
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on
-- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId
-- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, )
-- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId )
-- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
I have 3 tables with the follwing schema Table <Category> {
UniqueID, LastDate DateTime }
Assume the follwing tables with data following the above schema
Table Cat1 {
1, D1 2, D2 3, D3 } Table Cat2 {
2, D4 3,D5 4, D6 } Table Cat3 {
1, D7 3,D8 5,D9 }
I have a Master and the schema is as follows Table master {
UniqueId, Cat1 DateTime, -- This is same as the Table name Cat2 DateTime, -- This is same as the Table name Cat3 DateTime -- This is same as the Table name }
After inserting the data from all these 3 tables, I want the my master table to look like this Table Master {
I know that this is legal sql: "SELECT 1 AS Blah" I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData HRID (identity)
ResourceAllocation ID (identity) [Last Name] (linked to HRID) Project [Resource Start Date] [Resource End Date] [Percent Utilization]
tblHCvalues RAID (linked to ResourceAllocation.ID) a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.