I would propose the following workaround ...

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

I want to select all rows between 10 and 20 .......

I want to select all rows between 10 and 20 from a
view

how can I do this?

which database are you using?

Oracle has rownum or something i'm not sure about name which you can use directly.

but you need to justify the criteria for records to be return. does your table/view contain ID field, if yes then think of situation say where my 15th record is deleted?

in general what i would do is create a temporary with required columns and 1 identity column. then i'll insert records in this table.

after inserting i'll simple run query like select fields from temp_table where id in @.num1 and @.num2.

thanks,

satish.

|||

is it ok?

(MS SQL)

SELECT ProductID, ProductName FROM Products WHERE UnitsInStock BETWEEN '0' AND '35'

this example is from MSDN

hope it helps thanks

|||

if you are asking about MS SQL

I think it may be

select *

from a

where rows between '10' and '20'

thanks and hope it helps

I want to add two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatinate two text fields?
Thanks
RajuNo, there is no easy way. Why would you want to do this? Is your SQL
statement too long when you list both columns? This seems like a weird
requirement to me. Usually you use separate TEXT columns because you need
them separate. Otherwise, most applications could have just used a single
text column in the first place.
If you have less than 8000 characters in each column, you could use a view,
and say:
SELECT
..,
ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
+ CONVERT(VARCHAR(8000), textCol2)
FROM
..
Then, you don't have to change the underlying table, or any of the
procedures / apps that insert/update the data. (Or, better yet, if you
don't need TEXT, change the columns to VARCHAR.)
Otherwise, the best way would probably be to add the 3rd column, use an
external app to loop through, concatenate, and update the new column, and
then delete the first two columns. (An external app will be much more
friendly with joining the two values than SQL Server will be internally.)
A
"Raju" <npraju1@.hotmail.com> wrote in message
news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 +
> text
> column 2.
> Is there an easy way to concatinate two text fields?
> Thanks
> Raju
>|||Aaron,
Thank you for your response.
Actually we have legacy application, which has summary in one text column
and description in one text column.
Our new application has only one column, now my job is to get those two
columns into one new column in our new application.
Hope this helps.
BTW
Unfortunately I can't use your solution as both the columns have huge data,
more than 8000 bytes.
Thanks
Raju
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWjN6eCAGHA.1256@.TK2MSFTNGP15.phx.gbl...
> No, there is no easy way. Why would you want to do this? Is your SQL
> statement too long when you list both columns? This seems like a weird
> requirement to me. Usually you use separate TEXT columns because you need
> them separate. Otherwise, most applications could have just used a single
> text column in the first place.
> If you have less than 8000 characters in each column, you could use a
view,
> and say:
> SELECT
> ...,
> ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
> + CONVERT(VARCHAR(8000), textCol2)
> FROM
> ...
> Then, you don't have to change the underlying table, or any of the
> procedures / apps that insert/update the data. (Or, better yet, if you
> don't need TEXT, change the columns to VARCHAR.)
> Otherwise, the best way would probably be to add the 3rd column, use an
> external app to loop through, concatenate, and update the new column, and
> then delete the first two columns. (An external app will be much more
> friendly with joining the two values than SQL Server will be internally.)
> A
>
> "Raju" <npraju1@.hotmail.com> wrote in message
> news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
>

I thought I posted this one but dont see it. Problem with querying a query

It is my understanding that Views cannot have parameters. Also that
stored procedures can not be queried. My problem is this:

I want to select the rows that match a certain parameter.

From that I want to select the most current 20 rows (there is a date
field).

From that I want to select the lowest 10 rows based on a numeric
field.

Finally I want that to be input to a report and some calculations.

What this basically is the selection for USGA Golf Handicap Index. It
is the most current 20 rounds of golf by a golfer, then the best 10 of
those 20 and then finally the calculation.

Any help would be appreciated.Could be something like this. Here it is in SQL92:

SELECT S.employee_id, S.date_col, S.numeric_col
FROM Something AS S
LEFT JOIN Something AS T
ON S.employee_id = 1
AND T.employee_id = 1
AND (S.date_col < T.date_col
OR (s.date_col = T.date_col
AND S.numeric_col < T.numeric_col))
GROUP BY S.employee_id, S.date_col, S.numeric_col
HAVING COUNT(T.employee_id)<10 ;

Or, if you don't mind using the proprietary TOP modifier in SQL Server:

SELECT TOP 10 WITH TIES
employee_id, date_col, numeric_col
FROM Something
WHERE employee_id = 1
ORDER BY date_col DESC, numeric_col DESC ;

--
David Portas
SQL Server MVP
--|||Do some research on "inline user defined functions". These are essentially
views with parameters.

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407151031.3b92ccd2@.posting.google.c om...
> It is my understanding that Views cannot have parameters. Also that
> stored procedures can not be queried. My problem is this:
> I want to select the rows that match a certain parameter.
> From that I want to select the most current 20 rows (there is a date
> field).
> From that I want to select the lowest 10 rows based on a numeric
> field.
> Finally I want that to be input to a report and some calculations.
> What this basically is the selection for USGA Golf Handicap Index. It
> is the most current 20 rounds of golf by a golfer, then the best 10 of
> those 20 and then finally the calculation.
> Any help would be appreciated.|||Thank you. My brother who is a dba in CA with Oracle was trying to
explain this to me but I just couldn't get it. You example allowed me
to do it both ways. I don't mind using the Top 10 with TIES but never
heard of it before.

Again thanks, makes for cleaner than temporary tables.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I need to return all the recent ImportId Data

Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns 2550 rows of data.

In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns 2500. I need help retirenving the entire liste with the most recent OrbitimportId.

Help!

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetAttribute.AssetDescription, dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.ESNTracking.ReportTime, dbo.ESNTracking.CurrLocStreet, dbo.ESNTracking.CurrLocCity, dbo.ESNTracking.CurrLocState,
dbo.ESNTracking.CurrLocZip, dbo.ESNTracking.CurrLocCounty, dbo.ESN.EsnNumber, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.MapUrl,
dbo.InventoryOrigin.WarehouseDescription
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId LEFT OUTER JOIN
dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportId
WHERE (dbo.ESNTracking.OrbitImportId =
(SELECT MAX(OrbitImportId) AS Expr1
FROM dbo.ESNTracking AS ESNTracking_1))
ORDER BY dbo.AssetType.Description

danjaman:

Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns2550rows of data.

In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns2500. I need help retirenving the entire liste with the most recent OrbitimportId.

Did you mean the query missed 50(2550-2500) rows?

I need to find total for rows with a certain value: how?

I have a table that contains a column that collects discreet data (1 for on
and 0 for off, just those two values) and a date column which is a timestamp
containing a date and time. There are other columns in this table but they
are unimportant in this discussion.
A process inserts rows into this table about every second or so, depending
on the state of an object.
It has been requested of me that I prepare a report. Among other items,
this report must show the total time an object is turned on. In other
words, for every row that is set to 1, I need to add up the time and let the
users know how long that item was set to on for a given period of time, say
24 hours i.e. how long was that object in an on state during the last 24
hours.
I consider myself to be pretty handy at SQL but I must confess I'm at a loss
to come up with a query for this.
Any ideas and examples would be greatly appreciated?
Thanks in advance,
Chris SmithOh I neglected one important piece...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||jeez, typo... And this produces total Millliseconds, Change the ms inside
the datediff function to whatever you want it to produce if milliseconds is
not what you want:
sec, minute, hour, day, etc...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTimeMs
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LogDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||I assume that the table also contains an ObjecID - to identofy WHICH Object
is being Tuened On Or OFF...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of JOIN to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the immediate NEXT
time it entered the OFF state. Likewise, if it is the end time that is
stored, I need to find the immediate PREVIOUS time it left the OFF
state.
Another thing you will have to consider, though this has to do with
data integrity, how do u ensure no overlaps occur in the records (such
that your data says at a given point in time, the object was both ON
and OFF)? Do you allow time gaps in which you can't tell whether the
object was ON or OFF? if you don't how do u ensure there are no gaps?
If you do, how do you interpret the time where there are no records?
Please clarify...|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of join to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the NEXT time it
entered the OFF state. Likewise, if it is the end time that is stored,
I need to find the PREVIOUS time it left the OFF state.
Another thing you will have to consider, though this has to do with
integrity, how do u ensure no overlaps occur in the records? Do you
allow time gaps in which you can't tell whether the object was ON or
OFF? if you don't how do u ensure there are no gaps? If you do, how do
you interpret the time where there are no records?
Please clarify...|||Hi Sienko,
I'll try to answer your questions as best as I can.
First of all, it should be noted this isn't a typical database. This is
actually a database accessed through MS SQL 2000 called InSQL. InSQL allows
you to store real time data more efficiently than SQL Server while allowing
for more transactions per second and storing the data in a manner that
allows for smaller files on the harddrive than what SQL Server itself would
allow. InSQL itself is actually made up of extension tables to SQL Server
2000. Along with this, InSQL provides other things such as providing a
discreete data type which allows you to store values that tells you if a
device is on or off where 1=on/open and 0=off/close. This probably isn't
important for you to know but it might be. For the most part, T-SQL is
still valid so any solution you can help me find should still work.
A typical row will include a column for a tagname, a discreete value column
(contains a value of 1 or 0, that is all as far as I know of), and a
timestamp which is just a datetime column (this column is equal to the time
the discreete value was retrieved from a device, not the time the row was
created). There are other columns but they aren't important to this
discussion as far as I know of. An example row might look like:
tagname_here -- discreete value -- datetime
There are rows inserted for each tag about every second or so. We'll say a
row is inserted each second to keep this simple. The rows will be the same,
just the fact the discreete value is different along with the time. For
example, a valve maybe open at the time a sensor takes a reading so this
data is retrieved (the state of the valve along with the datetime the
reading was taken) and inserted into a table (discreetehistory is the name
of the table I think, we'll use that for the sake of this conversation). As
long as the valve is open, a row will be inserted into this table where that
row will have a 1 in the discreete column and the datetime that value was
taken. When the valve is closed, rows will be inserted into the table where
the discreete value is now 0 and it has a datetime with it. Again, as long
as the valve is closed, rows will be inserted into the table with a
discreete value of 0 along with it's datetime. In other words, we have a
process that is looking at equipment 24/7/365. This process determines the
state of devices and inserts rows into a table every second or so.
Everytime a row is written, that row represents the state of the device and
a datetime is stored in that row to let us know when the sensor reading was
taken.
I don't think the table will allow null values nor do I believe a null value
will ever occur but am not certain. There is a transition period between
opening and closing valves. After all, a valve doesn't instantly open or
close. Depending on the size of size valve, it might take 1 to 5 seconds to
switch states. But, I don't believe this transition state is being
recorded. I wish I was at work answering this. I could tell you for sure
then.
Again, the problem is I need a query where I can report how long a valve was
left open in the last two hours. In other words, for a time from 12:00PM to
2:00 PM, the valve might be open at first but close at 12:30. Then, the
valve might open at 12:40. Then, the valve might be close at 1:00PM and
then open again at 1:10 PM. In other words, the valve was opened and
closeded multiple times during this two hour block. In total, the valve was
open 1hr and 40 minutes during this two hour block with the valve being
closed for 20 minutes. I need a query that will read this table and
everytime the valve is open which is represented with a discreete value of 1
(where 1 = open), then it will sum up all the times and report this to me.
I thought I was pretty good with T-SQL but I've never done anything like
this before when it comes to time. I must admit I don't have the slightest
idea how to proceed with this one.
Any thoughts would be greatly appreciated. If you need more information,
please let me know.
Thanks for trying to help me out. I apologize for taking a while to respond
to you but I was busy and simply forgot about this.
Thanks again!
Chris Smith
"sienko" <sienko@.gmail.com> wrote in message
news:1112174062.024672.131620@.g14g2000cwa.googlegroups.com...
> It might be helpful for you to provide sample data, especially about
> this timestamp field. When a row is inserted with the on/off flag set
> to ON, what value is in the timestamp column? The way I see it, there
> are 3 options: -
> 1. The time the object entered the ON state only
> 2. The time the object left the ON state only
> 3. Both the time the objected entered the ON state + the time it left
> or the duration it was in this state.
> The 3rd option is easy because all you need to do is sum the duration
> (or calculate it from the Left - Entered Time and sum) for each state
> in a given time period.
> The 1st and 2nd options are a bit tough since you have to form a sort
> of JOIN to find the related state from which the object toggled from or
> to. e.g. if I store the time the object entered the ON state, to find
> out how long it stayed in this state, I need to find the immediate NEXT
> time it entered the OFF state. Likewise, if it is the end time that is
> stored, I need to find the immediate PREVIOUS time it left the OFF
> state.
> Another thing you will have to consider, though this has to do with
> data integrity, how do u ensure no overlaps occur in the records (such
> that your data says at a given point in time, the object was both ON
> and OFF)? Do you allow time gaps in which you can't tell whether the
> object was ON or OFF? if you don't how do u ensure there are no gaps?
> If you do, how do you interpret the time where there are no records?
> Please clarify...
>|||You usually model time as durations, so you would have
CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
. );|||On Sat, 2 Apr 2005 12:49:01 -0500, someone wrote:
-- Crosspost to non-existant group removed --
(snip description)
>I thought I was pretty good with T-SQL but I've never done anything like
>this before when it comes to time. I must admit I don't have the slightest
>idea how to proceed with this one.
>Any thoughts would be greatly appreciated. If you need more information,
>please let me know.
Hi Chris,
I think the best way to approach this problem is to start with a view to
reduce the load of imported data to just the relevant things: periods
without status change, with beginning and ending datetime.
CREATE VIEW Periods
AS
SELECT start.Status,
start.TheDatetime AS StartDT,
MAX(sameperiod.TheDatetime) AS EndDT
FROM RawData AS start
INNER JOIN RawData AS sameperiod
ON sameperiod.Status = start.Status
AND sameperiod.TheDatetime >= start.TheDatetime
AND NOT EXISTS
(SELECT *
FROM RawDate AS beetween -- deliberate misspelling: reserved word
WHERE beetween.TheDatetime > start.TheDatetime
AND beetween.TheDatetime < sameperiod.TheDatetime
AND beetween.Status <> start.Status)
WHERE NOT EXISTS
(SELECT *
FROM RawDate AS previous
WHERE previous.TheDatetime =
(SELECT MAX(TheDatetime)
FROM RawData
WHERE TheDatetime < start.TheDatetime)
AND previous.Status = start.Status)
GROUP BY start.Status, start.TheDatetime
With this query, your report becomes easy:
DECLARE @.StartReport smalldatetime
DECLARE @.EndReport smalldatetime
SET @.StartReport = '2005-04-02T12:00:00'
SET @.EndReport = '2005-04-02T14:00:00'
SELECT SUM(DATEDIFF(minute,
CASE WHEN StartDT < @.StartReport
THEN @.StartReport ELSE StartDT END,
CASE WHEN EndDT < @.EndReport
THEN @.EndReport ELSE EndDT END))
FROM Periods
WHERE StartDT < @.EndReport
END EndDT > @.BeginReport
I was not able to test the query and view above, since you have not
posted the CREATE TABLE and INSERT statements needed to create a test
database on my server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The thing is I'm not creating a table with multiple columns for dates. A
table is already present with one datecolumn only. I need to find all rows
within a timespan that have a value equal to 1 in the discreete value
column. Once I find all the rows, I need to add them all somehow such that
it will tell me the total time all the rows were set to a 1 in this column.
Any ideas on that?
Thanks!
Chris Smith
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112464694.412138.243320@.o13g2000cwo.googlegroups.com...
> You usually model time as durations, so you would have
> CREATE TABLE Events
> (event_id CHAR(10) NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means current
> .. );
>

I need to find the rows that exist in one table but not in the other with condition

I need to find the rows that exist in one table but not in the other
with this condition:

(prod_name exist in table1 and not in table2.prod_name ) AND

(prod_name exist in table1 and not in table2.'S'+prod_name )

explanation:
i want to know if the product not exit and if the combination of the
charachter "S" with the product Name also not exist at the other
table

B.R
yuviSELECT prod_name
FROM table1 as A
WHERE NOT EXISTS
(select * from table2 as B
where A.prod_name = B.prod_name)
AND NOT EXISTS
(select * from table2 as C
where A.prod_name = 'S' + C.prod_name)

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 01:28:26 -0700, yuval <yuvalbra@.gmail.comwrote:

Quote:

Originally Posted by

>I need to find the rows that exist in one table but not in the other
>with this condition:
>
>(prod_name exist in table1 and not in table2.prod_name ) AND
>
>
>(prod_name exist in table1 and not in table2.'S'+prod_name )
>
>
>explanation:
>i want to know if the product not exit and if the combination of the
>charachter "S" with the product Name also not exist at the other
>table
>
>
>B.R
>yuvi

|||On Wed, 20 Jun 2007 01:28:26 -0700, yuval wrote:

Quote:

Originally Posted by

>I need to find the rows that exist in one table but not in the other
>with this condition:
>
>(prod_name exist in table1 and not in table2.prod_name ) AND
>
>
>(prod_name exist in table1 and not in table2.'S'+prod_name )
>
>
>explanation:
>i want to know if the product not exit and if the combination of the
>charachter "S" with the product Name also not exist at the other
>table

Hi yuvi,

Some alternatives for the solution posted by Roy. Try to see if they run
faster or slower on your system.

SELECT prod_name
FROM table1 AS a
WHERE NOT EXISTS
(SELECT *
FROM table2 AS b
WHERE a.prod_name IN (b.prod_name, 'S' + b.prod_name));

(Only SQL2005)

SELECT prod_name
FROM table1
EXCEPT
SELECT prod_name
FROM table2
EXCEPT
SELECT 'S' + prod_name
FROM table2;

SELECT prod_name
FROM table1
EXCEPT
(SELECT prod_name
FROM table2
UNION ALL
SELECT 'S' + prod_name
FROM table2);

(All the queries above are untested - see www.aspfaq.com/5006 if you
prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis