Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2014-02-18 : 09:48:04
|
HiI 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 receiveddateSET NOCOUNT ONGO declare @num_of_times intdeclare @count intdeclare @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 @oldrequestidWHILE @@FETCH_STATUS = 0 BEGIN --do work here SET @num_of_times = NULLselect @num_of_times=datediff(month, receiveddate,expirationdate) from Request_Customer where requestid in (@oldrequestid) SET @num_of_times1 = @num_of_times+1set @count=0WHILE @count < @num_of_times1 BEGIN update table_keysset 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 SRON RR.requestid = SR.requestidwhere RR.requestid = @oldrequestidset @count=@count+1 ENDFETCH NEXT FROM db_cursor INTO @oldrequestidEND -- Cursor loopCLOSE db_cursorDEALLOCATE db_cursor Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2014-02-19 : 09:13:14
|
VisakhLet me explain a little more what the current process isWe 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 requestsNow 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 themThanksquote: Originally posted by visakh16 you dont need a cursor for thisjust use a calendar table and join onto your table for generating monhs, weeks etc in between.see logic herehttp://visakhm.blogspot.in/2010/02/generating-calendar-table.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-20 : 00:25:05
|
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 cursorDECLARE @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 SRON RR.requestid = SR.requestidCROSS JOIN master..spt_values vwhere RR.requestid = 149016AND v.type='P'AND v.number BETWEEN 1 AND datediff(month, RR.receiveddate,RR.expirationdate)+1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2014-02-26 : 09:22:37
|
Still not getting the desired resultsOn 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 requestWe 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 weeklyBelow is sample data for original request and the off cycles that I generate based on the cursor code providing the original request idOriginal Request Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate123456 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 origrequestid123457 2/1/2014 123 Monthly 2/1/2014 34 Testing Company 123456123458 3/1/2014 123 Monthly 3/1/2014 34 Testing Company 123456123459 4/1/2014 123 Monthly 4/1/2014 34 Testing Company 123456123460 5/1/2014 123 Monthly 5/1/2014 34 Testing Company 123456123461 6/1/2014 123 Monthly 6/1/2014 34 Testing Company 123456123462 7/1/2014 123 Monthly 7/1/2014 34 Testing Company 123456123463 8/1/2014 123 Monthly 8/1/2014 34 Testing Company 123456123464 9/1/2014 123 Monthly 9/1/2014 34 Testing Company 123456123465 10/1/2014 123 Monthly 10/1/2014 34 Testing Company 123456123466 11/1/2014 123 Monthly 11/1/2014 34 Testing Company 123456123467 12/1/2014 123 Monthly 12/1/2014 34 Testing Company 123456123468 1/1/2015 123 Monthly 1/1/2015 34 Testing Company 123456Below is sample data for original request and the off cycles that I would like help to generate weekly offcycles and Bi weekly OffcyclesOriginal Request Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate123654 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 origrequestid123655 1/22/2014 123 Weekly 1/22/2014 34 Testing Company 123654123656 1/29/014 123 Weekly 1/29/014 34 Testing Company 123654123657 2/4/2014 123 Weekly 2/4/2014 34 Testing Company 123654123658 2/11/2014 123 Weekly 2/11/2014 34 Testing Company 123654123659 2/18/2014 123 Weekly 2/18/2014 34 Testing Company 123654123660 2/25/2014 123 Weekly 2/25/2014 34 Testing Company 123654123661 3/4/2014 123 Weekly 3/4/2014 34 Testing Company 123654123662 3/10/014 123 Weekly 3/10/014 34 Testing Company 123654123663 3/17/2014 123 Weekly 3/17/2014 34 Testing Company 123654123664 3/24/2014 123 Weekly 3/24/2014 34 Testing Company 123654123665 3/31/2014 123 Weekly 3/31/2014 34 Testing Company 123654123666 4/7/2014 123 Weekly 4/7/2014 34 Testing Company 123654123667 4/11/2014 123 Weekly 4/11/2014 34 Testing Company 123654123668 4/18/2014 123 Weekly 4/18/2014 34 Testing Company 123654123669 4/25/2014 123 Weekly 4/25/2014 34 Testing Company 123654quote: 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 cursorDECLARE @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 SRON RR.requestid = SR.requestidCROSS JOIN master..spt_values vwhere RR.requestid = 149016AND v.type='P'AND v.number BETWEEN 1 AND datediff(month, RR.receiveddate,RR.expirationdate)+1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-27 : 02:48:29
|
Can you explain how you got the start date as 22 for weekly? What represents a week for you? whats starting day?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2014-03-04 : 09:51:24
|
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 |
|
|
|
|
|
|
|