.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

max() agreate function and having clause not working as expected

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server

I wil try to describe below my issue, starting with the query i use.

SELECT     b.[id_training_MD],
FROM [training_MasterData] as a
inner join [training_Log] as b
    on a.id_training_MD = b.id_training_MD
inner join [training_Q_Log] as c
    on b.id_training_Q_Log = c.id_training_Q_Log
WHERE (a.[Category] = 'Networking')  and  (c.[employeeID] = '123')

group by b.[id_training_Q_Log],b.[id_training_MD],b.[trainingScore]   
having b.[id_training_Q_Log] = (select distinct max(b.[id_training_Q_Log]) FROM [training_Log])

My tables are:

CREATE TABLE [dbo].[Training_MasterData](
    [id_Training_MD] [int] IDENTITY(1,1) NOT NULL,
    [Category] [varchar](50) NOT NULL,
    [TrainingName] [varchar](100) NOT NULL

Example of data from table:
[id_Training_MD]    [Category]         [TrainingName]

View Complete Post

More Related Resource Links

Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


javascript function not working with datalist


I have a datalist and there is a check box within this datalist in a .ascx page.  But I have to have an image out side the datalist.  So here is the code and its not owrking.  When I click on the image, I dont see any alert box. 


<script Language="JavaScript" type="text/javascript">


function validate_checkbox()



// set var checkboxSelect to zero

var checkboxSelect = 0;

var sProducts = "";


var multiCheck = window.document.forms["frmCompare"].CompareCheck.length;


if (multiCheck != null)


    // Loop from zero to the one minus the number of checkbox button selections

    for (counter = 0; counter < window.document.forms["frmCompare"].CompareCheck.length; counter++)


        if (window.document.forms["frmCompare"].CompareCheck[counter].checked)


            var seperator = (checkboxSelect > 0)?",":"";

            sProducts += sep

Textwrapping with buttons not working as expected

Guys and Gals, Got a bit of a problem with my application. I am trying to get the text and buttons in a groupbox to wrap when the page size changes (i.e. gets too small to fit it all on the page.) I have inserted the TextWrapping="Wrap" into the TextBlock line but it refuses to wrap. I have tried everything I can think of for this. Am I doing something wrong or is it impossible to do? If someone could point me in the correct direction it would be greatly appreciated. I don't want to use a scroll viewer either. <Window x:Class="Test.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="MainWindow" Width="Auto"> <StackPanel> <TextBlock >Load Data Mappings From File</TextBlock> <TextBlock TextWrapping="Wrap"> This task will truncate all existing data Maps and re-import from an excel spread sheet.<LineBreak/> </TextBlock> <GroupBox Header="File to Load" Name="groupBox1" Padding="10" Margin="10"> <StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock DockPanel.Dock="Left" VerticalAlignment="Center" Width=&qu

Why is the Content by Query Web Part CommonViewFields attribute not working as expected?

I am using the Content by Query Web Part to show data from a Custom List.  I have already added to the XSL "ItemStyle" sheet so that I can pick the correct Item Style.  That has worked fine - I have been able to show whatever fields I would like and apply any CSS Styles I would like to them.  The issue I am having is in formatting the field correctly.  I have two different fields in my current example that need to be formatted: [1] I want to show an Image, but right now it shows the URL  [2] I want to show Rich Text, but right now it shows all the HTML Code as Plain Text.  I have looked at many articles online, and they all tell me to change the following in the WEBPART file from: <property name="CommonViewFields" type="string" /> to: <property name="CommonViewFields" type="string">Thumbnail, Image;Teaser, RichHTML;</property> What I am doing wrong?  The Web Part still runs, but it does not format the fields any differently.  Thanks for the help!

Change password for FBA users in claims setup not working as expected

Hi, I am trying to add a standard asp:changepassword control for external users. In the previous 2007 sharepoint environment this control was working "out of the box". I am pretty sure what is wrong as well,- but not sure how to fix it. A workaround that makes me able to change password is as follows: in the asp - control, I have added the option DisplayUserName="true" Then in the actual web-page, i can see that my username is: 0#.f|acaspnetsqlmembershipprovider|xxxxxx and not xxxxxx as i would excpect, as i use xxxxxx to log in. Editing the username to just xxxxxx makes the changing of password work, but this solution is not really nice to deploy to the normal end-users.. Any suggestion on how to make the username right? I've tried to set the username using the option UserName="<%=SPContext.Current.Web.CurrentUser.LoginName%>", but it does not seem to evaluate..  

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.

"max-template-document-size" change not working as expected

Hi There I have a 2 node MOSS 2007 deployment hosting 5 webapps. The one web app I am having a problem with is a customised solution from a 3rd party. The product users need to save sites as templates, to which they get an error saying that the template is too large. I have run the max-template-document-size stsadm command and increased the value from 5mb to 50mb but it seems the change is not taking effect. The error page says the limit is still 5mb. When I use stsadm getproperty it shows that the value has in fact increased. I have made sure that I am doing this on the correct webapp. The save as template operation works if I do not select the "save content" option. The sites have no content so the cannot be greater than 50mb. Am I missing something extremely obvious?

a function in the where clause?

I have this where clause that I use in so many different places, I wanted to see if it is possible to have it as a function or something so that in the case I have to ever change it, I won't have to go through 50+ queries to change it.   Select * From Table1 Where IsTableAvailable = 1 AND (IsTableBroken = 0 OR IsTableFixed = 1)    

WCF function call in page load not working asynchronously

Hi all, I am trying to implement the WCF async function call in my page load event but, it not working asynchronously. Which means page content is displayed only after the asynchronous event is completed. This is my code         protected void Page_Load(object sender, EventArgs e)         {             if (!IsPostBack)             {                 ServiceReference1.Service1Client srv = new WebApplication1.ServiceReference1.Service1Client();                 srv.GetListCompleted += new EventHandler<WebApplication1.ServiceReference1.GetListCompletedEventArgs>(srv_GetListCompleted);                 srv.GetListAsync();             }         }           void srv_GetListCompleted(object sender, WebApplication1.ServiceReference1.GetListCompletedEventArgs e)         {         

!POSTBACK not working as expected

I have a site I am trying to set up where the form can be submitted via click of button but when refreshing the page, I do not want it to resend the message. i have found several sites that show using Session Variables and !Postback, but for some reason, when using this action I am still hitting the !Postback condition even when I  do a refresh which I don't think is supposed to happen. Also, when it refreshes it resets the Session["checkrefresh"] variable and throws an error  Object Reference not found i believe on the if statment with the .ToString() check against the ViewState.  Any ideas would be awesome because I'm running out of them         ASPX.CS pageusing System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace CarouselApplicationSuite { public partial class TEStRefresh : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // If this is not the first time and is a refresh (not button click) set Session Var if (!IsPostBack) { Session["CheckRefresh"] = Server.UrlDecode(System.DateTime.Now.ToString()); } } protected void Button1_Click(object sender, EventArgs e) { if

Possible bug: Calendar views browse month function not working when list is inserted as webpart.


Inserted a custom list with a calendar view on a page as webpart. Browse month function does not work, there is a postback but month is still the current month instead of the next or previous.

Is this a know issue/bug?

Order By clause in RowNumber function slowing down the performance of my query.



I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.






CQWP rollup not working as expected


I have some site columns like “Show on Home”, yes/no, and on the home page a cqwp which shows posts which have “Show on Home” = yes.


This was working fine, then realize today that some of the site I had been setting up I had omitted the step of adding these columns – no problems I thought, just add these columns to content type for Post. Sure enough then appearing in these sites missing them.


Then I noticed that on the home page all the blog items were appearing, and the filter not working.


Resource governor not working as expected.


Greetings. I'm trying to have Resource Governor (RG) not allow any queries run that use >= 1 second of CPU time, and it's simply not working.

I have no doubt that I am doing something incorreclty, but not sure what it is.

 I configure RG with the following:

create FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
  DECLARE @grp_name sysname
   IF (SUSER_NAME() = 'sa')
     SET @grp_name = 'GroupAdmin'
   IF (SUSER_NAME() = 'testAdhoc')
     SET @grp_name = 'GroupAdhoc'
   IF (SUSER_NAME() = 'testReports')
     SET @grp_name = 'GroupReports'
  RETURN @grp_name

/****** Object: ResourcePool [poolAdhoc]  Script Date: 09/28/2010 13:26:11 ******/
CREATE RESOURCE POOL [poolAdhoc] WITH(min_cpu_percent=0, 


/****** Object: WorkloadGroup [GroupAdhoc]  Script Date: 09/28/2010 13:25:30 ******/
CREATE WORKLOAD GROUP [GroupAdhoc] WITH(group_max_requests=0, 
		max_dop=0) USING [poolAdhoc]



 After doing this, I fire

Substring not working as expected in UDF


Apologies if this is very simple and part of the basic SQL training but i'm very new to User Defined Functions (SQL 2000)

I am trying to create a function to take two variables, a varchar financial year value and an nvarchar financial month value and convert this to a datetime format, with the date automatcially being the 1st of that month.

Example: Take '2010/2011' and 2 (2nd financial month) as variables and convert to 2010-05-01 00:00:00.000

The function i am trying to create is below and i know that the code inside the function works fine, as i've tested it. The problem seems to be that the substring function is not working as it should be within the udf i am trying to create. instead of returning 4 characters, its only returning 1. Is this a known issue with UDF, and if so, is there anyway around this?


CREATE FUNCTION FinYrDate (@FinYr varchar, @FinYr_Mnth nvarchar)
RETURNS datetime

DECLARE @ReturnedDate datetime

SET @ReturnedDate =
WHEN @FinYr_Mnth < 4 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,1,4)))
WHEN @FinYr_Mnth between 4 and 9 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,6,4)))
ELSE convert

