Excellent Permanent Job Based In Glasgow Scotland.

Mar 23, 2001

I have an outstanding opportunity for a talented Senior Support Analyst. You must have Transact-SQL, Sybase or Oracle, Visual Basic, with Powerbuilder or C being "nice to haves".
You will be working with our existing developers and provide 2nd level support. You will also work alongside our business analysts and so you will need to understand the business area.
Basic salary of �28,000 gbp(sterling) to �38,000 gbp (sterling) depending on skill level and experience.
Please note in this instance you must posses a current and valid UK (United Kingdom) work permit or be a UK national.
We offer exceptional career development for the successful candidate.
Contact me now...Steve@geordielad.demon.co.uk

emails only at this stage to save non specific phone calls.

View 1 Replies

Does 7.0 Create Permanent Indexes On Its Own??

Oct 29, 1999

Hello,

A few months ago, the company I work for switched to SQL 7.0 from 6.5. All is running fine but recently I noticed that we have a ton of indexes on each table that start like this: _WA_SYS_....0B679CE2 (here are some specific examples: _WA_SYS_PRODUCT_LINE_0B679CE2, _WA_SYS_RMA_DISPOSITION_0B679CE2)

Did SQL 7.0 do this? If so why? What is it trying to do? Why doesn't it use current indexes (clustered and non-clustered)? Why don't they get Fragmented like User defined indexes? I have a lot of questions associated with this if someone can explain it.

Tech Net, MSDN, BOL and other manual resources don't mention anything about it. At least I haven't found anything on it and I have spent some time looking.

Please help if you can.
Troy

View 2 Replies View Related

Is Possible To Create A New Permanent Table By A Procedure?

Mar 23, 2007

Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.
create procedure new_forum

@user_id int,
@title varchar(50),
@description varchar(200)

as
begin

declare @totalForuns int

select @totalForuns = count(*) from foruns

set @totalForuns = @totalForuns + 1

Insert into foruns (title,creation,country,[views],[description])
      values (@title,getdate(),'England',0,@description)

