asked
tsql query
hi, i have a query which gets all the active id's in the system. The below condition is for a single day. Declare @date1= '01-01-2012' Select
a.ID, a.locID, @date1 AS OperatingDate from dataset A where A.startDate <= @date1 AND (A.endDate >= @date1 or a.endDate is null) AND A.condition <> 1 the data looks like this when execution of this query id locID OperatingDate 716 30411 1-1-2011 828 10615 1-1-2011 . . some 100 records But when given a range i want to get the data in the following format id locID OperatingDate 7165 30411 1-2-2011 8284 10615 1-2-2011 . . some 100 records 7165 30411 1-3-2011 8284 10615 1-3-2011 . . some 92 records 7165 30411 1/3/2011 8284 10615 1/3/2011 . . some 99 records How can i achieve it,not sure what would be the best option to use a while or cursor or a CTE
What's causing the issue? Do your startDate and endDate fields have times on them? Also for range queries, what date (start or end) should be given as the OperatingDate?
no they dont. Sorry for the confusion none of the dates have times on them. its the excel formatting. I will edit them . The range for the queries could be anywhere between a week or month or a year. So. date1 = '2011-01-01 and date2 = '2011-01-31' but if we put start and end date as the range, we would get the one that are active for that particular period and that way its not getting the results i am expecting . instead the output should for each day of the range get the active members and give the output list for all the dates in the range.kind of union all of each of the days in the range.
So what determines an 'active' member, is it something to do with the end date? In other words what do the records you **don't want** look like?
Take a look at this quick example. I create a calendar cte that runs from the first date of your date range to the last, then join onto that for each record that fits within that date range (or enddate is null). Is this closer to what you need? drop table YourTable create table YourTable (ID int, locID int, startdate datetime, enddate datetime, condition int) insert into YourTable select 7165,30411,'1 jan 2012','3 jan 2012',0 insert into YourTable select 8284,10615,'1 jan 2012','3 jan 2012',0 insert into YourTable select 6666,30411,'20 jan 2012','25 jan 2012',0 insert into YourTable select 7777,10615,'2 jan 2012','14 jan 2012',0 insert into YourTable select 8888,30411,'30 jan 2012','3 feb 2012',0 insert into YourTable select 9999,10615,'3 jan 2012',null,0 --your original 1-day query Declare @date1 date = '01-01-2012' Select
a.ID, a.locID, @date1 AS OperatingDate from YourTable A where A.startDate <= @date1 AND (A.endDate >= @date1 or a.endDate is null) AND A.condition <> 1 --new query with date range Declare @date2 date = '01 jan 2012'; Declare @date3 date = '25 jan 2012'; with tally as (select top 10000 ROW_NUMBER() over(order by sv1.number) N from master.dbo.spt_values sv1, master.dbo.spt_values sv2) , date_cte as (select dateadd(d, N-1, @date2) as CalendarDate from tally where dateadd(d, N-1, @date2) between @date2 and @date3) select
a.ID, a.locID, date_cte.CalendarDate from YourTable a join date_cte on date_cte.CalendarDate between a.startdate and isnull(a.enddate, '31 dec 9999')
Kev,i really appreciate the effort. but there is one problem i am seeing is that it not picking the right version from the datatable. 30411 2004-04-01 2010-11-01 30411 2010-11-01 2011-09-01 30411 2011-11-01 2012-09-20 30411 2011-09-01 2011-11-01 30411 2012-09-20 2012-10-01 30411 2012-10-01 2012-11-01 30411 2012-11-01 NULL the result set is getting me only the version 2011-11-01 2012-09-20 but one i am expecting is 30411 2012-10-01 2012-11-01
and what date range are you using to try and get that one result?
so this is something that I want to pass as parameters. for example if I give @date1 = '2003-10-01' and @date2 = '2003-10-31'. this member does not even show up. for the second instance if I pass @date1 as 2010-05-01 and @date2 as 2010-05-31, there are supposed to be 31 rows with each date for of a
month.to differentiate we could even add the generated ID as I have modified in the previous comment for the first range for this member no rows of dates mentioned above primaryid id locID operatingdate 1 716 30411 2010-05-01 1 716 30411 2010-05-02 1 716 30411 2010-05-03 1 716 30411 2010-05-04 . . . 1 716 30411 2010-05-31
The answer above does do this, here's a cut down example that returns 31 rows create table YourTable (ID int, locID int, startdate datetime, enddate datetime, condition int) insert into YourTable select 7165,30411,'1 apr 2004','1 nov 2010',0 --new query with date range Declare @date2 date = '1 may 2010'; Declare @date3 date = '31 may 2010'; with tally as (select top 10000 ROW_NUMBER() over(order by sv1.number) N from master.dbo.spt_values sv1, master.dbo.spt_values sv2) , date_cte as (select dateadd(d, N-1, @date2) as CalendarDate from tally where dateadd(d, N-1, @date2) between @date2 and @date3) select
a.ID, a.locID, date_cte.CalendarDate from YourTable a join date_cte on date_cte.CalendarDate between a.startdate and isnull(a.enddate, '31 dec 9999')
10 People are following this question.
