Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
334 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

861 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