help me with this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help me with this query

Author

Topic

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2008-06-25 : 08:58:49

hi,

i have a query like this

select Org_OPY_OrganizationID,OPY_planYearID,OPY_PlanYearName,Convert(varchar,OPY_StartDate,101)as OPY_StartDate,Convert(varchar,OPY_EndDate,101)as OPY_EndDate ,
(CASE when ( convert(datetime, cast(getdate() as varchar(11)),101) > = opy_startDate
and convert(datetime, cast(getdate() as varchar(11)),101) <= opy_enddate) then
1
when ( convert(datetime, cast(getdate() as varchar(11)),101) < opy_startDate
and convert(datetime, cast(getdate() as varchar(11)),101) < opy_enddate) then
2
else
0
end) CurrentDate,row_number() over (partition by Org_OPY_OrganizationID order by OPY_StartDate asc) a
from tbl_organizationplanyear

which would give a result as follows

4, 1, Jan 2005 - Dec 2005, 01/01/2005, 12/31/2005, 0, 1
4, 2, Jan 2006 - Dec 2006, 01/01/2006, 09/30/2006, 0, 2
4, 11, 2007 Plan Year - BG, 10/01/2006, 12/31/2006, 0, 3
4, 13, 2008 Jan, 01/01/2008, 01/31/2008, 0, 4
4, 12, Plan Year 08, 02/01/2008, 12/31/2008, 1, 5
5, 3, Jan 2005 - Dec 2005, 01/01/2005, 12/31/2005, 0, 1
5, 4, Jan 2006 - Dec 2006, 01/01/2006, 12/31/2006, 0, 2
5, 14, Jan 2007 - Dec 2007, 01/01/2007, 12/31/2007, 0, 3
5, 28, Jan 2008 - Dec 2008, 01/01/2008, 12/31/2008, 1, 4
9, 5, Plan Year 2005 , 01/01/2005, 01/31/2006, 0, 1
9, 6, Plan Year 2006 , 02/01/2006, 12/31/2006, 0, 2
9, 8, Plan Year 2007 , 01/01/2007, 12/31/2007, 0, 3
9, 23, Plan Year 2008 , 01/01/2008, 12/31/2008, 1, 4

Now i need to take record having latest start date excluding the record which has currentdate = 1 for each organization....

i want the result as follows

4, 13, 2008 Jan, 01/01/2008, 01/31/2008, 0, 4,
5, 14, Jan 2007 - Dec 2007, 01/01/2007, 12/31/2007, 0, 3,
9, 8, Plan Year 2007, 01/01/2007, 12/31/2007, 0, 3,

i need to get this without using temp table...

any help..

Thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 09:04:36

[code]select * from
(
select row_number() over (partition by Org_OPY_OrganizationID order by OPY_StartDate desc) as a,*
from
(
select Org_OPY_OrganizationID,OPY_planYearID,OPY_PlanYearName,Convert(varchar,OPY_StartDate,101)as OPY_StartDate,Convert(varchar,OPY_EndDate,101)as OPY_EndDate ,
(CASE when ( convert(datetime, cast(getdate() as varchar(11)),101) > = opy_startDate
and convert(datetime, cast(getdate() as varchar(11)),101) <= opy_enddate) then
1
when ( convert(datetime, cast(getdate() as varchar(11)),101) < opy_startDate
and convert(datetime, cast(getdate() as varchar(11)),101) < opy_enddate) then
2
else
0
end) CurrentDate
from tbl_organizationplanyear)t
WHERE t.CurrentDate<>1
)r
WHERE r.a=1[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:09:12

[code]select Org_OPY_OrganizationID,
OPY_planYearID,
OPY_PlanYearName,
OPY_StartDate,
OPY_EndDate
from (
select Org_OPY_OrganizationID,
OPY_planYearID,
OPY_PlanYearName,
Convert(varchar, OPY_StartDate, 101) as OPY_StartDate,
Convert(varchar, OPY_EndDate, 101) as OPY_EndDate,
row_number() over (partition by Org_OPY_OrganizationID order by OPY_StartDate Desc) as a
from tbl_organizationplanyear
when DATEDIFF(DAY, '19000101', getdate()) >= opy_startDate
AND DATEDIFF(DAY, '19000101', getdate()) <= opy_enddate
) AS d
where a = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2008-06-25 : 09:13:12

Yes.. that works fine...
thanks for your reply..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:18:42

Just in case you like the BETWEEN operator

SELECT	Org_OPY_OrganizationID,
	OPY_planYearID,
	OPY_PlanYearName,
	OPY_StartDate,
	OPY_EndDate
FROM	(
		SELECT	Org_OPY_OrganizationID,
			OPY_planYearID,
			OPY_PlanYearName,
			CONVERT(VARCHAR, OPY_StartDate, 101) AS OPY_StartDate,
			CONVERT(VARCHAR, OPY_EndDate, 101) AS OPY_EndDate, 
			ROW_NUMBER() OVER (PARTITION BY Org_OPY_OrganizationID ORDER BY OPY_StartDate Desc) AS a
		FROM	tbl_organizationplanyear
		WHEN	DATEDIFF(DAY, '19000101', GETDATE()) BETWEEN opy_startDate AND opy_enddate
	) AS d
WHERE	a = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources