Stored procedure to search for files

Question

Post reply

Stored procedure to search for files

umangjain19

Grasshopper

Points: 20
More actions
November 30, 2009 at 11:22 pm

#218169

Hi,

I generate a report based on data sent by customer from Mon-Fri. Like if a customer sends data on Mon - his report will be generated on a Wednesday (some rules applied) and same for other weekdays.

Now I require to add extra functionality whereby if cust sends data on Saturday or Sunday , it also gets reported (which was not the case earlier).

Condition is:

"If cust sends data on Sat or Sun - it should be merged with Mon data and reported as if the data were send on Mon".

Now it is easy for me to just manually put Sat and Sun data into Mon data and do the reporting. However i would like to avoid doing it manually.

So if someone can help in out in designing a SP to search for any files on the last Saturday and Sunday & change the internal dates to make it look like it was loaded Monday (in every data file is included the date and time at which customer sent that particular file).

Tables used:

Data_control - stores the filename,fileadded(date),filedate,unique cust no.

Data_full - stores the details contained in the data sent by the customer.

Any help would be appreciated.

Umang

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

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

Raghavendra-499237 Default port Points: 1435 More actions · Answer 1

Raghavendra-499237

Default port

Points: 1435

December 1, 2009 at 6:05 am

#1085718

hi,

compare saturday and sunday with datename function (datename(w,datefield)) and build your logic.

hope this will help u.

thanks and regards

Raghavendra N S

umangjain19 Grasshopper Points: 20 More actions · Answer 2

umangjain19

Grasshopper

Points: 20

December 1, 2009 at 11:46 am

#1085995

Thanks Raghav.

Umang.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I'n not sure how complicated your code is but if it's not too long, you could post it and we might have an even better suggestion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

umangjain19 Grasshopper Points: 20 More actions · Answer 4

Guys,

Ive written a SP to handle Sat/Sun data. I ran the Exe to implement it, but it did not work for some reason. Can you help me out.



CREATE PROCEDURE usp_Load_Full

	@file NVARCHAR(100),

	@name NVARCHAR(30),

	@folder CHAR(8),

AS

DECLARE @i INT, @filedate CHAR(8)

IF (DATENAME(dw,@folder)= 'Saturday')

   BEGIN

	SELECT id

	FROM dbo.Customer C

	JOIN dbo.DataControl PC ON C.Number=PC.Number

	WHERE C.SatData=1

        SET @i = @@ROWCOUNT

      WHILE i >0

	BEGIN

	   SET @filedate = DATEADD(dd,2,@folder)

           INSERT INTO Data_Full

	   SELECT *, @name, @filedate, 0, 0

	   FROM Data_Load

	   WHERE Version = '1'

	   SET @i = @i -1

	END

      INSERT INTO Data_Full

      SELECT *, @name, @folder, 0, 0

      FROM Data_Load

      WHERE Version = '1'

    END

ELSE IF (DATENAME(dw,@folder)= 'Sunday')

   BEGIN

	SET @filedate = DATEADD(dd,1,@folder)

        INSERT INTO Data_Full

	   SELECT *, @name, @filedate, 0, 0

	   FROM Data_Load

	   WHERE Version = '1'

   END

The code should simply check for Sat data load, check a flag - if the flag is one then add 2 days to it to make it look it is Mon file..otherwise load as Sat file.

The sunday file does not check for any flag and simply should behave as if it is mond file.

Assume the 3 parameters passed to SP be available.

Lowell SSC Guru Points: 323482 More actions · Answer 5

something like this might get you started.

for any given week, this returns the starting date of monday(@12am, the start of friday, and the last possible datetime of friday.

from there, you could use dateadd to add minus two days to get the Saturday(teo days before) of "this weeks" monday,



SET DATEFIRST 1

SELECT

   DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) Monday,

   DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4 Friday,

   DATEADD(ms,-3,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) + 5 ) EODFriday

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

umangjain19 Grasshopper Points: 20 More actions · Answer 6

All,

I am alomost done with my bit to include weekend days, but failing in the last hurdle.

When i am using "SET" to modify the Sat date it is not alloowing the modified date to be stored in "yyyymmdd" format. It is storing in "Aug 31 2009" type format.

I want the modified date to look in "yyyymmdd" (the format of filedate is "yyyymmdd"). Below is the line in contention.



SET @filedate= DATEADD(dd,2,@filedate)

A quick response would be appreciated.

Thanks.