How To Compare Alphanumeric Values Ranges Using Select Query
Nov 6, 2007
Hi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
View 2 Replies
ADVERTISEMENT
Jul 31, 2007
Hello,
I am trying to write a query that will be able to select different segments of data based on spelling of the last name.
For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'
then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...
I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?
Thanks
View 6 Replies
View Related
Apr 24, 2015
How to select a column to compare two values in it with AND
Select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
from employee a, driver b, trip c
where a.e#=b.e#
and b.l#=c.l#
and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;
error no rows selected..I want c.reg# for the two values inclusive
View 4 Replies
View Related
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
View Related
May 11, 2015
Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc
The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....
View 7 Replies
View Related
Apr 2, 2008
Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
View 8 Replies
View Related
Dec 18, 2007
Is it possible to write a sql statement to skip aplpha numeric values? I got a field containing these values; 20, 70, 150, 140, 100, KORT, 90, 180, 160. And I'm trying to check if any value is bigger than 175 (@Limit), but I want to skip the value 'KORT'. So is it possible to check if a value is numeric or not? ISNULL( CONVERT(int, ProductVariant.Size), 0) > @Limit Regards, Sigurd
View 2 Replies
View Related
May 8, 2007
Hi,
I want to search for alphanumeric values between an upper and lower bound in a sql database.For example: search for a serial number like pvf-456-3b. Upper bound is q, lower bound is g.I should then get every serial number starting with g - q.If possible the bounds should be more specific like "search for serial number between gt2 and qy"Can anybody help me out?
View 7 Replies
View Related
Feb 11, 2015
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
View 9 Replies
View Related
Jun 14, 2012
I'll keep the analogy as basic as possible, but lets say we sell vouchers. The vouchers are individually numbered.
We sell the vouchers in batches, and are stored in the DB as one record per sale with a range of the vouchers sold (so they scan the first and last voucher number to get the range)
E.g.
Table1
SalesID Description From To
SalesID0001 Batch of paper 100001 100015
SalesID0002 More paper 100016 100150
The vouchers come back in individually however, so we might get voucher 100011 back and is stored in another table as an individual item.
I'm trying to write a report which shows which vouchers have not come back yet and I'm struggling since the data is stored in ranges not on an individual basis...?
My thinking is leading me to maybe create a temp table where I can convert those ranges into real numbers....which I'll be able to more easily compare - but how I might do that.
(Example output from above example
TempTable
SalesID Description Voucher
SalesID0001 Batch of paper 100001
SalesID0001 Batch of paper 100002
SalesID0001 Batch of paper 100003
SalesID0001 Batch of paper 100004
SalesID0001 Batch of paper 100005
etc, etc)
View 2 Replies
View Related
May 31, 2008
I have a table with date like this.
InstId--Date----Readings
--1---10/12/2008--10
--1---11/12/2008--10
--1---12/12/2008--10
--1---13/12/2008--9
--1---14/12/2008--10
--1---15/12/2008--10
--1---16/12/2008--10
--1---17/12/2008--10
--2---05/03/2008--8
--2---06/03/2008--6
--2---07/03/2008--8
--2---08/03/2008--8
--2---09/03/2008--8
--2---20/03/2008--8
Guys I want to get the date ranges instrument wise for which the instrument readings are constant.
For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.
StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8
Thanks for any help.
View 10 Replies
View Related
Jan 23, 2008
Hi All,
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?
Pls clarify.
Regards
Venkat.
View 1 Replies
View Related
Dec 4, 2011
I have alphanumeric data in a Table Assets. The column name is Milepost.
Column Values are like below
MilePost
1.24
1.61
4.56
4.78
5.45
6.91
7.19
[code]....
Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41
My below query is not returning proper values. So i need a correct sql query.
Select Milepost from Assets where Milepost between '1' and 'LR 4.41'
View 4 Replies
View Related
Sep 16, 2004
Hello.
I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.
Either way, I need the row returned at the end of the query.
I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.
For example:
User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.
Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.
Does anyone know how I may accomplish this?
View 2 Replies
View Related
Apr 28, 2015
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go
Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
View 2 Replies
View Related
Oct 10, 2006
Hi All,
I think what am trying to do is quite basic.
I have 3 paramaters@value1, @value2,@value3 being passed into a stored
proc and each of these parameters can be blank. If one of them is blank
and the rest of them have some valid values, then I should just exclude
the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would
do this
select * from tblName
where column1 like @value1 and column2 like @value2 and column3 like
@value3
else if I have one of the parameter being passed as empty, I should
ignore that parameter like
if@value1 is empty then my sql should be
select * from tblName
where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue.
I want it through a stored procedure only.
Also, all the three columns can have null values in the table.
Please let me know what is the best possible way to do this. Thanks in
advance !.
.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}
.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}
.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}
.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}
.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}
View 7 Replies
View Related
Aug 7, 2007
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100
(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Regards,
Steve G.
View 4 Replies
View Related
Jul 26, 2014
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
View 2 Replies
View Related
Apr 23, 2008
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
View 11 Replies
View Related
Jun 3, 2008
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] (
[TIME_ADDED_FIS] datetime NULL,
[PERSON_ID] int NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] (
[PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_ADDED_FIS] datetime NULL,
[LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
GO
View 2 Replies
View Related
Jul 23, 2005
I'm trying to create a query that will tell me which requeststook longer than 10 days to move one from particular state to anotherstate. The query I've created returns the correct requests,but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.I'm sure I'm missing something easy, but I can't figure out what itmight be. Any help is appreciated! Thanks,Myron-- remove SPAM-KILL from address to reply by email --DDL for table creation and data population:CREATE TABLE [dbo].[ReqHistory] ([Id] [int] NOT NULL ,[ReqId] [int] NOT NULL ,[ReqIDStateId] [tinyint] NOT NULL ,[ActionDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[RequestStates] ([ID] [tinyint] NOT NULL ,[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Requests] ([ID] [int] NOT NULL ,[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StatusChangeDate] [datetime] NULL ,[Status] [tinyint] NULL) ON [PRIMARY]GOinsert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))insert into RequestStates values(1,'Awaiting CSMC')insert into RequestStates values(2,'CSMC Review')insert into RequestStates values(3,'Reject Awaiting CSMC')insert into RequestStates values(10,'Awaiting MA Review')insert into RequestStates values(12,'Awaiting FO Review')insert into RequestStates values(13,'Awaiting IS Review')insert into RequestStates values(20,'MA Review')insert into RequestStates values(22,'FO Review')insert into RequestStates values(23,'IS Review')insert into RequestStates values(30,'Func Approval')insert into RequestStates values(31,'Func Approval Complete')insert into RequestStates values(96,'Resolved')insert into RequestStates values(97,'Planning')insert into RequestStates values(98,'Open')insert into RequestStates values(99,'Closed')The query that almost works:select irh.ReqID, irh.MAactiondate, irh.reviewstate,irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextStatefrom (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,irh.ReqIDStateID As IRHState, irs.statetext as ReviewStatefrom ReqHistory IRHjoin requeststates irs on irs.id = irh.ReqIDStateIDwhere irh.ReqIDStateID in (20, 23)group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irhjoin (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetextfrom (select min(actiondate) as actiondate, ReqID,min(ReqIDStateID) as IRH2Statefrom ReqHistory--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,--but I couldn't make it workwhere ReqIDStateID > 23group by ReqID) as irh2join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqIDjoin requests ir on ir.id = irh.ReqIDwhere irh.MAactiondate + 10 < irh2.Nextactiondateorder by irh.ReqIDThe data being returned is:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 96 ResolvedThe data that should have been returned:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMC
View 3 Replies
View Related
Nov 11, 2006
Here is my statement:
Dim MyCommand As SqlDataAdapter
MyCommand = new SqlDataAdapter("select * from Request T1, Users T2 where RunID= "+ intRun_Num +" and T1.UserID = T2.UserID ORDER By Username", MyConnection)
I'm getting error saying:
Input string was not in a correct format.
the problem is the intRunNum. When I put intRunNum.toString(), there is no error but no results are coming up.
How do I get past this?
View 3 Replies
View Related
Oct 22, 2007
hello there,
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
cheers
zolf
View 1 Replies
View Related
Oct 22, 2007
hello there,
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
cheers
zolf
View 2 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Feb 2, 2006
guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated
Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found
thanks again
this forum is the best
rik
View 5 Replies
View Related
Sep 20, 2013
I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?
View 4 Replies
View Related
Nov 17, 2005
Hi
i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?
thanks in advance.! any help?
well i actually get the answers by doin this...
select order1 from datetable group by order1 having count(*) > 1
however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins
eg
id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50
View 9 Replies
View Related
Sep 11, 2007
This is my first post and I am probably not using the correct terminology but I will try. I want to compare the value of one of my fields in a result set to the value of the same column/field, but from the previous row in the result set. I am trying to identify the row in the result set when the value in a specified column changes value. I want to identify this row by placing a 1 in the last column. If that value did not change, then place a 0 in the last column.
I am not sure if this will make sense to anyone out there. Please let me know if more clarification is needed and I will try to provide it. Thanks for all your help in advance.
Don
View 2 Replies
View Related
Jun 25, 2007
I am trying to compare the last two values in the same column of atable. First of all, I have a column titled Row_Index that uses anindex which starts at 1 and increments by 1. What I am trying to dois compare the values in the column 'Shift_Date' for the maximum valueof Row_Index and the (maximum value - 1) of Row_Index. I've beentrying to declare two strings, setting each string equal to one of myvalues, and then comparing the strings. But this doesn't seam to beworking right. I'm not sure I am even declaring the stringscorrectly. Does anyone know how I can do this. A sample of what oneof my values looks like in Shift_Date is ' 6/25/2007'.
View 3 Replies
View Related
Jul 30, 2007
Hi
I€™ve a table like this (in SQL Server 2000).
v1 v2
2 8
7 10
11 15
13 17
v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.
please advise
Thanks
View 9 Replies
View Related
Feb 17, 2008
I have a table with one column. The values can be Y or N. How do I compare the 2 values and return a result depending on the comparison. For example, if I my column data looks like this.
Y
N
I need to compare them and if they match return a specific value and if they are different return a specific value.
View 12 Replies
View Related
