Homework question

How do I create a database, and what's the best color to choose?I think mauve has the most RAM.|||I think mauve has the most RAM.

Thanks, I think that's what I read

So what's with all this join stuff?

Do I need more than 1 table?|||Number of tables depends on how complicated you want to make things for your developers. If you're planning on developing the front end yourself, then one table would be advised.

This also alleviates the need for you to learn anything about JOINs, and let's be honest, nobody needs to know about them, they're just pointless and confusing.

Lump it all in one table and you remove the complexity!

Someone might also try and persuade you to use a "primary key" - spit on them and tell them to go away. Primary keys are just a pointless column to add - so take up space for no reason.|||Oh and if you think mauve is good, you should see the new burgundy models!|||Oh and if you think mauve is good, you should see the new burgundy models!

Well how often do they have new releases?

I would hate to be behind in the latest color

Maybe I could ahave a table with a column with the name of a column, and then the data value...should I even need a coulumn for the table name?|||database shmatabase. just persist all of your data in text files. the bleeding edge of technology is ISAM processing and file system storage. I think they call it XML.|||Cool, I'll do that.

How do you spell XML?

And why is it that GetDate() still leaves you at home on a Saturday night?|||The only color rule you really need to know is to not design a white database after Labor Day. Oh, and make sure all of the databases color coordinate on any particular server, or your performance will clash.

Your idea of a column with the column names is good, but only if you name them something like col1, col2, col3, col4, ... You really should name them Value1, Value2, Value3, ... because after all, you are storing Values in the columns, and not columns.|||This is all the DDL you will ever need:
CREATE TABLE [DatabaseTable]
([PrimaryKey] [int] NOT NULL CONSTRAINT [DF_DatabaseTable_PrimaryKey] DEFAULT (1) PRIMARY KEY CLUSTERED,
[AllTheData] [xml] NULL)
GO
ALTER TABLE [dbo].[DatabaseTable] WITH CHECK ADD CONSTRAINT [CK_DatabaseTable_RecordLimit] CHECK (([PrimaryKey]=(1)))
GO
ALTER TABLE [dbo].[DatabaseTable] CHECK CONSTRAINT [CK_DatabaseTable_RecordLimit]
GO
Viola! Instant Universal Database Application!|||Excel!!!!!!!|||the solution to all database problems...

DROP DATABASE <EnterDatabaseName>

or xp_cmdshell 'FORMAT C:'

xp_cmdshell 'FORMAT D:'

xp_cmdshell 'FORMAT E:' etc... until you run out of letters|||the solution to all database problems...

DROP DATABASE <EnterDatabaseName>

or xp_cmdshell 'FORMAT C:'

xp_cmdshell 'FORMAT D:'

xp_cmdshell 'FORMAT E:' etc... until you run out of letters

I see, you could have a letters table I gues, then use a cursor, and WHILE @.@.FETCH_STATUS = 0, you could go until everything is just hunkey-dorey

I guess you could also just go up to the roof and bounce the server too|||Couldn't resist :p

DECLARE @.Start int
DECLARE @.End int
DECLARE @.SQL varchar(8000) --that should cover it!

SET @.Start = ASCII('C')
SET @.End = ASCII ('Z') + 1

WHILE @.Start < @.End BEGIN --C to Z
SET @.SQL = ''
SET @.SQL = 'xp_cmdshell ''FORMAT ' + CHAR(@.Start) + ''''
--EXEC
SET @.Start = @.Start + 1
END|||Do I need more than 1 table?
Yes, you need 42|||Yes, you need 42

Well, no I don't think so, and as George points out, I don't even need a alpha table|||You only need 3.
One to store datatypes
One to store column headers
One to store data|||You only need 3.
One to store datatypes
One to store column headers
One to store data

Isn't that 1 table and 3 columns?|||What was I thinking!
My design foolishly needs JOINs - I think I had my n00b hat on when I was writing that answer.|||Yes, you need 42...and a towel.

So Long, and Thanks For All the Fish!

-PatP|||Damn, this thread is not at all what I expected. I was looking for help with my Spanish homework.|||According to freetranslation.com winky is still winky in Spanish. I imagine that will cover all your requirements.|||donde está winky?|||No, no, no. Donde está LA winky.|||wouldn't that be El Winkie?|||Forgive me - I don't speak spanish but does that mean "where is the winky"? I would imagine Paul is more likely to ask "Please can I put my w....". You get the idea.|||Including the word "winky" I now know about 18 Spanish words... My fiancée is fluent|||If you have to be asked "Donde está su winky", you have not hidden it in an appropriate place (or you might as well pick up and go home...)

Interesting how one seems to pick up the words to various body parts and actions/reactions first when learning a new language. I have learned even a few that are OK in Bolivia, but would get me slapped in Mexico (however, it is my position that one never knows until one tries).

One of my favorites so far is "Pedorro" or "Pedorra" ~ "one who farts a lot".

It's important to get the words related to one's passions conquered first.sql

Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert

Ho to create custom trace events from TSQL?

Hi,

Is there a way to create custom trace events from TSQL code?

Because, ideally, I would like to be able to read those traces from the SQL Profiler tool (or the custom server side trace stored procedures). However, I can't find any documentation about that. Is it possible? There seem to be a User-Configurable trace category.

If this is not possible, what would be the best (easy, fast, ...) way to create an equivalent feature?

Thanks!

You could use sp_trace_create, sp_trace_setevent, sp_trace_setfilter stored procedures for it.|||

Hi Konstantin,

But, if I understand correctly, this is for *recording* a trace event, not for creating it?

|||

Oh, sorry, my english.

May be this will be helpful:

sp_trace_generateevent [ @.eventid = ] event_id [ , [ @.userinfo = ] 'user_info' ] [ , [ @.userdata = ] user_data ]|||

Ah I didn't see this one

thanks!

HL7 to flat file

Does anyone have logic to take an hl7 file format and create a flat file.
I am having a bit of difficulty...........
Any help would be a lifesaver...
Thanks
Greg
On Thu, 5 May 2005 07:46:02 -0700, Greg wrote:

>Does anyone have logic to take an hl7 file format and create a flat file.
>I am having a bit of difficulty...........
>Any help would be a lifesaver...
>Thanks
>Greg
Hi Greg,
I'm afraid you've chosen the wrong group. This group is a support group
for Microsoft English Query, a company product of Microsoft's SQL Server
database. I've never heard of the hl7 file format, but a quick google
indicates that this is has something to do with health services. Anyway,
converting one file format to another is not a typical database task.
I'd look into writing a batch program in a programming language of your
choice, googling for an of-the-shelf conversion utility, or finding an
other group that is more suitable to this question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

HistoryID Problem?

Hi I'm trying to use SOAP in a VAB Access 2003 Project. I can connect to the Reporting Services an Create fr example Folders. But if I want to Render a Report i am Getting a Problem.
Error: -2147221504
For ' snapshotID ' indicated parameter value does not correspond to the type of parameter.
I passed an empty String for the optional Value HistoryID.
I dont know where the problem is.
Can anybody help me ?
CODE:
Dim ReportName As String
Dim RenderFormat As String
Dim HistoryID As Variant
Dim DeviceInfo As String
Dim Parameters(2) As struct_ParameterValue
Dim Credentials() As struct_DataSourceCredential
Dim ShowHideToggle As String
Dim resultFile() As Byte
Dim resultEncoding As String
Dim resultMimeType As String
Dim resultParametersUsed() As struct_ParameterValue
Dim resultWarnings() As struct_Warning
Dim resultStreamIds() As String
Set RS = New clsws_ReportingService
ReportName = "/SampleReports/Employee Sales Summary"
RenderFormat = "mhmtl"
HistoryID = 0
DeviceInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
ShowHideToggle = ""
Set Parameters(0) = New struct_ParameterValue
Parameters(0).Name = "EmpID"
Parameters(0).Value = "38"
Set Parameters(1) = New struct_ParameterValue
Parameters(1).Name = "ReportMonth"
Parameters(1).Value = "6" ' June
Set Parameters(2) = New struct_ParameterValue
Parameters(2).Name = "ReportYear"
Parameters(2).Value = "2004"
resultFile = RS.wsm_Render( _
ReportName, _
RenderFormat, _
nil, _
DeviceInfo, _
Parameters, _
Credentials, _
ShowHideToggle, _
resultEncoding, _
resultMimeType, _
resultParametersUsed, _
resultWarnings, _
resultStreamIds)
thx for your helpSorry for double posting... but there was an error maeesage so i tryed again.sql

History table

Hi I wanted to create a history table, but it doesn′t really works:
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
Helpdesk.Computer_Idn,
Helpdesk.DeviceName,
Helpdesk.InsertDate,
Helpdesk.ProblemTitle,
Helpdesk.Duration,
Helpdesk.ProblemDetails,
Helpdesk.ProblemSolution,
Helpdesk.CallDispatcher,
Helpdesk.Responsible,
Helpdesk.Status
from Helpdesk
With my trigger all values will be written in the helpdesk_history table.
But I want that only new oder changed collums will should be insert in the
history table.
Any idea ?
Thx
WolfgangI think this is what you want
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
d.Computer_Idn,
d.DeviceName,
d.InsertDate, -- this would probably be getdate() if you want
today's date
d.ProblemTitle,
d.Duration,
d.ProblemDetails,
d.ProblemSolution,
d.CallDispatcher,
d.Responsible,
d.Status
from deleted d
http://sqlservercode.blogspot.com/|||Hi in case of update this works perfect,
but if I insert a new record the complete table helpdesk will be copied to
the helpdesk_history table.
Any idea?
thx
Wolfgang
"SQL" wrote:

> I think this is what you want
> CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
> FOR INSERT, UPDATE AS
> INSERT into dbo.Helpdesk_History
>
> (
> Helpdesk_History.Computer_Idn,
> Helpdesk_History.DeviceName,
> Helpdesk_History.InsertDate,
> Helpdesk_History.ProblemTitle,
> Helpdesk_History.Duration,
> Helpdesk_History.ProblemDetails,
> Helpdesk_History.ProblemSolution,
> Helpdesk_History.CallDispatcher,
> Helpdesk_History.Responsible,
> Helpdesk_History.Status
> )
> SELECT
>
> d.Computer_Idn,
> d.DeviceName,
> d.InsertDate, -- this would probably be getdate() if you want
> today's date
> d.ProblemTitle,
> d.Duration,
> d.ProblemDetails,
> d.ProblemSolution,
> d.CallDispatcher,
> d.Responsible,
> d.Status
>
> from deleted d
>
> http://sqlservercode.blogspot.com/
>|||When you do an insert the deleted table should not be available
Is there possible another trigger on the table that updates after
inserting?
http://sqlservercode.blogspot.com/|||Hi Wolfgang !
That would mean to build a block of code, comparing each column to the
new one, and keeping the information that this value was changed and
then issueing something like a dynamic sql statement, to only store the
information that were changed.
HTH, Jens Suessmeyer.|||Hi,
yes there is another trigger:
CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
FOR INSERT
AS
UPDATE Helpdesk SET Computer_idn =
(Select Computer.Computer_idn
from Computer
where
computer.devicename = helpdesk.devicename
and computer.SWLastScanDate =
(SELECT MAX(computer.SWLastScanDate) FROM computer))
This trigger updates the Computer_Idn ...and with this trigger it updates
the whole table. How can I change this trigger that only the Computer_Idn of
the new inserted value will be updated ?
Thanks
Wolfgang
"SQL" wrote:

> When you do an insert the deleted table should not be available
> Is there possible another trigger on the table that updates after
> inserting?
> http://sqlservercode.blogspot.com/
>|||On Wed, 9 Nov 2005 00:10:11 -0800, Wolfgang Dausend wrote:

>Hi,
>yes there is another trigger:
>
>CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
>FOR INSERT
>AS
>UPDATE Helpdesk SET Computer_idn =
>(Select Computer.Computer_idn
>from Computer
>where
>computer.devicename = helpdesk.devicename
>and computer.SWLastScanDate =
>(SELECT MAX(computer.SWLastScanDate) FROM computer))
>
>This trigger updates the Computer_Idn ...and with this trigger it updates
>the whole table. How can I change this trigger that only the Computer_Idn o
f
>the new inserted value will be updated ?
Hi Wolfgang,
Add
WHERE EXISTS
(SELECT *
FROM inserted
WHERE inserted.WhateverYourKeyIs = Helpdesk.WhateverYourKeyIs)
at the end of the UPDATE statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Highlight words from search expression

Hi,
I want to create a search engine which will display a short text
description in the surrounding of the searched words. The problem is I
can't find the exact words in my search phrase in the results because
of stemming. Is there any way I can which words in the results were
responsible finding this particular result due to stemming?
Thanks,
Lior
Microsoft's stemming algorithm is based on the Ported Stemming algorithm.
You can roll your own implementation of this, or something like this
http://www.indexserverfaq.com/sqlhithighlighting.htm
"Lior Shorshi" <lishorsh@.gmail.com> wrote in message
news:1177242161.666784.11460@.y5g2000hsa.googlegrou ps.com...
> Hi,
> I want to create a search engine which will display a short text
> description in the surrounding of the searched words. The problem is I
> can't find the exact words in my search phrase in the results because
> of stemming. Is there any way I can which words in the results were
> responsible finding this particular result due to stemming?
> Thanks,
> Lior
>

Highlight Keywords

I am using asp.net (C#) to create full text searching using SQL Server
2005 in our application. Everything is working fine, but I am trying
to highlight the resultset keywords from the full text results. Can
sql server provide you with a way to highlight the keywords from the
full text search?
I was able to kind of hack it by programatically replacing my keyword
search terms with the sql server resultset. However, when I do certain
searches, like "Frank's", sql server will return Frank using my
CONTAINS query, and I want the other results like "Frank" to be
highlighted too. Anyone know a good way to go about doing this?
Thanks
You have to implement your own version of Porter stemmer algorithm for this.
You can use Indexing Services for this, here is a link describing how to do
this.
http://www.indexserverfaq.com/sqlhithighlighting.htm
"guate911" <guatemala911@.gmail.com> wrote in message
news:1179417689.771131.84700@.k79g2000hse.googlegro ups.com...
>I am using asp.net (C#) to create full text searching using SQL Server
> 2005 in our application. Everything is working fine, but I am trying
> to highlight the resultset keywords from the full text results. Can
> sql server provide you with a way to highlight the keywords from the
> full text search?
> I was able to kind of hack it by programatically replacing my keyword
> search terms with the sql server resultset. However, when I do certain
> searches, like "Frank's", sql server will return Frank using my
> CONTAINS query, and I want the other results like "Frank" to be
> highlighted too. Anyone know a good way to go about doing this?
> Thanks
>

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95
SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>
|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;[vbcol=seagreen]
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500
>
|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did[vbcol=seagreen]
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do
>

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500[vbcol=seagreen]
>|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do[vbcol=seagreen]
>sql

higher compatibility level already exists

I am attempting to create a merge publication from SQL 2005 to SQL
2000. I am checking the 2005 & 2000 boxes for compatability, but
receive this message.
Publication 'edi' cannot be added to database 'EDI', because a
publication with a higher compatibility level already exists. All
merge publications in a database must have the same compatibiliy
level.
Publication 'edi' does not exist.
Changed database context to 'EDI'. (Microsoft SQL Server, Error:
21528)
I am creating the publications on the colo server (2005). The
publications will be filtered (one for each location). The main
office & all locations will be subscribers. All stores are running
SQL 2000 & the colo & main office SQL 2005.
AHIA,
Larry...
Hi Larry
I think what has happened here is that you have already created a
publication with a higher compatibility level in this database (i.e. SQL
2005). You can no longer create publications for a lower compatibility level
now.
IIRC this depends on specific settings, and it could be a single article, or
setting - which is preventing down-level compatibility publications.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1185461541.304316.20350@.l70g2000hse.googlegro ups.com...
>I am attempting to create a merge publication from SQL 2005 to SQL
> 2000. I am checking the 2005 & 2000 boxes for compatability, but
> receive this message.
> Publication 'edi' cannot be added to database 'EDI', because a
> publication with a higher compatibility level already exists. All
> merge publications in a database must have the same compatibiliy
> level.
> Publication 'edi' does not exist.
> Changed database context to 'EDI'. (Microsoft SQL Server, Error:
> 21528)
> I am creating the publications on the colo server (2005). The
> publications will be filtered (one for each location). The main
> office & all locations will be subscribers. All stores are running
> SQL 2000 & the colo & main office SQL 2005.
> AHIA,
> Larry...
>
|||Thanks Hilary...
sql

Hierarchy Problem

I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).

Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.

I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:

Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)

Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION

OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID

OPEN cursor2

FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END

ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END

CLOSE cursor1
DEALLOCATE cursor1

Hierarchy

Hello all,
I have the following table structure (legacy, not my design :-D ):
CREATE TABLE [dbo].[hierarchy]
([idproduct] [int] NOT NULL ,
[name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[idlevel0] [int] NOT NULL ,
[name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel1] [int] NULL ,
[name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel2] [int] NULL ,
[name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
ON [PRIMARY]
GO
Some data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
idproduct field: id of a product
name= name o a product
idlevel0 = id of the root hierarchy
name0 = name of the root hierarchy
idlevel1 = id of the second node in the hierarchy
name1 = name of the second node in the hierarchy
idlevel2 = id of the third node in the hierarchy
name2 = name of the third node in the hierarchy
basically, there can be only three levels in the tree but as much brances as
the number of records are.
what I want is to reformat the hierarchy this way:
nodeid / nodename/ parentid
any hints on this?
Kind regards,
TudorSorry, pressed send before i actually finished:
So, further data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1001, 'Product2', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1003, 'Product3', 1, 'root node', 10,
'Intermediate10', 101, 'Intremediate201')
insert into hierarchy values (1004, 'Product4', 1, 'root node', 11,
'Intermediate11', 100, 'Intremediate202')
insert into hierarchy values (1005, 'Product5', 1, 'root node', 12,
'Intermediate12', 102, 'Intremediate202')
I would like the data to be stored as this:
nodeid nodename parentid
1 rootnode
2 intermediate10 1
3 intermediate200 2
4 intermediate200 2
5 intermediate11 1
etc...
Thanks,
Tudor
"Tudor" <tudor@.hopscotch.com> wrote in message
news:eeN5qxbQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have the following table structure (legacy, not my design :-D ):
> CREATE TABLE [dbo].[hierarchy]
> ([idproduct] [int] NOT NULL ,
> [name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idlevel0] [int] NOT NULL ,
> [name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel1] [int] NULL ,
> [name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel2] [int] NULL ,
> [name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> )
> ON [PRIMARY]
> GO
> Some data:
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
>
> idproduct field: id of a product
> name= name o a product
> idlevel0 = id of the root hierarchy
> name0 = name of the root hierarchy
> idlevel1 = id of the second node in the hierarchy
> name1 = name of the second node in the hierarchy
> idlevel2 = id of the third node in the hierarchy
> name2 = name of the third node in the hierarchy
> basically, there can be only three levels in the tree but as much brances
> as the number of records are.
> what I want is to reformat the hierarchy this way:
> nodeid / nodename/ parentid
> any hints on this?
> Kind regards,
> Tudor
>
>
>

hierarchy

hi All,

i am facing a problem while creating a simple product hierarchy in SSAS.

i am able to create the hierarchy and deploy the cube,but in the browser while analysing i drag and drop the hierarchy .but only the key are displayed not the caption.

Please do help me with this

thanks in advance

Lalitha

You need to post this to the SQL Server Analysis Services forum.|||

There shouldn't be any secrets to getting this to work, so I would just double check what you've done. Check the definition of the hierarchy/level/attribute and ensure that you are browsing the correct object. If you are still having troubles, please provide more info such as what version of SSAS you are using (2000 or 2005), what browser you are using, and more details about just what the problematic hierarchy looks like (key columns, name columns, etc).

Hope this helps

|||

hi,

i am using SSAS 2005 . and i am using the browser tab which is available when the cube is proccessed.

i'll give the steps what i have done

i created a dimension of product consisting of attribute

productid

productname

producttypeid

priducttypename

productcategoryid

productcategoryname

then i am creating the heirarchy in the dimension Structure tab available.

the level were automatically identified and there was no caption avaliable

i draged and dropped the captions to the appropriate levels

and the caption didnot appear while browsing the cube..

This what is did

please do help me as soon as possible

Thanks in advances

Lalitha

|||

What exactly are you doing when you "drag and drop captions" to appropriate levels? I'm guessing you're dragging attributes onto the hierahcy panel and dropping them under levels which creates attribute relationships (and thus Member Properties), but is not related to the caption of the level.

Levels are based on attributes and get their members from the values of the attribute on which they are based. Each attribute contains a KeyColumns property that determines how members are uniquely identified by the server and a NameColumn property that determines how the members are displayed. If the NameColumn is not specified, then the KeyColumn will also be used as the name. (If multiple key columns are specified, then a name column must be specified.) (In addition to names, translations can also be specified in the translations tab but as long as you've not changed anything in the translations tab it should have no impact.)

|||

hi Matt Carroll

Thanks , I solved it. You'r information help me a lot

Regards

Lalitha

Hierarchical table (count)

Hello

for MS SQL 2000 i am having :

CREATE TABLE [dbo].[Items](
[id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_ItemsSup] [int] NULL,
[Name] [nvarchar] (100) NOT NULL,
[SubItems][int] DEFAULT (0)
) ON [PRIMARY]

with :
UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1
I get how many subItems has Item = 1

how can I update the Column SubItems (for each row) ?
to get the total of subItems for each Item ?

thank youupdate Items set subitems = (select count(*) from items where Id_ItemsSup = A.Id_ItemsSup) from Items A|||it works fine

thank you

hierarchical selection within a select statment

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))

INSERT INTO RS_A
VALUES ('S', 'shakespeare')
INSERT INTO RS_A
VALUES ('B', 'shakespeare')
INSERT INTO RS_A
VALUES ('P', 'shakespeare')

INSERT INTO RS_A
VALUES ('S', 'milton')
INSERT INTO RS_A
VALUES ('P', 'milton')
INSERT INTO RS_A
VALUES ('B', 'shelley')

INSERT INTO RS_A
VALUES ('B', 'kafka')
INSERT INTO RS_A
VALUES ('S', 'kafka')

INSERT INTO RS_A
VALUES ('P', 'tennyson')

SELECT * FROM RS_A

Now i need a select which selects based on hierarchy

if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row

So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson

Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

If any of you can help me with this right away, its is greatly
appreciated
Thanks in advance(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

SELECT * FROM RS_A
>
Now i need a select which selects based on hierarchy
>
if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row
>
So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson
>
Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

First translate the codes to numeric values with CASE, you can take
MIN, and then translate back:

SELECT CASE minval WHEN 1 THEN 'S' WHEN 2 THEN 'B' WHEN 3 THEN 'P' END,
ColB
FROM (SELECT ColB, minval = MIN(CASE ColA
WHEN 'S' THEN 1
WHEN 'B' THEN 2
WHEN 'P' THEN 3
END)
FROM RS_A
GROUP BY ColB) AS x

If there are many possible values for ColA, it would be better to
put the mapping in a table and then join with that table.

--
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|||Ingenius :
Thank you for the above and the RETURN was what was missing after
RAISEERROR

-RS

Hierarchical parameters

Hi all,
does anyone know if it is possible within sql server 2000 to create
dependent parameters ? Eg : i have one parameter "City". If a user selects a
ceratin city, i would want to see the other parameter "Street" being filled
with alle the available streets...
I thought i read somewhere that this is possible in sql server 2005, but I'm
not quite sure...
Thanks!Yes you can have cascading parameters... TO do this
1. Create a dataset which populates the second parameter...ie
select Streetname, streetid from mytable where City = @.city
Where city is the name of your city parameter...
2. Then in the Report Parameters window create the Street parameter, and
have it use the new dataset
3. Ensure the Street parameter comes AFTER the CITY parameter in the
parameter list...
Then you can use both City and Streetname in your dataset query to populate
the report...
Hope this helps
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Koen" wrote:
> Hi all,
> does anyone know if it is possible within sql server 2000 to create
> dependent parameters ? Eg : i have one parameter "City". If a user selects a
> ceratin city, i would want to see the other parameter "Street" being filled
> with alle the available streets...
> I thought i read somewhere that this is possible in sql server 2005, but I'm
> not quite sure...
> Thanks!|||Thanks Wayne,
gonna try it out later this day...
Koen
"Wayne Snyder" wrote:
> Yes you can have cascading parameters... TO do this
> 1. Create a dataset which populates the second parameter...ie
> select Streetname, streetid from mytable where City = @.city
> Where city is the name of your city parameter...
> 2. Then in the Report Parameters window create the Street parameter, and
> have it use the new dataset
> 3. Ensure the Street parameter comes AFTER the CITY parameter in the
> parameter list...
> Then you can use both City and Streetname in your dataset query to populate
> the report...
> Hope this helps
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Koen" wrote:
> > Hi all,
> >
> > does anyone know if it is possible within sql server 2000 to create
> > dependent parameters ? Eg : i have one parameter "City". If a user selects a
> > ceratin city, i would want to see the other parameter "Street" being filled
> > with alle the available streets...
> >
> > I thought i read somewhere that this is possible in sql server 2005, but I'm
> > not quite sure...
> >
> > Thanks!

Hierarchical data in result set

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

I didnot quite understand your question!

Is the "HierarchicalLevel" value calculated ? or it was stored in the DB?

IF it is the first case:

you'd better get the date first in the application layer,and then use recursion ways to calculated the "HierarchicalLevel" value .

it is easy to calculate the value in the application layer

|||

Shieldy, the HierarchicalLevel is a number to indicate in which node level the registry is.

So, imagine a family tree.

Grandfather __ Uncle
|
Father Daughter
|
Son

Grandfather is the level 0, the most high level on this hierarchy. Father and Uncle are the level 1, because they are under the level 0, its parent. And Son and Daughter are the level 2.

Just a detail, if I search for relationships about father, this changes.

Father -- Daughter
|
Son

In this case Father is the level 0, Daughter and Son are the level 1.

If you don't understand this examples, I can try explain it better.

|||

Hi Juliano,

The sequence of code that does what you need is inserted bellow. I wrote it based on the "The Guru's Guide To Transact-SQL" book.

DECLARE @.Categories TABLE (CategoryID Int, CategoryName Varchar(20), CategoryFather Int)

INSERT INTO @.Categories
VALUES (1, 'Video', 0)
INSERT INTO @.Categories
VALUES (2, 'DivX', 1)
INSERT INTO @.Categories
VALUES (3, 'WMV', 1)
INSERT INTO @.Categories
VALUES (4, 'Programming', 0)
INSERT INTO @.Categories
VALUES (5, 'Web', 4)
INSERT INTO @.Categories
VALUES (6, 'ASP.Net', 5)
INSERT INTO @.Categories
VALUES (7, 'ColdFusion', 5)

DECLARE @.TempCategories TABLE (HierarchicalLevel Int, CategoryID Int, CategoryFather Int)
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT 1, CategoryID, CategoryFather
FROM @.Categories

WHILE (@.@.RowCount > 0)
BEGIN
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT DISTINCT c1.HierarchicalLevel+1, c2.CategoryID, c1.CategoryFather
FROM @.TempCategories c1
INNER JOIN @.TempCategories c2 ON c1.CategoryID = c2.CategoryFather
WHERE c1.HierarchicalLevel=(SELECT MAX(HierarchicalLevel) FROM @.TempCategories)
AND c1.CategoryFather<>c2.CategoryID
END

SELECT t.CategoryID, c.CategoryName, c.CategoryFather, HierarchicalLevel = MAX(t.HierarchicalLevel)-1
FROM @.TempCategories t
INNER JOIN @.Categories c ON t.CategoryID=c.CategoryID
GROUP BY t.CategoryID, c.CategoryName, c.CategoryFather

Hope it helps!

|||

hi there

it looks like you need

to implement this using a self join.

Using Self-Joins

A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same ZIP Code area.

Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- - -- Dean Straight Dean Straight Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Dean Straight Dirk Stringer Dirk Stringer Dirk Stringer Livia Karsen Livia Karsen Dean Straight Livia Karsen Dirk Stringer Livia Karsen Livia Karsen Marjorie Green Marjorie Green Stearns MacFeather Stearns MacFeather (11 row(s) affected) 

To eliminate the rows in the results in which the authors match themselves and to eliminate rows that are identical, except the order of the authors is reversed, make this change to the Transact-SQL self-join query:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- -- -- Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Livia Karsen (3 row(s) affected) 

It is now clear that Dean Straight, Dirk Stringer, and Livia Karsen all have the same ZIP Code and live in Oakland, California.

Hiding field by asking if another field is empty

Hello,
I need to hide a field (or retangle) by asking if another field in the
report, like text field (or some other entity i can create on the fly,
that is not a Data field)
for eg:
I create a text field in a retangle.
The text field include expression that determine what data to display,
if any.
I want to hide the retangle if the text field if empty.
So, can i use in an expression somthing other then a Data field or a
Parameter?
Thanks in advance,
Eitan perryYou can use ReportItems("itemname").Value
But I'm not sure if it would work to hide the parent (rectangle) if the
child (text box) is empty. You'll just have to try, but I'd rather just use
the expression used in your text box on the visibility property of the
rectangle. If the textbox value = fields!Field1.Value & fields!Field2.Value,
you should be able to do something like
=IIF(IsNothing(fields!Field1.Value & fields!Field2.Value), true, false)
in the visibility parameter.
Kaisa M. Lindahl Lervik
"Perry.Eitan" <perry.eitan@.gmail.com> wrote in message
news:1165295155.869671.109210@.j44g2000cwa.googlegroups.com...
> Hello,
> I need to hide a field (or retangle) by asking if another field in the
> report, like text field (or some other entity i can create on the fly,
> that is not a Data field)
>
> for eg:
> I create a text field in a retangle.
> The text field include expression that determine what data to display,
> if any.
> I want to hide the retangle if the text field if empty.
>
> So, can i use in an expression somthing other then a Data field or a
> Parameter?
>
> Thanks in advance,
> Eitan perry
>|||Hey,
I need that the text that follows the textbox with visibility=false to
shift up. How can one go about it?
Regards,
Ankur
Kaisa M. Lindahl Lervik wrote:
> You can use ReportItems("itemname").Value
> But I'm not sure if it would work to hide the parent (rectangle) if the
> child (text box) is empty. You'll just have to try, but I'd rather just use
> the expression used in your text box on the visibility property of the
> rectangle. If the textbox value = fields!Field1.Value & fields!Field2.Value,
> you should be able to do something like
> =IIF(IsNothing(fields!Field1.Value & fields!Field2.Value), true, false)
> in the visibility parameter.
> Kaisa M. Lindahl Lervik
>
> "Perry.Eitan" <perry.eitan@.gmail.com> wrote in message
> news:1165295155.869671.109210@.j44g2000cwa.googlegroups.com...
> > Hello,
> >
> > I need to hide a field (or retangle) by asking if another field in the
> > report, like text field (or some other entity i can create on the fly,
> > that is not a Data field)
> >
> >
> > for eg:
> > I create a text field in a retangle.
> > The text field include expression that determine what data to display,
> > if any.
> > I want to hide the retangle if the text field if empty.
> >
> >
> > So, can i use in an expression somthing other then a Data field or a
> > Parameter?
> >
> >
> > Thanks in advance,
> > Eitan perry
> >|||Sorry for replying a bit late, I've been offline for some days.
What do you mean by "shift up"?
Kaisa M. Lindahl Lervik
<hellbent4u@.gmail.com> wrote in message
news:1166009076.861211.266620@.l12g2000cwl.googlegroups.com...
> Hey,
> I need that the text that follows the textbox with visibility=false to
> shift up. How can one go about it?
> Regards,
> Ankur
>
> Kaisa M. Lindahl Lervik wrote:
>> You can use ReportItems("itemname").Value
>> But I'm not sure if it would work to hide the parent (rectangle) if the
>> child (text box) is empty. You'll just have to try, but I'd rather just
>> use
>> the expression used in your text box on the visibility property of the
>> rectangle. If the textbox value = fields!Field1.Value &
>> fields!Field2.Value,
>> you should be able to do something like
>> =IIF(IsNothing(fields!Field1.Value & fields!Field2.Value), true, false)
>> in the visibility parameter.
>> Kaisa M. Lindahl Lervik
>>
>> "Perry.Eitan" <perry.eitan@.gmail.com> wrote in message
>> news:1165295155.869671.109210@.j44g2000cwa.googlegroups.com...
>> > Hello,
>> >
>> > I need to hide a field (or retangle) by asking if another field in the
>> > report, like text field (or some other entity i can create on the fly,
>> > that is not a Data field)
>> >
>> >
>> > for eg:
>> > I create a text field in a retangle.
>> > The text field include expression that determine what data to display,
>> > if any.
>> > I want to hide the retangle if the text field if empty.
>> >
>> >
>> > So, can i use in an expression somthing other then a Data field or a
>> > Parameter?
>> >
>> >
>> > Thanks in advance,
>> > Eitan perry
>> >
>

HideMemberIf OnlyChildWithParentName and member formula

Levels are defined using a SourceAttribute that points to an attribute used to create the level. In the definition of that attribute, the CustomRollupColumn points to a formula to be used in the custom rollup.

But if the level is hidden using HideMemberIf, does the level still participate - does that formula still get executed at query time?

In other words, are custom rollups still calculated, even if they are hidden using something like HideMemberIf?