Reputation: 2259
I've a view created by referring to two synonyms. The current query I've to harvest my View Table is as below
SELECT dbo.synonym1.JobNo
, dbo.synonym1.Customer
, dbo.synonym2.PostalService
, dbo.synonym2.FirstDate
, dbo.synonym2.SecondDate
FROM dbo.synonym1
INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
WHERE dbo.synonym2.PostalService = 'UPS'
Now I would like to find the difference between dbo.synonym2.FirstDate
and dbo.synonym2.SecondDate
in each row and INSERT
the same row again into the View Table as many times as there is a difference.
For example if the FirstDate
is 2012-06-03 00:00:00.000
and SecondDate
is 2012-06-05 00:00:00.000
then it should insert the same row twice.
I'm using SQL Server 2008 R2 and I'm a newbie to SQL. Please help me out
Sample data that gets populated currently with the above query in the view is as below,
JobNo Customer PostalService FirstDate SecondDate
1 ABC UPS 2012-06-03 00:00:00.000 2012-06-03 00:00:00.000
2 DEF UPS 2012-06-03 00:00:00.000 2012-06-06 00:00:00.000
3 XYZ UPS 2012-06-03 00:00:00.000 2012-06-05 00:00:00.000
I would need a result as below
JobNo Customer PostalService FirstDate SecondDate
1 ABC UPS 2012-06-03 00:00:00.000 2012-06-03 00:00:00.000
2 DEF UPS 2012-06-03 00:00:00.000 2012-06-05 00:00:00.000
2 DEF UPS 2012-06-03 00:00:00.000 2012-06-05 00:00:00.000
2 XYZ UPS 2012-06-03 00:00:00.000 2012-06-05 00:00:00.000
3 XYZ UPS 2012-06-03 00:00:00.000 2012-06-06 00:00:00.000
3 XYZ UPS 2012-06-03 00:00:00.000 2012-06-06 00:00:00.000
If you see the difference between the dates for JobNo 2 is 3 so data is repeated three times. and for the JobNo 3 is 2 so data is repeated twice.
Upvotes: 0
Views: 176
Reputation:
Create Table #temp
(
StartDate DateTime,
EndDate DateTime
)
Insert into #temp(StartDate, EndDate)
Values('2012-06-03 00:00:00.000', '2012-06-03 00:00:00.000');
Insert into #temp(StartDate, EndDate)
Values('2012-06-03 00:00:00.000', '2012-06-06 00:00:00.000');
Insert into #temp(StartDate, EndDate)
Values('2012-06-03 00:00:00.000', '2012-06-05 00:00:00.000');
With DateRange As
(
select Convert(DateTime, '2012-06-02 00:00:00.000') as StartDate,
DATEADD(dd, 1, Convert(DateTime, '2012-06-02 00:00:00.000')) as EndDate
Union All
select EndDate, DATEADD(dd, 1, EndDate) From DateRange
Where EndDate <= Convert(DateTime, '2012-06-10 00:00:00.000')
)
Select T.* from #temp T
Left Join DateRange R on ((T.StartDate < R.EndDate and T.EndDate > R.StartDate)
or (T.StartDate = R.EndDate and T.EndDate = R.StartDate))
order by T.EndDate
Drop table #temp
Upvotes: 0
Reputation: 1271241
Your request is a little confusing, because one doesn't generally insert into views.
However, the following will insert rows into a table
insert into ViewTable(JobNo, Customer, PostalService, FirstDate, SecondDate)
SELECT dbo.synonym1.JobNo, dbo.synonym1.Customer, dbo.synonym2.PostalService,
dbo.synonym2.FirstDate, dbo.synonym2.SecondDate
FROM dbo.synonym1 INNER JOIN
dbo.synonym2
ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
WHERE dbo.synonym2.PostalService = 'UPS' and
datediff(d, FirstDate, SecondDate) <> 0
You need to define ViewTable as an actual table though.
Upvotes: 1
Reputation:
it sounds like what you want is the union statement below to populate this temp table. unless i am misunderstanding what you are asking for.
DECLARE @MyTable as TABLE(JobNo VARHCAR(50), Customer VARCHAR(50),
Postalservice VARCHAR(50), MyDate DateTime)
INSERT into @MyTable
SELECT dbo.synonym1.JobNo
, dbo.synonym1.Customer
, dbo.synonym2.PostalService
, dbo.synonym2.FirstDate
FROM dbo.synonym1
INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
WHERE dbo.synonym2.PostalService = 'UPS'
UNION
SELECT dbo.synonym1.JobNo
, dbo.synonym1.Customer
, dbo.synonym2.PostalService
, dbo.synonym2.SecondDate
FROM dbo.synonym1
INNER JOIN dbo.synonym2 ON dbo.synonym1.JobNo = sbo.synonym2.JobNo
WHERE dbo.synonym2.PostalService = 'UPS'
INSERT into your view from @MyTable
Upvotes: 0