Output I am not getting in SQL server query

Question

1.00/5 (1 vote)

See more:

the below query is working

SQL

WITH MyCte AS 
(
select row_number() OVER( order by RRH_MR_NUM) AS RowNum,
RRH_LOCATION_CD,RRH_MR_NUM,RRH_FIRST_NAME,RRH_PAT_SEX,RRH_REGN_DT,RRH_PAT_DOB,AGE=DATEDIFF(YY,RRH_PAT_DOB,MCS_CRT_DT),dbo.fn_parsehtml (MCS_CASE_SUMMARY) AS MCS_CASE_SUMMARY,
MCS_CRT_DT,MCS_CRT_UID, CPA_ADDR_1,CPA_ADDR_2,CPA_ADDR_3,CPA_ADDR_AREA,CPA_ADDR_CITY,
CPA_STATE_CD,CPA_COUNTRY_CD,CPA_PIN_CODE from MR_CASE_SUMMARY_HISTORY_2015
left outer join RE_REGISTRATION_HEADER
on MCS_MRD_NUMBER =rrh_mr_num
LEFT OUTER JOIN CO_PATIENT_ADDRESS_DETAIL ON RRH_COMM_ID=CPA_COMM_ID AND CPA_MAILING_ADDR='1'
) SELECT * FROM MyCte
WHERE month(MCS_CRT_DT)=04 and day(MCS_CRT_DT) between 01 and 02

the below query is not working when i add between for month and day output shows emoty

SQL

WITH MyCte AS 
(
select row_number() OVER( order by RRH_MR_NUM) AS RowNum,
RRH_LOCATION_CD,RRH_MR_NUM,RRH_FIRST_NAME,RRH_PAT_SEX,RRH_REGN_DT,RRH_PAT_DOB,AGE=DATEDIFF(YY,RRH_PAT_DOB,MCS_CRT_DT),dbo.fn_parsehtml (MCS_CASE_SUMMARY) AS MCS_CASE_SUMMARY,
MCS_CRT_DT,MCS_CRT_UID, CPA_ADDR_1,CPA_ADDR_2,CPA_ADDR_3,CPA_ADDR_AREA,CPA_ADDR_CITY,
CPA_STATE_CD,CPA_COUNTRY_CD,CPA_PIN_CODE from MR_CASE_SUMMARY_HISTORY_2015
left outer join RE_REGISTRATION_HEADER
on MCS_MRD_NUMBER =rrh_mr_num
LEFT OUTER JOIN CO_PATIENT_ADDRESS_DETAIL ON RRH_COMM_ID=CPA_COMM_ID AND CPA_MAILING_ADDR='1'
) SELECT * FROM MyCte
WHERE RowNum BETWEEN 1 AND 10 and month(MCS_CRT_DT)=04 and day(MCS_CRT_DT) between 01 and 02

i use both between for rownum and month and day.

what is the mistake in my above sql query

What I have tried:

As above...

Posted 12-Dec-17 23:07pm

sudhakarthikeyan

Updated 4-Apr-18 0:13am

Maciej Los

v2

Add a Solution

Comments

CHill60 13-Dec-17 7:28am

It's a lot easier for us to help you if you show some sample data from the tables you are using

Richard Deeming 13-Dec-17 9:40am

If it's not returning any data, then the first 10 rows returned from your CTE are not on either 1st or 2nd April.

You'll need to look at your data to work out why. We can't do that for you, because we don't have access to your database.

Run the working query, and look at the RowNum column in the output. The problem should be fairly obvious.

Santosh kumar Pithani 27-Feb-18 1:36am

Your query is correct but you have to know how filters work with('AND','OR')

1 solution

Add a Solution

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)

Rahul_Pandit · Answer 1 · 2018-04-03T21:09:00

try this

SELECT * FROM 
(
select row_number() OVER( order by RRH_MR_NUM) AS RowNum,
RRH_LOCATION_CD,RRH_MR_NUM,RRH_FIRST_NAME,RRH_PAT_SEX,RRH_REGN_DT,RRH_PAT_DOB,AGE=DATEDIFF(YY,RRH_PAT_DOB,MCS_CRT_DT),dbo.fn_parsehtml (MCS_CASE_SUMMARY) AS MCS_CASE_SUMMARY,
MCS_CRT_DT,MCS_CRT_UID, CPA_ADDR_1,CPA_ADDR_2,CPA_ADDR_3,CPA_ADDR_AREA,CPA_ADDR_CITY,
CPA_STATE_CD,CPA_COUNTRY_CD,CPA_PIN_CODE from MR_CASE_SUMMARY_HISTORY_2015
left outer join RE_REGISTRATION_HEADER
on MCS_MRD_NUMBER =rrh_mr_num
LEFT OUTER JOIN CO_PATIENT_ADDRESS_DETAIL ON RRH_COMM_ID=CPA_COMM_ID AND CPA_MAILING_ADDR='1'
where month(MCS_CRT_DT)=04 and day(MCS_CRT_DT) between 01 and 02 
)  MyCte
WHERE RowNum BETWEEN 1 AND 10