Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Thursday, March 29, 2012

Filter in Replication

Hello,
I have a table JobDescription with the columns:
JobId
Email
StartTime
EndTime
Priority
etc.
I want the Filter in that way that only the rows of the JobDescription of a
certain Email Adress will be synchronized.
Can anybody tell me how this work?
thx
mathon
But which rows from which email would be displayed is only clear when the
user logs in the application with his email-adress. So I think the static
filter is not the right. Should I use dynamic filter? - How do I have to
quote it in that way in the Publication Process and then in my code? -
Unfortunately I haven't found a good sample for that.
mathon
|||Yes - dynamic filters is the way to go. If this is per
user, you can have a column for the loginname (the data
returned from select suser_sname()) and use that in the
filter. The dynamic filter option is a subset of the
horizontal filters part of the wizard and will appear if
you have selected the advanced options (first checkbox in
the wizard). Your dynamic filter will look like this:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.SuserSName = SUSer_SName().
If you don't want another column in the table, and want
to map logins to email addresses, you can publish the
mapping table and apply the dynamic filter to that table,
or alternatively encapsulate the whole thing in a UDF to
be used in the where clause.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Dear Mr. Ibison!
Let me reflect once to know If I'll be right:
The method in the Login-mask selects the the Email of the User (Engineer) ->
SELECT Email FROM Engineer WHERE UserId=? AND password=?.
So in the Publication Wizard:
1) First I check "Horizontally, by filtering the rows published data"
2) Then I check "Yes, enable dynamic filters"
3) Then I select the Table to filter - [dbo].[JobDescription]
4) Then the Select Statement looks as follows:
SELECT <published_columns> FROM [dbo].[JobDescription] WHERE
[dbo].[JobDescription].EmailEngineer = EngineerLogin()
But when I want to go further the following error occurs:
"The WHERE clause 'JobDescription.EmailEngineer = EngineerLogin()' could not
be executed on table '[dbo].[JobDescription]'.
Error 195. 'EngineerLogin() is not a recognized function name.
Do you know what went wrong? (The function exists in my class-file.)
Regards
mathon
"Paul Ibison" wrote:

> Yes - dynamic filters is the way to go. If this is per
> user, you can have a column for the loginname (the data
> returned from select suser_sname()) and use that in the
> filter. The dynamic filter option is a subset of the
> horizontal filters part of the wizard and will appear if
> you have selected the advanced options (first checkbox in
> the wizard). Your dynamic filter will look like this:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.SuserSName = SUSer_SName().
> If you don't want another column in the table, and want
> to map logins to email addresses, you can publish the
> mapping table and apply the dynamic filter to that table,
> or alternatively encapsulate the whole thing in a UDF to
> be used in the where clause.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Mathon,
firstly, I must clarify that the ability to filter data
on a per subscriber basis is part of merge replication
only (out of the box), so that is the scenario we are
looking at.
Your login mask may or may not be an issue. Is the
username and password used to log into sql server - ie is
this a SQL Server login? If it is, then filtering will be
simpler. In the wizard, ensure the advanced options are
enabled, and after selecting horizontal filters, select
the dynamic option. Your EngineerLogin() function as far
as I can see is a function you've created in your VB
code, so is not accessible to SQL Server (CLR integration
is a posibility in SQL 2005 but not SQL 2000). So, to map
the login name to the email address, you'll need a user-
defined function to be created in SQL Server. This
function will take suser_sname() as a parameter and will
return the email address as a return value.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
The password and userId is the log-in information for my mobile application
and not the login for the sql server. Is this more complicated?
regards
mat
"Paul Ibison" wrote:

> Mathon,
> firstly, I must clarify that the ability to filter data
> on a per subscriber basis is part of merge replication
> only (out of the box), so that is the scenario we are
> looking at.
> Your login mask may or may not be an issue. Is the
> username and password used to log into sql server - ie is
> this a SQL Server login? If it is, then filtering will be
> simpler. In the wizard, ensure the advanced options are
> enabled, and after selecting horizontal filters, select
> the dynamic option. Your EngineerLogin() function as far
> as I can see is a function you've created in your VB
> code, so is not accessible to SQL Server (CLR integration
> is a posibility in SQL 2005 but not SQL 2000). So, to map
> the login name to the email address, you'll need a user-
> defined function to be created in SQL Server. This
> function will take suser_sname() as a parameter and will
> return the email address as a return value.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||OK - in that case there might be issues. Do you use the
same login to SQL Server for everyone? Is the mobile
device used by an individual user or multiple users?
In the easiest scenario, the device is used by the same
user each time. Typically you'll be using a sql server
login which doesn't differentiate between users, so we
can't rely on this in the filter. If this is true, you
could set the filter to use HOST_NAME(). In the merge
agent's job step for this subscriber, add -HOSTNAME
[emailaddress] to filter the data (replace emailaddress
with the individuals email address). The filter will be
as follows:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.email = HOST_NAME().
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Yes the device is always used by the same user and everybody has the same
login and password (sa,sa) to the SQL Server database. But i do not exactly
know what you mean with merge agents job step? Where should I add
-HOSTNAME[emailaddress]? And is it then still dynamically when I am not able
to filter according to the login a user makes in my mobile application?
regards
mat
"Paul Ibison" wrote:

> OK - in that case there might be issues. Do you use the
> same login to SQL Server for everyone? Is the mobile
> device used by an individual user or multiple users?
> In the easiest scenario, the device is used by the same
> user each time. Typically you'll be using a sql server
> login which doesn't differentiate between users, so we
> can't rely on this in the filter. If this is true, you
> could set the filter to use HOST_NAME(). In the merge
> agent's job step for this subscriber, add -HOSTNAME
> [emailaddress] to filter the data (replace emailaddress
> with the individuals email address). The filter will be
> as follows:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.email = HOST_NAME().
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mathon,
the hostname will be dynamic in the sense of each
subscription will be able to get different data. This
means that each mobile device can have different data. It
doesn't mean that if a different user logs onto the
device he'll see different data - the data is partitioned
according to device. If there is a 1:1 relationship
between users and devices then this is fine.
The merge agent is a job. The middle step runs the merge
executable. In this step there will be a number of
parameters , and at the end you can add -HOSTNAME with a
value. If you are doing this programatically, the
HOSTNAME is a property of the merge object.
I recommend that you set it up and have a play around
with the dynamic filtering in a test environment to see
if it meets your requirements.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Unfortunately I am not so familiar with the Replication Mechanism. In my
application I realized the replication only in that way that all my tables
would be replicated and no filters were set. I do this in the Publication
Wizard in the SQL Server Enterprise Manager. In the code I only quote then
the properties for the Replication like login, publisher, etc. (The
Replication works, but as said I want to filter the one table
JobDescription.)
So I am not sure how I should execute your quoted steps to reach my aim. Can
you probably give me a simpler and/or more detailed explanation?:-/ sorry..
mathon

filter detail section of matrix report

Hi,
is there a way to filter details section of the report created using Matrix report type?
I just want to see rows that contain positive numbers.

Thanks,
Igor

Have you tried using the filter tab in the matrix properties dialog box.

I'd be careful though as each matrix cell is an aggregation of 1 or more detail rows. Depending on the nature of the source data and whether any aggregation is actually occuring you'll want to watch out for filtering underlying negative values vs a negative result of an aggregate.

Can you not do this in the source query?

Filter delete rows on subscriber

How does SQL server replication delete rows on the client that no longer
matches the filter? I need this to decide if I want to add a new table into
replication.
TIA.
Rajesh
There is a table with a large number of rows (5-6 million), with only a few
rows (2000-3000) replicated to each client. Each client needs to get a
different set of rows.
I want to know whether:
1. The filter is applied on the subscriber to delete the rows?
2. Or the IDs to be deleted is sent to the subscriber by the publisher?
3. Or the IDs on the subscriber is sent to the publisher, for which the
publisher responds with the IDs that should no longer be available in the
subscriber.
Due to the large number of rows, I'm just worried that #2 and #3 will lead
to a lot of inefficeincy. And if that is the case, I would rather sync this
data outside of replication.
Rajesh
"Rajesh" wrote:

> How does SQL server replication delete rows on the client that no longer
> matches the filter? I need this to decide if I want to add a new table into
> replication.
> TIA.
> Rajesh
sql

Tuesday, March 27, 2012

Filter and show TOP N rows

