CODEWITHSUNDEEP
sqlsql-serverdatewith-statement
user7753835
user7753835

Reputation:

How to dynamically call date instead of hardcoding in WHERE clause?

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.

Upvotes: 1

Views: 442

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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

Upvotes: 0

Horaciux
Horaciux

Reputation: 6477

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

Upvotes: 1

Related Questions