Antivirus install to db server: type

Showing posts with label type. Show all posts

Friday, March 23, 2012

question about numeric type

Hi,
I am kind of confused regarding the numeric data type. If I define a numeric
column to be precision 38 (the maximum allowed), in EM the column length
automatically turns to be 17. What's the relationship between 38 and 17?
ThanksThose are storage bytes, lookup decimal and numeric in BOL
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Jen wrote:
> Hi,
> I am kind of confused regarding the numeric data type. If I define a numeric
> column to be precision 38 (the maximum allowed), in EM the column length
> automatically turns to be 17. What's the relationship between 38 and 17?
> Thanks|||what is Storage bytes? Thanks
"SQL Menace" wrote:
> Those are storage bytes, lookup decimal and numeric in BOL
> Precision Storage bytes
> 1 - 9 5
> 10-19 9
> 20-28 13
> 29-38 17
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Jen wrote:
> > Hi,
> >
> > I am kind of confused regarding the numeric data type. If I define a numeric
> > column to be precision 38 (the maximum allowed), in EM the column length
> > automatically turns to be 17. What's the relationship between 38 and 17?
> > Thanks
>|||How many bytes on disk a value of that datatype requires.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> what is Storage bytes? Thanks
> "SQL Menace" wrote:
>> Those are storage bytes, lookup decimal and numeric in BOL
>> Precision Storage bytes
>> 1 - 9 5
>> 10-19 9
>> 20-28 13
>> 29-38 17
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> Jen wrote:
>> > Hi,
>> >
>> > I am kind of confused regarding the numeric data type. If I define a numeric
>> > column to be precision 38 (the maximum allowed), in EM the column length
>> > automatically turns to be 17. What's the relationship between 38 and 17?
>> > Thanks
>>|||Is it true that if the length is 17, then the minimum digits wil be 29 and
the maximum digits will be 38? Thanks
"Tibor Karaszi" wrote:
> How many bytes on disk a value of that datatype requires.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > what is Storage bytes? Thanks
> >
> > "SQL Menace" wrote:
> >
> >> Those are storage bytes, lookup decimal and numeric in BOL
> >>
> >> Precision Storage bytes
> >> 1 - 9 5
> >> 10-19 9
> >> 20-28 13
> >> 29-38 17
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
> >>
> >> Jen wrote:
> >> > Hi,
> >> >
> >> > I am kind of confused regarding the numeric data type. If I define a numeric
> >> > column to be precision 38 (the maximum allowed), in EM the column length
> >> > automatically turns to be 17. What's the relationship between 38 and 17?
> >> > Thanks
> >>
> >>
>|||No, that is now how it work. Compare to int vs. smallint, for instance. You would be saying that an
int cannot hold numbers lower than some 32767? That is not the case. A larger storage area can hold
a larger *span* of values. We are not really talking about digits, we are talking about the values
that these digits represent. The values can be represented as binary, 10 base decimal or
hexadecimal, us humans tend to prefer a 10 base decimal representation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:622544A1-7A46-434A-A539-82D3C5052A52@.microsoft.com...
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
>> How many bytes on disk a value of that datatype requires.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
>> > what is Storage bytes? Thanks
>> >
>> > "SQL Menace" wrote:
>> >
>> >> Those are storage bytes, lookup decimal and numeric in BOL
>> >>
>> >> Precision Storage bytes
>> >> 1 - 9 5
>> >> 10-19 9
>> >> 20-28 13
>> >> 29-38 17
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>> >>
>> >> Jen wrote:
>> >> > Hi,
>> >> >
>> >> > I am kind of confused regarding the numeric data type. If I define a numeric
>> >> > column to be precision 38 (the maximum allowed), in EM the column length
>> >> > automatically turns to be 17. What's the relationship between 38 and 17?
>> >> > Thanks
>> >>
>> >>
>>|||Jen,
If you define a column as decimal/numeric with size 29, then each value
will use 17 bytes of disk space. A column defined as decimal(29,0) can
hold the values -99999999999999999999999999999 thru
99999999999999999999999999999.
If you define the column as decimal(30,0), you can store even bigger
numbers (one decimal digit more). However, each value will still use
(only) 17 bytes of disk space. The same is true for a decimal(38,0).
If you define the column as decimal(28,0), you can 'only' store numbers
between -9999999999999999999999999999 and 9999999999999999999999999999.
However, for the values of this column only 13 bytes of disk space are
needed per value.
HTH,
Gert-Jan
Jen wrote:
> Is it true that if the length is 17, then the minimum digits wil be 29 and
> the maximum digits will be 38? Thanks
> "Tibor Karaszi" wrote:
> > How many bytes on disk a value of that datatype requires.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:E236F084-6DDA-46E6-9AC2-447ED7E1E364@.microsoft.com...
> > > what is Storage bytes? Thanks
> > >
> > > "SQL Menace" wrote:
> > >
> > >> Those are storage bytes, lookup decimal and numeric in BOL
> > >>
> > >> Precision Storage bytes
> > >> 1 - 9 5
> > >> 10-19 9
> > >> 20-28 13
> > >> 29-38 17
> > >>
> > >> Denis the SQL Menace
> > >> http://sqlservercode.blogspot.com/
> > >>
> > >>
> > >>
> > >> Jen wrote:
> > >> > Hi,
> > >> >
> > >> > I am kind of confused regarding the numeric data type. If I define a numeric
> > >> > column to be precision 38 (the maximum allowed), in EM the column length
> > >> > automatically turns to be 17. What's the relationship between 38 and 17?
> > >> > Thanks
> > >>
> > >>
> >
> >

question about numeric type

Tuesday, March 20, 2012

Question about How Data can be rendered on Report

I have a dataset that is comprised of several records of employees leave
types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig" wrote in message news:1C3=82976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...I used the Iif statement you wrote. It works fine, but what I don't want =it to do is to put the next record in the dataset onto the next row. =I want the next record to go into the next column as in previous post =example. I only want the next dataset record to go into the next row if the =group changes. See the example for clarification."Wayne Snyder" wrote:> Create the two text boxes for Sick and Vacation... =For the values use an> expression like> for Sick> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)> for Leave =textbox> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)> > =Hope this helps...> > -- > Wayne Snyder, MCDBA, SQL Server MVP> Mariner, Charlotte, NC>">http://www.mariner-usa.com">www.mariner-usa.com> =(Please respond only to the newsgroups.)> > I support the =Professional Association of SQL Server (PASS) and it's> community of SQL =Server professionals.>">http://www.sqlpass.org">www.sqlpass.org> > ="dillig" wrote in message> news:889=F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...> > I have a dataset that is comprised of several records of =employees leave> > types and hours. Each leave type record is =unique(sicks on record and> > vacation another). 2 fields of each record,I =group on (Employee and> > TimePeriod) and the Leave type hours I want =to display in separate> columns.> > What I am trying to =do is group by Employee with a page break after each> > =employee(this was easy), then group by period(same page, this was done> =too),> > but I also need to put the hours in different columns depending =on the> Leave> > type. BUT I DONT WANT IT TO CREATE A =NEW ROW. I WANT THE NEXT RECORD TO GO> IN> > THE NEXT COLUMN =UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW> ROW> => CREATED.> > i.e)> = > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> >> >> > BUT INSTEAD I AM GETTING =THIS> > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 0> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =0 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =0 = 4> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. =CAN ANYONE HELP'?> >> > =>

--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>

Question about handling Nulls

Hi,
If I am adding 3 columns of data type real in a select clause and if any one
of those columns is a null I get a null back instead of reslult of an
addition applied to non null columns. So I used isnull(field1,0) to convert
it to zero so I get some value back instead of a null. i.e.
select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
This converts null values to zero and always get a result back even though
the result may be 0 if all values are null.
My question is what if I want to get a null back ONLY if all 3 fields have
value Null otherwise some number after applying addition.
ThanksRick,
Use a CASE expression.
Example:
select case when field1 is null and field2 is null and field3 is null then
null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
col_result From XYZ
AMB
"Rick" wrote:

> Hi,
> If I am adding 3 columns of data type real in a select clause and if any o
ne
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to conver
t
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||This isn't probably the answer you want, but something you should think
about anyway.
Why do you ever want NULL for numeric values? Does it ever make
mathematical sense? We made a decision long ago to NEVER allow nulls for
numeric values. We always populate/default our numeric columns to the value
that makes most sense. So, for example, 'price' would always start at zero
rather than null. You will most likely find that if you build in default
values for all your numerics problems such as the one you are posing just
simply go away.
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
> Hi,
> If I am adding 3 columns of data type real in a select clause and if any
> one
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to
> convert
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||Thank You Alejandro.
"Alejandro Mesa" wrote:
> Rick,
> Use a CASE expression.
> Example:
> select case when field1 is null and field2 is null and field3 is null then
> null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
> col_result From XYZ
>
> AMB
> "Rick" wrote:
>|||Hi James
You are right. What you said makes sense in the retail business for example.
I am in the wastewater industry. I can't just create zeros and average them
for the runtime of an equipment say a motor pump flow. If I get a value Zero
from pump reading only then I will average it otherwise I have to deal with
nulls and nulls are not counted in aggregate functions.
Rick
"james" wrote:

> This isn't probably the answer you want, but something you should think
> about anyway.
> Why do you ever want NULL for numeric values? Does it ever make
> mathematical sense? We made a decision long ago to NEVER allow nulls for
> numeric values. We always populate/default our numeric columns to the val
ue
> that makes most sense. So, for example, 'price' would always start at zer
o
> rather than null. You will most likely find that if you build in default
> values for all your numerics problems such as the one you are posing just
> simply go away.
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
>
>|||Hmm, not sure I aggree, but then I'm not the expert. Let me try though
I have 3 pumps. Pump one pumps 100gpm, P2 50 gpm and P3 isn't turned on. I
would argue that the average still includes P3, and that is is pumping zero
gpm, not NUL gpm
I'd like to see your logic/formula - but then I probably wouldn't understand
it anyway ;-)
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||Rick,
I just posted my other reply, and then it occurred to me what the problem
might be. I bet you are using a de-normalized table. And your pumps are
columns in one table, rather than having a pumps table. That is why you
have nulls in your columns.
How off the mark am I?
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||James,
You assumed that if the pump if off the value is Zero. But thats not the
case all the time. Some times instruments read bogus value of say 2 gpm
(gallons per minute) but you don't want to count this value in an avergae
neither you want a zero.
So I am applying a range condition saying if the pump value is in between
certain range I don't want it in an average. But if it is a zer then count i
t.
Ricky
"james" wrote:

> Rick,
> I just posted my other reply, and then it occurred to me what the problem
> might be. I bet you are using a de-normalized table. And your pumps are
> columns in one table, rather than having a pumps table. That is why you
> have nulls in your columns.
> How off the mark am I?
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
>
>

Monday, March 12, 2012

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Friday, March 9, 2012

Question about data type sqlserver ?

I have two problem :

+ The first, This is table store all items in bookshop system

tblItems:
IDItem Identity(Auto number)
Namebook nvarchar2
Price nvarchar2
Chapters nvarchar2
Weight nvarchar2 (weight of book)
....
I design data type for Chapter,or Price,Weight is nvarchar2 ? <--I wrong ? (I want to refer to principle of design the database)

+ The second ,When i design Price is the int datatype ! The default value is 0 ( I don't want to have this value ,i want to it is a empty field )

I really sorry because i ask too much ! Because i am a new programming !

Any Help or Advice would like appreciately ! Thanks u !

In my opinion you should use these datatypes:
IDItem Identity(Auto number)
Namebook nvarchar2
Price decimal
Chapters integer
Weight decimal orinteger depending upon the unit of measure
I don't understand what you are saying about Price and the integerdatatype and having a default value of 0. Unless you do somethingspecial SQL Server will insert a NULL into that field if you haven'tspecified a value.

|||

Thank tomorton very much !
I really don't understand what happen to me !

But I design again table ,everything is good !

Friday, March 23, 2012

question about numeric type

question about numeric type

question about numeric type

Tuesday, March 20, 2012

Question about How Data can be rendered on Report

Question about handling Nulls

Monday, March 12, 2012

Question about datetime field

Question about datetime field

Question about datetime field

Friday, March 9, 2012

Question about data type sqlserver ?

Antivirus install to db server

Blog Archive

About Me