Need Time Dimension For Datawarehouse

Hi All,

I Need a time dimension record for my datawarehouse. does any one have it? actually just like DimTime table in AdventureWorksDW database, but i need more previous year and future year. Please help me.

Sincerely,

Yugi

Hello! Maybe can this blog post be of help if you are using SQL Server 2005:

http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

HTH

Thomas Ivarsson

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right
|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

sql

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

Need stored procedure that shows one value for null and another for not null

I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:

**************************************************************

@.WorkOrderNum numeric(18,0)

,@.StackNum numeric(18,0)

AS

--This procedure is used in iFIX. It looks for matching

--Work Order Number / Stack Number combination.

SELECT

work_order_no

,stack_no

FROM

dbo.prod_data

WHERE

(dbo.prod_data.work_order_no = @.WorkOrderNum)

AND (dbo.prod_data.stack_no = @.StackNum)

******************************************************************************

What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.

Any help would be greatly appreciated

You could do below:

Code Snippet

select case when exists(

SELECT *

FROM

dbo.prod_data as p

WHERE

p.work_order_no = @.WorkOrderNum

AND p.stack_no = @.StackNum
) then 2 else 1 end as matched

|||

Thanks it worked perfectly

Need SQL that will return all records which include a particular v

If I understand you, your table is of OrderItems... One record for each line
item on every order... If that's the case, then the query would look like th
is
Select Distinct OrderID
From OrderItems I
Where Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'A')
And Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'B')
or...
Select Distinct A.OrderID
From OrderItems A
Join OrderItems B
On B.OrderID = A.OrderID
Where A.Item = 'A'
And B.Item = 'B'
"Larry Woods" wrote:

> I have a situation where I have multiple records, let's say ORDERS, and I
> have a record for each line item included in an order. Now, I want to fin
d
> all ORDERS that includes item A and item G, for example. An ORDER could
> include many additional items but the ORDER #'s that I want returns must
> include AT LEAST item A and G.
> How do I do this?
> TIA,
> Larry Woods
>
>Thanks. I'm going with the join for now. But, here is the next question:
The output of the SELECT is a list of OrderID's. Assuming we have
'customerID' in the Order table AND we have 'customerState' in the Customer
table, how do I "expand" the selection to only give me customers from
California (value="CA"), for example?
Customer Table Order Table
orderID <<<< (SELECT
OrderID...etc.)
customerID <<<<<< customerID (foreign key)
customerState
Again, TIA,
Larry Woods
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:02D90644-CC28-4B8E-9AEF-41E6C30EB034@.microsoft.com...
> If I understand you, your table is of OrderItems... One record for each
line
> item on every order... If that's the case, then the query would look like
this
> Select Distinct OrderID
> From OrderItems I
> Where Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'A')
> And Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'B')
> or...
> Select Distinct A.OrderID
> From OrderItems A
> Join OrderItems B
> On B.OrderID = A.OrderID
> Where A.Item = 'A'
> And B.Item = 'B'
>
> "Larry Woods" wrote:
>
I
find|||On Sat, 12 Mar 2005 04:51:46 -0700, Larry Woods wrote:

>Thanks. I'm going with the join for now. But, here is the next question:
>The output of the SELECT is a list of OrderID's. Assuming we have
>'customerID' in the Order table AND we have 'customerState' in the Customer
>table, how do I "expand" the selection to only give me customers from
>California (value="CA"), for example?
>Customer Table Order Table
> orderID <<<< (SELECT
>OrderID...etc.)
> customerID <<<<<< customerID (foreign key)
> customerState
>Again, TIA,
Hi Larry,
SELECT o.OrderID
FROM Orders AS o
INNER JOIN Customers AS c
ON c.CustomerID = o.CustomerID
INNER JOIN (SELECT OrderID
FROM OrderItems
WHERE Item IN ('A', 'B')
GROUP BY OrderID
HAVING COUNT(*) = 2) AS oi
ON oi.OrderID = o.OrderID
WHERE c.CustomerState = 'CA'
(untested - see www.aspfaq.com/5006 for the steps required to get a
tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Need SQL Support

Dear friends

I am a new developer. I need some SQL Language examples to write some quaries.

Ex.

how you collect last record that relevent for the last record updated for a relevent employee in a record table which includes RecordNo and EmployeeNo, salses

Thanks

Any help is greatly apprciated

Amila

Hi,

e.g.

SELECT TOP 1
RecordNo, EmployeeNo, salses
From SomeTable
WHERE EmployeeNo = 'SomeValue'
ORDER BY TheColumnWheretheSalesDateisStored DESC

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Need some suggestion

I have a stored procedure to retrieve records from a database, each record contains the following fields: merchant_id, shop_id, shop_sales_amount

each merchant can have several shops and the corresponding shop_sales_amount

the format of the report(detail section) is like this:

merchant1 $10000
merchant1-shop1 $5000
merchant1-shop2 $5000
merchant2 $1000
merchant2-shop1 $500
merchant2-shop2 $500
merchant3 $100
merchant3-shop1 $50
merchant3-shop2 $50
.....

merchant1 is group 1, merchant 2 is group 2, merchant 3 is group 3...etc, the amount following the merchant name is the total sales amount of each shop under the merchant, the ordering of the group is in descending order of this total sales amount...

It is quite complicated, seriously need some suggestions or hints, I am quite new to crystal reports. Hope someone could help me out, thanksSo you should create two Crystal report groups, Merchant (#1) and Shop (#2), an order on descending sales amount beneath these (using the record sort expert). Insert the sum of the sales amount grouped at the shop level (Insert Summary, field to summarize: shop_sales_amount; calculate this summary: Sum; summary location: group 2) and move from the group2 footer to the header.

Viola! (if I understand you correctly.)sql

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

Need some help with a Trigger

I created an instead of insert trigger which checks to see if the "key" of
the inserted record already exists. If it does, it copies the existing
record to another table, deletes it and inserts the new one. The problem I
get is when the insert statement coming into the trigger looks like the one
below I don't get my idx returned.
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
here's the trigger:
ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
INSTEAD OF INSERT AS
declare @.count int, @.comp int
declare @.resft int, @.data float, @.alf datetime
select @.resft = inserted.resft, @.data = data, @.alf = inserted.alf from
inserted;
select @.comp = idx from research where idx in (select research from resft
where idx = @.resft)
select @.count = count(data)from resdata where resft = @.resft and data =
@.data
if (@.count > 0) /* This record already exists so we don't want it added*/
return; // Don't know what to put here
else
begin
/* Create the same record in the history table */
insert into resdatah (resft, alf, data, ohm) select resft, alf, data,
getdate() from resdata where resft = @.resft
/* Delete the existing record from the this (resdata) table */
delete from resdata where resft = @.resft
/* Insert the new record */
insert into resdata (resft, data, alf) select resft, data, alf from
inserted
end"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
Please move this into the SQL Server 2005 newsgroups...
http://www.aspfaq.com/sql2005/show.asp?id=1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I'm actually using 2000 and 2005 (for testing). The trigger needs to work in
2000.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#mcqNYcGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Please move this into the SQL Server 2005 newsgroups...
> http://www.aspfaq.com/sql2005/show.asp?id=1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
> I'm actually using 2000 and 2005 (for testing). The trigger needs to work
in
> 2000.
The EXECUTE AS syntax is new for 2005 so you're going to have some
problems there...
Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
should be used rather than SCOPE_IDENTITY() -- since the insert is being
done in the scope of the trigger, not in the scope of the initial INSERT
statement, @.@.IDENTITY should return the correct value. Second, you could
SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Your trigger will fail if more than one row is inserted. Never write
triggers like that.
When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
useful within a trigger itself because a trigger should always be able
to handle multiple row inserts.
EXECUTE AS CALLER isn't supported in SQL2000.
Please post DDL and sample data INSERTs if you need more help.
David Portas
SQL Server MVP
--|||ok so how do I set the value of idx for the inserted return?
idx = @.idx // if record already existed
and
idx = select_scope_identity() if the trigger inserted the row?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#7Qr6ecGFHA.552@.TK2MSFTNGP12.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
work
> in
> The EXECUTE AS syntax is new for 2005 so you're going to have some
> problems there...
> Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
> should be used rather than SCOPE_IDENTITY() -- since the insert is being
> done in the scope of the trigger, not in the scope of the initial INSERT
> statement, @.@.IDENTITY should return the correct value. Second, you could
> SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||This table is a read-only table except for a single user. The inserts are
done like this:
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
The goal is to check and see if there is a row with resdata =
inserted.resdata. If so, the existing record needs to be copied to another
table and the new one inserted.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109177795.995989.220250@.f14g2000cwb.googlegroups.com...
> Your trigger will fail if more than one row is inserted. Never write
> triggers like that.
> When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
> inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
> useful within a trigger itself because a trigger should always be able
> to handle multiple row inserts.
> EXECUTE AS CALLER isn't supported in SQL2000.
> Please post DDL and sample data INSERTs if you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
suggested but anyway IDENTITY should never be the only key of a table
therefore you can use an alternate key to retrieve the inserted
IDENTITY:
INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
SET @.id =
(SELECT id_col
FROM x
WHERE key_col = @.key_col)
David Portas
SQL Server MVP
--|||I'm . Given this insert statement (which is being generated from a
SqlDataAdapter):
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
how do I reference column idx to set the value in both cases? 1 - when I
want to set it to an existing idx; 2- when I want to set it to the new idx?
In case 2 I can use the @.@.IDENTITY to get the identity of the column but how
do I assign it so it's returned back. Maybe I'm with the way the
SqlDataAdapter gets the values back.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109179471.440296.167900@.f14g2000cwb.googlegroups.com...
> I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
> suggested but anyway IDENTITY should never be the only key of a table
> therefore you can use an alternate key to retrieve the inserted
> IDENTITY:
> INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
> SET @.id =
> (SELECT id_col
> FROM x
> WHERE key_col = @.key_col)
> --
> David Portas
> SQL Server MVP
> --
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u9rGUwcGFHA.332@.TK2MSFTNGP10.phx.gbl...
> This table is a read-only table except for a single user. The inserts are
> done like this:
> insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
> scope_identity() as idx
I could be wrong, but it looks to me like that is two separate statements:
1) insert into resdata(resdata, data, alf) values(@.p1, @.p2, @.p3)
2) select scope_identity() as idx

> The goal is to check and see if there is a row with resdata =
> inserted.resdata. If so, the existing record needs to be copied to another
> table and the new one inserted.
I think what your trigger needs to do is something like:
--Backup Matched Rows
INSERT INTO backuptable
SELECT * FROM maintable INNER JOIN inserted ON
maintable.resdata=inserted.resdata
--Delete Matched Rows
DELETE From maintable
WHERE EXISTS(
SELECT * from mainTable INNER JOIN inserted
ON maintable.resdata = inserted.resdata)
--Add All Rows
INSERT Into maintable
SELECT * from Inserted
Good Luck,
Jim

Need some help with a query

Hello everyone, I have a table that is setup to record a page of web
form elements. The form elements are dynamically created. Each page
contains x number of questions. (x depends on many different things
There are 4 pages. Since we never know how many form elements are on
the page, the DB was design as such
GroupID int
PageID int
QuestionID int
FormElementID int
FormElementValue varchar(500)
The problem is when trying to grab a combination of forms to display in
a report I have to write a query like this (let's assume I am trying to
grab all records that have $5/10 years) On page 1, question 4, the
form element 1 is the dollar amount and the form element 2 is the
number of years.
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
This works fine. My problem is when I have to grab all other
combinations. So let's say my client wants $5/10 years, $10/15 years,
$15/20 years and all other combinations as four distinct numbers. How
can I accomplish this? Currently I am doing this (which I know is poor
as it just looks wrong and takes forever to run.
SELECT GroupID FROM thistable WHERE GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=15)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=20)
Let me know if you have any ideas, thanks for your help in advance
(please note that I wrote the script above based on my real script, I
can't display the exact real code, but above is very close.night_day (night_day_8@.yahoo.com) writes:
> This works fine. My problem is when I have to grab all other
> combinations. So let's say my client wants $5/10 years, $10/15 years,
> $15/20 years and all other combinations as four distinct numbers. How
> can I accomplish this? Currently I am doing this (which I know is poor
> as it just looks wrong and takes forever to run.
> SELECT GroupID FROM thistable WHERE GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=10)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=15)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=20)
You should be able to sort this out, if you learn to master EXISTS/NOT
EXISTS. I show example with your first query to get you going:
SELECT t1.GroupID
FROM thistable t1
WHERE t1.PageID=1
and t1.QuestionID=4
AND t1.FormElementID=1
AND t1.FormElementValue=5
AND EXISTS (SELECT *
from thistable t2
WHERE t2.PageID=1
AND t2.Question_ID=4
AND t2.FormElementID=2
AND t2.FormElementValue=10
AND t1.GroupID = t2.GroupID)
The point here is that you may not need multiple subqueries, but could
then use OR conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you for your response. I've spend some time converting my
original query to use EXISTS, I can now generate the same output using
a version running NOT EXISTS as my current NOT IN statements.
Unfortunately, I've run the 2 queries in query analyzer and the NOT
EXISTS statements takes 12 seconds whereas my current NOT IN statement
takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
My query looks like so
SELECT d.GroupID
FROM thistable d
WHERE d.PageID=1
and d.QuestionID=4
AND d.FormElementID=1
AND d.FormElementValue=5
and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)|||night_day (night_day_8@.yahoo.com) writes:
> Thank you for your response. I've spend some time converting my
> original query to use EXISTS, I can now generate the same output using
> a version running NOT EXISTS as my current NOT IN statements.
> Unfortunately, I've run the 2 queries in query analyzer and the NOT
> EXISTS statements takes 12 seconds whereas my current NOT IN statement
> takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
> My query looks like so
> SELECT d.GroupID
> FROM thistable d
> WHERE d.PageID=1
> and d.QuestionID=4
> AND d.FormElementID=1
> AND d.FormElementValue=5
> and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
> t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
> AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
> t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
> t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)
Eh, the query is not exactly trivial to understand. I feel quite
bewildered. It would help if you posted:
o CREATE TABLE statement for the table.
o The output of sp_helpindex for the table.
o Some indication of number of rows and distribution.
It could also be interesting to see the query plans. You can this
by surrounding the query in SET SHOWPLAN_ALL ON. (This will not execute
the query.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need most recent record from views.

I'm working with a report that uses three views. There are duplicate records because the 'priority' which comes from one view has changed and SELECT DISTINCT sees it as a separate record. The users only want the latest record with the changed 'priority'. A second view contains an audit datetime stamp and a third view contains additional fields needed. Is it possible to get the MAX datetime from the second view, thereby getting the latest 'priority' from the linked views? I've tried to SELECT MAX(audit_datetime) and also coded it in the WHERE clause but SQL does not like that. I assume it's because there are a number of fields in the SELECT.could you please send us the query in order to check the code, it should be working as you say, but maybe the code has a syntax error.

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2