How To Fill Up Lacking Date Entries

Feb 10, 2004

Hi everyone,

the last years I solved tons of "problems" with plain SQL, but
I think this one will require some programming...
a stored procedure, a vb, I don't know ...

Here is the point:
I'm trying to build a simple OLAP-cube out of personal data.
I get table_person with number, name, birthdate, and so on.
I get table_costcenter with costcenternumber and description and
I get a table_person_costcenter with the following structure:

person-number, costcenter, FROM-Date (and some other data, e.g.
workinghours per week(this and headcount are the measures in the cube))

so...
Person 1 is on costcenter CC01 FROM-Date: 01.01.1998
person changed next year
1 CC02 FROM-Date: 01.01.1999
...
2 CC02 01.05.1999
2 CC03 01.01.2000
and so on....

I can think of loops reading the hole table (roundabout 2000 records),
than the one, two or more records with the person-number 1 and so on
and fill in the missing Dates (every first of a month) BETWEEN the
first FROM-Date to the next FROM-Date and so on till the last record with personnumber X which will lead to CURRENT DATE....
but I never programmed in MS SQL. :-(

So every code examples are heartly wellcome...

many thanks in advance

Michael
PS:
The result-table should look like this
person costcenter date
1 CC01 01.01.1998 january
1 CC01 01.02.1998 feb
1 CC01 01.03.1998 march
..
1 CC02 01.01.1999
1 CC02 01.02.1998
.. when no other connections "1" to costcenter ...
1 CC02 01.02.2004 (current month)

View 2 Replies


ADVERTISEMENT

How To Retrieve Only Entries With Date = NULL From The DB

Jan 9, 2008

I am trying to write a Stored proc to retrieve only records from my table tat do not have a DateTime value ie DateTime is NULL and for some reason simply adding a DateRemoved = NULL does not work. Can anyone please help me with solution to this issue please??? Thanks in Advance 

View 3 Replies View Related

Find Next Entries Value (as Sorted By Date)

Jul 29, 2015

In the database I am querying, there is a field called "group". I can find out when "group" changes with a time field that was logged whenever group changes. I am trying to find what the value was changed into.

Code:
SELECT
Assignment_Log.DBID,
Assignment_Log.Assigned_Group,
Assignment_Log.Submit_Date
FROM Assignment_Log

This will tell me:

The unique ID of the database
The group it was assigned FROM
The time the assignment took place

I need to find the assignment TO

so what I need to do for the last column is something along the lines of...

Find the next record after the current record by looking at the next Submit_Date and view the Assigned_Group.

If no "next record" can be found, leave the cell empty.

is this possible?

View 2 Replies View Related

List All Entries Made After Specified Date

Feb 17, 2014

Is it possible to find out when a value was last changed/updated? I want to list all entries made after a specified date.

View 6 Replies View Related

Stored Procedure To Fill A Date Field

Oct 27, 1998

I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.

View 2 Replies View Related

Procedure To Select Entries From Yesterdays Date

Mar 13, 2012

I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table. To execute this procedure you must enter the store number and the dates required.

What I am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.

I have tried using the following

DECLARE @dt DATETIME
SET @dt = CAST(CAST(MONTH(getdate())as varchar) + '-' +
Cast(DAY(GetDate()-1)as varchar)+ '-' +
Cast(YEAR(GetDate())as varchar)as DateTime)

set @sql = @sql + ' and hd.TXHD_START_DATE between ''''' + @dt + ''''' and ''''' + @dt + '23595999+9999'''''

But i am getting an error

Syntax error converting datetime from character string.

View 4 Replies View Related

Searching For Entries In Subsequent Log Entries?

Dec 2, 2013

If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?

I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.

------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry

[code].....

In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.

View 1 Replies View Related

Fill Dropdownlist

May 7, 2008

hi,
how can i fill dropdownlist through code not through visit.and i need to know which is fastest and easy way for web application throught this below query.
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT Emp_Name,Emp_ID FROM Employee "
da.Fill(ds, "data")
cmd.ExecuteNonQuery()
conn.Close()

View 4 Replies View Related

How To Fill A Dataset?

May 11, 2006

Can a SqlDataSource be used to fill a DataSet? If so, will you show me how it's done?

View 2 Replies View Related

Fill Factor Fix

Apr 3, 2001

Currently we have tables (in sql 6,5), many of them do not have primary keys.
While I was trying to re-index (re-org), many of them got an error:
"fillfactor 204 is not a valid percentage; fillfactor must be between 1 and 100."
(many tables' fillfactor exceed 100 or more...)
How can I fix them so I can upgrade to sql 7 ?
Thank you for your help.

View 3 Replies View Related

Fill Factor

Aug 13, 2001

I am really confused about this whole fill factor thing. The way I understand it, is if you have a table whose data remains pretty much static, you should use a higher fill factor. Suppose you had a database where you had at most 150 transactions a day that changed the data, should the fill factor be left at the default(0) or increased? How do you determine how much to increase it? Is there a rule of thumb that suggests if you have x number of changes against a table, you should have a fill factor between y and z percent?

Please Help

Chris

View 2 Replies View Related

Fill Factor

Mar 12, 2001

Hi all,
While creating indexes for a table, I specified a fill factor of 70%. I then inserted a few hundred rows into the table. Is it possible to check to what percent the pages are full after the rows have been inserted?

Thanks in advance,
Praveena

View 1 Replies View Related

Fill Factor

Jul 16, 1999

You have a db with 50,000 records and you want to add 100,000 more. What should the right fill factor be? Is there a way to "calculate" a fill factor if you don't want to use default? Any help is appreciated. Thank you.

View 3 Replies View Related

Fill Factor

Jan 18, 2005

If fill factor is specified as 100 for a table what will be the impact of this?I want to know any updation or insertion will be possible or not?

View 3 Replies View Related

How To Set Fill Factor?

Aug 27, 2007

Hellow, everyone"

I have a web online table that is inserted about 1500 record one day. Each night, a DST is running to pull all data to anther database. How to set fill factor on a one column index to get the best performance? Current fill factor is 80%.

Thanks

ZYT

View 7 Replies View Related

Fill Factor

Jul 13, 2007

Hi experts, I would like to ask regarding FILL FACTOR. I observed that our system's loading is a bit slow, and some of the modules take 1 to 2 minutes loading. Maintenance activity is regularly executed based on the scheduled sets. Then I tried to checked the tables indexes/keys turns out that the FILL FACTOR is set to ZERO(0). I would like to know if the FILL FACTOR set to zero will be a factor for the system to slow down..????


Darren Bernabe Blanco

View 2 Replies View Related

Fill Out An Order

Oct 4, 2007

How do you fill out an order form;
there is an Order(OrderID, CustomerID, Subtotal, Tax, Total), Orderdetail(OrderID, ProductID, Qty, UnitPrice, ExtendedPrice)

How do I get those tow together in the same form, which can be called order, or invoice it doesn't matter as long as I can get them in the same form numberd like order 1, then order 2, ect. is it by stored procedure, or by ado.net, or both ? or is there anywhere I can find information? like a book? or website ?

For example;

Order no. 1, Customer 3,

Product 3, Qty 2, UnitPrice $20.00, ExtendedPrice $40.00

Product 2, Qty 3, UnitPrice $10.00, ExtendedPrice $30.00

Product 4, Qty 2, UnitPrice $5.00, ExtendedPrice $10.00 ---here I can be adding as many as needed

Subtotal $80.00

Tax $5.00

Total $85.00

View 1 Replies View Related

Fill Problem

Oct 28, 2007

I have one complicated problem with SQL data files. Say that we have four data files:

F1=2000MB
F2=1000MB
F3=3000MB
F4=1500MB

We want to add 3000 MB on these files, and in the same time we want to make the files proportional one with each other:

Will do:

F1 + 500=2500MB
F2 + 1500=2500MB
F4 + 1000=2500MB

With my logic, the final result we’ll be:

F1=2500 MB
F2=2500 MB
F3=3000MB
F4=2500MB

There is an algorithm or a formula to calculate the right value to add to each F1, F2….Fn files having S1, S2….Sn sizes for a T total value to add? Thank you in advance.

P.S. The problem can be complicated when we deal with more than one filegroups and with disk space availability.

View 2 Replies View Related

Proportional Fill

Jul 23, 2005

All,SQL Server uses a proportional fill method to add data to file groups wherea file group has multiple files. In general this is a good thing.Unfortunately, the method appears to get switched off when new file(s) isadded. Other than exporting the data out of the filegroup, recreating thefilegroup and putting the data back in, is there a way to respread theexisting data back out and switching the proportional fill method back on?Thanks,Danny

View 2 Replies View Related

Fill Factor

Jul 14, 2006

How do i find out how much fill facot has been spcified in given table?.

View 1 Replies View Related

Fill Problem

Oct 28, 2007

Hi,



I have a very complicate problem with SQL data files. Say that we have four data files:



F1=2000MB

F2=1000MB

F3=3000MB

F4=1500MB



I want to add 3000 MB on these files, and in the same time I want to make the files proportional one with each other:



F1 + 500=2500MB

F2 + 1500=2500MB

F4 + 1000=2500MB



At the end I€™ll have:



F1=2500 MB

F2=2500 MB

F3=3000MB

F4=2500MB





I need and algorithm or a formula to calculate the values to add to each files F1, F2€¦.Fn files having S1, S2€¦.Sn sizes, for a T total value to add. Can anyone help me? Thank you in advance.

View 1 Replies View Related

Automatic Data Fill-up

Oct 28, 2006

Hello guys! I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition. I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).How do I accomplish this kind of dynamic filling up of fields? Thanks a lot! 

View 1 Replies View Related

I'm Tryiong To Fill One List From Another

Apr 14, 2007

I have a databound dropdownlist that returns an integer (listID)  I have a listbox that should return all the people on a membership list that aren't on the committee list with the ListID returned by the dropdownlist.  The listbos code is:<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ListID" DataValueField="ListID"></asp:ListBox> SqlDataSource2 code is:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mainshipsystemsConnectionString %>" ></asp:SqlDataSource> When the user clicks on the button for the dropdownlist to make their list selection I have the following in the code behind:Protected Sub BtnSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSelect.Click        'make visible panel containing code to move members        Dim sListID As Integer = DDLGetList.Text        SqlDataSource2.SelectCommand = "SELECT MemberID, First, Last, Prefix, Suffix FROM Mainship.CE_Members WHERE       (MemberID NOT IN (SELECT MemberID FROM Mainship.CE_Emails WHERE (ListID = 1)))"    End Sub I get the following error:Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not
contain a property with the name 'ListID'.I have listID = 1 in the above  example for testing purposes. Diane 

View 4 Replies View Related

Fill DataAdapter With DATATABLE

Jan 31, 2008

I'm reading XML data into a DataTable that is populated into a datagrid.  From here I need to update database and wanted to know how to Fill a DATA ADAPTER with a DATA TABLE?
I'm familiar with updating using a SQL command, but in this case, i have the dataTable already created from XML.
Please help.
Thanks,

View 3 Replies View Related

How To Generate An ID And Fill It In The Table

Feb 29, 2008

hi i have a table with Id and otehr fields,ID is autogenerate with datatype int.
But now i want to generate an Id in AU001 format and then want to insert into table where it must be autogenerate.How to do this.
Please help me with the problem,its very urgent....

View 6 Replies View Related

Error On DataAdapter.fill

Apr 11, 2006

i have this code :

if(! IsPostBack)
            {
           
    string strConnection = "server=localhost; uid=sa;
pwd=sasasa; database=northwind";
                string strCommand = "Select * from Customers";

           
    SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);

                DataSet dataset = new DataSet();

                dataAdapter.Fill(dataset, "Products");
           
    SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);

                DataTable dataTable = dataset.Tables[0];
                dgCustomer.DataSource = dataTable;
                dgCustomer.DataBind();
            }

when i run this code, error like this appear :


Server Error in '/Registeration' Application.


SQL Server does not exist or access denied. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.Source Error:



Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs    Line: 43
Stack Trace:



[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573

how can i solve this problem??? thank you...

View 1 Replies View Related

Error In DataAdapter.Fill

Apr 11, 2006

i have this code :

private void Page_Load(object sender, ....)
{

if(! IsPostBack)

            {
           
    string strConnection = "server=localhost; uid=sa;
pwd=**secret**; database=northwind";

                string strCommand = "Select * from Customers";


           
    SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);



                DataSet dataset = new DataSet();



                dataAdapter.Fill(dataset, "Products");
           
    SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);



                DataTable dataTable = dataset.Tables[0];

                dgCustomer.DataSource = dataTable;

                dgCustomer.DataBind();

            }
}



when i run this code, error like this appear :




Server Error in '/Registeration' Application.


SQL Server does not exist or access denied. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.Source Error:



Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs    Line: 43
Stack Trace:



[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573



how can i solve this problem??? thank you...

View 1 Replies View Related

Fill Factor -discussion

Apr 4, 2001

What are the criteria to decide the optimal(or close to optimal) fill factor?
Any input is appreciated.

View 2 Replies View Related

How To Calculate Fill Factor?

Aug 9, 2001

You have 50,000 records in a database file and you know you want to add another 100,000 records in the next several weeks. What fill factor would you use to maximize performance?
A.0(default setting)
B.30
C.70
D.100
which one is correct? And how to calculate fill factor?

View 1 Replies View Related

Clustered Index Fill

Nov 6, 2001

I am a little confused about what fill factor to use in creating a clustered index on one of our major tables.
This table has about 4.5 million rows and currently there are 14 indexes associated with it, but no clustered index.
I want to create a clustered index (on datestamp )on this table, but cannot really grasp what fillfactor I should use.
The main use of this table is in daterange reporting.
No deletes are applied to this table(except archiving on an annual basis).
It grows by about 5 000 rows daily.
From what I have read in BOL, a high fillfactor(100) is only applicable for a read only table, and I feel that at the time of creating a clustered index on this table, it is readonly, cos the data will never need to be reorganised to insert a new row into it, only at end.
BOL states that a low fillfactor could degrade database read performance,
and where the vast majority of our processing(reporting) hits this table,that is a major concern.
BUT BOL also says that a low Fillfactor is recommended where there are changes to the data.
Space is obviously important, but access speed is overwhelmingly more important.
Should I use a fillfactor of 70/80/90 etc. Any help would be appreciated

View 2 Replies View Related

Index Fill Factor > 100 !!

Sep 14, 1998

I have some non-clustered , non-unique indexes on a medium sized table (25,000 rows).
The fill factore is showing 248% on these indexes. I have tried setting the fill factor to various values 100% or less. The re build index seems to work, however the est min/avg/max size of the index all show appx 160Kb, whilst the actual size is in excess of 50Mb !!

We run a weekly rebuild of all indexes overnight, without any fill params and following this the fill on these indexes goes back to 248% !

I have also dropped and re-created the index with a fill of 100%, and it has still reverted to 248% following the weekly rebuild.

I have also looked at the server config, and the fill factor there shows a running value of 171% !!, although the current config is set to 0.

The server is stopped every evening, so no way should the config have a value of 171, especially since the max allowed value is 100.

Any advice/assistance would be gratefully received.

View 1 Replies View Related

Fill Factor And Performance

Jun 7, 2002

I know what fill factor is ... and know that I should set it high when I have static data tables (where the data rarely changes) and low when I expect to have page splits ...

but does anyone know what affect on performance this setting has ? I don't quite get what Books Online says about it.

Thanks
Carol

View 1 Replies View Related

What Is Fill Factor For Indexes

Aug 25, 2004

Hi ,


Can u plz tell me what is fill factor and what its role in defining the indexes. It is by default 0% and can be set upto 100%, but what it makes difference if i change the percentage? Where it is exactly impacts? If u know any links then plz forward me.

Thanks And Regards,
Shailesh

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved