Advanced Defaulting Data Parameters

I have to date parameters defaulted as follows:
startdate = dateadd("M",-1,Today())
enddate = today()
So it will display data for the past month from today. Now, what I
need is the following. I need a subscription to be sent out at the
start of the month (so June 1st would be the next one). I need it to
show all data for May (that's 31 days, so far this is doing 30 days).
So using this same logic, it will return May 2nd to May 31st. I need
it to return the first and last of the previous month.
ThanksThere is a great article on how to do date stuff in T-SQL that might help
with other ideas, as the functionally is the same in VB
http://www.databasejournal.com/features/mssql/article.php/3076421
Reeves
"Ayman" wrote:
> I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||Here's how to do it in T-SQL (you can add this as a little dataset into your
report) -- you'll have to adjust the string parsing in the nested select if
your server's date representation is not MMDDYYY, but it's got three parts
and they are all straightforward. The only exciting bit there is making
sure to account for the first month of the year when you put the date
together.
SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
FROM (
SELECT FirstOfLastMonth = CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
'/1/' +
CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
Here's the trick: get the first day of the last month first (that's the
nested select) because you know its day is always day #1 of that month.
Once you've done that, getting the *last* day of the previous month is
pretty easy, no matter how many days there are in the month! Just use
DATEADD to go forward one month from the first day, which you've already
derived, and then go back one day <g>. You are probably going to slap
yourself on the head when you read that <g>.
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Here's how to do it in T-SQL (you can add this as a little dataset into your
> report) -- you'll have to adjust the string parsing in the nested select if
> your server's date representation is not MMDDYYY, but it's got three parts
> and they are all straightforward. The only exciting bit there is making
> sure to account for the first month of the year when you put the date
> together.
> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
> FROM (
> SELECT FirstOfLastMonth => CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
> '/1/' +
> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
> Here's the trick: get the first day of the last month first (that's the
> nested select) because you know its day is always day #1 of that month.
> Once you've done that, getting the *last* day of the previous month is
> pretty easy, no matter how many days there are in the month! Just use
> DATEADD to go forward one month from the first day, which you've already
> derived, and then go back one day <g>. You are probably going to slap
> yourself on the head when you read that <g>.
> >L<
> "Ayman" <ayma...@.gmail.com> wrote in message
> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
> >I have to date parameters defaulted as follows:
> > startdate = dateadd("M",-1,Today())
> > enddate = today()
> > So it will display data for the past month from today. Now, what I
> > need is the following. I need a subscription to be sent out at the
> > start of the month (so June 1st would be the next one). I need it to
> > show all data for May (that's 31 days, so far this is doing 30 days).
> > So using this same logic, it will return May 2nd to May 31st. I need
> > it to return the first and last of the previous month.
> > Thanks
I took a slightly different approach but it worked great. I made two
data sets with the following code.
end of month dataset:
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
start of month dataset:
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Under Report>Report Parameters I just used the query for the default
values. Since both scripts only return one value, there is nothing
else that can be defaulted for either of these parameters. Works
well, although the first day of the month only puts the date and not
the time, it does start from midnight though. Thanks for everyone's
help.|||>>I made two data sets with the following code.
You can definitely do that -- and I'm glad you have something that works out
for you!
FWIW I like to have a single dataset with a whole bunch of parameter values
in it for reference (IOW a dataset that always has one row, with each
parameter or configuration value expressed as a column. That way, if you
need a lot of them, you don't have a lot of little datasets littering
around, and you can add in more columns with (say) formatted versions for
display in the report header, without a lot of work.
For example -- most of what you see here is just used in the report title,
but it's convenient to make it available to the report this way:
SELECT CONVERT(VARCHAR,@.From,101) AS FromDate,
CONVERT(VARCHAR,@.To,101) AS ToDate,
Left(DateName(month,@.From),3) AS FromCMonth,
Left(DateName(month,@.To),3) AS ToCMonth,
DATEPART(quarter,@.From) AS FromQtr,
DATEPART(quarter,@.To) AS ToQtr,
CAST(DATEPART(year,@.From) AS VARCHAR(4)) + '-' +
DateName(week,@.From) AS FromWeek,
CAST(DATEPART(year,@.To) AS VARCHAR(4)) + '-' +
DateName(week,@.To) AS ToWeek
...just a personal style thing, but it works really well...
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180613135.364413.119400@.q75g2000hsh.googlegroups.com...
> On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Here's how to do it in T-SQL (you can add this as a little dataset into
>> your
>> report) -- you'll have to adjust the string parsing in the nested select
>> if
>> your server's date representation is not MMDDYYY, but it's got three
>> parts
>> and they are all straightforward. The only exciting bit there is making
>> sure to account for the first month of the year when you put the date
>> together.
>> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
>> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS
>> LastOfPreviousMonth
>> FROM (
>> SELECT FirstOfLastMonth =>> CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
>> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
>> '/1/' +
>> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS
>> VARCHAR)
>> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
>> Here's the trick: get the first day of the last month first (that's the
>> nested select) because you know its day is always day #1 of that month.
>> Once you've done that, getting the *last* day of the previous month is
>> pretty easy, no matter how many days there are in the month! Just use
>> DATEADD to go forward one month from the first day, which you've already
>> derived, and then go back one day <g>. You are probably going to slap
>> yourself on the head when you read that <g>.
>> >L<
>> "Ayman" <ayma...@.gmail.com> wrote in message
>> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>> >I have to date parameters defaulted as follows:
>> > startdate = dateadd("M",-1,Today())
>> > enddate = today()
>> > So it will display data for the past month from today. Now, what I
>> > need is the following. I need a subscription to be sent out at the
>> > start of the month (so June 1st would be the next one). I need it to
>> > show all data for May (that's 31 days, so far this is doing 30 days).
>> > So using this same logic, it will return May 2nd to May 31st. I need
>> > it to return the first and last of the previous month.
>> > Thanks
> I took a slightly different approach but it worked great. I made two
> data sets with the following code.
> end of month dataset:
> select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> start of month dataset:
> select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> Under Report>Report Parameters I just used the query for the default
> values. Since both scripts only return one value, there is nothing
> else that can be defaulted for either of these parameters. Works
> well, although the first day of the month only puts the date and not
> the time, it does start from midnight though. Thanks for everyone's
> help.
>

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

ADO.NET, SqlParameter, and NULL

Im trying to execute the following:

mySqlCmd.Parameters.Add("@.Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID);

However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value?

Thanks

I found a solution:

SqlParameter sp = mySqlCmd.Parameters.Add("@.ParentID", SqlDbType.Int);
sp.IsNullable = true;

if (sectionUpdate.iSection.ParentID == 0)
sp.Value = DBNull.Value;
else
sp.Value = sectionUpdate.iSection.ParentID;