|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
View and using maximum datesOriginally, I had a query that looked like this: SELECT TOP 100 PERCENT co, id, salary, rate, ratePer, startDate, endDate FROM dbo.ERate WHERE (rateCode = 'Base') AND (startDate <= GETDATE()) AND (endDate >= GETDATE()) ORDER BY co, idnow this works find and well, unless, of course, the enddate is not greater than today's date. Most of the end dates are Jan. 1, 2100, so this is acceptable, but some are not. I wasn't the original author of this either, so I have no idea what the top 100 percent is all about. Anyway, what I'm trying to accomplish is get the information where the endDate is the most recent date available (either Jan 1, 2100, or, for example, March 3, 2006). Also there will be multiple previous endDates, so simply using less than won't work since it will pull all of those prior dates. So, an employee might have several enddates, such as Jan 1, 1999, Feb 20, 2005, and the most recent March 3, 2006. The startDate will never be in the future, so that isn't a concern. I believe the following is the DDL of the table in question if necessary: CREATE TABLE [dbo].[ERate] ( [co] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [rateCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [salary] [decimal](14, 2) NULL , [rate] [decimal](14, 6) NULL , [ratePer] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ecode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [cc1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [cc2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [cc3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [cc4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [cc5] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [jobCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [shift] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [startDate] [datetime] NULL , [endDate] [datetime] NULL , [paygrade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [step] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [guidfield] uniqueidentifier ROWGUIDCOL NOT NULL , [lastChange] [datetime] NOT NULL , [lastChangeUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) I'm not sure if I truly understand the issues here. (Having the DDL certainly helps, but some sample data with the expected results would be very nice.)
Question: Can an entry Start AND End on the same date? My WAG: SELECT e1.co , e1.id , e1.salary , e1.rate , e1.ratePer , e1.startDate , e1.endDate FROM dbo.ERate e1 WHERE ( e1.rateCode = 'Base' AND ( e1.startDate <= GETDATE() AND e1.endDate = ( SELECT max( EndDate ) FROM dbo.ERate e2 WHERE ( e2.co = e1.co AND e2.id = e1.id ) ) ) ) ORDER BY e1.co , e1.id -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam <mga***@gmail.com> wrote in message news:1151006302.118920.107800@b68g2000cwa.googlegroups.com... > Hello all, > > Originally, I had a query that looked like this: > SELECT TOP 100 PERCENT co, id, salary, rate, ratePer, startDate, > endDate > FROM dbo.ERate > WHERE (rateCode = 'Base') AND (startDate <= GETDATE()) AND (endDate >>= GETDATE()) > ORDER BY co, id > > now this works find and well, unless, of course, the enddate is not > greater than today's date. Most of the end dates are Jan. 1, 2100, so > this is acceptable, but some are not. I wasn't the original author of > this either, so I have no idea what the top 100 percent is all about. > > Anyway, what I'm trying to accomplish is get the information where the > endDate is the most recent date available (either Jan 1, 2100, or, for > example, March 3, 2006). Also there will be multiple previous endDates, > so simply using less than won't work since it will pull all of those > prior dates. So, an employee might have several enddates, such as Jan > 1, 1999, Feb 20, 2005, and the most recent March 3, 2006. > > The startDate will never be in the future, so that isn't a concern. > > I believe the following is the DDL of the table in question if > necessary: > CREATE TABLE [dbo].[ERate] ( > [co] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [rateCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT > NULL , > [salary] [decimal](14, 2) NULL , > [rate] [decimal](14, 6) NULL , > [ratePer] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [ecode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc5] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [jobCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [shift] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [startDate] [datetime] NULL , > [endDate] [datetime] NULL , > [paygrade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [step] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [guidfield] uniqueidentifier ROWGUIDCOL NOT NULL , > [lastChange] [datetime] NOT NULL , > [lastChangeUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NOT NULL > ) > It is certainly "possible" for an entry to have the same start date and
end date, but that would be unlikely (I suppose it would be correct for me to actually account for that though). As for some sample data here we go: co|id|salary|rate|ratePer|startDate|endDate A) 3365|101|null|15.00|hour|jan. 1, 2005|sept. 13, 2005 B) 3365|101|null|16.50|hour|sept. 14, 2005|dec. 31, 2100 C) 3365|200|35000|null|null|feb. 2, 2006|dec. 31, 2100 D) 3365|300|null|21.00|hour|dec. 1, 2005|dec. 15, 2006 The expected results here would be line B, C and D. I'll test out your code tomorrow when I get in. Thanks! Arnie Rowland wrote: Show quote > I'm not sure if I truly understand the issues here. (Having the DDL certainly helps, but some sample data with the expected results would be very nice.) > > Question: Can an entry Start AND End on the same date? > With SQL 2000, in a VIEW definition, one could use TOP in order to sort the
output (ORDER BY). Fortunately, that kludge does't work in SQL 2005. In SQL 2005, output from a VIEW is unsorted EVEN if TOP/ORDERBY is used in the VIEW definiition. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam <mga***@gmail.com> wrote in message news:1151006302.118920.107800@b68g2000cwa.googlegroups.com... > Hello all, > > Originally, I had a query that looked like this: > SELECT TOP 100 PERCENT co, id, salary, rate, ratePer, startDate, > endDate > FROM dbo.ERate > WHERE (rateCode = 'Base') AND (startDate <= GETDATE()) AND (endDate >>= GETDATE()) > ORDER BY co, id > > now this works find and well, unless, of course, the enddate is not > greater than today's date. Most of the end dates are Jan. 1, 2100, so > this is acceptable, but some are not. I wasn't the original author of > this either, so I have no idea what the top 100 percent is all about. > > Anyway, what I'm trying to accomplish is get the information where the > endDate is the most recent date available (either Jan 1, 2100, or, for > example, March 3, 2006). Also there will be multiple previous endDates, > so simply using less than won't work since it will pull all of those > prior dates. So, an employee might have several enddates, such as Jan > 1, 1999, Feb 20, 2005, and the most recent March 3, 2006. > > The startDate will never be in the future, so that isn't a concern. > > I believe the following is the DDL of the table in question if > necessary: > CREATE TABLE [dbo].[ERate] ( > [co] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [rateCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT > NULL , > [salary] [decimal](14, 2) NULL , > [rate] [decimal](14, 6) NULL , > [ratePer] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [ecode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [cc5] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [jobCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [shift] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [startDate] [datetime] NULL , > [endDate] [datetime] NULL , > [paygrade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [step] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [guidfield] uniqueidentifier ROWGUIDCOL NOT NULL , > [lastChange] [datetime] NOT NULL , > [lastChangeUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NOT NULL > ) >  |
|||||||||||||||||||||||
Other interesting topics