Transact SQL :: Query To Select Particular Record?
Sep 21, 2015
I have created a sample Table with inserting below data,
ID Subject CreatedDate
1 Test 2015-09-20 22:59:07.373
2 Test 2015-09-21 09:16:58.290
3 Test 2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:
DECLARE @BeginDate Datetime DECLARE @EndDate Datetime Set @BeginDate = '05-1-2015' Set @EndDate = '05-31-2015' SELECT a.AgentName, R.BALANCEBEFORE,
[Code] ....
AGENTNAME BALANCE BEFORE BALANCE AFTER DATE DOUGLAS 9738.75 9782.75 2015-05-11 DOUGLAS 9782.75 9804.75 2015-05-12 DOUGLAS 9804.75 9837.75 2015-05-13
In the sample data above, ideally I would like my query to return data as follow:
AGENTNAME BALANCE BEFORE BALANCE AFTER DOUGLAS 9738.75 (from first activity) 9837.75 (from last activity)
Not sure how I can write sql query to accomplish this.
SELECT U.UserFamilyName, F.FamilyName FROM Users U LEFT JOIN User_Friends F ON U.UserID = F.UserID WHERE U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.
-- Inserting new preferred supplier into R5CATALOGUE
DECLARE @DATEPROCESS DATETIME; SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE) INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG, CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)
If I wanted to run a query on any table and in the recordset that is returned have an 'id' field (or whatever) with the record id of that record, how would I do this?
I'm thinking something like
Select field1, field2, recordNumber // derived somehow - not an actual field from table
If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.
LeaseIDOpID Year MonthMCFFileDate 208801407982006 107660200807 208801407982006 107680200801 208801407982006 107784200704
Condition 1. Let's say, the current date is 5/9/2008 2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008 3. Every Main_ID only pickup the MAX Hit 4. Diff (column on the fly) = Hit - Hit2
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.
eg. of summary. Recipe Name Total Weight Hedge Shears - Lasher/kudu 500 Grass Slasher 200 eg.
But it needs to summarize when selected between start and end date.
I have the follwing function in my SQL data base which is hosted in Azure.
All date and time field in my db are store as UTC and I have a function define as below :
ALTER FUNCTION [dbo].[func_GetCurrentLocalTimeFromUTC] ( ) RETURNS datetime AS BEGIN DECLARE @OffSet as int = 1 DECLARE @CurrentDate as datetime = getUTCdate()
[Code] ...
Now what I have trying to do is to perform a select statement on a table where I have a dateTime value field and add the proper offset value to the field in order it is display based on where user will run the query. For example if I run this simple query :
SELECT INVENTORYDATE from Inventory
Will return the UTC dateTime value.
How can I use the select statement in order to get the field format with proper offset based on user pc timezone/. Please note that the query will be called from a rrs.
How I can get the desired result using query. I don't want any
'@' variable in the result. DECLARE @ST AS varchar(10)='AA' DECLARE @desc AS int=8 DECLARE @STID AS int=4 DECLARE @PP AS int=63 DECLARE @SS AS int=22 /* Desired Result */ Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As BEGIN BEGIN TRAN T1 SET NOCOUNT ON SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate(); UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id; SELECT * FROM #TempTable; DROP Table #TempTable; COMMIT TRAN T1 END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) { List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure; try { Command.CommandTimeout = CONNECTION_TIMEOUT;
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"." Here is my Query, sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go reconfigure SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I have daily data that I wish to summarise into monthly. The field [Close] will return the last day of the month that has a value.The query below summarises the data as I want it but using the max of [Close] in the month instead rather than the Last value for the month. In Access I used to use Last function that worked perfectly. From googling, Last doesn't seem to exist in T-SQL but there must be a way to do it.
D.YearMonth_Number,max(AT.[Close]) FROM tbl_Date ASD INNERJOINtbl_ASXStats ASAT OND.YearMonthDay_Number =AT.YearMonthDay_Number
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.
Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.
So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.
file_target_ID is identity column which will repeat per files_ID
Now, i just shown Target log for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date column
Like this ..
Target Date New Column 2015-09-09 00:00:00.000 2015-09-16 00:00:00.000 2015-09-16 00:00:00.000 2015-09-25 00:00:00.000 2015-09-25 00:00:00.000 New Target date after 25-9-2015
select computer, count(*) as MissedCount from WInUpdates_Neededreq WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE()) group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this
Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.
And this query produces wrong results, its giving me match and also non match records.
Query: SELECT m.MemberID, vw.Category, vw.Type, FROM dbo.TestVW vw JOIN dbo.TestMember m ON vw.MemberKey = m.MemberKey WHERE vw.Type = 'test' GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.
Student_name _____________ Rocky Albert Rocky Williams Albert Robert
The query will show
Student_name ______________ Rocky Rocky Albert Albert
