Comparing Time Values

Aug 25, 2005

I have a table called WorkItem. It models a chunk of work done during
a working day.

It has two columns that I'm interested in:

Start (smalldatetime) - the TIME the work block is begun
Duration (int) - the duration in minutes of the work block.

In another table called OvertimeRates I have information about rate
multipliers and a column that tells me the TIME that the rate
multiplier kicks in.

e.g.

OTRateBegins (smalldatetime)

In terms of calculating whether a particular work block starts after
the OTRateBegins, I could (I presume) do something like:

If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
OTRateBegins, 108)

However, would I be better off using DATEPART functions to get the hour
and minute parts of both the Start and OTRateBegins, and using them
instead? For some reason, (probably paranoia!), I am suspicious of the
CONVERT function.

Apologies for not posting DDL, but I felt that the situation didn't
really warrant it.

Thanks

Edward

View 6 Replies

Transact SQL :: Date Time Casting - Comparing Values And If Not Same Then Populating Record

Sep 9, 2015

I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.

I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"

Condition in Where clause:

cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)

Note: Both col1 columns are of type nvarchar

View 6 Replies View Related

Lookup / Merge Join / Script - Howto Look Up Values By Comparing To A Range Of Values?

Jun 4, 2007

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:

"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _

" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _

" AND street_suff = @street_suff AND street_post = @street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"

My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value

Any suggestions?

thanks for your time...

View 5 Replies View Related

Comparing The Previous Time With The Current Time

Dec 21, 2005

Manish writes "Hi there, any help will be greatly appreciated as i am struggling with this problem :)

I have a field in a database called time which also has the value time in it.

What i want to do is to have a sql query which will display all the records in that table where 5 mins have gone after that time."

View 4 Replies View Related

Comparing Only Time In Sql?

Nov 30, 2005

Comparing only Time in Sql?
Hi Dears!
I have two Different DateTime Values i want to Compare only their times independent of Dates How to do it?
let
DateTime @dt1='2004-10-30 19:30:00.917'
DateTime @dt2='2005-11-29 15:00:00.917'
accordint to my requirements
@dt1>=@dt2 should be true as 19:30>=15:00
because i have to compare only the time not the Date how to achieve this in sql .Function required.
Thnx In Advance

View 3 Replies View Related

Comparing Two Row Values

Jan 15, 2007

whats the best way to compare two row values and concatenate those two

For example

Table1
-------
Col1 Col2
----------
A 1
A 4
B 2
C 3
A 5

I need a query to return something like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

Thanks in advance

View 11 Replies View Related

Comparing Values

Feb 23, 2006

Hi All

I have 2 tables
dbo.aus

dbo.us

aus has column ausid

us has column usersid

how do i ran a query to filter out all matching values in the 2 tables
basically i want a list of values that are the same in both tables

thanks

View 3 Replies View Related

Comparing NULL Values

Nov 2, 2006

I have two tables with the same structure / different data. I run a query between the tables to find the different records for the out put. When the values of the records or NULL, I do not get the output.

Code:

SELECT dbo.Closest3StoresB_RBT.CustomerPost, dbo.Closest3StoresB_RBT.ClosestStore1,
dbo.Closest3StoresB_RBT.ClosestStore2,
dbo.Closest3StoresB_RBT.ClosestStore3
FROM dbo.Closest3StoresB_RBT INNER JOIN
dbo.Closest3StoresB_OLD_RBT ON dbo.Closest3StoresB_OLD_RBT.CustomerPost =
dbo.Closest3StoresB_RBT.CustomerPost

WHERE
(dbo.Closest3StoresB_OLD_RBT.ClosestStore1 <> dbo.Closest3StoresB_RBT.ClosestStore1) OR
(dbo.Closest3StoresB_OLD_RBT.ClosestStore2 <> dbo.Closest3StoresB_RBT.ClosestStore2) OR

(dbo.Closest3StoresB_OLD_RBT.ClosestStore3 <> dbo.Closest3StoresB_RBT.ClosestStore3)

CustomerPost | ClosestStore1 | ClosestStore2 | ClosestStore3

I want to avoid seting the NULL values to '0' . Any suggestions?

View 3 Replies View Related

Comparing Values Within Same Table

May 1, 2014

I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2

Example table

IDValue1Value 2
TEST1HouseTango
TEST2HouseTango
with test as (
select * from ExampleTable where ID= 'TEST'
),

[Code] ....

View 4 Replies View Related

Comparing Values Within Database

Sep 10, 2007

Hi,

There are two tables in my Database, tb1 and tb2 which both have the same attribute ID. I would like to ensure that there is nothing in ID in tb1 which is not listed in ID in tb2, can anyone help?

Thanks for any info.

Albert.

View 10 Replies View Related

Comparing Date With NULL Values

May 8, 2001

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

View 4 Replies View Related

Comparing Column Values Among 2 Or More Rows

Jun 29, 2012

I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.

Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.

Three tables to note in bold, with notable fields in italics below them:

EmployeeData
HRID (identity)

ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]

tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...

And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have

Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.

We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want

All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:

Here are the rules, and they should work for any number of multiple resource allocations for one employee:

Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)

I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?

View 6 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 4 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

View 1 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

View 4 Replies View Related

Comparing Values And Inserting A Record

Apr 25, 2007

SELECTIndustry,
100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent],
100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent]
FROMTCompanies
GROUP BYIndustry
ORDER BYIndustry

This code above is working as I need it but I need to insert some additional functionality. Thanks

I need to add something like this:

IF YesPercent > NoPercent
UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>'
Else If NoPercent > YesPercent
UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>'
Else
UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>'
End

View 1 Replies View Related

COMPARING WITH COLUM WITH SPECIFIC VALUES IN SQL

Feb 6, 2008

Im Working with stored procedure. How can i compare Columns with specific values. I want to get the greater values of those column and inserted it to other columns. i want something like these
CASE
WHEN a> b,c,d THEN a
WHEN b> a,c,d THEN b
WHEN c> a,b,d THEN c
WHEN d> a,d,c THEN d

is there any ways to implement this? i got an error..
thanks please help..

View 7 Replies View Related

Comparing GPS Lat/Lon Values From Database Within An Area

Apr 2, 2008

Hi,

I would be very grateful if someone could help me. I have very little SQL knowledge and would like a push in the right direction:

I have a application that receives GPS lattitude and longitude values. I was originally using file base system where I would load all the points from the file into memory and do the calculations in the software but I now would like to use SQL Server Express.

I have created the tables:

[Table 1] GPS Points:
id int Primary key
latitude real
longitude real
desc nvarchar(128)

[Table 2] GPS Locations (comprises of one or more GPS Points from [1] above) to form a boundary:
GPS Locations database structure is:
locationId int primary key
gpspoint1 int
gpspoint2 int
gpspoint3 int
gpspoint4 int
desc nvarchar(128)

Table 2 contains up to 4 gps points ids from table 1 and GpsPoints1-4 can be null.

I have created the tables and I have inserted GPS values into both tables.

My problem:
I am trying to compare the current Lat/Lon (which are passed into the SQL fuction as two real datatype value) and I want to return all Table 2 locationId's where the current Lat/Lon are withing GPSPoints1-4 area.
gpspoint1, gpspoint2, gpspoint3, gpspoint4 reference an id from Table 1. I am not sure how I can do this?

I have implemetned a SQL function that returns all points from Table 1 that are within the predefined radius of 1.5 miles from the current Lat/Lon values passed in.

select desc,latitude,longitude, acos(SIN( PI()* @LatDec /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* @LatDec /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* @LonDec /180)
)* 3963.191 AS distance
FROM GpsPoints
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* @LatDec /180)*SIN(PI() * latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
) < = Radius
ORDER BY 3963.191 * ACOS(
(SIN(PI()* @LatDec /180)*SIN(PI()*latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
)

where 3963.191 is earths radius in miles,
radius is 1.5,
@LatDec is the current latitude and
@LonDec is the current longitude

Uses a lot of Trig and (for me) is quite complicated. This works very well and is very accurate. It also only uses a single table. I want to be able to now look for the current position within a boundary of 3 or more positions from Table 1 and this is where I am struggling?

Also, any suggestions on how I could do this better would be very much appreciated?

I look forward to your reply and help.

Thank you in advanced.

View 2 Replies View Related

Comparing 2 Columns Containing Null Values

Sep 28, 2006

Hi All.

I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

I'd be grateful for any pointers!

Thanks in advance

View 4 Replies View Related

Comparing Values Between 2 Matrices (matrix)

Jun 8, 2007

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

View 4 Replies View Related

Comparing Datime(SQL ) Year, Month, Day,Time

Sep 21, 2007

hi i want to compare tow dates in my procedure, comparing all(year, month, day, time).can anyone help me. thanhs.

View 1 Replies View Related

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

SQL Server 2008 :: Comparing 2 Table Values

Jun 8, 2015

I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.

View 9 Replies View Related

SQL Server 2008 :: Comparing Tables For Like Values?

Jul 17, 2015

I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.

The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.

I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.

View 9 Replies View Related

SQL Server 2014 :: Comparing Two Rows Values?

Oct 30, 2015

I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)

Input Table Data:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2008
10104210001/1/200912/31/2009
10104220001/1/20081/15/2008
10104220001/16/200812/31/2008
10104220001/1/200912/31/2009
10104230001/1/200812/31/2008
10104230001/1/200912/31/2009
10104230001/5/201012/31/9999
101042DDPP5/16/200712/31/2007
101042DDPP2/16/20075/15/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

Output should be like this:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2009
10104220001/1/200812/31/2009
10104230001/1/200812/31/2009
10104230001/5/201012/31/9999
101042DDPP2/16/200712/31/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

View 3 Replies View Related

Wierd Query Results When Comparing Field Values

May 8, 2008

Hi Guys, I am experiencing weird results

SELECT DSNew, DTTM, RQDT
FROM dbo.Feb
INNER JOIN DMSEFL
ON ACTR = DSNew
where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20))
If I run the above query I get zero recs back.

If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')

Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.

Thank you,
Trudye

View 4 Replies View Related

Comparing And Rounding User Input Variables To Table Values?

Oct 24, 2006

HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!

View 9 Replies View Related

Comparing Values In Two Tables In Order To Do Insert -- Comparision Is Not Working!!

Jul 23, 2005

I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB

View 1 Replies View Related

Year (date) Totals Comparing To Column Values In Same Table?

Jan 31, 2014

how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,

create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')

[code]....

i would like to get 4 columns output

how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,

MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key

so the request is

need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below

1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'

for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as

insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')

for ex 2)

insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')

for ex 4) 2012 records there is not match acctno and name in 2013 recods

insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')

for ex 5) 2013 records there is no match of name and acct no with 2012 records

insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')

Expected Results (just for format)

AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877

View 9 Replies View Related

SQL Server 2008 :: Comparing Integer Values Across Tables And Database Servers?

Mar 6, 2015

how best to approach a problem involving two tables across two different servers.

Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING

Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH

What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.

1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?

2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.

View 0 Replies View Related

Generate Values Between A Starting And Stopping Point (time Values).

Apr 23, 2007

[RS 2005]

Given the starting and stopping points (time values), how do I generate values between these points.

For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.

From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.

So how do I accomplish this? In SQL or in the RS?

The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).

//Håkan

View 7 Replies View Related

Updating Several Values At The Same Time

Nov 16, 2007

Hi

I'm new to SQL and have a problem with the following script:-

INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')

The above script works OK on a one-off basis but....

Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).

Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?

I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.

Thanks for your help.

Jon

View 6 Replies View Related

Parsing And Formatting Time Values

Aug 17, 2007

Hello, I have an issue where I need to format the way my times are stored in my table to the way I can compare those times with the current time given by:
select right(convert(varchar,getdate(),100),7)
this provides a result like: 2:10PM
But my times are stored like: 2:10 P.M.
so, Im thinking i need to format the saved time in order to compare it to the current time so I can do the following select:
select * where startTime > (
select right(convert(varchar,getdate(),100),7)
)

Thanks a lot for your help!

View 4 Replies View Related