Determining A Range Of Values

Mar 29, 2012

suppose you have a large table with 2 columns

create table tick
(
ID bigint identity (1,1) primary key not null
, price money not null
)

and I want to know 3 things

Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10

So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.

I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.

View 2 Replies


ADVERTISEMENT

Determining Null Values In Columns

Sep 7, 2004

How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:


int count = (int)row["Count"];

and

int count = row["Count"] as int;


the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?

View 6 Replies View Related

Determining Default Values Automatically

Oct 4, 2007

I am building code to look into the database and create "CRUD" stored procedure scripts automatically for each table.
I am in the finishing stages as my procedures take into consideration all necessary parameters and keys wheter they are natural or identity. It also takes into consideration User Defined Types.

I am working on the section which defines the parameters and their default values. ie.
CREATE PROCEDURE [dbo].[TransactionHistoryInsertOne]
(
@ProductID Int = 0,
@ReferenceOrderID Int = 0,
@ReferenceOrderLineID Int = 0,
@TransactionDate DateTime = '1/1/1900', <-- Possibly arbitrary
@TransactionType nchar(1) = '?' <---- How to get this.
@Quantity Int = 0,
@ActualCost Money = 0,
@ModifiedDate DateTime = '1/1/1900'
)
AS...

I have three problems remaining.
1. Using the AdventureWorks database as a data model, I run into the TransactionType field in the TransactionHistory table, a non-nullable nchar(1). How do I determine and use any default values that may be defined for this object?
2. Do UDTs have default values already defined?
3. How do I make sure I am not arbitrarily setting the default values?

Here i my select statement into the system tables:

SELECT C.*, T.name as xtypename, U.name as xutypename, S.*, so.*
FROM syscolumns C

left join systypes T on (C.xtype = T.xtype) and (T.xtype = T.xusertype)
left join systypes U on C.xusertype = U.xusertype
left join sys.all_columns S on (C.id = S.object_id) and (C.name = S.name)
left join sysobjects so on S.default_object_id = so.id
WHERE (C.id = [TableId])";


Thanks for any help

View 1 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

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

Jan 13, 2015

I've got some records like this:

ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0

where each month field has a 0 or 1, depending on if the person was enrolled that month.

I'm being asked to generate a table like this:

ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014

Is there some slam dunk way to do this without a bunch of If/Then statements?

The editor compressed all my space fields, so the column headers are off in some places.

View 8 Replies View Related

Getting Values In A Particular Range

Jul 26, 2007

hi!
i have one datatable with date and some more fields are there in that. i want to query to return values from a particular date to another date. how can i do this? please help me!
thanks in advance!

View 1 Replies View Related

Query To Get Range Of Values Missing

Mar 7, 2008

I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.

For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!

StartStop
---------
20452296
26173775
568936948
3727084237
84409178779
179013179995
180278259121
259292306409
307617366511

View 6 Replies View Related

Use Stuff To Define Range Of Values?

Nov 13, 2014

I have this query that calculates the next possible shipping day, based on 3 conditions:

- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and

[code]...

So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.

DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))

View 2 Replies View Related

How To Define A Range Of Values For A Field?

Oct 24, 2007

Hi everyone,

Primary platform is sql25k running with sp2.


I'd like to define for a concrete field which only could have three values:
"a","b","c"

TIA

View 7 Replies View Related

How Can I Find Values Outside Of The Smalldatetime Range?

Mar 25, 2008

I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.

I have tried to find the values which are out of the smalldatetime range, with the following query


SELECT *

FROM midmar

WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'




Which doesn't quite work, and gives me values that are actually between those two values listed.

I have also tried having the WHERE clause read:

WHERE bday <'06/06/2079' AND

bday>'01/01/1900'


and that doesn't really work either.

What's going on? Is there likely another problem besides the structuring of my queries?

View 9 Replies View Related

T-SQL (SS2K8) :: Creating Range Between Values In Query?

Apr 26, 2015

On a new project i need to create possible ranges between a specific interval.

suppose i have this main product:

main product : LY E67F

Also, in first level i have a table classify by Group depending on Intensity.

table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800

I need to create these diferent options:

1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.

I fact, i need to find all available options possibles to build article code, like a matrix.

View 7 Replies View Related

Setting The Range Of Values For SSRS Parameters

May 8, 2007

Hoping someone may be able to help with a problem I'm having with SSRS parameters....

My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.

Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.

The second parameter is to appear as a drop-down of Departments to which the User has access.

The report is to be produced for the selected Department.

Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?

Any help is much appreciated.

View 3 Replies View Related

Specified Argument Was Out Of The Range Of Valid Values. Parameter Name: Index

May 21, 2007

This is the error I'm getting. I will paste my code below:









"
DeleteCommand="DELETE FROM [Friends] WHERE [FriendName] = @FriendName"
SelectCommand="SELECT * FROM [Friends] WHERE (([FriendName] = @FriendName))"
UpdateCommand="UPDATE [Friends] SET [UserName] = @UserName, [UserID] = @UserID, [IP] = @IP, [AddedOn] = @AddedOn, [FriendName] = @FriendName, [IsApproved] = @IsApproved WHERE [FriendID] = @FriendID">














Type="String" />



DataSourceID="request_source" DefaultMode="Edit" EmptyDataText="You have no pending friend requests"
GridLines="None" Height="50px" Width="125px">




ReadOnly="True" SortExpression="UserName" />



Text="Accept" /> 
CommandName="Delete" Text="Deny" />



Text="Edit" /> 
CommandName="Delete" Text="Delete" />



ReadOnly="True" SortExpression="FriendID" Visible="False" />









'>


'>

View 10 Replies View Related

Can I Set A Default Range For The Identity Values For Merge Replication?

May 8, 2006

Hello,

We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.

View 1 Replies View Related

Attempting To Select A Range Of Values Using Wildcard And Between Or Logical Operators?

Mar 16, 2014

I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -

Code:
declare @strBin varchar(10)
set @strBin = ''
if @strBin = ''
set @strBin = '#%'

declare @endBin varchar(10)
if @endBin = ''
set @endBin = 'z%'

My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -

Code:
where BINLOCAT.BINLABEL between @strBin and @endBin
order by BINLOCAT.BINLABEL

Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.

View 9 Replies View Related

SQL Server 2012 :: Query That Generates Values By Date Range

Dec 12, 2014

I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.

SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL

[Code] ....

Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000

And so on….

View 4 Replies View Related

Filtering Values In ResultSet By Setting Range On Complex Index

Aug 13, 2007

Hi!
I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value.
I tried to use such code:
...
command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null);
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
....
But it doesn't work. As a result i've got an empty result set.
Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions.
Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.

Thanx

View 1 Replies View Related

Reporting Services :: Chart - Group Values Depending On Date Range?

May 20, 2015

Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code

SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType

[Code] ....

The second dataset then has the date code changed to 

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'

The third

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'

The fourth

AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'

Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.

SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType

[Code] ....

If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.

View 8 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

Select Rows Where Value In A Range And Below A Range?

Nov 26, 2014

I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.

UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';

UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);

error message

(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.

View 5 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Determining If A Server Is 32 Bit Vs 64

Jun 11, 2007



Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. I need to know this to pick the correct upgrade version.

-Kyle

View 1 Replies View Related

Determining If A Server Is 32 Bit Vs 64

Jun 11, 2007

Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out.

-Kyle

View 4 Replies View Related

Determining What To Input As Server

Jun 9, 2006

I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon

View 2 Replies View Related

Determining Database Size

Oct 31, 2001

Is there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.

View 1 Replies View Related

Determining If A Table Exists

May 2, 2000

How do I find out if a temporary table named '##test' exists? I have a stored
procedure that creates this table and if it exists another stored procedure
should do one thing, if it does not exist I want the SP to do something else.
Any help as to how I can determine if this table exists at the current time
would be greatly appreciated.

Thanks in advance for you help,
Jon

View 6 Replies View Related

Determining DB Type/Character Set

Feb 26, 2002

Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!

View 1 Replies View Related

Determining Last DBCC Procedure

Mar 7, 1999

Hi,
Is it possible to determine when last a DBCC statement was run on a database & what that DBCC was.

TIA
Shaun Tinline
Database Administrator
Alcatel Altech Telecoms

View 1 Replies View Related

Determining Unique Children

Nov 17, 2005

Hi,

I need to be able to identify if a parent has the same child names as another parent.

Have a look at my data (in pic)

Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

So you would get something like:

Bob | 2
Bill | 1
Glen | 2

thanks,
david.

View 7 Replies View Related

Determining Database Size

May 9, 2008

Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.

My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.

I'd appreciate any insights.

Kamen

View 3 Replies View Related

Determining If SQL Statement Is A Query

Jul 27, 2007

Hi,

I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.

There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):

try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}

Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.

My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?

Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)

Thanks for your time, all ideas appreciated

Jim

View 1 Replies View Related

Determining What Columns Are All Null

Jul 6, 2006

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved