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

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

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

XML BASED STORED PROCEDURES

Jul 11, 2007

Hello friends.Can u please guide me to the links or other info about how to write xml based stored procedures.Your help will be very much appreciated.Thank you.gurjeetsaini@gmail.com

View 2 Replies View Related

How To Get Data Based On Value Of Another Sqldatasource

Feb 17, 2008

1st of all i'm using 3.5 and c#.
On my page I have 2 sqldatasource controls. sds1 and sds2
In my select statement of sds2 how do I make a select parameter based on a value or column I return from sds1? And please don't tell me to join my sds2 select in sds1, I don't want to do that. I need two seperate sds.

View 4 Replies View Related

Time Based Trigger

Feb 16, 2005

can i make a trigger to fire based on given date and time?

View 2 Replies View Related

Server Based Fuction

Mar 18, 2004

Hello All,

Let me fitst say that I have found this forum to be VERY good and the members have an excelent knowledgebase.

I am very impressed and appreciate all of the help.

I have a table in my SQL database that I need to modify a little and which contains a lot of data.

My question is in wondering if it is possible to write a function that would loop through all of the rows in a particular column field and update the values?

What I mean is that I have a few columns (fields) in that table that hold numeric values like (1, 2, 3, 4, 5, .......) and I need to loop through all of the entries in that table to add 10000 to each one and store the results back into that same table.

Example is to replace 1 >> 10001, 2 >> 10002, 3 >> 10003, etc.....

Could some one please tall me how this can be done and what I need to do?

Thanks,
Lonnie

View 6 Replies View Related

Filtering Based On Time Of Day

Feb 14, 2005

While using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day?

The easiest way I've found of doing so is :

Code:

SELECT *
FROM MyTable
WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00'))

To me that seems a bit complicated... but it works...

Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well)

-MBirchmeier

View 5 Replies View Related

Parameter Based DTS Transformation

Oct 22, 2004

How can I implement this logic in my DTS package?

IF GlobalVariable1 = '10/21/2004' THEN
Do Transformation1 (parameter1)
ELSE
Do Transformation2 (parameter1,parameter2)
END IF

View 3 Replies View Related

Where Based On Length Of Field.

Oct 10, 2005

Hey all.

I'm looking around for a solution on how to pull records back based on legnth.

So, if field A is more then 2 characters, return that row.

Thanks
Caden

View 2 Replies View Related

Web Based SQL Table Editor

Jun 21, 2004

Is anyone aware of a tool that will allow users to update the values of a sql table via a web page? What I am looking for is a tool that when given the name of a table will generate a web front end to allow editing of the table.

Thanks,
Todd

View 2 Replies View Related

Status On Set Based Operations?

Aug 26, 2004

Every time I mention cursors, I hear a lot of people pointing out how you shouldn't use cursors and how set based operations are much faster.

However, procedural code offers the big advantage that I can embed status/progress type commands into a cursor loop and get accurate data on progress and ETA.

If I execute a single huge UPDATE/INSERT, I am at the whim and mercy of SQL Server. Seemingly simple statements often run for 50 hours+ and I have no clue how far along it is or anything remotely close to an ETA. Often, that is absolutely inacceptable to have mission criticial hardware to be churning through a process for DAYS without the slightest idea if it is actually making progress or not.

Is there any way to get progress/status on such an UPDATE/INSERT command?

View 11 Replies View Related