Generating Records based on frequency

I did not change anything

---

Vamsi

Generating Records based on frequency

this generated the records as i want perfectly

Now,How do i just pass the requestid and have it create all the records in a differant table as i did with the cursor query i had in my original post. Is that something possible

Thanks much again

Vamsi

Generating Records based on frequency

this generated the records as i want perfectly

Now,How do i just pass the requestid and have it create all the records in a differant table as i did with the cursor query i had in my original post. Is that something possible

Thanks much again

Vamsi

Do you already have a table with date,frequency etc stored for all requestids? if not will you input all those information?

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c

Generating Records based on frequency

CAST(.. AS DATE) will only work in SQL Server 2008 and up.

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles

Generating Records based on frequency

CAST(.. AS DATE) will only work in SQL Server 2008 and up.

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles

Thanks . will change it

Now,How do i just pass the requestid and have it create all the records in a differant table as i did with the cursor query i had in my original post. Is that something possible

---

Vamsi

Generating Records based on frequency

Try

Select s.Requestid + Row_Number() Over(Partition By s.Requestid Order By c.dt) As Requestid,
  Cast(c.dt As smalldatetime) As ProductionMonth,
  s.assignedanalystid As AssignedAnalyst,
  s.frequency As freqcd,
  Cast(c.dt As smalldatetime) As EnteredDate,
  s.requesttypeid As rptdesc,
  s.CustomerName As groupname,
  s.Requestid As originalrequestid

INTO NewTable -- create new table on the fly

From @Sample s
Inner Join dbo.Calendar c On c.dt Between s.CompletedDate And DateAdd(month, 1, s.ExpirationDate)
Where (s.frequency = 'Monthly' And c.D = 1)
  Or (s.frequency = 'Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 7 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 1, s.ExpirationDate))
  Or (s.frequency = 'Bi-Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 14 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 2, s.ExpirationDate))

AND s.RequestId = @RequestID

Order By originalrequestid, Requestid;

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles

Generating Records based on frequency

We actually save the requestids in a table called TABLE_KEYS and we increment the requestid in that table

Below is the logic i used in my cursor query to update the TABLE_KEYS .How can i do this in your query

update table_keys

set key_id = key_id + 1

where table_name = 'adhoc'

---

Vamsi