General Design Question

Hey

I need to store something a little different in a DB and I was hoping one of
you guys might be able to help me.

Basically it represents a 'world'. I have an initial state and then I get
info like this...

27/11/03 17:21 Mary is born
27/11/03 17:21 Dave is born
27/11/03 17:22 Sean is born
27/11/03 17:23 Peter dies
27/11/03 17:23 Fred is born

I need to be able to run querys like this...

How many people are alive at 27/11/03 17:22
Who was born between 27/11/03 17:22 and 27/11/03 17:23
etc.

Problem is, I'm going to have hundres of 'world's each with thousands of
entrys.

All help is appreciated :)

Tnx

Naomi"Naomi Morton" <dopey_delete@.remove.iol.ie> wrote in message
news:1069954180.280897@.emeairlvalid.ie.baltimore.c om...
> Hey
> I need to store something a little different in a DB and I was hoping one of
> you guys might be able to help me.
> Basically it represents a 'world'. I have an initial state and then I get
> info like this...
> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born

Perhaps something like this:

CREATE TABLE Worlds
(
world_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE Persons
(
world_id INT NOT NULL REFERENCES Worlds (world_id),
person_name VARCHAR(25) NOT NULL,
birth_datetime DATETIME NOT NULL,
death_datetime DATETIME NULL, -- NULL if still alive
CHECK (death_datetime >= birth_datetime),
PRIMARY KEY (world_id, birth_datetime, person_name) -- simplification
)

> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22

DECLARE @.alive_at_datetime DATETIME
SET @.alive_at_datetime = '20031127 17:22'
SELECT world_id, COUNT(*) AS alive_at_datetime
FROM Persons
WHERE birth_datetime <= @.alive_at_datetime AND
(death_datetime IS NULL OR death_datetime > @.alive_at_datetime)
GROUP BY world_id

> Who was born between 27/11/03 17:22 and 27/11/03 17:23

DECLARE @.start_datetime DATETIME, @.end_datetime DATETIME
SET @.start_datetime = '20031127 17:22'
SET @.end_datetime = '20031127 17:23'
SELECT world_id, person_name, birth_datetime
FROM Persons
WHERE birth_datetime BETWEEN @.start_datetime AND @.end_datetime

> etc.
> Problem is, I'm going to have hundres of 'world's each with thousands of
> entrys.

Millions of rows should not present a problem at all.

Regards,
jag

> All help is appreciated :)
> Tnx
> Naomi|||> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born
> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22
> Who was born between 27/11/03 17:22 and 27/11/03 17:23
> etc.

Hi Naomi,

What you have is similar to banking transaction data. For example,
27/11/03 17:21 customer #1 debited $100 from his checking account. In
this case, the entity in question are individual accounts.

I assume you're creating a fantasy gaming world. The entity in
question are the character "avatars". To make a long story short, you
should have a WORLD table and an AVATAR table. The avatar is
populated by your journal transaction entries and should have worldID,
avatarID, birth, and death columns.

To query how many are alive:
select count(*) from avatar where death < @.death or death is null and
worldID=@.worldID

To query who was born between @.start and @.end:
select * from avatar where worldID=@.worldID and birth between @.start
and @.end

-- Louis

Future with SSIS

Hi,

We have a NT service built via Visual Basic 6 which fires dts processes on demand according some criteria. Well, on the whole and basically only for the following rule:

-dts process is executed when our service recognize a concrete plain file leaved in a folder. It’s generally generated for a mainframe JCL along with a activator file (.nul file, empty). Each application own a folder and each folder have lots of files as the aforementioned ones. Scheduler for that is an ASP application that lives within a intranet. It also is a monitor and logger

In that schema, keeping on mind that eight out of ten are ETL processes, critical ones and on daily-basis, our question is, how to handle on with this stuff using by sql25k and its powerful SSIS? Migrating such dts packages to SSIS packages is a piece of cake (now no before; thanks Jamie, Michael and other guys for your awesome help) because of its tipology. However our main concern is what the hell to use. Centralized dtsx package running all day long as a service, listening and checking for files? .Net windows application either vb or c#? aspx solution? Set of .vbs interlinked among them?

This is a old post/goal. In spite of we have not defined any deadline for that, what the heck as time goes by is more urgent for us. A restriction: we can’t to programme nothing from our sql25k production cluster physically. Any stuff will be done on workstations.

In terms of cost, no problem at all. We are interested in hear thoughts and point of view about which is the most effective way

Thanks in advance,

I don't like packages running all day, listening for events. The architecture was not designed for that, and we have not tested packages as long running listeners.

I would suggest using SQL Server Agent, with alerts, listening for WMI messages about files arriving. See: http://msdn2.microsoft.com/en-us/library/ms191508.aspx

The alerts can execute SSIS packages using the SQL Agent SSIS subsystem.

Donald

|||Thanks a lot for your answer Donald.sql

Funky Problem with Invisible Chart

So, I'm pretty new to Reporting Services and this is my first post in this forum. So basically I was creating a report which contains a chart. I configure the dataset, chart values, etc and the chart shows up blank. The title of the chart shows, but nothing else shows. The way I created the report in the designer is no different that another report that actually works.

I've searched google with every word I could think of and I haven't been able to find this issue anywhere. Is this some crazy Microsoft hiccup?

Here is the Stored Procedure that I am using as my dataset:

CREATE PROCEDURE YearlyTotalsInPercentages(@.Yearint) ASBEGINDECLARE @.TotalSumintSELECT SUM(dbo.Main.Hours) AS CBDCYearlyTotals, dbo.Project.ProductLine AS ProductLineINTO #tempTotalsFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @.TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, ROUND((t.CBDCYearlyTotals/@.TotalSum) * 100, 1) AS Percentage FROM #tempTotals tENDGO

I can present the rdl if necessary.

Muchos Gracias

So, in doing deeper searching I found another forum that said temp tables may not be supported and that table variables should be used instead. So, I'm attempting that route, but running into problems with the query when it comes to dividing by @.TotalSum. It returns 0 now and not the number that I had gotten previously.

Just one road block after the other. Ah, the joys of programming.

Anybody got any clues as to why this would happen?

function of drillthrough

Hi,

I can't visualize clearly what drillthrough is for. I dunno if this can cater what i'm looking for. basically, the client wants that from a default view to click on one of the dimensions and give him a more detailed view:

example:

GroupHeads Model No No.Of Units

A 1 10

B 2 14

C 2 20

Once the user clicks on the GroupHead A, he wants to see this detailed view:

Service Jobs Amount

1 10

2 20

3 50

4 11

....

10 9

Can this be done by drillthrough?

cherriesh

Hi,

The drillthrough functionality is used to drill into the a particular measure and produce a report based on the slice taken. So in your example above you might want to drill into the No.Of Units = 10, which is a slice of GroupHeads = A and Model No = 1

From that you can produce an output that makes up that value of 10 (you don't have to include the No.of Units) e.g. You might want to look at the service jobs, amount spent and the no units ordered:

Service Job Amount No.Of Units

2 12 5

8 36 2

10 1 3

If you slice it by something else e.g.

Year 2004 2005 2006

Service Job

A 12 10 11

B 22 20 21

C 32 30 31

If you drill through number 20, you will slice your drill through by year =2005 and service job = B e.g.

Service Job Amount No.Of Units

2 12 15

8 36 2

10 1 3

HTH

Matt

|||

Can drillthrough have more than 1 level? like after clicking on the first view, you click again on the 2nd view to show you another slice?

thanks a lot.

cherriesh

|||

Hi,

No you can only dril down once, unless you create an action to a report which in turn has the ability to drill down again in another report. But the standard drillthrough functionality I'm fairly sure you can't, as it takes you to the lowest level of granularity.

HTH

Matt