November 23, 2010 at 3:59 pm
Hi,
I am trying to build a query from one of the temporary table which gives me the Start date and End date in two rows. I am looking to build a query where it results Start date and End date in one row. This will help in other queries to check the date ranges/periods.
Attached is the sample table and data inserts. expected sample result output is also in attachment.
Much appreciate your time on helping out on this.
November 23, 2010 at 4:14 pm
CREATE TABLE auData
(
auID NUMERIC, -- This column will have Unique value always
Emp_ID NUMERIC,
dt_Start DATETIME,
dt_End DATETIME,
Emp_Cat INT
)
Go
INSERT INTO auData VALUES(3037,1,'2010-11-18 11:36:34.210',NULL,1)
GO
INSERT INTO auData VALUES(3038,1,NULL,'2010-11-18 11:49:00.917',1)
GO
INSERT INTO auData VALUES(3039,1,'2010-11-18 15:15:04.590',NULL,1)
GO
INSERT INTO auData VALUES(3093,1,NULL,'2010-11-23 09:25:41.970',1)
GO
INSERT INTO auData VALUES(3125,1,'2010-11-23 15:15:18.233',NULL,90)
GO
select * from auData
Ram,
How do you determine which two rows go together? It is based on Emp_ID and the 'previous row' is whatever has the most recent auID for that Emp_ID?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 4:29 pm
Hi Thanx for Reply...
Yes based on Emp_ID and start date. It is like the row follows with end date or if end date not there then next row will have new Emp_ID.
Am looking for Emp_ID's start date End date in one row. each Emp_ID may have more than one start date and End date. so the next row for same Emp_ID will have start date greater than enddate of previous row of same Emp_ID.
Hope am not confusing here.
November 23, 2010 at 10:25 pm
Ram2002 (11/23/2010)
Hi Thanx for Reply...Yes based on Emp_ID and start date. It is like the row follows with end date or if end date not there then next row will have new Emp_ID.
Am looking for Emp_ID's start date End date in one row. each Emp_ID may have more than one start date and End date. so the next row for same Emp_ID will have start date greater than enddate of previous row of same Emp_ID.
Hope am not confusing here.
Is there any chance of missing rows?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2010 at 7:09 am
Nope. it will be in order by Emp_ID and no missing rows. If the Emp has no end date which means still continueing.So there will be only one row with start date and end date should be null.
November 24, 2010 at 11:24 am
CELKO (11/24/2010)
I hope you know why NUMERIC(s,p) is not like DECIMAL(s,p).
Before some poor soul gets misled by this...
It's not?
http://msdn.microsoft.com/en-us/library/ms187746.aspx
decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
While we're on the subject, how about including a link to these international standards you harp about constantly? Googling: ISO-11179 DDL ends up with all your posts coming up, instead of something useful, like the actual standard.
Opps! You screwed up the the DDL, so that the natrual primary key cannot be declared. That makes for complicaetd code:
Or OOPS! You screwed up real life again, and forgot that time clock systems actually log this way, outside of the DBA's control!
WITH X
AS
(SELECT *,
(ROW_NUMBER() OVER (PARTITION BY emp_id
ORDER BY COALESCE (employment_start_date, employment_end_date))
+1) /2 AS pairs
FROM Timesheets)
SELECT X.emp_id, MAX(employment_start_date) AS employment_start_date,
MAX(employment_end_date) AS employment_end_date,
MAX(emp_cat)
FROM X
GROUP BY emp_id, pairs;
Actually some useful code and ideas. Well, you do usually eventually get there.
(To others, yes, I'm feeding the troll, but just because ISO differentiates between decimal and numeric doesn't mean T-SQL does, and I needed to make sure that was mentioned here.)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 24, 2010 at 1:29 pm
Unfortunatly i have to do this on MS SQL 2000. So can i have the equivalent for MSSQL2000 please.
November 24, 2010 at 2:15 pm
Ram2002 (11/24/2010)
Unfortunatly i have to do this on MS SQL 2000. So can i have the equivalent for MSSQL2000 please.
FYI: you posted this in a SQL 2008 forum, so that is why you received a SQL 2008 answer.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 2:25 pm
Ram2002 (11/24/2010)
Unfortunatly i have to do this on MS SQL 2000. So can i have the equivalent for MSSQL2000 please.
There isn't one. You're going to have to do some fancy footwork to normalize the data. I would recommend the quirky update. Can find it by using the search component above, in the upper right, just filter to articles only.
You'll want to drop your data to a #tmp table with a clustered index against the employee ID and then you'll have to create a column that's basically a COALESCE of start/end times. From there, you'll be looking at using the quirky (make sure you read it through, it's not simple). That will get you a usable result set.
There is no easy way out of this in 2k. As Wayne mentioned above, you're in the wrong forum for 2k. This might be worth a repost there with the better defined request (make sure to provide the full test structure for people to test from) so that people who only monitor specific forums may be able to provide better assistance. Check the first link in my sig if you're preparing to do that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 24, 2010 at 2:33 pm
Thanx Criag. I appreciate all you ppl in this post time.
This was the first post so went wrong. Next time will be in appropriate forum.
November 24, 2010 at 3:00 pm
Craig Farrell (11/24/2010)
Ram2002 (11/24/2010)
Unfortunatly i have to do this on MS SQL 2000. So can i have the equivalent for MSSQL2000 please.There isn't one. You're going to have to do some fancy footwork to normalize the data. I would recommend the quirky update. Can find it by using the search component above, in the upper right, just filter to articles only.
You'll want to drop your data to a #tmp table with a clustered index against the employee ID and then you'll have to create a column that's basically a COALESCE of start/end times. From there, you'll be looking at using the quirky (make sure you read it through, it's not simple). That will get you a usable result set.
There is no easy way out of this in 2k. As Wayne mentioned above, you're in the wrong forum for 2k. This might be worth a repost there with the better defined request (make sure to provide the full test structure for people to test from) so that people who only monitor specific forums may be able to provide better assistance. Check the first link in my sig if you're preparing to do that.
I think that you can dump the data into a #temp table, add the clustered index to have the data sorted correctly, and then add an identity column to assign the proper numbering. From there, we can omit the CTE and row_number() function call from below, and use the select with max() and group by to select the data.
I would suggest to continue this here. But, please post the table definitions and sample data as per the link that Craig has already mentioned - note that it is also the first link in my signature. Please include enough sample data to show all the issues that we will have to deal with. I'm not sure if this can be handled without using the Quirky Update, but if it can, it might be a better way to proceed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 3:10 pm
How does this work for you?
-- need a temporary table with a clustered index on emp_id + auID,
-- enforce PK on the auID, and add an identity column with an unique index on that also
IF object_id('tempdb..#auData') IS NOT NULL DROP TABLE #auData;
CREATE TABLE #auData(auID NUMERIC PRIMARY KEY NONCLUSTERED, -- This column will have Unique value always
Emp_ID NUMERIC NOT NULL,
dt_Start DATETIME,
dt_End DATETIME,
Emp_Cat INT,
RowID INT IDENTITY UNIQUE,
UNIQUE CLUSTERED (Emp_ID, auID));
-- you will have to insert the data into the #temp table
-- utilizing a SELECT statement with an ORDER BY Emp_ID, auID
INSERT INTO #auData
SELECT auID, Emp_ID, dt_Start, dt_End, Emp_Cat
FROM auData
ORDER BY Emp_ID, auID;
SELECT auID = min(auID),
Emp_ID,
dt_Start = max(dt_Start),
dt_End = max(dt_End),
Emp_Cat = max(Emp_Cat)
FROM (SELECT *,
Grp = (RowID+1)/2
FROM #auData) CTE
GROUP BY Emp_ID, Grp;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 4:17 pm
Ram2002 (11/24/2010)
Thanx Criag. I appreciate all you ppl in this post time.This was the first post so went wrong. Next time will be in appropriate forum.
Except for it being in the wrong forum, you did really good with the file attachment. Well done. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2010 at 4:35 pm
Jeff Moden (11/24/2010)
Ram2002 (11/24/2010)
Thanx Criag. I appreciate all you ppl in this post time.This was the first post so went wrong. Next time will be in appropriate forum.
Except for it being in the wrong forum, you did really good with the file attachment. Well done. 🙂
Apologies, I should have been clearer in that with my comment above. Providing the final output you're expecting with more clarification as to the associations would be assistive if you do repost. However, yes, I agree with Jeff, the beginning was well organized. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 24, 2010 at 4:59 pm
Jeff Moden (11/24/2010)
Ram2002 (11/24/2010)
Thanx Criag. I appreciate all you ppl in this post time.This was the first post so went wrong. Next time will be in appropriate forum.
Except for it being in the wrong forum, you did really good with the file attachment. Well done. 🙂
Agreed. We really have to work with some people to get them to post in 20 attempts what you did in your first post. As I pointed out, you got a 2008 answer because of which forum you posted in... and then you got an answer for 2000. I can handle the wrong forum... it's people that don't post the DDL/DML that is harder. You did good in all of this! Thank you for helping us help you!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply