Formula to Calculate weekdays in a month

I am having trouble coming up with a formula that will tell me how many weekdays there are in a month. I would need this formula to give me the number of weekdays in every month. Does anyone know how I would go about doing this? I have been struggling with this for 4 days now and ANY help would be GREATLY appreciated.

Thanks in advance.Hi,

Try this download.

Click here (http://support.businessobjects.com/communityCS/FilesAndUpdates/cr9_business_days_samples.zip.asp)

formula column

Hi,
I would like to create a calculated column using the formula
section for a table. I am having some trouble doing this.

The table's name is ReportParameter. The calculated column's name is
tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
I would like to use an If then statement such as the following (in
psuedo code):

If tb1 = 1 then tbcalculatedcolumn = 1
Elseif tb2 = 1 then tbcalculatedcolumn = 2
Endif

Thanks for the help,
BillHi

Maybe something like:

CREATE TABLE MyTable ( tb1 bit, tb2 bit, tbcalculatedcolumn AS CASE WHEN
tb1 = 1 then 1
WHEN tb2 = 1 then 2
END )

INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,1 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,0 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 0,1 )

SELECT * FROM MyTable
/*
tb1 tb2 tbcalculatedcolumn
-- -- ------
1 1 1
1 0 1
0 1 2
*/

John
"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0310010526.3ce4be47@.posting.google.c om...
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
> The table's name is ReportParameter. The calculated column's name is
> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
> If tb1 = 1 then tbcalculatedcolumn = 1
> Elseif tb2 = 1 then tbcalculatedcolumn = 2
> Endif
> Thanks for the help,
> Bill|||> I would like to create a calculated column using the formula
Why? Why not just put a CASE expression in a query or view rather than
create an extra redundant column.

> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
There is no Boolean data type in SQLServer. You mean a numeric column
(presumably BIT).

--
David Portas
----
Please reply only to the newsgroup
--

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0310010526.3ce4be47@.posting.google.c om...
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
> The table's name is ReportParameter. The calculated column's name is
> tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
> If tb1 = 1 then tbcalculatedcolumn = 1
> Elseif tb2 = 1 then tbcalculatedcolumn = 2
> Endif
> Thanks for the help,
> Bill|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications.

>> The table's name is ReportParameter. <<

That is not a table name; a table is an entity or a relationship. It
is also too vague to be a data element. You need to read a book about
database design.

>> The calculated column's name is "tbcalculatedcolumn" ...<<

Please tell me that "tbl-" is not a silly redundant prefix; and there
is always a better name than "calculated_column" for a calculated
column -- what extactly did you you compute? Interest? discounts? To
be is to be something in particular; to be nothing in particular is to
be nothing.

>> and tb1 and tb2 are boolean columns in the table. <<

There are no BOOLEAN variables in SQL; it would destroy the 3VL. Look
up the CASE expression in any pf the books on SQL you clearly have
never read.|||> There are no BOOLEAN variables in SQL; it would destroy the 3VL.

Aren't Booleans defined in SQL99?|||Yes. Joe goes into denial when confronted with SQL99 ;-)

--
David Portas
----
Please reply only to the newsgroup
--

"Christian Maslen" <christian.maslen@.techie.com> wrote in message
news:b9c8cfba.0310021514.11d51a@.posting.google.com ...
> > There are no BOOLEAN variables in SQL; it would destroy the 3VL.
> Aren't Booleans defined in SQL99?|||>> Joe goes into denial when confronted with SQL99 <<

So does everyone else who worked on the draft documents :)

They had to re-define the foundation to get them into SQL-99 and this
is oneof many reasons nobody is gallopping to SQL-99. The US
government requires SQL-92 and refers to it as "a standard in
progress" in their bid forms.

The problem is that a data type in SQL must be NULL-able; a NULL
doesnto have a data type itself, but holds a place for a value which
may or may not be determined later.

1) The fundamental rule of a NULL is that it propagates.

2) The fundamental rule of 3VL is that your have TRUE, FALSE and
UNKNOWN as the only possible values.

These fundamentals don't go together if you can have a column with a
3VL datatype. The "solution" was to make NULL = UNKNOWN but only in a
BOOLEAN column and then worry about null propagation. This screws up
3VL operators in some pretty awful ways that can drive an SQL engine
nuts:

