Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.

> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documente
d
> in the SQL Server Books Online.
>
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If yo
u
> later try to insert a row that requires more than 8060 bytes of actual dat
a,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Handle datetime in SQL Server 2005

Are there any function in SQL Server 2005 which can help to calculate the total no. of days and months? Let's said if I provide 2 dates, 28-Feb-2001 and01-Mar-2004, it can return 36 Months and 2 Days. The concept is like the functionmonths_between in Oracle. Are there any function in SQL Server 2005 can achieve this?

You can use DATEDIFF function: for example

DATEDIFF

(m,'1/1/2007',getdate())as monthDiff

DATEDIFF(d,'1/1/2007',getdate())as dayDiff

|||

Try the DateDiff function

|||

try datediff function is very helpfull

Thanks