FTS Index Size: code

显示标签为“code”的博文。显示所有博文

2012年3月27日星期二

Flattening Parent Child, an issue, please help

Hello Experts,
Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?
Please help.
Thanks.
SoumyaDip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
How do modify the code to work for any level rather than hard coding
the level up to "5"?

If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.

If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding
the level up to "5"?

>
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
>
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.|||Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. <<

>
I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

>
One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.
>
A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.
>
The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.
>
You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

Did you even look at the article I posted the link to?

What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.

It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)

I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.

What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
Did you even look at the article I posted the link to?
>
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
>
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
>
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
>
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Flattened results: How to get column names?

I read on this forum that you can get flattened results using this code:

table = New DataTable()

Using dataAdapter As AdomdDataAdapter = New AdomdDataAdapter(command)

dataAdapter.Fill(table)

End Using

When you do this, the first few columns in the datatable are actually row header values. That's exactly what I want. Correspondingly I expect the first few rows to be the column header values. But they are not. Why not? Is there any easy way to get the column header values?

I think you will find that the ColumnName property of the column contains some sort of concatenation of the column headers.

|||The column name is something like [abc].[def].&[ghi].[jkl].&[mno]. I guess I could parse this myself. So parsing this is the only way to get the column headers? That seems unreliable. For row headers I can easily find them in the body of the table, and I don't have to do any parsing.

|||

This is just how the flattened rowsets work. If you have ever used a linked server in SQL Server back to an SSAS server you will have seen similar result sets. The only other option would be to get a cellset (which is multi-dimensional) and flatten it yourself.

sql

Flatten out an XML Document (Currently using OPENXML)

I am attempting to take a column with an XML datatype and make it available for reporting with as little code as possible. Specifically, we are storing credit report info in a column that has an XML datatype. We are using the OPENXML command to navigate the XML structure:

DECLARE @.idoc int
DECLARE @.doc xml
SET @.doc = 'GET THE XML COLUMN HERE
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/XML_INTERFACE/CREDITREPORT/OBJECTS/DBCOMMCREDITSCORE',2)
WITH (
Rating varchar(10) 'DBRATING',
OverFlow xml '@.mp:xmltext')
EXEC sp_xml_removedocument @.idoc

The result will be a column that shows the Rating field and an Overflow column that shows the XML string.

I can take the above code and put it in a stored procedure to return the values in a flat format. My questions:

Dan O

1. The XQuery "nodes()" function will work for this. It is similar to OPENXML, but it is just used from within a normal SELECT statement, so you could create views on top of it.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

2. nodes() seems like the way to go, but you could also try pre-shredding the xml into a more convenient relational structure if your XML has a lot of nesting that would require many joins to reassemble.

2012年3月19日星期一

fixing orphan users

Hi All,
I'm trying to fix all the orphans users for all the databases in my sql
server 2000 using the code above, but I'm getting an error that the second
cursor already exist.
Can I do a cursor inside another one?
How can I fix this problem?
Any ideas?
Tks in advance
JFB
DECLARE @.DBName sysname
,@.DBStatus int
,@.dbid int
,@.TempDBName nvarchar(70)
SELECT @.DBName = '*'
SELECT @.TempDBName = ' '
DECLARE DBs CURSOR FOR
SELECT name, dbid, status, name
FROM master..sysdatabases
WHERE [name] <> 'tempdb'
and [name] <> 'master'
and [name] <> 'model'
FOR READ ONLY
OPEN DBs
FETCH NEXT FROM DBs INTO @.DBName, @.dbid, @.DBStatus, @.TempDBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Print @.TempDBName
DECLARE @.tempString nvarchar(255)
SELECT @.tempString = 'USE ' + @.TempDBName +'
DECLARE @.UserName nvarchar(50)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @.UserName
WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.UserName '' user name being resynced''
EXEC sp_change_users_login ''Update_one'', @.UserName, @.UserName
FETCH NEXT FROM orphanuser_cur INTO @.UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur'
EXEC (@.tempString)
FETCH NEXT FROM DBs INTO @.DBName, @.dbid, @.DBStatus, @.TempDBName
END
CLOSE DBs
DEALLOCATE DBsOn Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:

