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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

SQL Query help needed

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :ASP.Net
 
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         


View Complete Post


More Related Resource Links

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

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.


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


Equivalent Query needed

  

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

 

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

How to send an email with information in query string

  

First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.


help needed: Ldap User authentication using userDN and password

  

Hi

Is it possible to authenticate a user using userDN and password? If so, then tell me the syntax.So far i have tried to authenticate using username and password from my c# code using directoryentry which takes the parameters like domainname,username and password. But i need to authenticate using Userdn and password.



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