EARN OVER 9850 DOLLAR in 3 CENTURIES !

JT skrev:

> I think it's only fair that you mention to interested parties that your
> "business opportunity" requires a camcorder and sex with farm animals.
>
Interesting, JT... And how much have you earned yourself so far ;)
/impslayerI'm not involved in this. I just saw the expose on Dateline NBC, that all
!!!
"impslayer" <impslayer@.hotmail.com> wrote in message
news:1138807657.399419.317930@.g47g2000cwa.googlegroups.com...
> JT skrev:
>
> Interesting, JT... And how much have you earned yourself so far ;)
> /impslayer
>|||How do they get the farm animals to operate the camcorder?
"JT" <someone@.microsoft.com> wrote in message
news:%23l9uRZ0JGHA.3696@.TK2MSFTNGP15.phx.gbl...
> I'm not involved in this. I just saw the expose on Dateline NBC, that all
> !!!
> "impslayer" <impslayer@.hotmail.com> wrote in message
> news:1138807657.399419.317930@.g47g2000cwa.googlegroups.com...
>|||I would assume that's the easy part. ;-)
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eEyWpc0JGHA.2628@.TK2MSFTNGP15.phx.gbl...
> How do they get the farm animals to operate the camcorder?
> "JT" <someone@.microsoft.com> wrote in message
> news:%23l9uRZ0JGHA.3696@.TK2MSFTNGP15.phx.gbl...
>sql

dynamically creating temp table names

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @.l_personsUID int

select @.l_personsUID = 9842

create table ##Test1table /*then the @.l_personsUID */
(
resultset1 int

)

The key to the problem is that I want to use the variable
@.l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

> Thanks for you help.
> Billy|||billy_cormic@.hotmail.com (Billy Cormic) wrote in message news:<dd2f7565.0311251937.cf18cf9@.posting.google.com>...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
> Thanks for you help.
> Billy

You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynam...html#Sales_yymm

Simon|||I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<2jWwb.144035$ji3.17559@.twister.nyroc.rr.com>...
> "Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
> news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> > For example:
> > DECLARE @.l_personsUID int
> > select @.l_personsUID = 9842
> > create table ##Test1table /*then the @.l_personsUID */
> > (
> > resultset1 int
> > )
> > The key to the problem is that I want to use the variable
> > @.l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table9842 not ##Test1table.
> May I ask why?
> You can probably do this by dynamically building the string.
> But it's going to be messy.
>
> > Thanks for you help.
> > Billy|||>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.|||OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311261052.12098cb6@.posting.google.com>...
> >> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000. <<
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.

Dynamically creating SQL Server Report

I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>

Holds.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;
// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
 {
if (!IsPostBack)
 {
 Session["Season"] =null;
 }
if (Session["Season"] !=null)
 {
 ConfigureCrystalReports();
 }
 }
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
 {
 myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
 myReport.Load(reportPath);
 DataSet dataSet = DataSetConfiguration.Holds;
 myReport.SetDataSource(dataSet);
 CrystalReportViewer1.ReportSource = myReport;
 }
// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
 {
if (ddlSeason.SelectedIndex == 0)
 {
 Session["Season"] =null;
 }
else
 {
 Session["Season"] = ddlSeason.SelectedValue;
 ConfigureCrystalReports();
 }
 }
}

DataSetConfiguration.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
 {
//
 // TODO: Add constructor logic here
 // }// Handles creating holds datasetpublic static DataSet Holds
 {
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;
 DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";
 dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
 SqlDataAdapter cmd =new SqlDataAdapter(query, conn);
 cmd.Fill(dataSet,"Holds");
return dataSet;
 }
 }
}

Thanks!

I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>

Holds.aspx.cs

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } }

DataSetConfiguration.cs is the same.

FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.