Hi all,
I´m designing a report with a kind of Ranking. I have a list of products and
I have to show the totals by product.
What I need to show in my ranking table is only the Top 10 records so I need
to filter by something like "Top 10". My questions are:
- Which is the correct expresion to write in the group filter?
- Is it possible to add a column to show the row number?
(Additional information)
My result should be something like:
Position > Product > Total
1 > Product1 > 1000
2 > Product2 > 900
3 > Product3 > 850
4 > Product4 > 725
5 > Product5 > 700
6 > Product6 > 680
7 > Product7 > 500
8 > Product8 > 330
9 > Product8 > 210
10 > Product10 > 200
I know how to do it creating a new Dataset using SQL syntax, but I can´t
touch this. The dataset available contains the complete list with all the
values, I mean, my dataset is like:
Product > Value
Product1 > 3
Product1 > 4
Product2 > 1
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
[more records...]
So I have to group data and sum values to show totals.
Could anybody help me?
Thank you in advanced.
MónicaOn Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Hi all,
> I=B4m designing a report with a kind of Ranking. I have a list of product=s and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I n=eed
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can=B4t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> M=F3nica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =3DRowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you!
Top N filter works but not the RowNumber function :(.
If I add =RowNumber(Nothing) in a new column it shows the product RowNumber,
counting all the rows grouped.
i.e.
Product > Total > RowNumber
Product1 > 73 > 73
Product2 > 12 > 85
Product3 > 12 > 97
Product4 > 7 > 104
Product5 > 3 > 107
Product6 > 3 > 110
Product7 > 3 > 113
Product8 > 2 > 115
Product9 > 1 > 116
Product10 > 1 > 117
Note that every RowNumber is the sum of the 2 previous totals, so we can say
it shows the row number of the product, counting all the rows grouped.
How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
Another question about this. I´ve tried to show the total in the table
footer but the total shown does not filter my subtotals.
What I really need is to have the following:
Ranking >Product > SubTotal
1 > Product1 > 73
2 > Product2 > 12
3 > Product3 > 12
4 > Product4 > 7
5 > Product5 > 3
6 > Product6 > 3
7 > Product7 > 3
8 > Product8 > 2
9 > Product9 > 1
10 > Product10 > 1
TOTAL TOP 10 > 117
TOTAL OTHER > 550
TOTAL > 667
Could anybody tell me how to do it?
My needs are:
- To be able to SUM only filtered rows
- To be able to show RowNumber as a Ranking position
- To be able to SUM products not included in the filter
(TOTAL is the only field I have with no problem :D)
Thank you
Regards,
Mónica
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
> Hi all,
> I´m designing a report with a kind of Ranking. I have a list of products
> and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I
> need
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can´t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> Mónica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =RowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =3DRowNumber(Nothing) in a new column it shows the product RowNu=mber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can =say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I=B4ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> M=F3nica
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118843=0691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I=B4m designing a report with a kind of Ranking. I have a list of produ=cts
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can==B4t
> > touch this. The dataset available contains the complete list with all t=he
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > M=F3nica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =3DRowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =3DSum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Yes, I know... The problem is that I can not create new Datasets or touch
the one I have.
I'll try a workarround... :(
Thank you anyway.
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188526929.514501.144250@.i13g2000prf.googlegroups.com...
On Aug 30, 9:51 am, "Mónica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =RowNumber(Nothing) in a new column it shows the product
> RowNumber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can
> say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I´ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> Mónica
> "EMartinez" <emartinez...@.gmail.com> escribió en el
> mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I´m designing a report with a kind of Ranking. I have a list of products
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can´t
> > touch this. The dataset available contains the complete list with all
> > the
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > Mónica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =RowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =Sum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 31, 2:20 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Yes, I know... The problem is that I can not create new Datasets or touch
> the one I have.
> I'll try a workarround... :(
> Thank you anyway.
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118852=6929.514501.144250@.i13g2000prf.googlegroups.com...
> On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Thank you!
> > Top N filter works but not the RowNumber function :(.
> > If I add =3DRowNumber(Nothing) in a new column it shows the product
> > RowNumber,
> > counting all the rows grouped.
> > i.e.
> > Product > Total > RowNumber
> > Product1 > 73 > 73
> > Product2 > 12 > 85
> > Product3 > 12 > 97
> > Product4 > 7 > 104
> > Product5 > 3 > 107
> > Product6 > 3 > 110
> > Product7 > 3 > 113
> > Product8 > 2 > 115
> > Product9 > 1 > 116
> > Product10 > 1 > 117
> > Note that every RowNumber is the sum of the 2 previous totals, so we can
> > say
> > it shows the row number of the product, counting all the rows grouped.
> > How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> > product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> > Another question about this. I=B4ve tried to show the total in the table
> > footer but the total shown does not filter my subtotals.
> > What I really need is to have the following:
> > Ranking >Product > SubTotal
> > 1 > Product1 > 73
> > 2 > Product2 > 12
> > 3 > Product3 > 12
> > 4 > Product4 > 7
> > 5 > Product5 > 3
> > 6 > Product6 > 3
> > 7 > Product7 > 3
> > 8 > Product8 > 2
> > 9 > Product9 > 1
> > 10 > Product10 > 1
> > TOTAL TOP 10 > 117
> > TOTAL OTHER > 550
> > TOTAL > 667
> > Could anybody tell me how to do it?
> > My needs are:
> > - To be able to SUM only filtered rows
> > - To be able to show RowNumber as a Ranking position
> > - To be able to SUM products not included in the filter
> > (TOTAL is the only field I have with no problem :D)
> > Thank you
> > Regards,
> > M=F3nica
> > "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el
> > mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> > On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> > > Hi all,
> > > I=B4m designing a report with a kind of Ranking. I have a list of pro=ducts
> > > and
> > > I have to show the totals by product.
> > > What I need to show in my ranking table is only the Top 10 records so= I
> > > need
> > > to filter by something like "Top 10". My questions are:
> > > - Which is the correct expresion to write in the group filter?
> > > - Is it possible to add a column to show the row number?
> > > (Additional information)
> > > My result should be something like:
> > > Position > Product > Total
> > > 1 > Product1 > 1000
> > > 2 > Product2 > 900
> > > 3 > Product3 > 850
> > > 4 > Product4 > 725
> > > 5 > Product5 > 700
> > > 6 > Product6 > 680
> > > 7 > Product7 > 500
> > > 8 > Product8 > 330
> > > 9 > Product8 > 210
> > > 10 > Product10 > 200
> > > I know how to do it creating a new Dataset using SQL syntax, but I ca=n=B4t
> > > touch this. The dataset available contains the complete list with all
> > > the
> > > values, I mean, my dataset is like:
> > > Product > Value
> > > Product1 > 3
> > > Product1 > 4
> > > Product2 > 1
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > [more records...]
> > > So I have to group data and sum values to show totals.
> > > Could anybody help me?
> > > Thank you in advanced.
> > > M=F3nica
> > Right-click the table/matrix control and select 'Properties' -> select
> > the 'Groups' tab -> select the 'Edit...' button -> select the
> > 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> > below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> > your sort order you can control which items are top 10. To get the row
> > number you can use the expression =3DRowNumber(Nothing) in a new
> > column.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> RowNumber should give you what you need. You just want to set the
> scope for it: RowNumber(Scope). To get the correct sums, you will want
> to create separate datasets and then reference them via: =3DSum(Fields!
> Total.Value, "DataSetName") as the expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Another alternative for the sums might be to group the dataset based
on the top N and bottom N and then try to use the InScope function in
a sum expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Monday, March 26, 2012

FillFactor considerations

I have a table with about 200 million rows of data. I add a couple million rows of data each week to the table in a single load process. The table is used for reporting purposes only and there are never (not intentionally at least) any updates or deletes to the table. The data is always being added to the "end" of the table with the new AsOfDate being the main factor in the clustered index.

My question is this: Since I'm not "inserting" rows that would split pages, should I have my FILLFACTOR for the table set to 100, or am I missing something? I obviously want to save physical hard drive space, but I also don't want to slow down the import process.

BTW, I'm using SQL2000

If you are certain that your data loads is 'always' putting the new data at the end of the clustered index, you may have a good case for NOT leaving empty space on the data pages -and a FILLFACTOR of 100 would be in order.

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.
Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>
|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>>I agree with the others that it is probably not related to the fill
>>factor. I would guess blocking first, disk queues next (checkpoints etc.)
>>and maybe even file auto growths. Make sure there is plenty of free space
>>in the data and log files.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>>
>

Fill in missing date ranges

I have a table AssetValues that contains several rows of data for a
particular date. The table has missing dates and I need to populate these
missing dates one-time to create historical data and then daily to populate
missing data on an ongoing basis. The missing dates are not related to the
particular day of the w.
The logic for inserting new rows is as follows:
If there is a date without data, insert data using the previous date that
has data as long as the previous day is in the same month. So if I was
missing data for 20060430 and there was data for 20060429 then copy the data
from 20060429 changing the date to 20060430. If I was missing 20060430 and
20060429 then use the data from 20060428.
If I was missing 20060501 do not use the data from 20060430. The data needs
to be in the same month. In this case I would need to use the data from
20060502 if this was available or the next day in May when the data is
available. If the data is not available then take no action until it is.
I have a calendar table if needed.
Thanks to anyone who could help.
CREATE TABLE AssetValues
(
AssetValueDate datetime,
Category char(1),
Subcategory int
)
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 1 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','B', 2 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','C', 3 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 4 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 5 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','C', 6 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','A', 7 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 8 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 9 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','A', 10 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','B', 11 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 12 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 13 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','B', 14 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','C', 15 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 16 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 17 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','C', 18 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','A', 19 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 20 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 21 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','A', 22 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','B', 23 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 24 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 25 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','B', 26 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','C', 27 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 28 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 29 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','C', 30 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','A', 31 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 32 )
DROP TABLE AssetValuesTry,
create view v1
as
select a.dt
from dbo.calendar as a
where not exists (
select * from dbo.AssetValues as b
where b.AssetValueDate = a.dt
)
and a.dt between convert(char(6), (select min(AssetValueDate) from
dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
'01'))
go
select *
from v1
go
-- previous available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
v1.dt) = 0)
go
-- next available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
a.AssetValueDate) = 0)
go
AMB
"Terri" wrote:

> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues
>
>|||This duplicates all rows from the previous day. Is that what you want?
CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
DECLARE @.Date DATETIME
, @.MaxDate DATETIME
SELECT @.Date = MIN(AssetValueDate)
, @.MaxDate = MAX(AssetValueDate)
FROM AssetValues
WHILE @.Date <= @.MaxDate BEGIN
INSERT Dates
SELECT @.Date
SET @.Date = @.Date + 1
END
GO
SELECT DateValue
, AssetValueDate
, Category
, Subcategory
FROM AssetValues av
, Dates
WHERE YEAR(DateValue) = YEAR(AssetValueDate)
AND
MONTH(DateValue) = MONTH(AssetValueDate)
AND
EXISTS
(
SELECT *
FROM AssetValues av1
WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
HAVING COUNT(DISTINCT AssetValueDate) = 1
)
ORDER BY DateValue, AssetValueDate
--Alan
Terri wrote:
> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues|||Thanks that worked. The only thing I still need to change is that the script
populated all the days in May from May 2nd data which was the most recent
data I had. I think I can add a date filter that will prevent population of
future dates.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87565D10-F136-4D26-988B-5208CBEED90A@.microsoft.com...
> Try,
> create view v1
> as
> select a.dt
> from dbo.calendar as a
> where not exists (
> select * from dbo.AssetValues as b
> where b.AssetValueDate = a.dt
> )
> and a.dt between convert(char(6), (select min(AssetValueDate) from
> dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
> convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
> '01'))
> go
> select *
> from v1
> go
> -- previous available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
> v1.dt) = 0)
> go
> -- next available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
> a.AssetValueDate) = 0)
> go
>
> AMB
>|||No, but thanks. Sometimes I need to populate a date with data from a more
recent date when that data becomes available.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1146684129.666739.113960@.j73g2000cwa.googlegroups.com...
> This duplicates all rows from the previous day. Is that what you want?
> CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
> DECLARE @.Date DATETIME
> , @.MaxDate DATETIME
> SELECT @.Date = MIN(AssetValueDate)
> , @.MaxDate = MAX(AssetValueDate)
> FROM AssetValues
> WHILE @.Date <= @.MaxDate BEGIN
> INSERT Dates
> SELECT @.Date
> SET @.Date = @.Date + 1
> END
> GO
> SELECT DateValue
> , AssetValueDate
> , Category
> , Subcategory
> FROM AssetValues av
> , Dates
> WHERE YEAR(DateValue) = YEAR(AssetValueDate)
> AND
> MONTH(DateValue) = MONTH(AssetValueDate)
> AND
> EXISTS
> (
> SELECT *
> FROM AssetValues av1
> WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
> HAVING COUNT(DISTINCT AssetValueDate) = 1
> )
> ORDER BY DateValue, AssetValueDate
> --Alan
> Terri wrote:
these
populate
the
that
data
and
needs
>

Friday, March 23, 2012

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.
Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.
|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.co...ng_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
--
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.com/rebuilding_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.c...ing_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

fill down formulas without selecting the sheet

At the moment I have a bunch of formulas along the top row of a sheet and I
wish to copy them down a predfined number of rows. At the moment I activate
the sheet and then select the rows I want to copy down with the row
containing the formulas at the top. It would be much er if I could
accomplish this in the background as it were without having to select the
sheet. Is there a way of effectively filling down the formula without having
to activate the sheet?
Any help much appreciated, kind regards, MarkAre you sure you posted in the right NG ? That should go for the excel ng as
I hear from your problem ?!
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mark Stephens" wrote:

> At the moment I have a bunch of formulas along the top row of a sheet and
I
> wish to copy them down a predfined number of rows. At the moment I activat
e
> the sheet and then select the rows I want to copy down with the row
> containing the formulas at the top. It would be much er if I could
> accomplish this in the background as it were without having to select the
> sheet. Is there a way of effectively filling down the formula without havi
ng
> to activate the sheet?
> Any help much appreciated, kind regards, Mark
>
>

Sunday, February 26, 2012

File source error : so much rows

Hello,

I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.

If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.

Thank you for your answer.

File source error : so much rows

Hello,

I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.

If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.

Thank you for your answer.