tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as ( select srv.srvType as Type ,srv.srvName as ServerName ,srv.srvIP as ServerIP ,ins.instNetName as InstanceNetName
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank FROM RPL ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value..my script are this one just to having an example:
Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4 from fn where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31' AND adoc = '02634' Group by docnome,adoc,u_dim4 ORDER BY 2 asc
I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:
select a.* from myTable A
The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.
The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.
Perhaps using the datediff() function, or other functions as required to achieve what I need?
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results. 2. very slow to retrieve results (> 1 minute)
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine. However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example: "SELECT TOP 20 to 40 * FROM Northwind" Hopefully this makes sense. Is there any way of doing this? Thanks in advance,Russ
My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?
I have a SQL question which I suspect is very easy to answer but can't seem to find one for.
I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.
So, the SP would for example accept the parameters
@Upperbound = 80 @Lowerbound = 61
and would return a recordset of 20 records being the 61st thru 80th record from the queried table.
Thanks for the help Guy
PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.
if @persontype<>"" begin set @cond= @cond+"and persontype="+@persontype end if @name<>"" begin set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) " end if @activeaccount<>-1 begin set @cond= @cond+'and activeaccount='+@activeaccount end if @adddatetime<>"" begin set @cond= @cond+'and adddatetime='+@adddatetime end if @country<>"" begin set @cond= @cond+'and x_country='+@country end print @cond exec( " select * from users where 1=1 "+@cond) GO
I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike
I'm trying to write a select statement that will return only "billable" records. A record is billable if: - it is the only record for a Case (Case is an FKey ID column in my records table) OR - the last billable record for Case X has a DateTime > 24 hours before the record in question.
Getting records for the first condition is easy:
SELECT ID FROM Records r LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case] WHERE r2.[Case] IS NULL
It's the second part I'm having trouble with.
UNION SELECT ID FROM Records JOIN ? .... WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??
I have to assume there can be any number of records for a case.
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [age] int , [sex] char(1) , [address] Varchar(30) , [status] int );
Insert Into #request Values('2342','Jack','23','M','Texas','0'); Insert Into #request Values('223452','Tom','45','M','Ohio','1'); Insert Into #request Values('22353','Bobby','23','M','Austin','0'); Insert Into #request Values('22362','Guck','23','M','Austin','0'); Insert Into #request Values('22392','Luck','23','M','Austin','1'); Insert Into #request Values('22362','Buck','23','M','Austin','0'); Insert Into #request Values('2564392','Jim','23','M','Austin','1'); Insert Into #request Values('2342','Jasm','23','M','Austin','0'); Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.
Thanks
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data] -- Add the parameters for the stored procedure here @AssetID int, @Timestamp datetime, @FaultCode int, @State int AS BEGIN SET NOCOUNT ON; IF @State = 3 BEGIN INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift) VALUES (@FaultCode, @AssetID, @Timestamp, 0, CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS' WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS' ELSE 'NIGHTS' END) END
IF @State <> 3 BEGIN DECLARE @Count int SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0) IF @Count <> 0 BEGIN DECLARE @StartTime datetime SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0) UPDATE LineFaultData SET Duration = DateDiff(s,@StartTime, @Timestamp) WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END END
END
SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription, dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift FROM dbo.LineFaultDescription INNER JOIN dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')
WHERE (dbo.LineFaultData.AssetID = {Asset_ID}) AND (dbo.LineFaultData.Shift = '{@Shift}') ORDER BY dbo.LineFaultData.StartTime DESC
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields. SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR" SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue) I have more code to show, if this looks correct. Just let me know. Thanks in advance.
Hi I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1 The SQL I have written however returns all records SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2 WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1"> Please could someone help Many thanks Colin
