SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select Null depending on due date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 03/19/2013 :  17:28:59  Show Profile  Reply with Quote
Hi, I have a basic job request system where occassionally an assigned person will slip the due date back without entering a reason why. The boss wants a view that shows when the due date is moved back with the reason and in particular one that shows when no reason is entered and which assigned person has moved it back.
This is a sample of the data:
"tp_id","userid","created","title","description","due","addcomm","createdby","version"
124,9,1/3/2013 00:00:00,"Sharepoint permissions",,8/3/2013 00:00:00,Null,"4",2
124,9,1/3/2013 00:00:00,"Sharepoint permissions","Can't get into Share point anymore",8/3/2013 00:00:00,"Can't get into my site","4",1
124,9,1/3/2013 00:00:00,"Sharepoint permissions",,8/3/2013 00:00:00,"Resolved","4",3
125,9,2/3/2013 00:00:00,"Word doc corrupt","Can't open Word doc",9/3/2013 00:00:00,"Got doc","5",1
125,9,2/3/2013 00:00:00,"Word doc corrupt",,9/3/2013 00:00:00,"Resolved","5",2
130,3,2/3/2013 00:00:00,"video edit",,9/3/2013 00:00:00,Null,"2",4
130,3,2/3/2013 00:00:00,"video edit",,18/3/2013 00:00:00,Null,"2",5
130,3,2/3/2013 00:00:00,"video edit",,9/3/2013 00:00:00,Null,"2",2
130,3,2/3/2013 00:00:00,"video edit","I need H&S video editing",9/3/2013 00:00:00,"Need video editing please","2",1
130,3,2/3/2013 00:00:00,"video edit",,9/3/2013 00:00:00,"Got video from customer","2",3
132,14,4/3/2013 00:00:00,"Req save button","Still waiting for Save button on the order form",11/3/2013 00:00:00,"Getting spec off customer","8",1
132,14,4/3/2013 00:00:00,"Req save button",,11/3/2013 00:00:00,"Got requirements off customer","8",2
135,14,4/3/2013 00:00:00,"Req save button",,18/3/2013 00:00:00,"Put date back - requires sign off by customer","4",3
136,9,7/3/2013 00:00:00,"bug on efos",,14/3/2013 00:00:00,"Calculating date wrong","5",2
136,9,7/3/2013 00:00:00,"bug on efos",,14/3/2013 00:00:00,"Cant find bug","5",3
136,9,7/3/2013 00:00:00,"bug on efos","Subtotal is wrong",21/3/2013 00:00:00,Null,"5",1
I'm not sure if I should be grouping the due date and selecting where duedate count > 1 to find where the duedate has been moved, base a another select on these records or do a join with the same table. Any help greatly appreciated. Thanks.

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 03/19/2013 :  18:34:04  Show Profile  Reply with Quote
Any change you can put your data in a consumable format? It makes it easier for us to help you.

Also, given that sample data, what do you want for output?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 03/29/2013 :  08:36:08  Show Profile  Reply with Quote
Hi Lamprey, Thanks for the links and sorry for my ignorance. I'll try and make life easier next time. I have created the SQL to get what I wanted and it does the job. I don't have any performance problems (at the moment) but am always willing to learn other/better ways - could you have a look and see if it could be done any other way or improved? Many thanks.

SELECT * FROM
(
SELECT t1.tp_ID, MIN(tp_version) AS tp_version FROM
(
SELECT tp_id,MAX(datetime1) AS due
FROM
alluserdata
GROUP BY tp_id
HAVING tp_ID in
(
SELECT tp_id
FROM
(
SELECT tp_id
FROM AllUserData
GROUP BY datetime1, tp_id
)a
GROUP BY tp_id
HAVING COUNT(tp_id)>1
)
)t1
INNER JOIN AllUserData ON t1.tp_ID = AllUserData.tp_ID AND t1.due = AllUserData.datetime1
GROUP BY t1.tp_ID
)t2
INNER JOIN AllUserData ON t2.tp_ID = AllUserData.tp_ID AND t2.tp_version = AllUserData.tp_Version
WHERE ntext1 IS NULL
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 03/29/2013 :  12:47:12  Show Profile  Reply with Quote
I just did a quick look over and made a few changes that should produce the same results. If you care to include sample data in a consumable format, I, or someone else, might be able to tweak it further. But, here is what I put together (note: untested):
SELECT * FROM 
(
	SELECT t1.tp_ID, MIN(tp_version) AS tp_version FROM 
	(
		SELECT 
			tp_id,
			MAX(datetime1) AS due 
		FROM 
			alluserdata
		GROUP BY 
			tp_id
		HAVING COUNT(*) > 1
	)t1
	INNER JOIN 
		AllUserData 
		ON t1.tp_ID = AllUserData.tp_ID 
		AND t1.due = AllUserData.datetime1
	GROUP BY 
		t1.tp_ID
)t2
INNER JOIN AllUserData 
	ON t2.tp_ID = AllUserData.tp_ID 
	AND t2.tp_version = AllUserData.tp_Version
WHERE 
	ntext1 IS NULL
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 03/29/2013 :  12:58:35  Show Profile  Reply with Quote
I had a second and was thinking that some paritioning might work in this case. Again not sure if this'll work or not:
SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY tp_ID ORDER BY datetime1 DESC, tp_version ASC) AS RowNum,
		COUNT(*) OVER (PARTITION BY tp_ID) IDCount
	FROM 
		alluserdata
)t2
WHERE 
	ntext1 IS NULL
	AND RowNum = 1
	AND IDCount > 1
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 03/30/2013 :  16:54:34  Show Profile  Reply with Quote
Many thanks for both of those. Both work (as I'm sure you knew anyway :) ). The first is nice n easy to follow. The second one is fantastic - reduced code and a couple of techniques that I haven't used before. Going to take it to pieces and step through to see how you did it. Again, many thanks for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000