?
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
Medium Priority
?
342 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
[X]
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
  • 3
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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