FALSE OR UNKNOWN = UNKNOWN -- definition of OR
FALSE OR NULL = NULL = UNKNOWN -- null propagation
TRUE OR UNKNOWN = TRUE -- definition of OR
TRUE OR NULL = NULL = UNKNOWN -- null propagation

likewise,

TRUE AND UNKNOWN = UNKNOWN -- definition of AND
TRUE AND NULL = NULL = UNKNOWN -- null propagation
FALSE AND UNKNOWN = FALSE -- definition of AND
FALSE AND NULL = NULL = UNKNOWN -- null propagation

formatting with expressions trouble

I am trying to make a whole row "bolder" if a field in that row return true.
iif(field = true,"bolder","normal")
Isnt that the way it is written inside the format weight in the properties.Should be =IIF(Fields!YourField.Value = True, "Bold", "Normal")
"Benw" <Benw@.discussions.microsoft.com> wrote in message
news:476371E6-E970-4144-8D40-10873AC9530B@.microsoft.com...
>I am trying to make a whole row "bolder" if a field in that row return
>true.
> iif(field = true,"bolder","normal")
> Isnt that the way it is written inside the format weight in the
> properties.

Formatting Suggestions - Please Help

Hello,

I have a matrix with a dynamic number of columns (1-10). The trouble is that hiding one ore more columns still leaves space reserved for all 10 columns, which is ugly. This is because the size of the TextBox that oversees the columns is not dynamic, and it is set to the size of all 10 text boxes.

In other words, a matrix with 5 columns looks like this:

Item Total

Col1 Col2 Col3 Col4 Col5 Total

5 5 5 5 5 25

While a matrix with 2 columns (the last 3 have visibility set to false) looks like this:

Item Total

Col1 Col2 Total

5 5 25

I am going crazy trying to solve this one. Does anyone have any ideas at all that can help me? Merging all the columns into a single column would not work well for me, as each column is a drill-down for the others. And making each column small (.1in), doesn't work either because there is no "no-wrap" property.

ANY suggestions would be appreciated. How have others dealt with this issue?

Michael

I have also found that the invisible function is SQL reports is no good. What determines how many columns there will be on the report? Can you use that determination to choose between different reports -- say one that is a two column report and another that is a five column report? Don't use invisibility at all.|||

Greg,

That's what I did. I created a small, medium, and large version of my report (1-2, 3-5, 6-10), that seems to be reasonable even when the visibility column is hidden for some of the columns. The problem is that I have a dozen reports I want to use with the variable column in the left axis, and that means I need to create 36 reports instead of 12. Fortunately, the top axis auto-fits items so I only need one report for the top axis, or else I would be in BIG trouble.

sql

Formatting reports in SQL PLUS

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

Quote:

Originally Posted by Mateo

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

It depends on where and what you want to achieve. If you're in Query Analyzer, go to Query (menu) -> Results in grid. If you use it from some code, you'll have to work it out yourself - depending on what and where you need (sorry). Alternatively select fewer columns by listing the ones the you really need. Screen has limited width... ;-)|||I think you posted at the wrong section.
Sql plus is an oracle tool.
Just run the command from the prompt with the sqlplus.exe and redirect (>) it to an output file.

You should the be able to read the files more clearly (use the pspad viewer, it's free and it can handle long records)sql

Format number with 2 decimal places

I am having trouble formatting a DATEDIFF with 2 decimal places.
Can anyone help?
tbVisitLog
VisitID | ChildID | DateTimeIn | DateTimeOut | etc...
tbChild
ChildID | ChildLastName
When calculating duration of stay...
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the child") AS Amou
ntDue
FROM tbVisitLog v
INNER JOIN tbChild ON c.ChildID = v.ChildID
WHERE c.ChildID IN (group of children leaving)
I want to format the duration * rate to be a two-decimal number. Without for
matting I get numbers like this:
ChildName | 0.52000000000000002
ChildName | 4.0199999999999996
Thanks
Sonny
--
--Try this (untested):
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(cast(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the
child") as decimal(14,2)) AS AmountDue
FROM ...
ML