question regarding CASE and GROUP BY

I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?

SELECT CASE
WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM
dbo.CONSULTATION C
GROUP BY
C.GLG_DELEGATE_ID
, C.GLG_IDNo, just repeat your CASE code in the WHERE clause:

SELECT CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM dbo.CONSULTATION C
GROUP BY CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID END
,C.GLG_ID

Question on using CASE

Hello All,

I am pretty new to SQL Server and I am trying to create a view to gather the necessary data I need. I am not sure if CASE is what I should be using, or if I can even do what I need, but I am trying to capture the following information (I know the data looks a bit wacky, but I cannot post the real data so it is just an example).

Example data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/06 - 9/23/06
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/2/06 - 9/4/06

I want to return the following data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/4/06 - 9/4/06

Basically in my mind I am thinking along the lines of:
IF DESC = "Pool" THEN STARTDATE = "minimum STARTDATE"
ELSE STARTDATE = "ENDDATE"

I am having an issue trying to figure out how to create the syntax for this CASE statement.

Any help is appreciated.

ThanksWell you don't need a CASE statement for this. A simple aggregate query will handle the problem you describe:
select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC...but I bet you will find that your problem is more complex than you describe, and that you are going to have to deal with gaps between date ranges that a simple MIN and MAX will overlook.|||Thanks for the reply.

I had tried that type of query but the real issue is I need the min STARTDATE if the DESC = "Pool". If the DESC is anything else I want the STARTDATE to take the ENDDATE.

Probably not possible?|||select ID,
DESC,
case when DESC = 'Pool' then STARTDATE else ENDDATE end as STARTDATE,
ENDDATE
from (select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC) Subquery

You could probably do this without the subquery as well, but it is a little odd mixing aggregate and non-aggregate values in a single column.

Question on table footer

Will the table header row not print on the last page if the only output for
that page is the table footer row? This seems to be that case in my report,
but I would like the table header row to also print. Can this be done?No. The header and footer of a group are siblings. A repeatable header will
be repeated with its children (subgroups or details) not with its siblings.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Kemerling" <roger.kemerling@.matcomcorp.com> wrote in message
news:ehnCCZlpEHA.3988@.tk2msftngp13.phx.gbl...
> Will the table header row not print on the last page if the only output
for
> that page is the table footer row? This seems to be that case in my
report,
> but I would like the table header row to also print. Can this be done?
>sql

Question on large volume of training dataset

Hi, all experts here,

Thanks a lot for your kind attention.

I have a question on training large volume of datasets. In this case, the training will take a long while to complete, is there anything we can do to improve that? I know, we obviously cant split the training dataset into different smaller datasets. What we can do to improve that?

Hope my question is clear for your help.

Thank you very much in advance for your advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Generally, the performance of the training operation depends on the size of the training set and there is not much one can do about this. Sometimes, the accuracy of the model is not improved significantly by adding new data. you might try smaller samples first and see if you really need all the data.

Certain optimizations could be done, depending on the algorithm. For example, if you are using the Neural Network algorithm you might want to make sure that the continuous columns are treated as continuous and not discretized, particularly if the the column is predictable. Also, make sure the model does not include unnecessary columns and only the required columns are marked as Predictable.

All these would improve the performance, but not significantly

Alternately, if you are trying just to do some sort of data exploration, you might want to start with Naive Bayes, which takes little time for training.|||

Hi, Bogdan,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

Question on large cube update processing

Hi, all experts,

Is there any way for us to process the updated cube partly ? In my case I only want to add KPIs to my cube which are related to a few measure groups. In this case, it seems like we have to process the whole partitions related to these measure groups which the new added KPIs are related to. But as to large cube with miliions of records, this could take a reasonable time. Therefore, I am wondering if there is any way that we are able to update and process only the new added properties such as these new added KPIs to the cube instead of having to process the large chunk of the cube data? I personally think it will be really great to have this feature.

Is there any way to achieve this in SQL Server 2005 Analysis Services? I am looking forward to hearing from you shortly and thanks a lot for your advices.

With best regards,

Yours sincerely,

Hi,

I think you just need to deploy your project.

Regards

|||

Hi,

Thanks. But as what I have always seen in my BIDS , whenever I deploy a project, the system process it first and then deploy it.

I am not quite sure what you really mean by deploy here if that is not the 'deploy' I understand.

Regards,

|||

Lucas is right, you just need to deploy without processing.

There is an option under the deployment settings of the project that sets whether the project is processed after it is deployed. I think the default setting is "Always Process", but you can change this to "Never" and the project will be deployed without initiating a processing operation.

For changes to things like the calc script and KPI's, you do not need to reprocess your data, deploying your changes is enough.

question on date (or string after using convert function) comparis

select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
the result return is 'T' why? (2005 should be < 2006)
On Mon, 13 Feb 2006 19:05:26 -0800, kei wrote:

>select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
>the result return is 'T' why? (2005 should be < 2006)
Hi kei,
You are comparing two string constants. They may look like dates to you
and me (though probably not the same dates - I'm from the part of the
world that uses dd/mm/yyyy), but SQL Server doesn't try to interpret
what you write - it takes you literally.
You could try
SELECT CASE WHEN CAST('02/12/2005' AS datetime) >=
CAST('01/02/2006' AS datetime) THEN 'T' ELSE 'F' END
and pray that SQL Server interprets the ambiguous date format the same
way you do.
Or you could switch to a non-ambiguous date format:
SELECT CASE WHEN CAST('20051202' AS datetime) >=
CAST('20060201' AS datetime) THEN 'T' ELSE 'F' END
For more information, check Tibor Karaszi's article on SQL Server date
and time handling: http://www.karaszi.com/SQLServer/info_datetime.asp
Hugo Kornelis, SQL Server MVP

Question on column mappings between mining structure and case table for lift chart

Hi, all experts here,

I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.

Why is that? Could any experts here give me some explanation on that?

Hope my question is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The mapping of the predicted column is required to compare the prediction with the actual test data. It is not used in the actual prediction|||

Hi, Bogdan,

Thanks a lot for your advices.

With best regards,

Yours sincerely,

Question on case table and nested table

Hi, all here,

As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.

I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Selecting the same table as both case and nested table makes sense when the table actually has two keys and represents a one-to-many relationship. An example is a transaction table, which contains products purchased by each customer. It could look like below:
CustID ProductID
1 Beer
1 Milk
1 Coke
1 Chips
2 Oreo
2 Milk
...

As you see, there is a one-to-many relationship between CustID and ProductID.
Typical modeling with nested tables would require a separate table, with customer information (containing the distinct customers, their IDs and possibly other information) and a relationship from that table to this one, with CustID acting as a Foreign Key inside this transaction table.

However, when:
- no Customer table is present OR
- no additional useful information is available in the Customer table
it is helpful to use only the transaction table for mining.

Internally, the implementation issues two queries, one of them selecting all distinct customers.
Hope this clarifies the scenario and any benefits|||

Hi, Bogdan,

Thanks a lot for your very helpful advices.

With best regards,

Yours sincerely,

Question on case

I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.Originally posted by exdter
I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.

Try this:

case when my_column is null then 'char_string' else my_column end 'column'|||Can I use an isnull here? Doesn't matter. Your solution worked. Thanks alot.|||Use isull(mycolumn,'CharString')|||Thats what I thought too but I used Snails method already.
Thanks.|||I tried it this way too and it works fine and is much neater.
Thanks.|||coalesce(mycolumn,'charstring') is also much neater and it uses standard sql

;)|||Thanks r937!