asked
Dynamic Date Range
I have this query that I am trying to find the count of IDS for each product between the two time ranges. The time range I want is between 201010 and 201109. but I don;t want it hardcoded. I don't want to hard code dates in because I want whenever the query is ran to look back at the 4 previous quarters. It is taking the time ranges into account. Do I need to add something else somewhere? SELECT COUNT (distinct
a1.ID)TotalIds, 'Last day of Previous Quarter' = dateadd(QUARTER, datediff(QUARTER, 0, getdate()), -1), 'First Day 4 Quarters ago' = dateadd(QUARTER, datediff(QUARTER, 0, getdate()) - 4, 0), a1.ProdID FROM table1 a1 JOIN table2 a2 ON a1.ProdID = a2. ProdID GROUP BY a1.ProdID
@Pavel Pawlowski already answered that you need a where clause, you just need to add it correctly. Please try this: declare @LastDayPrevQuarter datetime; declare @FirstDay4QuartersAgo datetime; declare @StartYearMonth varchar(6); declare @EndYearMonth varchar(6); select @LastDayPrevQuarter = dateadd(q, datediff(q, 0, getdate()), -1), @FirstDay4QuartersAgo = dateadd(q, datediff(q, 0, getdate()) - 4, 0), @StartYearMonth = convert(varchar(6), @FirstDay4QuartersAgo, 112), @EndYearMonth = convert(varchar(6), @LastDayPrevQuarter, 112); -- now you can do your select with the where clause: select count(distinct
a1.ID) TotalIds, [Last day of Previous Quarter] = @LastDayPrevQuarter, [First Day 4 Quarters ago] = @FirstDay4QuartersAgo, a1.ProdID from table1 a1 inner join table2 a2 on a1.ProdID = a2.ProdID where a1.YearMonth between @StartYearMonth and @EndYearMonth group by a1.ProdID; Oleg
You have to add a WHERE condition in which you filter the data according the time range you want. Let's say, you have a `aDate` field in the table1, then you add your `DATEADD` function to the WHERE Clause. WHERE a1.aDate >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 4, 0) AND a1.aDate
Ah.. so it's look like you `YearMonth` field is a `varchar` data type. And so if you put into the condition a `datetime` value, SQL server tries to convert the `YearMonth` do date time. But if your year month contains only year and month information, that it cannot be converted into date time. If you say have the `YearMonth` in format of `yyyymm`, then you can modify the condition to: where a1.YearMonth >= LEFT(6, CONVERT(varchar(8), DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 4, 0), 112)) AND a1.YearMonth
@Oleg, you are right and this is exactly what I have mentioned in the comment above. :-) (after @sqlnewb posted the name of that field) But it is not working for him, so it could be that the format yyyymm is not consistent in his field.
the convert code 112 returns format 20070828 My format is 200708. So i increased the left function it runs but doesnt retrun results. I think it is because they are different formats, so I can't compare them.: where a1.YearMonth >= LEFT(6, CONVERT(varchar(6), DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 4, 0), 112)) AND a1.YearMonth <= LEFT(6, CONVERT(varchar(6), GETDATE(), 112))
If you have different formats in the `YearMonth` field, then you are out of luck. You will have to standardize the format in that field. Of course, there could be a possibility to generate list of Year-Months for the period requested and the use `WHERE YearMonth IN ....` but this could be a road to hell. Better if you are able to standardize the field. Ideally if it could be an integer.
@Pavel Pawlowski Actually it looks like OP has the YearMonth column which is simply in YYYYMM format. It probably means that every record for the same month and year gets inserted with the same value for that column (201010 this month, 201009 for every record in September etc). If this is the case then convert(varchar(6), desiredDateTimeValue, 112); should do the trick.