CREATE TABLE [dbo].[@totalForuns](
      [thread_id] [int] IDENTITY(1,1) NOT NULL,
      [user_id] [int] NOT NULL,
      [last_user_id] [int] NOT NULL,
      [title] [nvarchar](50) NOT NULL,
      [creation] [datetime] NOT NULL,
      [last_answer] [datetime] NOT NULL,
      [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers] DEFAULT ((0)),
      [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views] DEFAULT ((0)),
      [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed] DEFAULT ((0)),
      [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos] DEFAULT (''),
CONSTRAINT [PK_threads] PRIMARY KEY CLUSTERED
(
      [@totalForuns] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[@totalForuns] WITH CHECK ADD CONSTRAINT [FK_threads_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[@totalForuns] CHECK CONSTRAINT [FK_threads_users]

end
Go

Thank you very much.

View 5 Replies View Related

Create Permanent Association Between Two Tables

May 17, 2015

I've two tables in SQL Server 2014, in database named cnc

I wish to create a permanent association between the two tables, with referential integrity. I cannot find, in all the books I've gone through, nor the websites I've visited, how to do this.

Table 1:
name: dbo.p
fields in table 1:
pID autoenumerating, indexed
p_nm_l varchar(25)
p_nm_f varchar(25)

Table 2:
name: dbo.pe
fields in table 2:
pe_ID BigInt
pe_ssn1_
pe_ssn2_
pe_ssn3_
pe_dobm_
pe_dobd_
pe_doby_

I wish to associate - with a one to one - pID with pe_ID with pID as the primary field. I've no clue how to do this.

View 3 Replies View Related

ASP Authentication With SQL Server 2005 - Not Express, Permanent Change

May 8, 2006

Hi all,
I've read up on the fact that if you use SQL server 2005 instead of SQL express you need to change the connection string in web.config.
Is there anyway to make this default connection type, or do I have to do it for every website I create. i.e. is there a global file that this connection can be entered into. Then whenever I start a new website I don't have to put the string in.
Just me being lazy really. But it's nice to know if it's possible.

Many thanks
DrB2k2

View 2 Replies View Related

Updating A Permanent Table From A Dataset Within The Data Flow

Feb 14, 2007

I have a dataset that was created via a source + lookups + derived columns.

I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.

In sql this is what I am trying to do:

SELECT COUNT(*) AS Count_of_Employees, DEPT
FROM Employees
GROUP BY DEPT

UPDATE Departments
SET Number_of_Employees = Count_of_Employees
WHERE Dept = dept.

View 1 Replies View Related

How To Transform An .MDF Database Into A Permanent Database In Sql Server Express?

Apr 10, 2007

Hi, i have a MDF file used by an asp.net application (with sql server 2005 express).
I want to make it to a permanent database in sql server express.
I know that i can attach it from Management Studio, but is there no better way?
Does it exist a way to 'import' or 'migrate' or copy' a .MDF.database into the central sql server, as if created directly in sql server express? After that, i can delete the .MDF file.
Thanks
Tartuffe

View 3 Replies View Related

Copying Temp Table Data To Permanent Table

Nov 23, 2007

Hello guys..

Can u plz help me by giving me an idea how i can copy the temp table data to permanent table

Thanks,
sohails

View 1 Replies View Related

Global Temp Table Vs. Permanent Table Use

Dec 17, 2004

I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.

Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?

Your suggestions?

View 1 Replies View Related

ODBC-based To OLE DB-based Data Transfer

Oct 7, 2006

I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

View 1 Replies View Related

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

Feb 15, 2007

Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;

DBPROP rgProps[1];
DBPROPSET PropSet[1];

rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}

View 6 Replies View Related

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?

Oct 18, 2007

Hi!

I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:

Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?

Thanks!

View 3 Replies View Related

How To Think In Set Based SQL

Oct 31, 2006

Hi,

Yesterday I asked for help in what I thought was a complicated query (Want to avoid cursors ... need help with query, but obviously wasn't (evidenced by the speed in which my question was answered and a comment made by another poster in the thread).

I have figured out how the query works. I even figured out how to modify it so that the date diff was displayed on the first row instead of the second:

SELECT t1.clt_num, t1.autbeg_dte, t1.autend_dte,
isnull(datediff(day, t1.autend_dte, t2.autbeg_dte), 0)
FROM #testit t1 left join #testit t2
ont1.clt_num= t2.clt_num
andt2.autend_dte= (select min(autend_dte) from #testit x where x.clt_num = t2.clt_num and x.autend_dte > t1.autend_dte)
order by t1.clt_num, t1.autbeg_dte

What I would like to know (if possible) is what “thought process�? was used to come up with this solution or do you just “intuit�? it (I hope not, because I sure don’t).

I know this is a very vague question, and maybe if I get a few responses I may be able to refine it.

Also can anybody recommend any books or web pages that teach this sort of thing (not syntax … I don’t have a problem with that).

Thanks,

Laurie

View 18 Replies View Related

More Help In Set Based Sql ...

Jan 8, 2007

Hi,

I have a problem that I can solve in a procedural way but I would rather solve with set based and I am having a hard time wrapping my mind around it.

First I will try an English description of problem:

The tables involved are (data insert statements are at end of post) … The ProgramData table:

create table #ProgramData
(clt_num int,
pgm_num int,
beg_dte datetime)

Following business rules listed below this table will be used to determine updates and inserts into the CafasData table:

create table #cafasData
(clt_num int,
log_on varchar(10))

pgm_num are associeated with log_on values in a one to many relationship defined in this lookup table:

create table #cafas_log_on_lookup
(pgm_num int,
log_on varchar(10))

There can be multiple rows for one client in the ProgramData table but only one row for clients in the CafasData table. As a client can be in two or more programs that match to different log_ons at the same time a weight has been assigned each log_on and the log_on with the greater weight is used in the CafasData Table. DDL for weight table:

create table #CafasLogOnWeights
(Log_on varchar(10),
weight int)

Programs have begin and end dates (end dates for this problem will always be in the future so I did not include them in the ddl).

The basic rules for assigning a log_on to a client is:
1.Once in a log_on it will not change until after the program has ended. Regardless if there is another current log_on with more weight.
2.Always look at the minimum beg_dte that has an end date in the future (assume all test data does have an end date in the future).
3.If two or more programs start on the same date and none of them are for the current log_on then change the log_on to the one with the greatest weight (see #CafasLogOnWeights table below).

Ok ... If that is completely confusing I will try some pseudo procedural code for explaining what needs to be done:

For a given clt_num:
Get all rows in the ProgramData table with a minimum beg_dte
If one row then
If clt_num in CafasData then
Update CafasData with log_on =
associated log_on for given pgm_num
Else
Create new row in CafasData with log_on =
associated log_on for given pgm_num
End if
Else
If clt_num in CafasData then
If one of the pgm_nums translates to the current log_on value then
Do nothing
Else
Pick log_on with 'heaviest' weight
End if
Else
Create new row in CafasData with log_on with 'heaviest' weight
End if
End if
End for

Sample data for the four tables defined above:

insert into #cafasData (clt_num, log_on)
select 1, 'SB' union all
select 2, 'Intake' union all
select 3, 'HB' union all
select 5, 'Wrap' union all
select 6, 'SB' union all
select 7, 'Intake'

insert into #cafas_log_on_lookup (pgm_num, log_on)
SELECT 1220, 'SB' UNION ALL
SELECT 1222, 'Wrap' UNION ALL
SELECT 1223, 'Wrap' UNION ALL
SELECT 1228, 'HB' UNION ALL
SELECT 1229, 'HB' UNION ALL
SELECT 1233, 'Intake' UNION ALL
SELECT 1234, 'Other' UNION ALL
SELECT 1238, 'Other' UNION ALL
SELECT 1239, 'Intake'

insert into #CafasLogOnWeights (log_on, weight)
SELECT 'HB', 5 UNION ALL
SELECT 'Wrap', 4 UNION ALL
SELECT 'SB', 3 UNION ALL
SELECT 'Intake', 2 UNION ALL
SELECT 'Other', 1

insert into #ProgramData (clt_num,pgm_num,beg_dte)
Select 1, 1222, '11/30/2006' union all
select 1, 1220, '11/30/2006' union all
select 2, 1222, '11/30/2006' union all
select 2, 1220, '11/30/2006' union all
select 3, 1222, '11/30/2006' union all
select 3, 1220, '11/30/2006' union all
select 4, 1222, '11/30/2006' union all
select 4, 1220, '11/30/2006' union all
select 5, 1222, '11/30/2006' union all
select 5, 1220, '11/30/2006' union all
select 6, 1234, '10/30/2006' union all
select 6, 1222, '11/30/2006' union all
select 6, 1220, '11/30/2006' union all
select 7, 1234, '10/30/2006' union all
select 7, 1222, '11/30/2006' union all
select 7, 1220, '11/30/2006' union all
select 8, 1234, '10/30/2006' union all
select 8, 1222, '11/30/2006' union all
select 8, 1220, '11/30/2006' union all
select 9, 1223, '1/15/2008' union all
select 10, 1222, '1/1/2007' union all
select 10, 1223, '1/1/2007'

Expected Output (if I didn't make any mistakes or typos):

Inserted Rows
Clt_numlog_on
4Wrap
8Intake
10Wrap

Changed Rows
Clt_numlog_on
2Wrap
3Wrap
5Wrap
6Intake

No Change
Clt_numlog_on
1SB
7Intake

clt_num = 9 is a trick row as the begin date is in the future and there fore should have no affect on the CafasData table

Please let me know if this does not make any sense and give me the opportunity to clarify any points as I really do need to solve this problem.

Thanks in advance for any help

Laurie

P.S. I will be leaving work at 4:30 est. but will check back first thing in the morning. Please do not think I am ignoring any responses.

View 6 Replies View Related

What Is Set Based Sql

Nov 29, 2007

Dear all,

What is set based sql any body explain briefly.

Thanks&Regards,

Msrs

View 1 Replies View Related

Web Based Admin

Jun 25, 2006

Does anyone know of a web based front end to Microsoft SQL Server 2005 Express edition? I tried using the SQL Server 2000 server web admin tool from microsoft, but it will not allow me to install it because it says it need the 1.1... framework installed. I have 1.0,1.1,and 2.0 installed, is there anyway to circumvent this? Or has anyone else figured another way to get the webadmin tool to work with 2005 express edition.
Thanks!

View 6 Replies View Related

Web Based Admin MS SQL

Dec 1, 2005

Is there a web based admin to administer MS SQL? If there is a free one, that would be better.

View 2 Replies View Related

SQL Server Based On GMT?

Oct 19, 2001

Is there a way to get SQL Server to be based on GMT (Greenwich MeanTime)? If so, would there be no impact of the fallback time change on 28Oct01? We're planning to stop SQL Server for 1 hour during the time change period so the transaction and error logs do not have duplicate times or events and transactions out of sequence. Is this common practice?
Thanks

View 1 Replies View Related

Calculate Age Based On Dob

Aug 26, 2004

hi all;

I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals datetime.

when I run the following query:

SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup

All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into.
I have already created a CurrentDate column which has a (getdate()) value attached to it.

This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.

I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).

View 14 Replies View Related

Set Based Soultion

Jul 8, 2004

Yeah..my brain hurts...they're actually making me work....

It's running now, but there's got to be a set based solution

DECLARE myCursor99 CURSOR FOR
SELECT [ID], JOBNAME FROM PSI_JobFlow ORDER BY [ID]

DECLARE @ID int, @JOBNAME varchar(20), @SAVE_JOBNAME varchar(20)

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @ID, @JOBNAME

WHILE @@FETCH_STATUS = 0
BEGIN
IF @JOBNAME IS NOT NULL AND @JOBNAME <> @SAVE_JOBNAME
BEGIN
SELECT @SAVE_JOBNAME = @JOBNAME
END
IF @JOBNAME IS NULL
BEGIN
UPDATE PSI_JobFlow_New SET JOBNAME = @SAVE_JOBNAME WHERE [ID] = @ID
END
END

CLOSE myCursor99
DEALLOCATE myCursor99

View 14 Replies View Related

Need A Trigger Based On A Row

Feb 18, 2005

Hello, everyone

I need to create a trigger to update a FLAG column by row modification. The table likes,

Col1FLAG
a0
b0
c0
d0
e0

If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.

Anyhelp will be approciated.

Thanks a lot

ZYT

View 4 Replies View Related

Set-based Solution

Mar 15, 2006

Thought I'd got my head round using a set-based approach but my brain's gone dead on this one

CREATE TABLE #mytable (SWID INT , T INT , DateA DATETIME , DateB DATETIME)

INSERT #mytable (swid , t , DateA , DateB)

SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 , CAST('31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST('31-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST( '31-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006' AS DATETIME), CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) ,CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME), CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 , CAST('31-Dec-2005'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Dec-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST('30-Nov-2005'AS DATETIME) , CAST( '01-Aug-2006' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '30-Nov-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
select * from #mytable order by SWID desc, DateA desc

The Columns where T values are 1 and 0 are OK having already been derived. I need to UPDATE the remaining rows from the Default T Value of 9999
to Decrementing values (starting at -1) commencing at the highest remaining (ie non 9999 T Value) DateA value and working 'backwards'
'grouping' on SWID

The DateB value is irrelevant for this purpose

The desired output is below with derived T values

SWIDTDateADateB
6396712006-03-31 00:00:00.0002006-08-01 00:00:00.000
6396712006-03-31 00:00:00.0002007-02-01 00:00:00.000
6396702006-03-15 00:00:00.0002006-08-01 00:00:00.000
6396702006-03-15 00:00:00.0002007-02-01 00:00:00.000
63967-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
63967-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
63967-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
63967-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
1005112006-03-31 00:00:00.0002006-08-01 00:00:00.000
1005112006-03-31 00:00:00.0002007-02-01 00:00:00.000
1005102006-03-15 00:00:00.0002006-08-01 00:00:00.000
1005102006-03-15 00:00:00.0002007-02-01 00:00:00.000
10051-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
10051-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
10051-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
10051-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
10051-32005-12-31 00:00:00.0002006-08-01 00:00:00.000
10051-32005-12-31 00:00:00.0002007-02-01 00:00:00.000
10051-42005-11-30 00:00:00.0002006-08-01 00:00:00.000
10051-42005-11-30 00:00:00.0002007-02-01 00:00:00.000

Thanks in advance

View 3 Replies View Related

Set Based Solution For ....

Jan 8, 2004

CREATE TABLE [Cube_fact_table] (
[ISIN_ID] [bigint] NOT NULL ,
[CLIMSTID] [bigint] NOT NULL ,
[HOLDID] [bigint] NOT NULL ,
[Quantity] [bigint] NULL ,
[Holding] [numeric](15, 0) NOT NULL ,
[Cost] [numeric](18, 3) NOT NULL ,
[Close_Price] [numeric](18, 3) NOT NULL ,
[Tran_Value] [bigint] NULL ,
[Date] [datetime] NULL ,
[UCOA] [bigint] NULL ,
[COA] [bigint] NULL
) ON [PRIMARY]
GO

INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '1', '2000', 2000, 170.100, 170.100, '340200', '11/28/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '3', '-1000', 1000, 185.500, 185.500, '-185500', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '5', '48600', 48600, 225.000, 225.000, '10935000', '11/27/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '8', '-48575', 25, 243.000, 243.000, '-11803725', '12/4/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '9', '12575', 12600, 254.000, 254.000, '3194050', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '10', '-12120', 480, 232.000, 232.000, '-2811840', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '11', '12120', 12600, 219.000, 219.000, '2654280', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '12', '-12120', 480, 265.000, 265.000, '-3211800', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '13', '4261', 4741, 224.000, 224.000, '954464', '12/8/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '14', '9059', 13800, 223.000, 223.000, '2020157', '12/11/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '15', '10200', 24000, 233.000, 233.000, '2376600', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '16', '-12188', 11812, 243.000, 243.000, '-2961684', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '17', '12188', 24000, 234.000, 234.000, '2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '18', '-12188', 11812, 234.000, 234.000, '-2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '19', '788', 12600, 254.000, 254.000, '200152', '12/16/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '20', '8000', 20600, 223.000, 223.000, '1784000', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '21', '-13754', 6846, 231.000, 231.000, '-3177174', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '22', '10554', 17400, 245.000, 245.000, '2585730', '12/19/2003 12:00:00 AM', NULL, NULL)

Now what i want to do is this ...

UCOA is to be updated with the average of all previous transactions where the climstid and isin_id are the same.

example

take case where holdid(transaction no) in the sample data is 14.
What i need is a query which will sum up the data in the tran_value column upto holdid 5.

Again I need a set based solution. Gurus .. please help

View 3 Replies View Related

Selecting Based On The Value Of A SUM

Apr 21, 2008

Okay, let's see if I can explain this one. I am summing multiple lines of data from a labor detail table, by status. Using this query

SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1

I get these results...

LastnameWBS1WBS2LongnameHELDLABORTBWrittenOffLaborWRITTENOFFLABOR
Boulet0001039.000100S.r. 41 & Del Prado Shopping Center/miscellaneous civil engineering18408.6309923.47
Boulet0001039.000102S.r. 41 & Del Prado Shopping Center/rezoning process008790
Boulet0001039.000106S. R. 41 & Del Prado Shopping center / const plan rev for environ planting2200.6800
Boulet0001039.000107S.r. 41 & Del Prado Shopping Center/cpd rezoning9335.4600

Okay, so now, of coarse, I want to change everything. I only want to return rows if there is a value > 0 in either Held Labor or TBWrittenOffLabor. Otherwise, no row return.

Here's what I tried, but it didn't work out because it still returns rows, it just zero's out the values for written off labor.

SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor,
SUM(CASE WHEN LD.HELD > '0' THEN LD.WrittenOff ELSE '0' END) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1

View 7 Replies View Related

Getting Top 5 Based On The Count?

Aug 19, 2013

I am using the following query

select top(5) COUNT(incident),name from company_info group by name
order by COUNT(incident) desc ;

to get most repeated incidents.

countincident
17ghi
15def
14abc
11z
9x

I want to get the same output with out using order by query.

View 4 Replies View Related

Update Using Max Value Based On ID

May 6, 2015

doc_id --- version --- activate_doc
d1 --- 1 --- N
d1 --- 2 --- N
d1 --- 3 --- N
d2 --- 1 ---N
d2 --- 3 ---N
d2 --- 4 ---N
d3 --- 1 ---N
d3 --- 2 ---N

I want to update "activate_doc=Y" where the version having Max value based on the doc_id.

expected results:
d1 --- 3 --- Y
d2 --- 4 ---Y
d3 --- 2 ---Y

View 3 Replies View Related

Set Based Solution

Jun 28, 2006

How can I get a all the Tickets Information related to one chain

If you see TicketID = 9 This has been replaced by TicketID 1571,
If you see TicketID = 12 This has been replaced three times.

If I pass 1574 to a SP , I need the whole chain ( i.e 1574 , 1573 , 1572 , 12 ) .
Is there a SET Based Solution for this

TicketID OldTicketID CancelReplaceStatus
----------- ----------- -------------------
2 NULL NULL
3 NULL NULL
4 NULL NULL
5 NULL NULL
6 NULL NULL
7 NULL NULL

9 NULL R
1571 9 NULL

10 NULL NULL
11 NULL NULL

12 NULL R
1572 12 R
1573 1572 R
1574 1573 NULL

Thx in Advance
Venu

View 2 Replies View Related

Can A Set Based Approach Help Me Here...

Feb 1, 2007

im practicing set based approaches... and what im trying to do is grab each value from a table , scramble it and put it back in the table... i dont want the solution to this as id rather figure it out myself for practice...

the thing im stuck at is i can do this with a cursor but i want to avoid cursors in future, how would i use a set based approach to get each value of a table and work with it?

View 4 Replies View Related

Based On Id, How To Know Table Name

Dec 4, 2007

Dear All,
based on objectid column in sysindexes, how to know the table name?

select object_name(id) as table_name, rows from sysindexes
where indid<2 order by rows desc

actually i'm trying to replace the query
select count(*) from mytable

with
select object_name(id) as table_name, rows from sysindexes
where indid<2
query

please guide me

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 6 Replies View Related

Best Way For A Set-based Query

Jul 23, 2005

I have a database where each row has a set of keywords separated bycommas eg .ROW 1 ID,<other columns>,"green,hat,man,boat"ROW 2 ID,<other columns>,"green,boat,seaside"ROW 3 ID,<other columns>,"red,hat"I want users to be able to find similar records to the one they arelooking it (this is art BTW). ie if they are looking at row 1 andclick "Find Similar Works of Art" I want to do a query where I findrecords that have a commonality of keywords and obviously the onesthat match the most keywords should be shown first. The keywords arecurrently just stored comma-separated in a single column but I am notattached to this. Nor are they guaranteed to come in any order (iethey are not alpha sorted) but they could be.Number of records is around 15000 on a hosted server.Any tips for best query/algorithm/data storage to achieve this? orreferences to useful sources?Thanks,RB

View 3 Replies View Related

Cursor Based SQL?

Jul 20, 2005

Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.

View 7 Replies View Related

Set Based Code

Dec 20, 2007

Hi,
I have created a SP that uses a cursor. But, I would like to know how can I change my SP not to use Cursor.
Below is my SP:

Code Block
Create procedure [dbo].[sp_table_size]
as
-- Create cursor to select all tables
DECLARE tbl_space CURSOR
FOR
SELECT table_name FROM INFORMATION_SCHEMA.Tables;
--Truncate table to load the table sizes
truncate table table_size;
-- Open Cursor
OPEN tbl_space
DECLARE @tbl_name sysname
FETCH NEXT FROM tbl_space INTO @tbl_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- execute sys_procedure to find size and insert into table
insert into table_size
EXEC sp_spaceused @tbl_name
FETCH NEXT FROM tbl_space INTO @tbl_name
END
CLOSE tbl_space
-- close cursor
DEALLOCATE tbl_space
-- select rows from the inserted table
SELECT * FROM TABLE_SIZE
ORDER BY TABLE_NAME

View 2 Replies View Related

Datetime-Based Operations

Apr 26, 2007

I'm new to SQL Server and relatively new to database design and I have a specific problem I'm trying to resolve. I have a collection of records in a table (let's call them 'tasks') which represent some list of things that needs to be completed. Each task has an associated datetime on which this action is to commence, and my applications is responsible for executing these tasks. My first instinct is to regularly poll the 'tasks' table to determine if there are any tasks which have not been processed and are past their schedule start datetime. But, I'm wondering if there is some sort of database feature that would recognize that a task's starttime has arrived, and could somehow communicate this to my application without my application having to constantly poll the database. Thanks.

View 5 Replies View Related