October 5, 2010 at 12:21 pm
Hello I am trying to query when a serial number is opened in a report within 60 days of the last time that same serial number was opened. The problem I am having is in the WHERE statement I do not know or can find for my life on how I can do this.
Can someone help? It woudl be greatlly appreciated!
Thanks!
October 5, 2010 at 12:26 pm
We'd need a bit more info from you to help out with an accurate query
Refer to this post by the resident metal bar hater: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 5, 2010 at 12:29 pm
Thank you I will check out that site.
Forgive me im very brand new only been doing this for about a week. But in the future when I have a question like that would you want to have like all my syntax/code? Is that what you are looking for?
October 5, 2010 at 12:31 pm
Yep.
Table structures, some sample data, expected output, and what you have written so far will be enough to answer 99% of the questions you will ever post.
October 5, 2010 at 12:34 pm
SELECT
a.Request_ID,
c.Serial_ID,
a.Place_ID,
b.event_dt
FROM
request a JOIN request_event b
ON a.request_id = b.request_id
JOIN request_product c
ON b.request_id = c. request_id
WHERE
Here is where I get into the clueless mode!
So what I want to see pop up is when the serial_id number is created on a different request id within 60 days of the first one being created. I hope this makes better sense sorry for the first couple posts.
October 5, 2010 at 12:54 pm
Getting closer..just need some specifics.
How do you want it to be displayed? Do you just want a list of serial numbers that have been ordered twice in the last 60 days? Or do you want 2+ rows for every serial number and all corresponding order dates within the last 60 days of the most recent purchase?
Some sample data, and your full expected output would help.
October 5, 2010 at 1:02 pm
Thank you first of all for such fast response I do appreciate the help! 😀
Thinking it over I would like to see any serial numbers that have been "ordered" more than once over the last 60 days from the current date.
October 5, 2010 at 1:07 pm
So say serial number A1234 came up twice in the last 60 days I would like to output:
Request_ID SERIAL_ID PLACE_ID EVENT_DT
123456 A1234 IL1234 11/10/10
123457 A1234 IL1234 11/20/10
123458 A9999 WI2222 11/1/10
123599 A9999 WI2222 11/2/10
125999 A9999 WI2222 11/20/10
So in the example the query would pull the request id (auto generated by our crm to serialize each "call") The serial ID number the serial ID place location (Place ID) and the event date of when the Request ID was created.
October 5, 2010 at 1:07 pm
Something like this would just return the serial numbers ordered 2 or more times in the last 60 days from today's date. Without table schemas/data I can't completely verify it..but it looks like it will work just by eyeballing. Make sure you have indexes on serial_id and event_dt on the appropriate tables.
;WITH CTE AS ( SELECT
RN = ROW_NUMBER() OVER (PARTITION BY rp.SERIAL_ID ORDER BY re.event_dt desc),
rp.Serial_ID,
re.Event_Dt
FROM request_product rp
INNER JOIN request_event re
ON rp.request_id = re.request_id
WHERE event_dt > DATEADD(dd,-60,getdate())
)
SELECT
Serial_ID
FROM CTE
WHERE RN = 2
October 5, 2010 at 1:08 pm
Sorry that didnt come out as nicely as I thought but the colums are the same as my SELECT statement.
October 5, 2010 at 1:12 pm
I will try that out thanks very much!
October 5, 2010 at 1:21 pm
davemcsheffrey (10/5/2010)
I will try that out thanks very much!
Ah I was posting that as you replied with the extra criteria..
try this
;WITH CTE AS ( SELECT
RN = ROW_NUMBER() OVER (PARTITION BY rp.SERIAL_ID ORDER BY re.event_dt desc),
re.request_id,
r.place_id
rp.Serial_ID,
re.Event_Dt
FROM request_product rp
INNER JOIN request_event re
ON rp.request_id = re.request_id
INNER JOIN request r
ON a.request_id = re.request_id
WHERE event_dt > DATEADD(dd,-60,getdate())
)
SELECT
request_id,
Serial_ID,
place_id,
event_dt
FROM CTE
WHERE Serial_ID IN (
SELECT Serial_ID from CTE
WHERE RN = 2
)
October 5, 2010 at 1:31 pm
I am testing now and tweeking just a bit but I think you've got it!! Wow I hope one day I can do somthing this complex as fast as you I really thank you for the help!
October 5, 2010 at 1:33 pm
Any time 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply