Create a variable type TABLE

I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TESTE]

ON[dbo].[TABTESTE]

FOR INSERT

AS

DECLARE

@.YearTablenvarchar(4),

@.IdClientINT,

@.MyTableTABLE

(

IdClientINT,

SituNVARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED

SET @.MyTable = 'TABLE' & @.YearTable

UPDATE@.MyTable

SET

Situ= 'X'

WHEREIdClient = @.IdClient

END

GO

Erros:

Msg 156, Level 15, State 1, Procedure TESTE, Line 9

Incorrect syntax near the keyword 'TABLE'.

Msg 137, Level 15, State 1, Procedure TESTE, Line 17

Must declare the scalar variable "@.MyTable".

Msg 1087, Level 15, State 2, Procedure TESTE, Line 18

Must declare the table variable "@.MyTable".

I don't have much experience working with triggers but here's a shot...

-Try changing the @.MyTable to varchar(100)

-Declare another variable @.sql varchar(500)

set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient

exec (@.sql)

You may need to play with the number of single quotes around X to get the string to build correctly.

|||

can you explain what are you trying to do with this statement.

SET @.MyTable = 'TABLE' & @.YearTable

Are you trying to concatenate the string?

|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.
I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.

Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?

|||

hi MauricioBogo,

declare @.MyTable Table

(

IdClient int,

Suit nvarchar(50)

)

after you declare @.MyTable as a "Table"

in this Transaction, the @.MyTable is a "local database object" Already.

not a variable value.

this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "

and update @.MyTable .

you can try this, as below:

declare @.sSQL varchar(Max)

declare @.Mytable varchar(255)

select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted

set @.MyTable = 'TABLE' + @.Yeartable

set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)

--or--

or add any script you need.

try it.

hoping this can help.

Best Regrads,

Hunt.

|||

Yes, I'm trying to concatenate the string, but I already changed to + . Right ?

Thanks

|||

Hunt

Ok it works, thanks

Mauricio

|||

Table variables need to be declare alone:

DECLARE

@.YearTable nvarchar(4),

@.IdClient INT

DECLARE

@.MyTable TABLE

(

IdClient INT,

Situ NVARCHAR(50)

)

sql

Create a Table of Contents in Reporting Services

Hi,
I need to be able to create a Table of Contents for a Price List
Catalog in Reporting Services. Using the AdventureWorks example report
(Product Catalog.rdl), I need to be able to create something similar to
the following:
Table of Contents
Accessories
Bike Racks 2
Bike Stands 2
Bottles and Cages 3
Cleaners 4
Fenders 4
Helmets 5
Hydration Packs 5
Lights 6
Locks 7
Panniers 7
Pumps 8
Tires and Tubes 8
Bikes
Mountain Bikes 13
Road Bikes 15
Touring Bikes 19
Clothing
Bib-Shorts 22
Gloves 23
Jerseys 24
Shorts 25
etc.
It seems that Reporting Services would have something built into it to
do this since it is similar to the Document Mapping feature, but I have
not been able to locate it. Has anyone out there done this? Or have
any suggestions?
Thanks in advance,
Amy BoldenDoes these values all come from a data set? Then you could give you the
names of the items if you create a table against that dataset, and just show
the categories and subcategories. I'm not sure how you could do the page
numbers, though. In first case, I find that page numbers sometimes get
really mixed up when you save a report to PDF, because it behaves
differently in the browser than in the exported document. Also, I'm not sure
if you can pick up the page numbers that the report renders. You might be
able to do some tricks by counting items in each subcategory and dividing
them by a given number of rows, which would approximately give you the right
page numbers.
Do you have to have the page numbers? If not, you can use Jump to navigation
to jump from the table of contents to any of the items in your report /
catalogue.
Kaisa M. Lindahl Lervik
"Amy" <abolden@.lexington.com> wrote in message
news:1164643397.216130.159050@.j72g2000cwa.googlegroups.com...
> Hi,
> I need to be able to create a Table of Contents for a Price List
> Catalog in Reporting Services. Using the AdventureWorks example report
> (Product Catalog.rdl), I need to be able to create something similar to
> the following:
> Table of Contents
> Accessories
> Bike Racks 2
> Bike Stands 2
> Bottles and Cages 3
> Cleaners 4
> Fenders 4
> Helmets 5
> Hydration Packs 5
> Lights 6
> Locks 7
> Panniers 7
> Pumps 8
> Tires and Tubes 8
> Bikes
> Mountain Bikes 13
> Road Bikes 15
> Touring Bikes 19
> Clothing
> Bib-Shorts 22
> Gloves 23
> Jerseys 24
> Shorts 25
> etc.
> It seems that Reporting Services would have something built into it to
> do this since it is similar to the Document Mapping feature, but I have
> not been able to locate it. Has anyone out there done this? Or have
> any suggestions?
> Thanks in advance,
> Amy Bolden
>

Create a table and put it in a file

When I create a table I can create it in a filegroup, but how do I put it in a file. Example

I have two files in one filegroup called "Secondary" file A and file B.

I want to create a new table called tableA and put it in file B on filegroup Secondary.

I looked at bookes online and can't find it.

Thanks

Thomas

Hi,

It's part of the CREATE TABLE syntax.

CREATE TABLE myTable
(myID int)
ON [Secondary]

You cannot control on which file you create it because different files in a filegroup is exactly for spreading the table over multiple files.

Kind regards

Create a scheduled report delivery through C#

HI,
Is it possible to create a sceduled delivery of a report through C#?
For example, if I wanted to send User1 a Report1 at 4PM, and I had to do
this in my C# application, can this be done, and how?
Please, I need to be sure.
Regards,
PM"PacMan" <PacMan@.discussions.microsoft.com> wrote in message
news:23E1E830-DC13-4D00-90AF-A6A7303CBEC1@.microsoft.com...
> HI,
> Is it possible to create a sceduled delivery of a report through C#?
> For example, if I wanted to send User1 a Report1 at 4PM, and I had to do
> this in my C# application, can this be done, and how?
> Please, I need to be sure.
> Regards,
> PM
Hello,
Yes you can do it, no problems.
The answer is not simple, you have to look for the API
CreateDataDrivenSubscription
But if you discover C#, .NET, WebServices, XML and Reporting services
perhaps you should ask some help to someone that already knows C#.
Remi|||Thank you very much. That's all I needed to know.
"Remi THOMAS [MVP]" wrote:
> "PacMan" <PacMan@.discussions.microsoft.com> wrote in message
> news:23E1E830-DC13-4D00-90AF-A6A7303CBEC1@.microsoft.com...
> > HI,
> >
> > Is it possible to create a sceduled delivery of a report through C#?
> >
> > For example, if I wanted to send User1 a Report1 at 4PM, and I had to do
> > this in my C# application, can this be done, and how?
> >
> > Please, I need to be sure.
> >
> > Regards,
> > PM
> Hello,
> Yes you can do it, no problems.
> The answer is not simple, you have to look for the API
> CreateDataDrivenSubscription
> But if you discover C#, .NET, WebServices, XML and Reporting services
> perhaps you should ask some help to someone that already knows C#.
> Remi
>sql

CR Format

Hello,

Is there a way to format the data when it is passed into the Crystal Report(CR).. for example.. In the original table, a number may be left aligned, how to change it to right alignment?.. in other words I want to change it's

1. Format: Horizontal Alignment
2. from Left To Right
3. if the data passed in is an Integer, Currency, or DateTime.

4. There is a Format Formula Editor beside it. Does anyone know what's the syntax to do the alignment either in crystal syntax or in basic syntax.

thanx1. You can add a Parameter field in Crystal Report.

2. Pass the value to this Parameter field.
// Solution

Report.ParameterFields(1).AddCurrentValue (strkey1)

Remark : (a) ParameterFields(1) is the FIRST PARAMETER ADDED IN CR.
(b) strkey1 is the value or variable that contains the string or
any value that is received to be passed on to CR.
(c) Place this parameter field wherever you require in CR you
created.

3. You can format this Parameter field in Crystal Report as you WISH.

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/defaul...NoWebContent=1.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/defaul...NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
2
> 2004-02-12.
> to
to
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
>
http://support.microsoft.com/defaul...NoWebContent=1.
being
> 2
trying
> to
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> will
> use
datetime?
> (IMO,
>
http://groups.google.com/groups?oi=...ublic.sqlserver
date
> number
Something
wrote
> in
> datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
shows
> opposed
>

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > For some reason if I do a convert to a datetime, the time ends up being
2
> > days off. For example if I do a cast or convert of 38027 I get
> 2004-02-12.
> > If I put that same value in excel and make the field date, it shows
> > 2004-02-10. The 10th is the correct date and its making me crazy trying
> to
> > figure out what the problem is. I have tried using decimal as opposed
to
> > float and I still get the same results. Any ideas out there?
> >
> >
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > James,
> >
> > Two days difference come because of two reasons:
> > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> > while Excel as 1
> > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > Solid Quality Learning
> > More than just Training
> > www.SolidQualityLearning.com
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > For some reason if I do a convert to a datetime, the time ends up
being
> 2
> > > days off. For example if I do a cast or convert of 38027 I get
> > 2004-02-12.
> > > If I put that same value in excel and make the field date, it shows
> > > 2004-02-10. The 10th is the correct date and its making me crazy
trying
> > to
> > > figure out what the problem is. I have tried using decimal as opposed
> to
> > > float and I still get the same results. Any ideas out there?
> > >
> > >
> >
> >
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> > Why do you expect that converting to some strange float representation
> will
> > result in a meaningful value? Especially assuming that two products will
> use
> > the same rules when converting from some strange float value to
datetime?
> > Excel and SQL Server uses different internal representation of datetime.
> > This is why you never should rely on the internal representation of the
> > datetime datatype. Always use the supported character representation.
> (IMO,
> > SQL Server shouldn't even allow for such CAST or CONVERT...)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > > But I am using Excel XP and Excel is the one that is reporting the
date
> > > correctly. If I do select convert(float,getdate()) and paste that
> number
> > in
> > > excel, convert the field to date, it show it as two days ago.
Something
> > is
> > > amiss.
> > >
> > >
> > > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si>
wrote
> in
> > > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > James,
> > > >
> > > > Two days difference come because of two reasons:
> > > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
> datetime),
> > > > while Excel as 1
> > > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > > >
> > > > --
> > > > Dejan Sarka, SQL Server MVP
> > > > Associate Mentor
> > > > Solid Quality Learning
> > > > More than just Training
> > > > www.SolidQualityLearning.com
> > > >
> > > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > > For some reason if I do a convert to a datetime, the time ends up
> > being
> > > 2
> > > > > days off. For example if I do a cast or convert of 38027 I get
> > > > 2004-02-12.
> > > > > If I put that same value in excel and make the field date, it
shows
> > > > > 2004-02-10. The 10th is the correct date and its making me crazy
> > trying
> > > > to
> > > > > figure out what the problem is. I have tried using decimal as
> opposed
> > > to
> > > > > float and I still get the same results. Any ideas out there?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Counting unique FK

Hi,
I'm not sure how to explain my problem, so I'll go ahead with an example.
There are two tables: tblContact & tblContactAddress
tblContactAddress has a FK ContactId.
I want my SP to return something like this:
ContactAddressId ContactId ... Position
-- -- -- --
1 100 ... 1
2 100 ... 1
3 101 ... 2
4 102 ... 3
5 103 ... 4
6 103 ... 4
7 103 ... 4
8 103 ... 4
9 104 ... 5
So Field 'Position' should increase one every time FK ContactId Changes.
We're on SQL 2000
TIA!
MichaelIt would have been good, if you had given the ddl and insert script.
Anyways, here is the answer :)
create table tbl (ContactAddressId int, ContactId int)
insert into tbl values(1 ,100 )
insert into tbl values(2 ,100 )
insert into tbl values(3 ,101 )
insert into tbl values(4 ,102 )
insert into tbl values(5 ,103 )
insert into tbl values(6 ,103 )
insert into tbl values(7 ,103 )
insert into tbl values(8 ,103 )
insert into tbl values(9 ,104 )
select a.ContactAddressId,a.contactid, count(distinct b.ContactId) from tbl
a, tbl b
where a.ContactId >= b.ContactId
group by a.ContactAddressId,a.contactid
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi Omnibuzz,
Thanks for your input.
The result is indeed what i needed.
The only downside is slow performance (six seconds for only 2.700 records).
Kind regards,
Michael
"Omnibuzz" wrote:

