• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Create a copy of a month's data, and inserting into next month while respecting Days of the week.

I'm looking for a bit of guidance, if not some code.  Here is the premise I'm trying to work out.

I have a transaction table in SQL that holds a StartDate.  I want to offer users of my webapp a button on a monthly calendar that says:  "Copy Last Month".  When they click that button, I want to run a script that examines the current month the person is looking at (passed in parameter), selects all the records from the immediately previous month's data, and inserts new records into the current month respecting these rules:

1.  All records from the previous month will be copied based on the Day Of The Week rather than on the actual date - so, dateadd(m,startdate,1) won't do it.
2.  If an item toward the end of a month would be placed outside of the "target" month, then it will be ignored.
3. [I have more logic for whether to insert the record, but that is not relevant to this question]

So, let's say Tuesday February 8, 2011 has an item in it, and the user wants to copy that item to March.  I would expect this magic query to Insert a new item into Tuesday March 8, 2011.  But, that's too easy since Feb usually has 28 days and therefore March has matching Dates/Days.

What about something like Tuesday, April 12, 2011.  I would expect this query to create an item for Tuesday, May 10 2011.

This might be a bit insane, but I'm locked in on this concept and want to play it out a bit.  Any thoughts?
0
Steve Krile
Asked:
Steve Krile
  • 3
  • 2
1 Solution
 
tigin44Commented:
simply you can transfer the content of the previous month to the next month by using

dateadd(week, 4, getdate())

you should also add some controls like checking the month of the new date and new year etc...
0
 
Steve KrileAuthor Commented:
But, not every month has 4 weeks.  Right?  So, what about early dates like 1,2,3?  The above for May 1, 2011 would result in May 29.  Or is that your point....check the resulting date and if it's in the "target" month, write it - otherwise, don't copy it?
0
 
tigin44Commented:
yes you can improve your solution...
what will you in the case above ignore or shift it to another time slut?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Aaron ShiloChief Database ArchitectCommented:
hi you can use these select statements to find what you need

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
0
 
Steve KrileAuthor Commented:
@tigin - I'm pretty sure I want to ignore a date that would spill past the target month.  Even though this may lead to some misleading scenarios...when the user copies the previous month, everything April 29 and 30 would be blank.

Maybe it would make more sense to take each day of the target month, subtract four weeks, and copy whatever is in the previous 4th weeks day.  That way, you would never spill over.

Then a date like April 29th would look back 4 weeks and see April 2.  I think Copying that would still be logical.  Hmmmm.
0
 
Steve KrileAuthor Commented:
Didn't mean to take so long getting back to this, but this is the approach I'll be taking.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now