October 4, 2012 at 8:09 am
Hello everyone
I need to do date difference in rows.
create table #temp1
(
[aId] [char](9) NOT NULL,
[dtDate] [datetime] NOT NULL
)
insert into #temp1 values ('001','2012-08-30 00:00:00.000')
insert into #temp1 values ('001','2012-08-31 00:00:00.000')
insert into #temp1 values ('001','2012-09-04 00:00:00.000')
insert into #temp1 values ('001','2012-09-05 00:00:00.000')
insert into #temp1 values ('001','2012-09-06 00:00:00.000')
insert into #temp1 values ('001','2012-09-07 00:00:00.000')
insert into #temp1 values ('002','2012-08-30 00:00:00.000')
insert into #temp1 values ('002','2012-08-31 00:00:00.000')
insert into #temp1 values ('002','2012-09-04 00:00:00.000')
insert into #temp1 values ('002','2012-09-05 00:00:00.000')
insert into #temp1 values ('002','2012-09-06 00:00:00.000')
insert into #temp1 values ('002','2012-09-07 00:00:00.000')
insert into #temp1 values ('002','2012-09-10 00:00:00.000')
insert into #temp1 values ('002','2012-09-11 00:00:00.000')
as per the task, i need to set parameter for dtDate. and from that i need to do date difference by aId.
so for e.g.
if dtDate = '2012-09-10' - 5(set date difference value)
then as per date difference, it will display values between '2012-09-10' and '2012-09-04'
if dtDate = '2012-09-06' - 5 (set date difference value)
then as per date difference, it will display values between '2012-09-06' and '2012-08-30'
please help me to do this.
Thanks
October 4, 2012 at 9:31 am
using some code found on stackoverflow you can try this.
create table #temp1
(
[aId] [char](9) NOT NULL,
[dtDate] [datetime] NOT NULL
)
insert into #temp1 values ('001','2012-08-30 00:00:00.000')
insert into #temp1 values ('001','2012-08-31 00:00:00.000')
insert into #temp1 values ('001','2012-09-04 00:00:00.000')
insert into #temp1 values ('001','2012-09-05 00:00:00.000')
insert into #temp1 values ('001','2012-09-06 00:00:00.000')
insert into #temp1 values ('001','2012-09-07 00:00:00.000')
insert into #temp1 values ('002','2012-08-30 00:00:00.000')
insert into #temp1 values ('002','2012-08-31 00:00:00.000')
insert into #temp1 values ('002','2012-09-04 00:00:00.000')
insert into #temp1 values ('002','2012-09-05 00:00:00.000')
insert into #temp1 values ('002','2012-09-06 00:00:00.000')
insert into #temp1 values ('002','2012-09-07 00:00:00.000')
insert into #temp1 values ('002','2012-09-10 00:00:00.000')
insert into #temp1 values ('002','2012-09-11 00:00:00.000')
declare @dtDate datetime,
@dtEnd datetime
set @dtDate = '2012-09-10'
set @dtEnd = dateadd(dd,-5,@dtDate)
;WITH dates AS (
SELECT @dtEnd AS dt
UNION ALL
SELECT DATEADD(dd, 1, dt)
FROM dates s
WHERE DATEADD(dd, 1, dt) < @dtDate)
SELECT *
FROM #temp1 tmp
inner join dates dts
on tmp.dtDate = dts.dt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply