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


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

Equivalent Query needed

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Hi All,

 I have a table with Note ID and Comment Columns

Note ID           Comment

100                  Text 1

100                  Text 2 

101                  Text 3 

101                  Text 4

 I am using the query below to achieve my desired result

select NoteID, STUFF(
	(SELECT ',' + a.Comments AS [text()]
	from Notes a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH(')),1,1,'	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

 My result set i obtain after using the query is

NoteID            Comment

100                 Text1,Text2

101                 Text3,Text4

 



View Complete Post


More Related Resource Links

SQL Query help needed

  
I made a change to my SQL tables but I am having trouble changing my query to acommodate that change.  This query works:   select members.playername, ratings.rating, MAX(ratings.DATE), ratings.type from roles left join ratings on roles.fk_member_id=ratings.member_ID_FKleft join members on roles.fk_member_id=members.pk_id where roles.fk_member_id in (SELECT ROLES.FK_MEMBER_ID FROM ROLES    WHERE ROLES.TYPE = 'Player'   And ratings.type='8'   AND FK_TEAMBAR_NUMBER = 109)group by playername, date, ratings.type, ratings.rating   The change is that now my application tracks historical data on a member's rating.  So now there are multiple records for each member.  I want my query to return a table of the entire team members and their most recent rating (most recent date).  The above query will pull all ratings for each team member.  I just need the most recent rating - therein lies my problem. Here are the table structures: RATINGS TABLE: RATING_ID          intMEMBER_ID_FK   intDATE                   smalldatetimeSET_ID_FK          intRATING         

SQL 2005 Query Assistance needed

  
Question: I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], ' ' AS [Debit], (CONVERT(varchar, SUM(pvp.totalfee))) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.CompanyI

SQL query help needed

  
The following query works: SELECT playername,        rating,        [DATE],        [TYPE] FROM   (SELECT members.playername,                ratings.rating,                ratings.DATE,                ratings.TYPE,                Row_number() OVER(PARTITION BY members.playername ORDER BY                ratings.DATE                       DESC )                rn         FROM   roles                LEFT JOIN ratings                  ON roles.fk_memb

SQL 2005 Query Assistance needed ASAP

  
I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 ($225.00 charge minus $65.00 adjustment). SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], 0.0 AS [Debit], SUM(pvp.totalfee) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.Compa

Where is 'Change query type' (Enterprise manager) equivalent in the Management Studio

  

In sql server 2000 Enterprise manager, there was a ‘Change Query type’ icon and on clicking it, it gave you the option of changing the current query to a

Select
Insert into
Insert From
Update
Delete
Create Table

I can’t seem to find this in SQL Server Management Studio, does it exist somewhere or is there an equivalent way of doing it. Any help would be appreciated.

 

Thanks,

Kay


employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani


employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani

 

 

The Table Like this


Create table emptime
(
 EMPCODE VARCHAR2(5),
 CARDNO VARCHAR2(5),
 EMP_CODE_NAME VARCHAR2(25),
 INOUTTIME TIMESTAMP(6),               
 IOGATENO VARCHAR2(10),               
 DOOR VARCHAR2(20),               
 IN_OUT VARCHAR2(10)               
 CARDDATE DTE
);      

Refactor TSQL Query Help Needed

  

Hello,

I am looking at a lengthy query that needs refactoring and clean up. The overall jist of the query is this:

We have a customer with several items they have subscribed to.

We have a lookup table for these subscriptions with the number of days the subscription is good for.

We have another table that has a count of the days since the subscription began.

We basically then do a join and a big case (I mean really big) case statement with different logic for each subscription code that matches what the customer has subscribed to. In the end the sp returns the subscription code and a Y or N flag for if the subscription has expired.

I've looked at PIVOT and I can't see how it would help here. ROW_NUMBER isn't much use as there really aren't any sub queries. I'm racking my brain trying to figure out a way to simplify this monsterous case statement and coming up empty so I was hoping for some ideas from others. Redesign of tables and approach is an option as we are already moving alot of hard coding to table driven etc.

Thank You for your help and ideas.


Looking for Client Query Analyzer (SQL Server 2008) or Equivalent Reporting Tools

  

I've recently upgraded from SQL Server 2000 to SQL Server 2008.  My accounting and marketing team is skilled and has been using SQL Query Analyzer for years to run smaller queries and build small Crystal Reports.  Unfortunately Query Analyzer is now bundled into the management studio.  That's a bit too much software for this level of power users.

Is there a client-side query analyzer for SQL Server 2008?  I'd rather use a Microsoft product than a 3rd party tool, but I suppose I could do that too.  Looking for something simple that will:

1.  Show users the database tables and/or views

2.  Run queries

Perhaps query analyzer isn't the way to go and my team needs a different reporting system.  If this is the way MS is heading, could you direct me towards a product or service that allows power users to get the information they need?  They will still need to look at raw data to build Crystal Reports though.


urgent needed an sql query

  

Hiii Frnds,

I have to generate a unique token no which will have three fields an incrementer,comp_code and

date.

I have problem in generating a unique incrementer. Incrementer should be incremented by 1 like 0001,0002 etc, should be unique and based on no of record inserted.

Plz rep asap

thanx and regards


FOR XML Path Query - Help Needed

  

Hi All,

I'm attempting to write a query using FOR XML PATH and I'm having a few issues that maybe someone can help me with. I'm pretty sure it's something simple that I'm missing, but here are the details.

This is the expected output:

<CWCS_TRANS> 
<DTG>05-04-2011 12:25:00 CST</DTG> 
<SEQ_NUM>00002</SEQ_NUM> 
<COLLECTION>CW1</COLLECTION> 
<DISTRICT>0147</DISTRICT> 
<SCHOOLS> 
 
<SCHOOL>0002</SCHOOL> 
 
<RECORD_TYPE>WCT</RECORD_TYPE> 
 
<YEAR>2011</YEAR>    
 
<TERM>1S</TERM>              
 
<SECTION> 
 
<SECTION_ID>WORLD HISTORY</SECTION_ID>

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

  
Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

  
If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Response.BinaryWrite help needed

  

Hi all

Im new to .NET and stuck now, so i hope anyone can help my with my problem.

I have a MS SQL database where i have stored some documents as Binary data. I cant figure out how to retrieve them and show/download them in my browser.

I found some guides online and have try to put something together.


Download.aspx.cs:

private void Write()
 {
 int id = 0;
 string strID = Request.QueryString["id"];
 int.TryParse(strID, out id);

 var db = new ModelDataContext();
 var doc = db.DocumentDatas.Where(x => x.ID == id).FirstOrDefault();

 byte[] byteArray = File.ReadAllBytes(doc);

 Response.BinaryWrite(byteArray);
 }

How do i fill the byteArray with the data?

Thanks in advance :)

/Ryan


sql query that don't run

  

Hello....

I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query

  

I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:


Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5
&n

Categories: 
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