how to show list the latest rows for every week

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I am creating one application in ado.net. In this application I create one Excel file which is sent to the client side. In that Excel file I have patient data. My problem is I want to show only the latest data which is entered between Monday and Saturday. There should not be showing any previous data entered (by? ... about) user. Any tips or an idea would really be appreciated. Below I show you my table structure:

RNo RDate      PName              DOB          CNo   Occupation Sex
1   10/01/2013 Mr. Akshay Dighe   05/10/1987 45444   abc        male 
2   11/01/2013 ms.        kavita  06/04/1987  6565   abc        female

Desired output:

RNo RDate      PName              DOB          CNo   Occupation Sex
1   11/01/2013 ms.        kavita  06/04/1987  6565   abc        female 
2   10/01/2013 Mr. Akshay Dighe   05/10/1987 45444   abc        male

Hope you are all getting my point. What I want to say. Exactly.

I wrote one sql query but it is not working. The error message tells me:

'RDate' is not a recognized datepart option

Here is my query:

SELECT p1.PName,p1.RDate,p1.CNo,p1.Sex,p2.ww FROM Physio_cureTable p1
  INNER JOIN(SELECT MAX(RDate)mxdate,DATEPART(RDate,RDate)ww
    FROM Physio_cureTable
     GROUP BY DATEPART(RDate,RDate))p2
       ON p1.RDate=p2.mxdate
        AND datepart(RDate,p1.[RDate])=t2.ww

Posted 27-Jan-13 19:57pm

Atul Rokade

Updated 28-Jan-13 7:28am

RedDk

v2

Add a Solution

Comments

RedDk 28-Jan-13 13:33pm

I edited your question extensively so that it makes a little more sense. But really, there's a JOIN you're making here and no mention of THAT table (no 1). Secondly, the specific error refers to the function DATEPART(). It takes arguments for which you haven't provided a correct format. I would suggest highlighting each method in your code and hitting F1 so that the HELP volume opens up. The examples there in the BOL make everything very clear.

Jibesh 28-Jan-13 20:03pm

what is the data type of RDate field?

Atul Rokade 29-Jan-13 10:59am

jibesh sir it varchar

RedDk 29-Jan-13 14:09pm

<pre>
/* off the "Copy Code" type (whatever that might be) */

SELECT DATEPART(year,'10/01/2013')

/* See? TSQL is very smart. It knows what the type is ... BUT!*/

CREATE TABLE [instance].[cpqa].[tblRA_no_01]( [RNo][int], [RDate][datetime],

[PName][nvarchar](78), [DOB][datetime], [CNo][int],

[Occupation][nvarchar](22), [Sex][nvarchar](16) )

BULK INSERT [instance].[cpqa].[tblRA_no_01] FROM 'C:\Users\RA\copycode.txt'

SELECT * FROM [instance].[cpqa].[tblRA_no_01]

/* Without specifying format in datetime type you get a default. Now apply that same "smart" DATEPART but leverage some more TSQL "system" by using SELECT */

SELECT DATEPART(year,(SELECT [RDate] FROM [instance].[cpqa].[tblRA_no_01] WHERE [RNo] = 2)) AS [RAYear]

/* Got the same result ... Now, I could have CAST your [nvarchar] as datetime and everything would still work ... */

SELECT DATEPART(year,CAST('10/01/2013'AS datetime)) AS [RAYear]/*

But it actually makes more sense to use datetime as the type since there's a variety of

formats available ... like I said yesterday, see the BOL for SQL2005. If you haven't got it,

download it because having the help file for the app you're using is highly recommended.

*/
</pre>

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)