property setting in report server (2008) config file is not working as expected.



I have set the <WorkingSetMinimum> property to 2.4GB in my reportserver 2008 config file.

By defintion, <WorkingSetMinimum> setting means,

  1. Lower limit of service memory consumption; memory will not be released if the consumption is below this amount, though the service will not reserve the designated amount of memory when it starts.
  2. If service uses more than the designated amout of memory, it will release back the memory only till this declared value and not beyond that, which means it should keep 2.4GB as designated.

See this link for definition: http://books.google.com/books?id=mzY_Xo_9J-gC&pg=PA589&lpg=PA589&dq=What+is+%3Cworkingsetminimum%3E+in+report+server+configuration+file&source=bl&ots=r_xhRp8erj&sig=E_o0-Dh2SXOgMNrW3sptt5oZrYI&hl=en&ei=f5mjTNeNM5O8ccmErJMI&sa=X&oi=book_result&ct=result&resnum=4&ved=0CCAQ6AEwAw#

iframe function working in firefox but not working in internet explorer


I have a page where it will store temporary data in gridview,before we send the values to database.The values in temporary table gridview will give a link to google,where it will search the word and display results in iframe.
This works fine in firefox,but not working in internet explorer.
Is there any ways to overcome this guys?

Here's my aspx code :


    <td class="style1">
    <asp:Label ID="Label1" runat="server" Text="Caller Info :"></asp:Label>
    <td style="width: 100px">
    <asp:TextBox ID="TxtName" runat="server" class="cls" onekeypress=""  autocomplete="off" ToolTip="Press Enter key for new input"></asp:TextBox>
  <td class="style1">
      <asp:Label ID="Label2" runat="server" Text="Date :" Visible="False"></asp:Label>
  <td style="width: 100px">
  <asp:TextBox ID="TextBox3" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend