June 23, 2014 at 6:48 am
Hi All,
I am having below query which gives me result fine only if i give Y.YEARINFO = 2014(here if i give any year data is coming rite, e.g. 2015,2020 etc.) but if I give condition like Y.YEARINFO <= 2014 output is coming wrong.... I got stucked about this condition... How to do or which thing i need to implement here. Please suggest idea....
select distinct YR,MTH,ISNULL(COUNT(DISTINCT PERSON_ID),0) STARTCOUNT from
(select distinct R1.YR,R1.MTH,R1.PERSON_ID,R1.effective_date
from
(select distinct A.YEARINFO YR,A.MONTHINFO MTH,PERSON_ID,Effective_date
row_number() OVER (partition by person_id,mname order by date_start desc ,effective_date desc desc) rn
from
(SELECT Y.YEARINFO, M.MONTHINFO,datepart(MM,D.DATESTART ) mname,min(D.DATESTART) min_date,max(D.DATESTART) max_date
FROM DAYINFO D LEFT JOIN MONTHINFO M ON D.PARENTMONTH = M.CALMONTH
LEFT JOIN QUARTERINFO Q ON M.PARENTQUARTER = Q.CALQUARTER
LEFT JOIN YEARINFO Y ON Q.PARENTYEAR = Y.CALYEAR
where Y.CALYEAR = 2014 AND datepart(MM,D.DATESTART ) <= datepart(MM,GETDATE())
group by Y.YEARINFO ,M.MONTHINFO, datepart(MM,D.DATESTART)
)A,
(select distinct p.personid,(effective_start_date) effective_date,p.DATE_START
from PersonInfo P
)B
where B.effective_date < A.min_date
and B.Date_start < A.min_date)R1
WHERE RN = 1
)m1
group by YR,MTH
Thanks
Abhas
June 23, 2014 at 7:14 am
abhas (6/23/2014)
Hi All,I am having below query which gives me result fine only if i give Y.YEARINFO = 2014(here if i give any year data is coming rite, e.g. 2015,2020 etc.) but if I give condition like Y.YEARINFO <= 2014 output is coming wrong.... I got stucked about this condition... How to do or which thing i need to implement here. Please suggest idea....
select distinct YR,MTH,ISNULL(COUNT(DISTINCT PERSON_ID),0) STARTCOUNT from
(select distinct R1.YR,R1.MTH,R1.PERSON_ID,R1.effective_date
from
(select distinct A.YEARINFO YR,A.MONTHINFO MTH,PERSON_ID,Effective_date
row_number() OVER (partition by person_id,mname order by date_start desc ,effective_date desc desc) rn
from
(SELECT Y.YEARINFO, M.MONTHINFO,datepart(MM,D.DATESTART ) mname,min(D.DATESTART) min_date,max(D.DATESTART) max_date
FROM DAYINFO D LEFT JOIN MONTHINFO M ON D.PARENTMONTH = M.CALMONTH
LEFT JOIN QUARTERINFO Q ON M.PARENTQUARTER = Q.CALQUARTER
LEFT JOIN YEARINFO Y ON Q.PARENTYEAR = Y.CALYEAR
where Y.CALYEAR = 2014 AND datepart(MM,D.DATESTART ) <= datepart(MM,GETDATE())
group by Y.YEARINFO ,M.MONTHINFO, datepart(MM,D.DATESTART)
)A,
(select distinct p.personid,(effective_start_date) effective_date,p.DATE_START
from PersonInfo P
)B
where B.effective_date < A.min_date
and B.Date_start < A.min_date)R1
WHERE RN = 1
)m1
group by YR,MTH
Thanks
Abhas
Start by providing us some information. Saying the output is wrong does not exactly tell us what is going on. Please take a few minutes and read the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 7:25 am
Just trying to paste this into a query window to get a good view on what you're doing and I'm getting syntax errors. It looks like some of the code is missing or incorrect. You have stuff like "effective_date desc desc" which just can't work. Removing that I'm getting errors elsewhere. However urgent it may be, there's no way to help you when we don't understand your logic, can't see your structure, and the one piece of information we do have to work with, is broken. We're trying to help, but you have to take into account that we're not sitting in your chair, looking at what you're looking at. All we can see is what you give us.
Assuming y.CALYEAR is an integer value, any number below 2014 should return correctly. If it's some other type of data type, it completely depends on what data is in it how it will behave. So y.CALYEAR <= 2014 should return values for 2013 and 2012, etc. What are you seeing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply