.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

urgent needed an sql query

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net
 

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




View Complete Post


More Related Resource Links

urgent help needed regarding transactional replication2005

  
urgent help needed regarding transactional replication2005   I did set up transactional replication on sql 2005 and also in article properties have selected to replicate foreign keys,default constraints and indexes. I checked the schema properties at publisher and subscriber and found that on one table a foreign key is not replicated which has an on delete cascade. I am not sure why is not replicating that constraint.The other table has a foreign key which is also not replicated.   I would appreciate all your help. thanks, Satya

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

Urgent Query Request

  
Hi Guys, Task of my package is to move FolderA csv files only to separate folder. However, the business rule is to move only those files which are matching in FolderB csv files. But I have to read the file first and get the distinct on Date Column and StoreId. Now, I build package using two foreeach loops one for folderA and one for folderB. Now in side the foreach loops I got dataflow where the files got read and using sort I removed all the duplicates and dump into temptableA and temptableB. My temptables column names are  DestinationFileName                                           Source PluSales_119809_20100421040237              \\fw-sql01\E\PROCESSED_FILES\PluSales_119809_20100421040237 PluSales_119809_20100421040237              \\fw-sql01\E\PROCESSED_FILES\PluSales_119809_20100421123456 Source column is actually a strFileName mapped in foreeach loop.   After loading the files, I am using execute sql task query for matching files and non matching files then using foreach loop and file system task I am moving files to separate folder.    Assuming, after completion of dataflow task,  each file dumped 2 distinct records with same storeID and different source name as

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: help needed signing a SOAP 1.1 Message's Body+Timestamp, and send over SSL

  

Hi:

I have a deadline that is about to whoosh by having underestimated how tricky this would turn out to be.

Specs:

 

  • SOAP 1.1
  • WS-Security for timestamp and signing
  • signature has to be single signature made from 2 elements: body + timestamp
  • Asymmetric Algorithm: SHA1
  • Key algorithm: RSA 
  • sent over SSL
  • client has to authenticate to server via cert with well known CN=
  • Server is not .NET but Weblogic, with policies that cannot be changed.

Basically, a secure (SSL) based transport, with signing to protect against tampering as well as replay.

 

wsHttpBinding

Seemed like a good choice at first as it has WS-Security built in.After setting up a behavior that defines client and server certs setting binding/  

Unfortunately wsHttpBinding creates a signature from too many elements in the message (Body, Action, RelatesTo, Timestamp).

There is no apparent way of controlling what gets selected as an element of the signature.  

For example: as the ws-Security headers (Timestamp, etc.) are not part of the proxy Request message class definition, and are added/injected somewhere down the line, and are no

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

 

URGENT HELP NEEDED - RECEIVING ERROR MSG: Procedure or function usp_InsPatient has too many argument

  

error msg detail:

System.Data.SqlClient.SqlException was caught
  class="16"
  ErrorCode=-2146232060
  LineNumber=0
  Message=Procedure or function usp_InsPatient has too many arguments specified.
  Number=8144
  Procedure=usp_InsPatient
  Server=.\sqlexpress
  Source=.Net SqlClient Data Provider
  State=2
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnSt

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


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