Transact SQL :: Another Way Of Finding Max Values?
Oct 2, 2015
I have a query I am currently attempting to optimise. Â The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. Â For example:
SELECT Column1
,Column2
,DateColumn
FROM Table1 T
INNER JOIN
(
SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt
FROM Table1
GROUP BY Column1 + Column2
) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
Hi I am new to SQL. My scenario is I get a string from the user I need to match this string with value present in 2 cloumns. If the string is present in either of the column then i should select that particular record from the table. Now my question is if i Have to match with one cloumn i can used charindex function is there any function with which i can match the string with data present in more than one cloumn.
I want to write a query to bring out any records in a table where any of its fields contain a null value. I could do it in this way:
select *
from customers where name is null or role is null or email is null u003c/p>u003cp styleu003d"margin:0cm 0cm 0pt">u003cfont faceu003d"Times New Roman" sizeu003d"3">u003cfont coloru003d"#000000">However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance.u003cspan>Â u003c/span>I was hoping there was a simple way to do the same thingu003cspan> ie find any rows with a blank fieldu003c/span>?u003cspan>u003cfont faceu003d"Arial" sizeu003d"2">Â u003c/font>u003c/span>u003c/font>u003c/font>u003c/p>u003c/span>u003c/font>u003c/div>",0] ); D(["ce"]);
//--> However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance. I was hoping there was a simple way to do the same thing ie find any rows with a blank field?
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single 2) Married 3) In Relationship 4) Divorced 5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE" Column Name: "Relationship_Status" Value Type: INT
There are around 6 more classes which I haven't mentioned. The resultant table that I need would be something like this.
Sections Class1 Class4 Others
-------------------------------------------------
A 30 40 50
B 40 10 25
C 20 5 15
Total 90 55 90
What I want is the top two maximum values to appear and everything else to be clubbed into Others and correspondingly their numbers to be summed up i.e. Dynamically depending on the total only those classes with maximum 'Total' value to appear.
Hi all...I've got a group of tables and I wanted to first see which tablescontained a fields containing the "Date/Time" data type. And I wassuccessful in doing so...here's the query:(select a.name tablename, b.name colnamefrom sysobjects a, syscolumns bwhere a.name = object_name(b.id)AND B.TYPE = 61 AND A.XTYPE = 'U')Now...my only issue is find which query I can run (On each table)which returns to me ONLY the values containing the date/time stampwith milli seconds (Ex: 01/01/2007 10:10:50:987)I require this since we are planning on shifting those tables to theOracle platform, and we've had issues doing this while loading theextracted information, since it was not taking the values containingthe "milliseconds".Is there a way to do this? Or is it better to simply remove themilliseconds all togeather? I wouldn't mind doing so provided that itdoesn't includes changes to the actual data type.
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b task_c task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus: queue_num task_num team queue_1 task_a red task_b red task_c red task_d red
No bonus for any team here queue_num task_num team queue_1 task_a red task_b blue task_c red task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
I am trying to find the members who are having monthstartdate continuously for 11 months ;
here in my example 123 wont have monthstartdate continuesly for 11 months it has break for february '2014-02-01'; where as 222 and 223 has continus 11 months , so i need to pull such members .finding out the members continuesly(enrolled) having 11 months.
Below is the sample data i am referring.
memid    MonthStartDate 123 2014-01-01 123 2014-03-01 123 2014-04-01 123 2014-05-01 123 2014-06-01 123 2014-07-01
I need to find the last purchase price for each product. Â If I run the following code, I correctly get 1 result for each productID and the last purchase order number.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article
Now I need to add in the price for that product on that orderID. Â I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article
How can I get it to simply add the price to the first query?
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".Â
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values') ) DROP TABLE #hard_values; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data') ) DROP TABLE #dummy_data; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
SELECT * INTO TEMP FROM (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X
I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd) SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 1 ELSE 0 END AS isFirstVisit, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 0 ELSE 1 END AS isRepeatVisit FROM#Visits
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
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 cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. Â I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE (  Id  INT,  PublicationId  INT,  MetricId    INT,  ProviderId   INT,  DateCreated   DATE,  Amount     FLOAT
I have a scenario here where the data looks like -
IDÂ Â Â Â Â Â Â Date 100Â Â Â Â Â Â 07/01 100Â Â Â Â 07/02 100Â Â Â 07/03 100Â Â 08/01 100Â Â 08/02 100Â Â 08/15
Now I need to find out unique occurrences of ID - 100 ( where count =Â unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â Â Â Â Â Â Â Â Â Â Â InsCode value 1002 should always mapped to 1006 Â Â Â Â Â Â Â Â Â Â Â InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I can not modify DDL of the table, so creating a trigger etc is not an option.  This is my syntax that I am using, and each #Testing.abcd has different possible options. Â
For example, when #Testing.abcd = Test1 then #Data.field2 okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Select a.uid , a.name , mewp.data As Association from Asset a Join wshhistory wsh on a.uid = wsh.assetid Join worksheet w on wsh.wshid = w.uid left join
[code]...
When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables..
I am using Visual Web Developer Express 2005 and SQL Server Express 2005. I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine. I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed. How can I check for null values and skip a block of code within my Transact Sql trigger. Thanks.....
I have [TableAccount.AccountType] field which can store these values:
Value C S E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
