Interesting join

I'm looking for some guidance on how to create a join between two tables
given the following...
Table1
ID SeqNum
A 1
A 2
B 1
B 2
B 3
Table2
ID SeqNum
A 33
A 36
B 66
B 67
B 99
The problem here is that Table2's Seqnum field does not contain the same
value as Table1's Seqnum field. However, it is guaranteed to contain the
same sequential ordering within ID's. Therefore, the lowest Seqnum value in
Table1 logically links with the lowest Seqnum value in Table2 for each ID.
For example, given this sequential relationship, the following is the join
we're trying to achieve for ID B:
ID Table1.SeqNum Table2.SeqNum
B 1 66
B 2 67
B 3 99
Is there any way to logically join these two tables given the differing
Seqnum's?
Thanks in advanceSo we are talking about a simple join of the 2 tables on seqnum except that
there is an unknown offset between the actual numbering of seqnum?
Determine (query) what this offset is, store the result in a variable, and
incorporate the offset as needed in the join condition. Of course you know
that this is not a normalized relationship.
declare @.offset as int
select @.offset =
(select max(seqnum) from table2) - (select max(seqnum) from table1)
select
table1.id,
table1.seqnum,
table2.id,
table2.seqnum
from
table1
left join
table2
on table2.seqnum = (table1.seqnum + @.offset )
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
in
> Table1 logically links with the lowest Seqnum value in Table2 for each ID.
> For example, given this sequential relationship, the following is the join
> we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Cipher, try,
SELECT ...
FROM T1 AS T1A JOIN T2 AS T2A
ON T1A.id = T2A.id
AND (SELECT COUNT(*) FROM T1 AS T1B
WHERE T1B.id = T1A.id
AND T1B.seqnum <= T1A.seqnum) =
(SELECT COUNT(*) FROM T2 AS T2B
WHERE T2B.id = T2A.id
AND T2B.seqnum <= T2A.seqnum)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM Table1 t1
INNER JOIN (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table2 t3
INNER JOIN table2 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
if the SeqNums in Table1 are not sequential, you have to repeat the dose for
Table1 as well:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table1 t3
INNER JOIN table1 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t1
INNER JOIN (
SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
FROM Table2 t5
INNER JOIN table2 t6
ON t5.SeqNum <= t6.SeqNum
GROUP BY t5.ID, t5.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
Jacco Schalkwijk
SQL Server MVP
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Let's hope he doesn't have 1M rows or this join would occupy his server's
CPU all night long.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM Table1 t1
> INNER JOIN (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table2 t3
> INNER JOIN table2 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
> if the SeqNums in Table1 are not sequential, you have to repeat the dose
for
> Table1 as well:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table1 t3
> INNER JOIN table1 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t1
> INNER JOIN (
> SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
> FROM Table2 t5
> INNER JOIN table2 t6
> ON t5.SeqNum <= t6.SeqNum
> GROUP BY t5.ID, t5.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
the
value
each
the
>|||Thanks Itzik, this works perfectly.
I was going down the road of using cursors and programmatically replicating
the relationship logic but this has saved me from that pain.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OkUfSSiZFHA.3984@.TK2MSFTNGP10.phx.gbl...
> Cipher, try,
> SELECT ...
> FROM T1 AS T1A JOIN T2 AS T2A
> ON T1A.id = T2A.id
> AND (SELECT COUNT(*) FROM T1 AS T1B
> WHERE T1B.id = T1A.id
> AND T1B.seqnum <= T1A.seqnum) =
> (SELECT COUNT(*) FROM T2 AS T2B
> WHERE T2B.id = T2A.id
> AND T2B.seqnum <= T2A.seqnum)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||With proper indexing on a million row table, it will take a number of
minutes, but nothing close to all night. But at least it provides the
required result.
Jacco Schalkwijk
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:uqwKEYiZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Let's hope he doesn't have 1M rows or this join would occupy his server's
> CPU all night long.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> for
> the
> value
> each
> the
>

Interactive reports in ASP.NET application

Hi, I am looking for some guidance on the way to go for achieving the task described below.

I am working on a project to generate various statistical reports for the Revenue managers.

The application is aimed to be a browser based application usingASP.NET. The reports shall be interactive with all the functionalities like annotations, dynamically changing the range of the x-axis and report-click should take the user to a new report/web page, context menus, multiple reports on the same page - charts and matrix/tabular.

My boss is envisioning the applications to have interactive charts just like those you find on the Yahoo Finance website.http://finance.yahoo.com/charts. They seem to be using the Flash player.

Questions:

We have a license for SQL Server 2005 reporting services. We had a hard time incorporating the SQL Server reports into the
ASP.NET AJAX enabled web application, using the ReportViewer control that comes along with VS2005, and they are pretty much static. Is there a better approach? I have looked at Dundas Charts they don't quite seem to be as interactive as the google finance and yahoo finance charts.Is the same thing possible without SSRS?. In terms of having Flash like report interactivity on the webpages?.Do Silverlight and/or WPF offer me the capability of building a RIA ASP.NET website (Rich Internet Application) with support for charting.

Any reponse is appreciated.

Thanks

Your boss is envisioning something that has taken a lot of work to put together. Have you checked how much he's willing to invest?

As to the questions:

1. Dundas charts is pretty much the top of the game as far as 3rd party charting components goes. Especially for charts that integrate with SSRS

2. Yes. You need to do a bit (lot) more work. Get the data yourself, format it yourself, present it yourself. Google and Yahoo look so good because they spend a lot of time getting each chart just right, and they're all custom built.

3. Silverlight. You should be able to do something like this, buit it might take a bit more work to get right. 3rd party Flash based charting is probably easier as a starting point.

|||

Thanks for the response.

I have come accross Adobe Flex 2, which boasts of its highly interactive rich charing controls of presentation on client browsers.

Can we integrate ASP.NET applications with those client components developed etc.

Just curious.

Thanks.

|||

I don't know about the Adobe Flex charts but the I've used Infosoft Global Charts in an Asp.Net application with good results. They are also Flash charts that allow for some good interactivity with moseovers and onclicks. You pass data to them in XML format and they animate very nicely. They also have a good JavaScript library so you can set/reset the data on the client-side in order to refresh them via Ajax if necessary.

Here's a link to their chart gallery.

http://www.fusioncharts.com/Gallery.asp

Hope this helps,

Al

|||

alcsharp, Thank you so much.

I will check it right away. I am leaving this post open for now so I can get more ideas from the folks.

Later!

Interactive Reporting in ASP.NET using SSRS (2005)

Hi, I am looking for some guidance on the way to go for achieving the task described below.

I am working on a project to generate various statistical reports for the Revenue managers.

The application is aimed to be a browser based application usingASP.NET. The reports shall be interactive with all the functionalities like annotations, dynamically changing the range of the x-axis and report-click should take the user to a new report/web page, context menus, multiple reports on the same page - charts and matrix/tabular.

My boss is envisioning the applications to have interactive charts just like those you find on the Yahoo Finance website.http://finance.yahoo.com/charts. They seem to be using the Flash player.

Questions:

We have a license for SQL Server 2005 reporting services. We had a hard time incorporating the SQL Server reports into the
ASP.NET AJAX enabled web application, using the ReportViewer control that comes along with VS2005, and they are pretty much static. Is there a better approach? I have looked at Dundas Charts they don't quite seem to be as interactive as the google finance and yahoo finance charts.Is the same thing possible without SSRS?. In terms of having Flash like report interactivity on the webpages?.Do Silverlight and/or WPF offer me the capability of building a RIA ASP.NET website (Rich Internet Application) with support for charting.

Any reponse is appreciated.

Thanks

Try the Digital Dashboards & Executive Dashboards.

http://www.dundas.com/Dashboards/index.aspx?Campaign=ASPAlliancePS

|||

Thanks Momo_Stev,

I have taken a look at them, however they lack a little on the rich presentation side. After I posted this query, I came accross the below article which sounds to be doable in my case.

Article breifly explains how to integrate Flash into client side with ASP.NET server scripting.

http://www.4guysfromrolla.com/webtech/032603-1.shtml

Thank you.