> It would have been good, if you had given the ddl and insert script.
> Anyways, here is the answer :)
> create table tbl (ContactAddressId int, ContactId int)
> insert into tbl values(1 ,100 )
> insert into tbl values(2 ,100 )
> insert into tbl values(3 ,101 )
> insert into tbl values(4 ,102 )
> insert into tbl values(5 ,103 )
> insert into tbl values(6 ,103 )
> insert into tbl values(7 ,103 )
> insert into tbl values(8 ,103 )
> insert into tbl values(9 ,104 )
>
> select a.ContactAddressId,a.contactid, count(distinct b.ContactId) from tb
l
> a, tbl b
> where a.ContactId >= b.ContactId
> group by a.ContactAddressId,a.contactid
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||slow performance..
you need an index on ContactId..
You can't do without the self join... So I guess its your call :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Michael Maes" wrote:
> Hi Omnibuzz,
> Thanks for your input.
> The result is indeed what i needed.
> The only downside is slow performance (six seconds for only 2.700 records)
.
> Kind regards,
> Michael
> "Omnibuzz" wrote:
>|||If you are using SQL Server 2005, then you can use the dense_rank() function
,
Much simpler..
select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contacti
d)
from tbl a
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> slow performance..
> you need an index on ContactId..
> You can't do without the self join... So I guess its your call :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Michael Maes" wrote:
>|||Thanks for your help Omnibuzz.
Unfortunatly most of our customers haven't migrated to 2005 yet :-(
"Omnibuzz" wrote:
> If you are using SQL Server 2005, then you can use the dense_rank() functi
on,
> Much simpler..
> select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contac
tid)
> from tbl a
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Omnibuzz" wrote:
>

Counting no. of records

Hi

I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:

Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test

I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.

Many thanks in advance

If an aggregate of that column doesn't already exist then create one using something like

SUM(Ref No) AS 'Number of Records'

You could do this in the SQL or you can create it as a function which would look something like

=SUM(Ref No)

This field should be added to the footer column of the group.

Hope this helps

|||Hi,
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")

I think you know, how to set page break after group ends....

Do this....You will get the answer?
If you have any queries..let me know...

M Sivakumar|||Thanks for your reply Harley.

I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.

Cheers|||Hi Siva

First of all thanks for your help in trying to help, greatly appreciated.

Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:

=CountRows(Fields!RefNo.Value)

the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:

The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Thank you again for your help

|||Sorry,

I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)

thanks|||

Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")

if u renamed into "refno"
=CountRows("refno")

I think, it will help your need.

|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.

The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:

=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.

I had a group set up as my criteria and have used this and seems to be working .

By the way, a good reference for this function and others supported by Microsoft use this website, its really good:

http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley

I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.

Cheers for your help too.

counting based on bit flag

I have a table with an id field (int) and a bit flag. example below

id flag

1 true

1 true

1 false

1 true

2 true

2 false

I am looking for a query that will provide me the following results if possible

id true false

1 3 1

2 1 1

Any and all help is appreciated.

My efforts so far aren't worth sharing. I am looking for completely new approaches.

Thanks a ton

Use something like this:

Code Snippet

DECLARE @.MyTable table
( ID int,
Flag smallint
)

INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 0 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 2, 1 )
INSERT INTO @.MyTable VALUES ( 2, 0 )

SELECT
[ID],
True = sum( CASE Flag WHEN 1 THEN 1 ELSE 0 END ),
False = sum ( CASE Flag WHEN 0 THEN 1 ELSE 0 END )
FROM @.MyTable
GROUP BY [ID]

ID True False
-- -- --
1 3 1
2 1 1

|||Excellent!! Thank you so much!

Counting a group of data as one

How do I go about counting a group of data as 1 item? For example, I have a user who worked on 11 work orders, but only 10 were completed and I am trying to get the # of work orders completed. The problem I am having is it is counting each work day for each completed work order, so for example, if one work order was worked on for 5 days, it counts that work order as five and not one. I need it to ignore the number of work days and just give me the one work order in the count.use distinct count instead.|||I tried that, but it does not work in this case. I am getting closer with it, but now I have a different problem. It seems that the total I need is always off by 1. For example, if a person worked on 7 work orders and 5 of them are closed or completed, it is giving me a count of six. I need to figure out how if a work order has a certain status, to take that total and subtract 1 from it, otherwise leave it alone. Any ideas?|||It would be good if you would post an example of your current report and your desired result.|||You might be able to use a running total with a formula to determine which records to count; reset at the appropriate level.