Finding Multiple Values
May 24, 2008
Hello,
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
Thank you experts!
View 1 Replies
ADVERTISEMENT
Mar 22, 2006
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.
Thanks Much
View 2 Replies
View Related
Apr 10, 2007
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?
View 4 Replies
View Related
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)?
View 19 Replies
View Related
Jul 17, 2007
Hi Everyone,
This is the table I have:
Sections Class1 Class2 Class3 Class4
--------------------------------------------------------------------
A 30 20 30 40
B 40 15 10 10
C 20 5 10 5
Total 90 40 50 55
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.
View 9 Replies
View Related
Mar 29, 2013
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.
View 5 Replies
View Related
May 23, 2007
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.
View 1 Replies
View Related
Mar 13, 2008
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?
View 4 Replies
View Related
Jan 28, 2008
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.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
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.
Thanks
Bob
View 4 Replies
View Related
Jun 8, 2005
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
View 2 Replies
View Related
Jun 12, 2014
The following is sample data I am dealing with.
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.
View 3 Replies
View Related
Aug 26, 2014
I'm trying to come up with a query for this data:
CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)
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
[Code] ....
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
[Code] ....
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
View 3 Replies
View Related
Apr 15, 2015
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
[code]....
View 2 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Nov 16, 2006
I have a table that contains the following
UserName - Which is unique to each user
TimeStart - Which is the time and date that the user logs in
TimeEnd - Which is the time and date that the user logs out
Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.
View 1 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Jul 20, 2005
I am writing an installer that needs to put a dll file with a bunch ofextended stored procedures in the BINN directory. The problem is thatif the machine is running multiple instances of SQL Server, there ismore than one BINN directory. The installer has a dynamicallypopulated dropdown with the name of the instances, so does anyone knowif this logic is correct and/or reliable assuming SQL Server 2000?If the instance name = "MSSQLSERVER"then path to BINN directory = registry value ofHLMSOFTWAREMicrosoftMSSQLServerSetupSQLPath + "BINN"If the instance name != "MSSQLSERVER"then path to BINN directory = registry value of HLMSOFTWAREMicrosoftSQL Server<Instance Name>SetupSQLPath + "BINN"Thanks in advance for any help.
View 1 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Mar 24, 2015
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
View 9 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Mar 29, 2007
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Apr 26, 2015
I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.Â
View 5 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Jan 18, 2002
PO table has
itemnum - over 16000 records
closedate - can be 2001, 2002
location - is 1, 2, 3 etc
Like to find itemnum where location is 1 and itemnum has closedate of 2001, if it does have close date of 2001 than check if another record of same item with closedate of 2002. If yes, than diplay that item.
The result would be itemnum which have closedate of 2001 as well as 2002. Each item may have more than one record for each year.
View 1 Replies
View Related
Jun 17, 2008
Hi.
I;m trying to sort out some values.
Ok so for sort. We have a contract and a renewal column.
A contract can have many renewals.
I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.
so im trying this..
select distinct c.policyno,c.renewalno
from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID
where
c.renewalno in (select max(crn.renewalno) from contract crn
where
c.id= crn.id
But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15)
Any help?
View 16 Replies
View Related
Oct 25, 2007
hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
View 8 Replies
View Related
Jan 22, 2008
Hi there
I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts.
Thanks
View 1 Replies
View Related
Jun 2, 2004
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
View 2 Replies
View Related