How to make this query...

Hi all. I have tried making this query for a while and i didn't manage to.

I know it is something related to JOIN, but i didn't manage to make it work :\

I have two tables.

Table1- db_stockprices

Table2- db_stockSymbols

db_stockprices is getting updated every day with stock daily prices.

db_stockprices looks like:

ID, SymbolID, Price,Date

1 1 33.21 1/1/2007

2 1 33.14 1/2/2007

3 1 34.21 1/3/2007

4 2 11.42 1/1/2007

5 2 11.73 1/2/2007

6 3 18.32 1/1/2007

7 3 19.11 1/2/2007

8 3 18.41 1/3/2007

9 4 52.44 1/1/2007

and so on for all the stocks.

ID is Primary Key

SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information.

What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID.

Example:

If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted.

Is this possible within 1 SQL Query?

I would really appreciate a good query example for this example..

I'm away from a SQL Server computer and i did not test the following but try it on a test table:

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@.yourdatavalue)

hth

|||Hi ggciubuc

This wont do it right.. This query will delete the whole table.

Code Snippet

select Symbolid from db_stockprices where Date<>@.yourdatavalue

this will select all symbolIDs that does not have @.yourdatevalue which is most cases is the whole SynbolIDs..

Am i correct?

|||

No, let's examine the select I proposed :

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@.yourdatavalue)

let's say

"select Symbolid from db_stockprices where Date<>'1/3/2007' " return "2" and "4" so then select for deleting will be transformed in

delete from db_stockprices where symbolid in ("2","4")

so will be deleted all rows you desired.

So, this solution I think will work.

|||

I see what you mean.

I thought by writing "select Symbolid from db_stockprices where Date<>'1/3/2007' "

it will also return SymbolID "1" and "3" because "1" and "3" contains dates which are different from "1/3/2007" (the 1/2/2007 and the 1/1/2007)

ID, SymbolID, Price,Date

1 1 33.21 1/1/2007

2 1 33.14 1/2/2007

3 1 34.21 1/3/2007

4 2 11.42 1/1/2007

5 2 11.73 1/2/2007

6 3 18.32 1/1/2007

7 3 19.11 1/2/2007

8 3 18.41 1/3/2007

9 4 52.44 1/1/2007

|||

Hi Folks,

Unfortunately, the answer Gigi gave won't work. The problem is that

select Symbolid from db_stockprices where Date<>@.yourdatavalue

will return the symbols from all rows whose dates don't match your date. I.e., if a stock has a price row on a date other than your date, then it will be returned. The only symbols that won't be returned will be those that only had a price for the date entered.

You can build up a query that will do what you want. There may be other, better ways to do this, but here's one way to think through the problem. Start by selecting all symbols that do have a trade on the date you want:

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

If @.date is 1/3/2007, this will return the symbols 1 and 3. It may seem counterintutive to do this, but now you can find all symbols that are not in this list:

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

)

Again, with @.date = 1/3/2007, this will return symbols 2 and 4. Now you can build the delete:

DELETE FROM db_symbols WHERE SymbolID IN (

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

)

)

Hope this helps!

-Isaac

|||

Yes Isaac but to have a full answer for this post raindm have to associate DELETE CASCADE option to the relationship between db_stockSymbols and db_stockprices; in this way all rows from db_stockprices will be deleted and deleteing operation can be made in 1 SQL.

hth

|||

Gigi,

Good pointI missed that from the original post.

Cheers,

-Isaac

|||

Hi, now it makes more sense

About the " DELETE CASCADE " I do it from SQL Server Management (2005) ? If so where exactly?

|||

I found where to change the "Delete Cascade"

In db_stockprices i select SymbolID (its a forien key) and press relationship. I have a relationship i already created -"FK_db_stockprices_db_stockSymbols" . there i have INSERT and UPDATE specifications.. I see the options inside "INSERT and UPDATE specifications.." and i see DELETE RULE and there is "CASCADE". Is this correct? does it effect each other no matter from what table i delete a row?

I understand I can use "Update rule" so every update to the SymbolID in table1 will affect table2 and the opposite?

|||When you delete a row in table1 that supply foreign key for table2 the correspondent rows in table2 will be deleted (DELETE CASCADE) when modify the key in the table1 the values for foreign keys in table2 will be modified too (UPDATE CASCADE).|||

Okey,

thanks a lot you guys.

How to make DB2 Connection String Dynamic, Password Problem

Hi All,

The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?

Thanks,

Manoj Kumar

Try setting ProtectionLevel to "SaveSensitiveWithPassword".

If you are using a configuration file to set the connection string, you can edit the .dtsconfig file directly to add the password into the connection string, but you should make sure the config file is stored in a secure location if you do this.

|||

I am using configuration setting and its based on (XML File and Table) XML Files points the configuration Database and Table stores the all configuration information so If I have to append the password configuration Table entry need to be changed but this is not required I am dealing with some sensitive data so they dont want the password of that db user stored somewhere exposed, some of the few questions related to this which I wanted to ask are as under.

1)If I Save password in Package then is there anywayout to bypass the password part from connection setting means package take password from the saved location not search in connection string. (user on different production databses is the same so mostly the dynamic part will be the Database only)

2)Is there anywayout to encrypt that password in configuration table entries.(Some users have access to the DB which holds the configuration table but they dont have access to production server)

if someone knows some other wayout to deal with this situation except the solution earlier provided.

Thanks and Regards

Manoj Kumar

sql

How to link details tables to list fields

Hi,
I am trying to create a report with a list for each row, which also contains
tables of related data (1 to many relationship).
There are various drill-down techniques described in the documentation, but
I have been unable to determine how to pass a field from the primary record
as a parameter to the queries for the sub data.
Eg. Have tables Employees and Sales. Sales is related to employees by
employeeID field in the Sales table. I want to show a single employee details
in list, then multiplae sals records in a table embedded in the list.
I need to fgigure out how to pass the current employee.ID field to the table
query as a paremeter.
Any help on this would be appreciated (even if I have to get this working
using sub reports)
Thanks,
...Derek
--
DerekYou have two options for doing this, either use nested data regions or use
subreports:
First here's how to use nested data regions. Write one dataset query to
return employee and sales information. This will likely be a join sql query.
Place a list data region on your screen in Layout view (list or table will do
- these data regions can contain other data regions). >Inside< the list data
region place a table data region. Both list and table must be bound to the
same dataset query you created. Put employee fields in the list data region
outside the table. Put sales fields inside the table. That's it.
A second option is to use subreports. First create a report with sales
information. The report should take employeeid as a parameter. save the
report. Now create the main report with a data region that displays employee
information. This report lists all employees and should have in its dataset
query an employeeid field. There is no parameter used. Inside the data region
with employee information, place a subreport control. Go to the properties of
the subreport. On the General tab of the Properties dialog there is a textbox
where you can specify your subreport. This is the report you first created
and saved. In the same dialog box there is a tab where you specify
parameters. select your employeeid parameter and specify the employeeid field
from the main report dataset. That's it.
Both solutions will give you what you want.
HTH
Charles Kangai, MCT, MCDBA
"DerekJMiller1" wrote:
> Hi,
> I am trying to create a report with a list for each row, which also contains
> tables of related data (1 to many relationship).
> There are various drill-down techniques described in the documentation, but
> I have been unable to determine how to pass a field from the primary record
> as a parameter to the queries for the sub data.
> Eg. Have tables Employees and Sales. Sales is related to employees by
> employeeID field in the Sales table. I want to show a single employee details
> in list, then multiplae sals records in a table embedded in the list.
> I need to fgigure out how to pass the current employee.ID field to the table
> query as a paremeter.
> Any help on this would be appreciated (even if I have to get this working
> using sub reports)
> Thanks,
> ...Derek
> --
> Derek|||Hi,
For me first option is working well. Thanks...
I want to display header on each page.
How to put header titles on each page. If I put table header on top in the
list, it is repeating for all records of the list.
If I put header table outside list, it is not visible on next page.
I hope, my question is clear.
Please help
"Charles Kangai" wrote:
> You have two options for doing this, either use nested data regions or use
> subreports:
> First here's how to use nested data regions. Write one dataset query to
> return employee and sales information. This will likely be a join sql query.
> Place a list data region on your screen in Layout view (list or table will do
> - these data regions can contain other data regions). >Inside< the list data
> region place a table data region. Both list and table must be bound to the
> same dataset query you created. Put employee fields in the list data region
> outside the table. Put sales fields inside the table. That's it.
> A second option is to use subreports. First create a report with sales
> information. The report should take employeeid as a parameter. save the
> report. Now create the main report with a data region that displays employee
> information. This report lists all employees and should have in its dataset
> query an employeeid field. There is no parameter used. Inside the data region
> with employee information, place a subreport control. Go to the properties of
> the subreport. On the General tab of the Properties dialog there is a textbox
> where you can specify your subreport. This is the report you first created
> and saved. In the same dialog box there is a tab where you specify
> parameters. select your employeeid parameter and specify the employeeid field
> from the main report dataset. That's it.
> Both solutions will give you what you want.
> HTH
> Charles Kangai, MCT, MCDBA
>
> "DerekJMiller1" wrote:
> > Hi,
> >
> > I am trying to create a report with a list for each row, which also contains
> > tables of related data (1 to many relationship).
> >
> > There are various drill-down techniques described in the documentation, but
> > I have been unable to determine how to pass a field from the primary record
> > as a parameter to the queries for the sub data.
> >
> > Eg. Have tables Employees and Sales. Sales is related to employees by
> > employeeID field in the Sales table. I want to show a single employee details
> > in list, then multiplae sals records in a table embedded in the list.
> >
> > I need to fgigure out how to pass the current employee.ID field to the table
> > query as a paremeter.
> >
> > Any help on this would be appreciated (even if I have to get this working
> > using sub reports)
> >
> > Thanks,
> > ...Derek
> > --
> > Derek

How to know allocation place for each object?

Hi all of you,

My current dutie is try to obtain for each table their filegroup.

I'm seeing sysobjects table but I can't see nothing related to do with

Thanks a lot for your time,

Ok, if you run this query you obtain such name but it is not enought for my goal:

sp_help <table>

|||

Not sure if this is something you're after, this will list each object with their associated filegroup, you can filter sysobjects for tables only, not pretty unfortunately but works:

SET NOCOUNT ON

DECLARE @.sqltxt varchar(4000);

DECLARE @.tblName varchar(4000);

DECLARE GetFG_Cursor CURSOR FOR

Select 'sp_objectfilegroup ' + CAST(id AS VARCHAR(4000)), name from sysobjects;

OPEN GetFG_Cursor;

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT 'Table Name: ' + @.tblName;

EXECUTE(@.sqltxt);

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

END

CLOSE GetFG_Cursor;

DEALLOCATE GetFG_Cursor;

|||

hi xrayb,

That's a good approximation. Thanks indeed.

It'll be helpful.