more problems with unique sequences

I have another different schema with the same data, but this one its like this

Code Snippet

CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));

ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);

GO

CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO

Create View particle AS
select * from lepton
Union all
select * from Jet;
GO

I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.

The way that you are going about this is likely to cause some problems.

For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)

It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:

CREATE VIEW Particle
AS

SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon

UNION ALL

SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron

UNION ALL

SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO

With this example, each idap will be unique, and also give you a clue about the constitutent component.

More efficient query than this?

I have two tables:
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
where tblB/ColA references tblA/ColA.
I need to find the most recently added row from tblB given a value of
ColA.
I wrote the following but wondered if there was a better, more
efficient method.
declare @.ColA int
select ColB, p.dateadded
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA and
b.dateadded = (select max(b.dateadded)
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA)
TIA LarsMhhm,
if there is a constraint between the two why do you need the Join ?
declare @.ColA int
select ColB, MAX(p.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>|||Jens,
Thanks for the reply. I don't think I explained the problem well
enough.
The table definition
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
and some test data
ColA ColB DateAdded
---
100 32 2-10-2005
100 16 3-01-2005
100 24 3-16-2005
100 20 1-15-2005
101 ...
I would like a query which would return the single row whose ColA
value = 100
100 24 3-16-2005
since it has the latest date.
Lars
On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>Mhhm,
>if there is a constraint between the two why do you need the Join ?
>declare @.ColA int
>select ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>HTH, Jens Smeyer
>--
>http://www.sqlserver2005.de
>--
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>|||OK, whats wrong with that?
declare @.ColA int
Set ColA = 100
select b.ColB, MAX(b.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
Jens Smeyer.
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
> Jens,
> Thanks for the reply. I don't think I explained the problem well
> enough.
> The table definition
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> and some test data
> ColA ColB DateAdded
> ---
> 100 32 2-10-2005
> 100 16 3-01-2005
> 100 24 3-16-2005
> 100 20 1-15-2005
> 101 ...
> I would like a query which would return the single row whose ColA
> value = 100
> 100 24 3-16-2005
> since it has the latest date.
> Lars
>
> On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||It returns all 4 rows when I expect only 1.
On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>OK, whats wrong with that?
>declare @.ColA int
>Set ColA = 100
>select b.ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>Jens Smeyer.
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
>|||Sorry youre right, forget about that, try this.
Select * from TableB Where
COLB =
(
Select TOP 1 COLB
From TableB
WHERE COLA = 100
Group by COLB
Order by MAX(dateadded) DESC
)
HTH (now), Jens Smeyer ;-)
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:j5k061hvmpdqbgjog7nf78qrq9ms5ki73k@.
4ax.com...
> It returns all 4 rows when I expect only 1.
> On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||Try This...
Select ColA, ColB, DateAdded
From TblB B
Where DateAdded =
(Select Max(DateAdded)
From TblB
Where ColA = B.ColA)
And ColA = @.ColA
Leave out the last row ( And ColA = @.ColA) to get the results for all ColA
values
"larzeb" wrote:

> I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> .... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>
>sql

MonthName Chart Problem

I'm having a problem printing the name of the month on a monthly chart.

The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.

When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.

The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.

The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.

Thanks!

Dataset Code:

<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>

...
</Chart>

I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:

2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May

I bet you see the labels as shown in the "2005" line, right?

You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.

-- Robert

|||

Thanks. You are of course entirely correct.

Eureka it works! The chart is alive. It's ALIVE!

MonthName Chart Problem

I'm having a problem printing the name of the month on a monthly chart.

The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.

When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.

The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.

The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.

Thanks!

Dataset Code:

<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>

...
</Chart>

I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:

2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May

I bet you see the labels as shown in the "2005" line, right?

You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.

-- Robert

|||

Thanks. You are of course entirely correct.

Eureka it works! The chart is alive. It's ALIVE!

money format ..need some help..please

Hi everybody.
Here's my Product table

ID int,
Title nvarchar(50),
Price money

How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34

Thanks very much...I am a beginner. Sorry for foolish question

Formatting is generally applied at the page where the data is going to be displayed as opposed to altering the datasource itself.

If you were going to databind your sql data to something such as a GridView, then you can alter the data's format by specifying a DataFormatString.

for currency you can use: {0:c}
for numeric data with 2 digits after the decimal you can use: {0:n2}

For example:

 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Price" DataFormatString="{0:c}" HtmlEncode=False HeaderText="With Currency Symbol" /> <asp:BoundField DataField="Price" DataFormatString="{0:n2}" HtmlEncode=False HeaderText="Without Currency Symbol" /> </Columns></asp:GridView>
  

If you are actually looking to alter the data in sql server, please let us know.

|||

Thanksmbanavige very much for your answer. I use DataList to display my items and I want to display1 pricein my page. I try to use this code

<%If((Eval("Price") *100) mod 100 >0) { %>

<%#Eval("Price","{0:N2}")%>

<% } else {

%> <%#Eval("Price","{0:c}") %>

<% } %> . But it seem wrong. How can I do something like that ?

|||

I'm not sure i follow what you're trying to do with that code. If you want one price, then choose only one of the sample formats i provided

use this: <%#Eval("Price","{0:c}") %>

or this: <%#Eval("Price","{0:N2}")%>

but not both at the same time.

Unless you're dealing with unpredictalbe currencies, i'd probably just go with: <%#Eval("Price","{0:c}") %>

|||

mbanavige:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Price" DataFormatString="{0:c}"
HtmlEncode=False HeaderText="With Currency Symbol" />
</Columns>
</asp:GridView>

Hi Mike,

What would cause a page with say ten colums, all formated in the same manner, to only have five colums display currency correctly and the remainder to just be numbers?

I have this weird scenario going on and it is not a syntax error, as I have checked in dozens of times.

|||

Did you set HtmlEncode to False for all the columns?

|||

Yes I did. I just did a character by character study of two similar pages. The first page is for one State the next page for a different State. Every single character is identical. Therefore I have no option to conclude that there must be some fundamental change in the SQL database tables themselves. each State has it's own table. I will go in and make sure that each field is set to exactly the same data type.

If there is any other possibility that you can think of, please let me know. I am certain that this is not a code issue, the code is working, the reason I am not getting the formatting to work is probably because what is expected in the DB table, is something else. I'll drop you a line after I have checked the tables.

|||

Oaky, I was correct, the problem was in the database. The fields in question on the working table were 'money' and the same fields in the non-working table were 'float'. The instant I changed over to 'money', the web pages formatted perfectly.

There is an interesting sideline to this. In the beginning all state tables were identical, and the database itself was hosted on remote servers in Dallas, Texas in the USA. The database is now hosted on remote servers in Sydney, Australia and when the databse was transferred it somehow mysteriously changed. The technique of restoring a backup on the new server, then attaching it, was how we did the transfer, but it was not 100% flawless. We could not then nor now figure out why some very minor changes occured, perhaps there may have been some slight version differences on server software.

At any rate it's all fixed now, so the moral to the tale is this, don't always assume your code is incorrect, sometimes, it might be other things, as in this case, an issue with the database.

Best wishes.