March 19, 2014 at 2:27 pm
I'm newbie, trying my hands on SQL .
working on to provide detail report where complaint register for the same account number with different date.
How do i do that ?
Current process shows
Account No complaint type creationdate
123-456 Surface 2013-07-30 09:50:43.000
123-456 Surface 2013-09-08 20:31:38.000
123-456 Detail 2013-10-17 10:26:24.000
123-456 Detail 2013-10-31 12:15:25.000
Any help apperciated.
Thank you
March 19, 2014 at 2:44 pm
You could use HAVING clause to get what you need (there are other options as well).
It's considered a good practice to post your sample data in a consumable format along with your expected results. You can read more about this in the article linked in my signature. Because you're relatively new, I did it for you this time to show you how it could be done.
Here's an example for your problem:
CREATE TABLE #Test(
AccountNovarchar(10),
complainttypevarchar(20),
creationdatedatetime)
INSERT #Test
VALUES(
'123-456', 'Surface', '2013-07-30 09:50:43.000'),(
'123-456', 'Surface', '2013-09-08 20:31:38.000'),(
'123-456', 'Detail', '2013-10-17 10:26:24.000'),(
'123-456', 'Detail', '2013-10-31 12:15:25.000')
SELECT AccountNo, complainttype, MIN(creationdate), MAX(creationdate)
FROM #Test
GROUP BY AccountNo, complainttype
HAVING DATEDIFF(DD, MIN(creationdate), MAX(creationdate)) > 0
GO
DROP TABLE #Test
Be sure to understand what it's doing and post any questions that you have.
March 20, 2014 at 8:12 am
Thank you. Apperciate your help.
Apology for the question format. I will be careful next time. 🙂
once again thank you ,
it worked.
March 20, 2014 at 9:42 am
You're welcome. Be sure to understand how and why does it work.
I'm glad I could help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply