SQLTeam.com | Weblogs | Forums

Sql help with me query


#1

The script below pulls the by the date however, I would like to enter a number as my parameter instead of by date. I like how my script is working below I just want to change it to be able to pull the number. I would like to enter the number from my database and it pulls everytime that number came and shows the dates of all the time it came with 5 days before and 5 days after. So if I put in 333 in as my parameter and the date this number fell on was 2/1/2012. It will show. Along with the numbers that fell after , for instance 333 and I would like to be able to see what fell on 2/2/2012 and so on.
PARAMETERS [Enter Number:] Text;
SELECT "YourTable" AS SourceTable, YourDate, YourNumber
FROM YourTable
WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
UNION ALL
SELECT "Cash4Table", YourDate, YourNumber
FROM Cash4Table
WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
UNION ALL
SELECT "GAFiveTable", YourDate, YourNumber
FROM GAFiveTable
WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
UNION ALL SELECT "Fantasy5Table", YourDate, YourNumber
FROM Fantasy5Table
WHERE YourDate BETWEEN [Enter Date:]-4 AND [Enter Date:]+5
ORDER BY SourceTable, YourNumber;

Thanks


#2

Create a stored procedure.

CREATE PROCEDURE MyProc 
@EnterDate
AS
BEGIN

   SELECT
        'YourTable' AS SourceTable
      , YourDate
      , YourNumber
   FROM YourTable
   WHERE YourDate BETWEEN DateAdd(dd, -5, @EnterDate) AND DateAdd(dd, 5, @EnterDate)
   UNION ALL
   SELECT
       'Cash4Table'
     , YourDate
     , YourNumber
   FROM Cash4Table
   WHERE YourDate BETWEEN DateAdd(dd, -5, @EnterDate) AND DateAdd(dd, 5, @EnterDate)
   UNION ALL
   SELECT
       'GAFiveTable'
      , YourDate
      , YourNumber
   FROM GAFiveTable
   WHERE YourDate BETWEEN  DateAdd(dd, -5, @EnterDate) AND DateAdd(dd, 5, @EnterDate)
   UNION ALL SELECT 
       'Fantasy5Table'
      , YourDate
      , YourNumber
   FROM Fantasy5Table
   WHERE YourDate BETWEEN DateAdd(dd, -5, @EnterDate) AND DateAdd(dd, 5, @EnterDate)
   O

#3

Thank you so much for your response. I have one more question what can I do if I just wanted to enter ex. 333 and i want it to show me how many times it came and I would like to be able to see the other number that came 5 days after the number 333 came and 5 before the number 333 can. Is this possible?

Thank you


#4

@TheSpecialist,
I don't understand your question. Care to take another stab at it?


#5

Is it possible to enter a number like 333 and it shows all the times 333
showed up in the history. Also can iy display what numbers cane 3 days
before the 333 and the number that came two days after


#6

@TheSpecialist,
Please post DDL to create the table(s) you are querying, and insert statement(s) for a small amount of test data.