Select Null depending on due date

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)

Select Null depending on due date

Author

Topic

jaycee
Starting Member

18 Posts

Posted - 2013-03-19 : 17:28:59

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-19 : 18:34:04

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

jaycee
Starting Member

18 Posts

Posted - 2013-03-29 : 08:36:08

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-29 : 12:47:12

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-29 : 12:58:35

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

jaycee
Starting Member

18 Posts

Posted - 2013-03-30 : 16:54:34

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources