1

In my code using SQL Server, I am comparing data between two months where I have the exact dates identified. I am trying to find if the value in a certain column changes in a bunch of different scenarios. That part works, but what I'd like to do is make it so that I don't have to always go back to change the date each time I wanted to get the results I'm looking for. Is this possible?

My thought was that adding a WITH clause, but it is giving me an aggregation error. Is there anyway I can go about making this date problem simpler? Thanks in advance

EDIT

Ok I'd like to clarify. In my WITH statement, I have:

 select distinct
 d.Date
 from Database d

Which returns:

+------+-------------+
|      | Date        |
+------+-------------|
|    1 |  01-06-2017 | 
|    2 |  01-13-2017 |
|    3 |  01-20-2017 |
|    4 |  01-27-2017 |
|    5 |  02-03-2017 |
|    6 |  02-10-2017 |
|    7 |  02-17-2017 |
|    8 |  02-24-2017 |
|    9 |   ........  |
+------+-------------+

If I select this statement and execute, it will return just the dates from my table as shown above. What I'd like to do is be able to have sql that will pull from these date values and compare the last date value from one month to the last date value of the next month. In essence, it should compare the values from date 8 to values from date 4, but it should be dynamic enough that it can do the same for any two dates without much tinkering.

Improve this question
5
  • You could wrap this up in a stored procedure which accepts parameters which will simplify it. You will always need to provide date values unless you describe some logic to derive them automatically What kind of database is this? Commented Mar 24, 2017 at 1:29
  • should date1 BETWEEN begindate and enddate work on this? Commented Mar 24, 2017 at 1:47
  • So you want this to roll along automatically with the currect date? Please explain in detail the rules. If today is Fri 24 March, what is the begin and end date to be used? Commented Mar 24, 2017 at 1:49
  • @programr .. this should help you out determining dynamic dates are per problem. Commented Mar 24, 2017 at 3:16
  • I'm still confuse about what you need. Add some data samples and expected results. Commented Mar 24, 2017 at 20:16

2 Answers 2

Reset to default
1

If I didn't misunderstand your request, it seems you need a numbers table, also known as a tally table, or in this case a calendar table.

Recommended post: https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

Basically, you create a table and populate it with numbers of year's week o start and end dates. Then join your main query to this table.

+------+-----------+----------+
| week | startDate | endDate  |
+------+-----------+----------+
|    1 |  20170101 | 20170107 |
|    2 |  20170108 | 20170114 |
+------+-----------+----------+

Select b.week, max(a.data) from yourTable a 
 inner join calendarTable b 
  on a.Date between b.startDate and b.endDate
group by b.week
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

dynamic dates to filter by BETWEEN

select dateadd(m,-1,dateadd(day,-(datepart(day,cast(getdate() as date))-1),cast(getdate() as date)))  -- 1st date of last month

select dateadd(day,-datepart(day,cast(getdate() as date)),cast(getdate() as date))  -- last date of last month

select dateadd(day,-(datepart(day,cast(getdate() as date))-1),cast(getdate() as date))  -- 1st date of current month

select   dateadd(day,-datepart(day,dateadd(m,1,cast(getdate() as date))),dateadd(m,1,cast(getdate() as date))) -- last date of the month
Improve this answer

2 Comments

this should help you out determining dynamic dates are per problem.
these are not subqueries.. that's your formula to get dynamic dates. anywhere you like in short.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.