Solved

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

Posted on 2011-02-24
6
324 Views
Last Modified: 2012-08-14
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
Comment
Question by:Steve Krile
  • 3
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 34973812
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
 
LVL 16

Author Comment

by:Steve Krile
ID: 34973837
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
 
LVL 26

Expert Comment

by:tigin44
ID: 34973866
yes you can improve your solution...
what will you in the case above ignore or shift it to another time slut?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34973995
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
 
LVL 16

Author Comment

by:Steve Krile
ID: 34974062
@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
 
LVL 16

Author Closing Comment

by:Steve Krile
ID: 35156960
Didn't mean to take so long getting back to this, but this is the approach I'll be taking.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now