SQL Query Help Needed

Question

SQL Query Help Needed

shwstpr8800

Valued Member

Points: 71
More actions
October 7, 2010 at 11:17 am

#241384

Hello I am new to writing sql queries and am not sure how to write this up. Here is what I have so far:
--ISC Open RMA Report
SELECT
a.req_type,
a.request_id,
a.status,
b.return_reason
FROM
request a
join request_line b ON
a.request_id = b.request_id
WHERE
a.req_type = 'DEPO' and
a.status = 'OP' and
b.return_Reason in ('RR','EXCH')
So this is start but what I need it to do is take this informaton bring in open "RR" and "EXCH" currently. However what I really want the query to pull in is to show if this same serial id has been in or "OP" this same status within the last 60 days while the serial number is currently open with either the "RR" or the "EXCH" return reason.
Might not be clear but let me know if you can help. Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

shwstpr8800 Valued Member Points: 71 More actions · Answer 2

Well I did add code I have tried sorry like I said I'm about two weeks into doing queries. I will load the ddl asap I can see what you mean on why that would be useful thanks and I'll replay shortly.

tshelly SSC Veteran Points: 208 More actions · Answer 3

This resembles another recent post:

http://www.sqlservercentral.com/Forums/Topic998651-392-2.aspx?Highlight=Serial

Looks like you just have some added criteria with the need to consider status and return_reason. See if that link is a start for you.

tshelly SSC Veteran Points: 208 More actions · Answer 4

Sorry, meant to add as a hyperlink..

http://www.sqlservercentral.com/Forums/Topic998651-392-2.aspx?Highlight=Serial

shwstpr8800 Valued Member Points: 71 More actions · Answer 5

Well I have been working on it more and still dont have the diagram sorry running into a problem with that. Let me show you where I am .....

--ISC Open RMA Report

SELECT

a.req_type,

a.request_id,

a.status,

b.return_reason,

c.serial_id,

d.event_dt,

d.event_type

FROM

request a

left outer join request_line b ON

a.request_id = b.request_id

left outer join request_product c ON

b.request_id = c.request_id

left outer join request_event d ON

c.request_id = d.request_id

WHERE

a.req_type = 'DEPO' and

a.status = 'OP' and

b.return_Reason in ('RR','EXCH') and

d.event_type = 'OPEN'

--event_dt > DATEADD(dd,-60,getdate())

ORDER BY

c.serial_id

The purpose of this report is to see if this serial number that is currently in for repair on an open ticket has been in repair within the last 60 days from the current date. So the query I currently have created has pulled all open serial numbers that are in for repair. However what I want it to report is to show only the serial numbers that are in for repair but have also been in for repair in the past 60 days. I do want it to report everything I have in the select statement. Just not sure how to get that last filter in there. I saw that other post and added it in as a comment in my WHERE statement just not sure how to use it?

tshelly SSC Veteran Points: 208 More actions · Answer 6

Hi shwstpr8800~

In the hyperlink that I pasted on your thread, you can see that Derrick used a CTE (common table expression)... temporary result set which can be self-referencing. This is where he performed the date function. Please refer to the BOL for a better understanding of CTEs. Why don't you take exactly what he's provided and see if that part works for you. It worked like a charm for me, as I used it to find pieces of equipment that had been in for service more than once in the last 90 days. As the script is now, it doesn't care about status though.... just serial numbers that have been affected more than once in the last 60 days.

Good luck.