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
328 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

929 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

11 Experts available now in Live!

Get 1:1 Help Now