how to show the month name?

I am using

selectmonth(date_field)

to display the month but it's showing the month number and I want to show as names, e.g. Jan, Feb, Mar. etc..

|||

Jassim:

Lookup the DATENAME function in books online; it should be something like:

select datename (mm, date_field)

Or if you want to only show the first three letters of the month:

select left(datename (mm, date_field), 3)

|||Select Convert(Varchar(3),Datename(mm,getdate()))

how to show the month name?

I am using

selectmonth(date_field)

to display the month but it's showing the month number and I want to show as names, e.g. Jan, Feb, Mar. etc..

|||

Jassim:

Lookup the DATENAME function in books online; it should be something like:

select datename (mm, date_field)

Or if you want to only show the first three letters of the month:

select left(datename (mm, date_field), 3)

|||Select Convert(Varchar(3),Datename(mm,getdate()))

How to Show Last Admit Date

I have a report that shows patient information, such as patient number, name, and admit date. It also has a date parameter. I enter in the date and it shows all the patients admitted that day. I would like to show the last date the patient was admitted before the date I ran the report for.

For example, if I run the report for 12/20/06, I want to see the patients that were admitted that day and their info like name, and patient number. I would also like to see the last date they were admitted.

Could you just do a subquery to get the max date for each patient from before the date parameter? Something like this:

select ...
(select max(admit_date) from patient_admittances pa where p.patient_id = pa.patient_id and admit_date < @.RunDate)
from patients p
inner join ...

Jarret

How to show distinct rows of the column of the dataset and number of distinct rows of that colum

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

Have a table and map it to the dataset. Insert a group (say table_Group1) to the table with the grouping column being "Fields!field1.Value". In the group header row of your table, use Fields!field1.Value and CountDistinct(FIelds!field1.Value, "table_Group1") to get your desired values.

Pls mark the post as answered if your problem is solved.

Shyam

How to show all the fields in a dataset.

I have a sp that returns a different dataset depending on the parameter
values, it is dyanamic sp which has different field names and also the
number of fields.
Is there a way to incorporate this in reporting services, like in .Net
we can display the dataset by just binding it to the datagrid and
letting datagrid handle the rest.
Thanks,
TonyNo. RS does not work that way. It expects a consistent number/name of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107965810.679249.274270@.c13g2000cwb.googlegroups.com...
> I have a sp that returns a different dataset depending on the parameter
> values, it is dyanamic sp which has different field names and also the
> number of fields.
> Is there a way to incorporate this in reporting services, like in .Net
> we can display the dataset by just binding it to the datagrid and
> letting datagrid handle the rest.
> Thanks,
> Tony
>|||Thanks for the prompt reply Bruce, Is there any other workaround for
the issue I have any ideas or suggestions would be great.
Thanks again,
Tony|||You can have multiple datasets in a report, each of them calling the
procedure with the appropriate parameter. This does mean the SP will get
called multiple times. Then you should be able to hide the table on the
report if there is no data. I haven't tried this but I think you can. You
still might end up with some blank lines though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107967055.899883.298640@.f14g2000cwb.googlegroups.com...
> Thanks for the prompt reply Bruce, Is there any other workaround for
> the issue I have any ideas or suggestions would be great.
> Thanks again,
> Tony
>|||You could also at the end of your proc pivot (or is this really
unpivoting) the data. So for a table with structure
# keycol # col1 # col2 # col3 # col4 # ... # col<n> #
you would end up with
# keycolvalue # colname # colvalue #
this will have n rows for each row in the original table. You may want
to modify your proc to build that to begin with rather than pivot data
later.
then you could drop that into a matrix.
This would work fairly well if all variable columns are of the same
type. Otherwise you'd have to do some formatting in the stored
procedure.

How to show all tables info in Task Pad?

In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.

1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?

2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?

Thanks.Task pad should show Next and Last options in the bottom of the page.
You can also get all user table info by executing this sql..
select * from information_schema.tables where table_type like 'BASE TABLE'|||First, in TaskPad, there is no next or last button, second that line of code you gave:

select * from information_schema.tables where table_type like 'BASE TABLE'

Did not return the # of rows and KB size of all of my tables.

THis is what I am looking for.

Anyone else know?

I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.|||I'd suggest:SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS data_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid = 255 THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
, Object_Name(si.id)
FROM dbo.sysindexes AS si
GROUP BY si.id-PatP|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.|||sp_spaceused?

EDIT: Found this...

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.Change the 5s to 15s and try again.

It shows some interesting space observations, by table.

-PatP|||pat,

That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.

Is this what is expected? Is the data_mb column the table size?

Thanks for the help, it is greatly appreciated.|||Brett,

Wonderful!!!!!!!!!!

That was it!!!!!!

Thanks a million!!!!!!|||Does my total match the taskpad total?

-PatP

How to set week start number in RS

How to set week start number in RS.
I would like to use WeekdayName and Weekday RS function but it returns
wrong values (Tuesday instead of Monday)
TIA,
KamelOn 14 Lut, 16:32, "kamel" <kwic...@.gmail.com> wrote:
> How to set week start number in RS.
> I would like to use WeekdayName and Weekday RS function but it returns
> wrong values (Tuesday instead of Monday)
> TIA,
> Kamel
OK don't mind...
you can set it as attribute in Weekday function
Kamel

How to set the database to always use next increment number?

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

I think its good way if ID is incrementing by this you can come to know which record inserted when and so on ....

A good article on identity

http://msdn2.microsoft.com/en-us/library/ms971502.aspx

|||

it is possible but you have to do it yourself by switching identity insert ON and OFF in your code and taking care about inserted value. It is not recommended way because it slow down insert process and also it can destroy your related record information if you have any, If you would like to have records order information for some purposes you can always do select statement like:

select identity(int,1,1) new_ID, old_id
into #test
from yourtablename
select * from #test
order by new_ID

and next select by new_ID your result.
You can also use ROW_NUMBER:

select ROW_NUMBER() OVER(ORDER BY old_id ASC) [OrderNo] from from yourtablename

|||

Many thanks for all your responses. The reason I was thinking about doing that is because this table is for linking pages. These links will be added and deleted often..and if there is no way to keep the ID in order, I'm afraid the ID may get too big but with less than 40 or 50 actual records in the table.

|||

There is no inbuilt solution for your problem.. you need to use alternative methods..

2 of those methods are discusses here.

http://www.sqlteam.com/item.asp?ItemID=765

|||

Many thanks for the link. It looks like I could use this:

declare @.intCounter int
set @.intCounter = 0
update Yaks
SET @.intCounter = YakSequenceNumber = @.intCounter + 1

The question I have is, do I run the above code everytime when I want to update and insert? Or do I run this code at the begingin where there is no record yet in the table?

|||

I think you need to use this while you are inserting. Is this make sense

|||

Okay, so can you give me a sample code of how I would use this while inserting in C# code behind? I'm still unclear as to how I would use it. By the way, if I'm using this, do I turn of the column Identity?

|||

I will create one example for you can give it to you

|||

This one solution has one big disadvantage that it will slow down your insert so maybe you should run this one time a day(or hour) for your table instead of running it on every insert?

But solution is very nice

How to set precision of a decimal number

there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?

thks

You can change yourcolumn to numeric or decimal data type, for example, decimal(18,2) or numeric(18,2), which will return 10.33 in your case.

You can look up the difference between numeric and float data type from Books Online which covers everything you need for SQL Server.

HTH

|||thks for HTH, i have solved my problem with your method, thks again