>I'm trying to fix all the orphans users for all the databases in my sql
>server 2000 using the code above, but I'm getting an error that the second
>cursor already exist.
>Can I do a cursor inside another one?
>How can I fix this problem?
>Any ideas?
Hi JFB,
The first thing to do when troubleshooting dynamic SQL is to change
EXEC (@.tempString)
to
PRINT @.tempString
and inspect the results.
If you do that, you'll instantly note that the length of your dynamic SQL
exceeds the 255 character you used in the declaration of @.tempString.
Another problem you'll find after fixing this one is here:
> PRINT @.UserName '' user name being resynced''
This should be changed to
PRINT @.UserName + '' user name being resynced''
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes Hugo, Tks for you reply and help.
I got those problems...
Rgds
JFB
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f8v9115l8464ppt54s56bo45knisli5l3u@.
4ax.com...
> On Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:
>
> Hi JFB,
> The first thing to do when troubleshooting dynamic SQL is to change
> EXEC (@.tempString)
> to
> PRINT @.tempString
> and inspect the results.
> If you do that, you'll instantly note that the length of your dynamic SQL
> exceeds the 255 character you used in the declaration of @.tempString.
> Another problem you'll find after fixing this one is here:
> This should be changed to
> PRINT @.UserName + '' user name being resynced''
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes Hugo, Tks for you reply and help.
I got those problems...
Rgds
JFB
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f8v9115l8464ppt54s56bo45knisli5l3u@.
4ax.com...
> On Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:
>
> Hi JFB,
> The first thing to do when troubleshooting dynamic SQL is to change
> EXEC (@.tempString)
> to
> PRINT @.tempString
> and inspect the results.
> If you do that, you'll instantly note that the length of your dynamic SQL
> exceeds the 255 character you used in the declaration of @.tempString.
> Another problem you'll find after fixing this one is here:
> This should be changed to
> PRINT @.UserName + '' user name being resynced''
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Fixing a slow, brute force set of SQL calls

I've got a huge inefficiency in my code that I'm trying to fix. I'm coding in VB.NET using ASP.NET and an MSSQL 2000 server.

I'm working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record's fields in the temporary table.

Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I'm dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.

Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?

Thanks in advance for whatever help you can give,
-StarwizMy suggestions would be to use this sort of SQL command:


DELETE
FROM
 myTable
INNER JOIN
 #myTempTable ON #myTempTable.column1 = myTable.column1 <etc>

Terri|||Wouldn't this delete the records from the temporary table, too?|||No, the FROM clause specifies the table to use for the DELETE statement. Only the records from myTable matching the JOIN condition will be deleted.

Terri|||Okay then...but I can't even run it to see, since I get an error:

With the statement:
Delete from PPC inner join PPCTemp on PPC.searchengine = PPCTemp.searchengine and PPC.[date] = PPCTemp.[date] and PPC.keyword = PPCTemp.keyword

I get the error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'inner'.

Any ideas?|||That's because I gave you the wrong syntax and somewhat incorrect information ;-) The table name also has to follow the DELETE statement, and the table name following the DELETE is the one from which records are deleted:


Delete PPC from PPC inner join PPCTemp on PPC.searchengine = PPCTemp.searchengine and PPC.[date] = PPCTemp.[date] and PPC.keyword = PPCTemp.keyword

You should check out the DELETE topic in SQL Server Books Online for more background information on this topic.

Terri|||With the right syntax, it works great (lol)...a million times faster, too!

Thanks a lot.

2012年2月26日星期日

first day of previous month date parameter

Hi,
Hope somebody can help.
I am looking for the code to place in the default value of a parameter wich
will give me the previous months first days date (i.e. last month would be
01/03/2005).
I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
so something similar to this would be great.
Thanks in advance.
Paul=DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
Month(DateAdd(DateInterval.Month, -1, Now)), 1)
Charles Kangai, MCDBA, MCT
"pcalv" wrote:
> Hi,
> Hope somebody can help.
> I am looking for the code to place in the default value of a parameter wich
> will give me the previous months first days date (i.e. last month would be
> 01/03/2005).
> I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> so something similar to this would be great.
> Thanks in advance.
> Paul|||That worked great, thanks for your help.
Paul
"Charles Kangai" wrote:
> =DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
> Month(DateAdd(DateInterval.Month, -1, Now)), 1)
> Charles Kangai, MCDBA, MCT
> "pcalv" wrote:
> > Hi,
> >
> > Hope somebody can help.
> >
> > I am looking for the code to place in the default value of a parameter wich
> > will give me the previous months first days date (i.e. last month would be
> > 01/03/2005).
> >
> > I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> > so something similar to this would be great.
> >
> > Thanks in advance.
> >
> > Paul

First Date in the Month

I have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :

Table A :
Code as nvarchar
Ammount1 as money
Date as datetime -> transaction date
primary key are Code and Date1

Table B :
Code as nvarchar
Ammount2 as money
Date as datetime -> transaction date
primary key are Code and Date2

I like joining this table and create table/view that result :

Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date

The parameter only for @.Date.

First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.

Anybody help me ?
Thx a lot.
ad1k4r4first date of the month for getDate...

select cast('01/' + '0'+cast(month(getDate()) as varchar) + '/' + cast(year(getDate()) as varchar) as datetime)

replace getDate with @.Date and you should be right... I think...|||I like this method because it is more concise:

select convert(char(7), getdate(), 120) + '-01'|||hehehe I knew there must be a better way,... that's why I'm not a db developer. ;)

Is there a way to do it with a 3 character month?? I perfer 3 char month names because then you don't get any confusion with date time settings etc...|||I'm sure there is. Look up the formatting parameters for the CONVERT function. You can use the technique of converting the result to a shortened CHAR value whenever the results of the CONVERT function are consistent in the length of their output.

I understand you desire to use three character month names to make the result more easily readable by carbon based processors, but formatting style "120" is more universally recognized by silicon based processors. Personally, I would refer any more complex formatting to the user interface.

The other big advantage of style "120" is that the result: YYYY-MM-DD sorts correctly even as a string.|||actually the reason I like the 3 char month is because you can never rely on people to set up servers or accounts with the correct language settings... and depending on what settings you have 01-04-2004 could be recorded in the system as 1 April or 4 Jan where as with either set up 1-Apr-2004 is always 1 April.

The database will then hold the value in whatever format it likes and reformating it on the way out is very much the role of whatever interface you want to implement. :)|||Ok, I'd start with:CREATE PROCEDURE dbo.patp
@.pdAsof DATETIME
AS

DECLARE @.dFirst DATETIME -- First of the month

SELECT @.dFirst = Convert(CHAR(8), @.pdAsOf, 121) + '01'

SELECT
Coalesce(a.code, b.code) AS code
, a.amount1
, b.amount2
, (SELECT Sum(c.amount1)
FROM tableA AS c
WHERE c.code = Coalesce(a.code, b.code)
AND c.[date] = DateAdd(year, -1, Coalesce(a.[date], b.[date])) AS amount1_last_year
, (SELECT Sum(d.amount1)
FROM tableA AS d
WHERE d.code = Coalesce(a.code, b.code)
AND d.date BETWEEN @.dFirst AND
Coalesce(a.[date], b.[date])) AS amount1_mtd
FROM tableA AS a
FULL OUTER JOIN tableB AS b
ON (b.code = a.code
AND b.[date] = a.[date])

RETURNThat's only part of the solution, but let's see if that part does what you want before we get too crazy!

-PatP

First CTP and spatial

I just download and install first CTP. When I execute query

Code Snippet

select * from sys.types

i don't see any spatial data types?

May be i do something wrong? Or spatial unsupported in first CTP?

Spatial data feature is not included in the June CTP. The demo at Tech-Ed is based on a non-CTP build.

Hope this helps!

Changqing|||

>>Or spatial unsupported in first CTP?

No, this is not available in the first CTP. For a list of features in the CTP, open Books Online and search for " What's New". There's a What's New (Database Engine), What's New (Reporting Services), etc. topic for each SQL Server component that describes the new features available in the CTP with links to the relevant topics.

Regards,

Gail

|||Can you give us a time estimate when this WILL be included ;-)|||I've seen a similar question here already, and the correct answer is: When it's finished. Microsoft develop using a different strategy now, they include features when they seem to be ready for the market, so as far as I've understood the situation no new features are officially scheduled for a specific release or a specific date.|||

The spatial functionality I demo'ed at TechEd is not part of CTP3 or the upcoming CTP4. We cannot give an official delivery date yet, but we hope to have it available after CTP4. ;-)

Best regards

Michael

First CTP and spatial

I just download and install first CTP. When I execute query

Code Snippet

select * from sys.types

i don't see any spatial data types?

May be i do something wrong? Or spatial unsupported in first CTP?

Spatial data feature is not included in the June CTP. The demo at Tech-Ed is based on a non-CTP build.

Hope this helps!

Changqing|||

>>Or spatial unsupported in first CTP?

Regards,

Gail

The spatial functionality I demo'ed at TechEd is not part of CTP3 or the upcoming CTP4. We cannot give an official delivery date yet, but we hope to have it available after CTP4. ;-)

Best regards

Michael

订阅：博文 (Atom)

2012年3月27日星期二

Flattening Parent Child, an issue, please help

Flattened results: How to get column names?

Flatten out an XML Document (Currently using OPENXML)

2012年3月19日星期一

fixing orphan users

Fixing a slow, brute force set of SQL calls

2012年2月26日星期日

first day of previous month date parameter

First Date in the Month

First Date in the Month

First CTP and spatial

First CTP and spatial

FTS Index Size

Blog Archive

About Me