How to get total page count for individual groups

Hello,

I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.

For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,

For 1st group

'Page 1 of 3’ when user click next page it should be 'Page 2 of 3' etc

Similarly for other groups as wll.

Thanks!

Hello,

Brian Welcker has a blog post that covers this:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

but you will want to modify his code slightly to use a hash table as he notes at the end.

Let me know if you need assistance implementing this solution or if this doesn't solve your issue.

Larry

|||

Hi Larry,

Thanks a lot for the reply.

I am able to implement the code but I am not able to get the total page count for the individual group.

Let say there are total 15 Pages in the report and there are 3 groups with page break then I need something like

For Group 1 paging would be -> Page 1 of 3, Page 2 of 3.....etc

For Group 1 paging would be -> Page 1 of 5, Page 2 of 5, Page 3 of 5...etc

For Group 1 paging would be -> Page 1 of 7

Currently I am getting like

Group 1 - Page1 of 15, Page 2 of 15...ect

Group 2 - Page 1 of 15, Page 2 of 15..etc

Group 3 - Page 1 of 15, Page 2 of 15..etc

Thanks

|||

I see. I didn't fully read your original post. Take a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1807270&SiteID=1

where Lisa posts a method of doing what you want. It requires some external code as the pages in a report are rendered in a single pass and it would require two passes (one to count and one to update) to accomplish what you are wanting.

Larry

HOW TO GET THE RESULTS IN THE SAME SEQUENCE IN ORACLE 9i AND SQL SERVER 2005?

In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!|||Just to make it clear: Oracle result is correct, SQL Server 2005 result is wrong?

If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.|||I'd use an ORDER BY clause.

-PatP|||Pat Phelan ,thank you very much.

Where is the clause "Order by " added?Please give me some example.

Anxoix to get your reply.|||You will have to use an ORDER BY somewhere. Not even Oracle guarantees that the results will always be returned in the same order if you don't use an ORDER BY.
Remember you are dealing with (mathematical) sets which do not have an implicit order. A DBMS is not a spreadsheet.

Using CONNECT BY does impose an implicit order, that's why in your statement it's very likely that the sorting will stay that way in Oracle, but if you want the SQL Server result sorted, tell the server to do so.|||If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.he did, he posted in both, and the duplicate was removed, so he re-posted another duplicate, and now they are merged

jp7234, please do not cross-post|||both platforms same syntax for this

select blah
from table
where 1=1
order by blah

select blah
from table
where 1=1
union
select blah
from table1
where 1=1
order by blah

Or order by can use column number to order

IE order by 1, 2|||r937,thanks.

Thank you for your care. I wll not give the same questions in different forums.This time, for I am anxious to get help as soon as possible I do so.|||Thank you,all of above friends.

I have used the Clause "Order by",but I failed.

The results in Oracle 9i shows a tree-like structure.

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470

2461 2462
2462 2465
2462 2466

We can find four branches:
1.the first 4 records show two branches
(1) 2461--2463--2469
(2) 2461--2463--2470

2.the first record and No. 5-7 record show other two branches
(3) 2461--2462--2465
(4) 2461--2462--2466

We can find that Parent node(0,2461) have two direct children node(2461,2463).(2461,2462). And the node information of the children node(2461,2463).(2461,2462) is sequencecd in different block.

But the results in SQL Server 2005 can not show these brances.And the results can not be sequenced in different block. I want to get the results with tree-like structure (the first branche,the second branche,and ....)in SQL Server 2005.

Please help me. If possible ,please give me your emails so that I can describe what I need in the results.

Thank a lots.|||I think that what you are describing here are analtic functions native to Oracle
CONNECT BY PRIOR

To my knowledge Sql Server does not have this functionallity at this time.

The only way to accomplish what I think that you are tring to accomplish is within a SP and build your result while cursoring through recordsets.

If you have a defined/finite number og related items
ie grand parent
Parent
child

You can accomplish your task using you table joined to itself.

select lvl1.Parent_node
, lvl2.Parent_node
, lvl3.Parent_node
from FROM TEST lvl1
left outer join test lvl2 on lvl1.folder_node =lvl2.Parent_node
left outer join test lvl3 on lvl2.folder_node =lvl1.Parent_node|||This can be done in TSQL, without using cursors, and without using recursion.

But before I go to the (not inconsiderable) trouble of explaining how, please explain WHY it is so important that the data be returned in that particular order. Data ordering is normally irrelevant to the database server, whether Oracle or MSSQL. Oracle just "happens" to return the data in that order for this particular non-standard function, so don't expect it to be a simple matter to force MSSQL to follow another engine's internal logic.

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.

> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.

> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

How to get the matrix row subtotal value

Hi,
i created a report like this:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
i wana show the percentage, so i need to get the row subTotal value, can
anyone help me?
--
Pony TsuiHi Pony,
Thank you for your posting!
Based on my understanding, you want to know how to get the row sub Total
value. If I misunderstood your concern, please feel free to let me know.
You could add the subTotal to your row group. Just right-click the Group
and click SubTotal, then, you could use get the Subtotal under the detail
row.
Hope this will be helpful and if you have any questions or concerns, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
Thanks for your reply!
I know how to add the subTotal field, i want to get the row subtotal value to
compute the percentage in each cells
as the sample:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 100%
i need to get subtotal 100 in the Q1 - Q4 Columns, i already found out i can
type:
Sum(Fields!Amount.Value, "matrix1_Customer")) to get the row subtotal value,
i can get percentage of Q1-Q4, but in subtotal, the percentage will not 100%,
it show the percentage of (rowsubtotal / total), so how to fix this problem?
Pony Tsui|||Hi Wei,
As i wrote in my previous mail, the percentage of subtotal field will show
the rowSubTotal / Total, i wanna get the resule:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 100%
but the report show:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 50%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 50%
How to fix this problem?
Pony Tsui|||Hi Pony,
Thanks for the reply.
Would you please send your report file to me? Also, please send some sample
data so that I can troubleshoot and re-produce your problem. Thank you!
I understand the information may be sensitive to you, my direct email
address is weilu@.ONLINE.microsoft.com(Please remove ONLINE when you send
the email), you may send the file to me directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Pony,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How to get new sql server logs without restart SQL server (not SQL transaction log)

hi
I'm using SQL 2000 SP4 on Win2003 Server
By default, when we restart the SQL server, the new "sql server logs" will
be created, my question is, how to achieve the same result by NOT restart
the SQL server
Thanks a lot> By default, when we restart the SQL server, the new "sql server logs" will
> be created, my question is, how to achieve the same result by NOT restart
> the SQL server
EXEC sp_cycle_errorlog
--
Hope this helps.
Dan Guzman
SQL Server MVP
"gbkhor" <gbkhor@.time.net.my> wrote in message
news:uJSGIj4UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> hi
> I'm using SQL 2000 SP4 on Win2003 Server
> By default, when we restart the SQL server, the new "sql server logs" will
> be created, my question is, how to achieve the same result by NOT restart
> the SQL server
> Thanks a lot
>|||Thanks Dan, it work well
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:1E79D060-BADE-4703-9E23-EB3D374F27F9@.microsoft.com...
>> By default, when we restart the SQL server, the new "sql server logs"
>> will be created, my question is, how to achieve the same result by NOT
>> restart the SQL server
> EXEC sp_cycle_errorlog
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "gbkhor" <gbkhor@.time.net.my> wrote in message
> news:uJSGIj4UIHA.6060@.TK2MSFTNGP05.phx.gbl...
>> hi
>> I'm using SQL 2000 SP4 on Win2003 Server
>> By default, when we restart the SQL server, the new "sql server logs"
>> will be created, my question is, how to achieve the same result by NOT
>> restart the SQL server
>> Thanks a lot
>

How to get new sql server logs without restart SQL server (not SQL transaction log)

hi
I'm using SQL 2000 SP4 on Win2003 Server
By default, when we restart the SQL server, the new "sql server logs" will
be created, my question is, how to achieve the same result by NOT restart
the SQL server
Thanks a lot
> By default, when we restart the SQL server, the new "sql server logs" will
> be created, my question is, how to achieve the same result by NOT restart
> the SQL server
EXEC sp_cycle_errorlog
Hope this helps.
Dan Guzman
SQL Server MVP
"gbkhor" <gbkhor@.time.net.my> wrote in message
news:uJSGIj4UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> hi
> I'm using SQL 2000 SP4 on Win2003 Server
> By default, when we restart the SQL server, the new "sql server logs" will
> be created, my question is, how to achieve the same result by NOT restart
> the SQL server
> Thanks a lot
>
|||Thanks Dan, it work well
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:1E79D060-BADE-4703-9E23-EB3D374F27F9@.microsoft.com...
> EXEC sp_cycle_errorlog
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "gbkhor" <gbkhor@.time.net.my> wrote in message
> news:uJSGIj4UIHA.6060@.TK2MSFTNGP05.phx.gbl...
>

How to get INSERT-SQL for rows?

Hi

I'm using the SQL Server 2005 Express edition.
I've manually created some rows in a table "Books".

If I right-click on "dbo.Books" and choose "Script Table As --> Create To --> New Query Editor Window", I get the SQL for creating that table.

But how do I get the INSERT-SQL for the rows already inserted?

Kind Regards

hi,

there's no support for that task, you have to do it your self..

or you can have a look at a free prj of mine, amInsert, available at http://www.asql.biz/en/Download2005.aspx

regards

|||

Hi Andrea

Thanks for your ImInsert!
It worked perfectly!

Kind Regards