March 31, 2012 at 10:55 pm
I have used IBI Focus for over 20 years now and am just starting SQL Server. It has been interesting to say the least transposing my Focus code into SQL Server. Now I have a problem that is stumping me.
I have a file that has columns with name,department,Departmentenddate. This gives me information on what day an individual ended working at a department. When he moves to a different department, then the date changes.
Name Dept Enddate
Bill Shipping 01/20/2009
Bill Receiving 05/15/2010
Bill Accounting 03/28/2011
Sara FInance 05/14/2010
Sara Shipping 10/11/2011
What I want to do is to create a start date in conjunction with the enddate and using between, create a date range with a start date and an enddate.
Using focus, this was rather easy using a command called last. As it pulled int a row, it would pull a column of your choice from the last row. For example, the above table would look like this if I used the last command on Department end date. Since I want the start to increase by one day then I add a day to the end date. The code basically looks like this:
IF name = last name then startdate=startdate + 1 else start date. If it was the first record for an employee, then the start and enddate would be the same which would be ok else it would add a date to the start date.
Name Start Date End Date
Bill 01/20/2009 01/20/2009
Bill 01/21/2009 05/15/2010
Bill 05/16/2010 03/28/2012
Sarah 05/14/2010 05/14/2010
Sarah 05/16/2011 10/11/2011
Is there such a command in Sql-Server (2008)
Thanks
April 1, 2012 at 6:44 am
Using SQL Server it would be a little more tricky:
DECLARE @tbl TABLE
(
Name VARCHAR(30),
Dept VARCHAR(30),
Enddate DATETIME
)
SET DATEFORMAT mdy
INSERT INTO @tbl
VALUES
('Bill','Shipping','01/20/2009'),
('Bill','Receiving','05/15/2010'),
('Bill','Accounting','03/28/2011'),
('Sara','FInance','05/14/2010'),
('Sara','Shipping','10/11/2011')
;
WITH cte AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Enddate) pos
FROM @tbl
)
SELECT
cte1.name,
cte1.Dept,
ISNULL(cte2.EndDate+1,cte1.EndDate) AS Startdate,
cte1.EndDate
FROM cte cte1
LEFT JOIN cte cte2
ON cte1.name=cte2.name AND cte1.pos=cte2.pos+1
If a database would allow to use "last" without any specific ORDER BY and a defined column (or list of columns) I wouldn't consider this a relational database. In a RDBMS the order of rows in a table doesn't matter.
April 1, 2012 at 9:00 am
I agree this Lutz about the design. In SQL Server 2012 this becomes a little easier with the introduction of lag and lead functions.
declare @tbl table
(
Name VARCHAR(30),
Dept VARCHAR(30),
EndDate DATETIME
)
SET DATEFORMAT mdy
INSERT INTO @tbl
VALUES
('Bill','Shipping','01/20/2009'),
('Bill','Receiving','05/15/2009'),
('Bill','Accounting','03/28/2011'),
('Sara','Finance','05/14/2010'),
('Sara','Shipping','10/11/2011')
select *,
dateadd(dd,1,lag(EndDate,1,NULL) over (partition by Name order by EndDate)) as StartDate
from @tbl
Ths LAG function uses the OVER clause (as does Row_Number() etc in the previous version). The number that follows is the number of records that the current records lags by (eg 1 for the previous record). The NULL is the value to show if no previous record exists at that position (eg this is the first record). If you knew the start date for the first position then this could be added using an ISNULL function. In the example you gave originally the start and end dates for the first position held were the same.
select *,
isnull(dateadd(dd,1,lag(EndDate,1,NULL) over (partition by Name order by EndDate)),EndDate) as StartDate
from @tbl
The DateAdd is to calculate the start date as the next day after they left the previous dept.
Other similar functions added in SQL Server 2012 are LEAD, FIRST and LAST.
A note of caution for those trying this with CTP3, the default cannot be set to NULL for the LAG or LEAD functions as this would hang the connection. This is fixed in RC0 and RTM versions.
Fitz
April 1, 2012 at 9:32 am
Since this is the 2008 forum I didn't even consider 2012 syntax as being an option. 😉
Even if, I wouldn't be surprised if the new syntax would perform less efficient than the version I posted.
I've lost confidence in MS that the new syntax they provide also performs better back when they added PIVOT/UNPIVOT...
April 1, 2012 at 9:36 am
Sorry did not mean to undermine the SQL 2008 answer that was given. The Focus functionality is a lot close to SQL 2012 that 2008 in this respect.
Fitz
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply