SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Generating Records Weekly,Bi Weekly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 02/18/2014 :  09:48:04  Show Profile  Reply with Quote
Hi
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate feild that i use in the subtraction for calculating the number of months.

Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate



SET NOCOUNT ON

GO 

declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int

 
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)

DECLARE db_cursor CURSOR FOR 
SELECT Requestid from Request_Customer where requestid in (149016)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @oldrequestid


WHILE @@FETCH_STATUS = 0   
BEGIN  
     
     --do work here    
     
SET @num_of_times = NULL
select @num_of_times=datediff(month, receiveddate,expirationdate) from Request_Customer where requestid in (@oldrequestid) 
SET @num_of_times1 = @num_of_times+1




set @count=0
WHILE @count < @num_of_times1 
BEGIN
     
update table_keys
set key_id = key_id + 1 
where table_name = 'adhoc'

Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

     
INSERT INTO [dbo].[renoffcyc]
           ([roc_id]
           ,[prodmth]
           ,[opa_id]
           ,[freqcd]
           ,[entereddt]
           ,[rptdesc]
           ,[groupname]
           ,[origrequestid]
           )
  
           
SELECT @newrequestid
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,SR.Assignto
           ,RR.defineschedule
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
,SR.rptdesc
           ,RR.CTNAME
           ,@oldrequestid
   

             
FROM dbo].[Request_Customer] RR INNER JOIN SELECTED_CUSTOMER SR
ON RR.requestid = SR.requestid
where RR.requestid = @oldrequestid



set @count=@count+1 
END

FETCH NEXT FROM db_cursor INTO @oldrequestid
END  -- Cursor loop
CLOSE db_cursor
DEALLOCATE db_cursor



Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2014 :  12:31:05  Show Profile  Reply with Quote
you dont need a cursor for this
just use a calendar table and join onto your table for generating monhs, weeks etc in between.
see logic here
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 02/19/2014 :  09:13:14  Show Profile  Reply with Quote
Visakh
Let me explain a little more what the current process is

We get a request through an intake webform and within the request the user selects a recurring frequency.This frequency is used by us to send the user the reports that they are looking for.

So for exmaple a request comes in Jan 2014 with frequency selected as monthly and the expiration date is Dec 2014. we have to send the user monthly reports until Jan 2014 for which my monthly logic submits requests and one of our team member uses that automatic request that the system generates to process that monthly requests

Now we have a requirement to generate weekly requests and biweekly requests for which iam not sure how to make use of my existing logic.

We need to generate requests weekly and biweekly automatically so that our team members can use them

Thanks
quote:
Originally posted by visakh16

you dont need a cursor for this
just use a calendar table and join onto your table for generating monhs, weeks etc in between.
see logic here
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/20/2014 :  00:25:05  Show Profile  Reply with Quote
Sorry I didnt understand how you decide on frequency. Your posted code has no field to indicate frequency.
As per posted code this is what you can use to avoid cursor

DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)
Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

INSERT INTO [dbo].[renoffcyc]
           ([roc_id]
           ,[prodmth]
           ,[opa_id]
           ,[freqcd]
           ,[entereddt]
           ,[rptdesc]
           ,[groupname]
           ,[origrequestid]
           )
  
           
SELECT @newrequestid + v.number
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,SR.Assignto
           ,RR.defineschedule
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
,SR.rptdesc
           ,RR.CTNAME
           ,149016               
FROM dbo].[Request_Customer] RR INNER JOIN SELECTED_CUSTOMER SR
ON RR.requestid = SR.requestid
CROSS JOIN master..spt_values v
where RR.requestid = 149016
AND v.type='P'
AND v.number BETWEEN 1 AND datediff(month, RR.receiveddate,RR.expirationdate)+1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 02/26/2014 :  09:22:37  Show Profile  Reply with Quote
Still not getting the desired results

On a monthly basis I identify recurring requests within our system which have been completed and for which we need to generate off cycles based on the frequency selected within the original request
We have in total four types of frequencies that the users can select while submitting the first request and they are Quarterly, Monthly, Weekly, Biweekly.

The offcycles that the system generates are going to be used by the analysts to complete monthly or weekly

Below is sample data for original request and the off cycles that I generate based on the cursor code providing the original request id

Original Request
Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate
123456 12/31/2013 Monthly 123 34 Testing Company 1/15/2014 12/31/2014



Monthly Offcycles for original request 123456
Roc_id ProductionMonth AssignedAnalystid freqcd entereddt rptdesc groupname origrequestid
123457 2/1/2014 123 Monthly 2/1/2014 34 Testing Company 123456
123458 3/1/2014 123 Monthly 3/1/2014 34 Testing Company 123456
123459 4/1/2014 123 Monthly 4/1/2014 34 Testing Company 123456
123460 5/1/2014 123 Monthly 5/1/2014 34 Testing Company 123456
123461 6/1/2014 123 Monthly 6/1/2014 34 Testing Company 123456
123462 7/1/2014 123 Monthly 7/1/2014 34 Testing Company 123456
123463 8/1/2014 123 Monthly 8/1/2014 34 Testing Company 123456
123464 9/1/2014 123 Monthly 9/1/2014 34 Testing Company 123456
123465 10/1/2014 123 Monthly 10/1/2014 34 Testing Company 123456
123466 11/1/2014 123 Monthly 11/1/2014 34 Testing Company 123456
123467 12/1/2014 123 Monthly 12/1/2014 34 Testing Company 123456
123468 1/1/2015 123 Monthly 1/1/2015 34 Testing Company 123456


Below is sample data for original request and the off cycles that I would like help to generate weekly offcycles and Bi weekly Offcycles



Original Request
Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate
123654 12/31/2013 Weekly 123 34 Testing Company 1/15/2014 12/31/2014



Weekly Offcycles for original request 123456
Roc_id ProductionMonth AssignedAnalystid freqcd entereddt rptdesc groupname origrequestid
123655 1/22/2014 123 Weekly 1/22/2014 34 Testing Company 123654
123656 1/29/014 123 Weekly 1/29/014 34 Testing Company 123654
123657 2/4/2014 123 Weekly 2/4/2014 34 Testing Company 123654
123658 2/11/2014 123 Weekly 2/11/2014 34 Testing Company 123654
123659 2/18/2014 123 Weekly 2/18/2014 34 Testing Company 123654
123660 2/25/2014 123 Weekly 2/25/2014 34 Testing Company 123654
123661 3/4/2014 123 Weekly 3/4/2014 34 Testing Company 123654
123662 3/10/014 123 Weekly 3/10/014 34 Testing Company 123654
123663 3/17/2014 123 Weekly 3/17/2014 34 Testing Company 123654
123664 3/24/2014 123 Weekly 3/24/2014 34 Testing Company 123654
123665 3/31/2014 123 Weekly 3/31/2014 34 Testing Company 123654
123666 4/7/2014 123 Weekly 4/7/2014 34 Testing Company 123654
123667 4/11/2014 123 Weekly 4/11/2014 34 Testing Company 123654
123668 4/18/2014 123 Weekly 4/18/2014 34 Testing Company 123654
123669 4/25/2014 123 Weekly 4/25/2014 34 Testing Company 123654





quote:
Originally posted by visakh16

Sorry I didnt understand how you decide on frequency. Your posted code has no field to indicate frequency.
As per posted code this is what you can use to avoid cursor

DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)
Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

INSERT INTO [dbo].[renoffcyc]
           ([roc_id]
           ,[prodmth]
           ,[opa_id]
           ,[freqcd]
           ,[entereddt]
           ,[rptdesc]
           ,[groupname]
           ,[origrequestid]
           )
  
           
SELECT @newrequestid + v.number
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
           ,SR.Assignto
           ,RR.defineschedule
           ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
,SR.rptdesc
           ,RR.CTNAME
           ,149016               
FROM dbo].[Request_Customer] RR INNER JOIN SELECTED_CUSTOMER SR
ON RR.requestid = SR.requestid
CROSS JOIN master..spt_values v
where RR.requestid = 149016
AND v.type='P'
AND v.number BETWEEN 1 AND datediff(month, RR.receiveddate,RR.expirationdate)+1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Edited by - jim_jim on 02/26/2014 09:23:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/27/2014 :  02:48:29  Show Profile  Reply with Quote
Can you explain how you got the start date as 22 for weekly? What represents a week for you? whats starting day?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 03/04/2014 :  09:51:24  Show Profile  Reply with Quote
Sorry for the late response as i did not get notification this time . The start date is based on the original request completion date.the original request was completed on 1/15/2014 so we have to start generating offcycles for it from a week later to the starting date
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000