-
SUb query bug
I have the following sql error with below statement.
Internal SQL Server error.
Looking this up it looks like this is a bug in sql 2000, anyone dealt with this before?
declare
@SiteID varchar(35),
@HousingID varchar(35),
@BeginDate datetime,
@EndDate datetime
set @SiteID = 'eastern'
set @HousingID = 'A'
set @BeginDate = '04/01/2007'
set @EndDate = '04/17/2007'
select v1.*,v2.*
from MARDemo.dbo.mar_vw_rpt_orders_history v1
inner join
(Select distinct orderid,hh,scheduletimesorderby,
Min(Case When scheduledday = 1 Then medadminstat End) As Day1,
Min(Case When scheduledday = 2 Then medadminstat End) As Day2,
Min(Case When scheduledday = 3 Then medadminstat End) As Day3,
Min(Case When scheduledday = 4 Then medadminstat End) As Day4,
Min(Case When scheduledday = 5 Then medadminstat End) As Day5,
Min(Case When scheduledday = 6 Then medadminstat End) As Day6,
Min(Case When scheduledday = 7 Then medadminstat End) As Day7,
Min(Case When scheduledday = 8 Then medadminstat End) As Day8,
Min(Case When scheduledday = 9 Then medadminstat End) As Day9,
Min(Case When scheduledday = 10 Then medadminstat End) As Day10,
Min(Case When scheduledday = 11 Then medadminstat End) As Day11,
Min(Case When scheduledday = 12 Then medadminstat End) As Day12,
Min(Case When scheduledday = 13 Then medadminstat End) As Day13,
Min(Case When scheduledday = 14 Then medadminstat End) As Day14,
Min(Case When scheduledday = 15 Then medadminstat End) As Day15,
Min(Case When scheduledday = 16 Then medadminstat End) As Day16,
Min(Case When scheduledday = 17 Then medadminstat End) As Day17,
Min(Case When scheduledday = 18 Then medadminstat End) As Day18,
Min(Case When scheduledday = 19 Then medadminstat End) As Day19,
Min(Case When scheduledday = 20 Then medadminstat End) As Day20,
Min(Case When scheduledday = 21 Then medadminstat End) As Day21,
Min(Case When scheduledday = 22 Then medadminstat End) As Day22,
Min(Case When scheduledday = 23 Then medadminstat End) As Day23,
Min(Case When scheduledday = 24 Then medadminstat End) As Day24,
Min(Case When scheduledday = 25 Then medadminstat End) As Day25,
Min(Case When scheduledday = 26 Then medadminstat End) As Day26,
Min(Case When scheduledday = 27 Then medadminstat End) As Day27,
Min(Case When scheduledday = 28 Then medadminstat End) As Day28,
Min(Case When scheduledday = 29 Then medadminstat End) As Day29,
Min(Case When scheduledday = 30 Then medadminstat End) As Day30,
Min(Case When scheduledday = 31 Then medadminstat End) As Day31
from MARDemo.dbo.mar_vw_history_mo
-- where fullmedadmindate between convert(datetime,(convert(varchar(10),@begindate,1 01)))
-- and convert(datetime,(convert(varchar(10),@enddate,101 )))
-- Group By orderid,scheduletimesorderby,hh
) v2
on v1.orderid = v2.orderid
and v1.PatientCurrentSiteID = @siteid
and (@HousingID is null or v1.HousingID = @HousingID)
order by v1.inmateid,v2.orderid,v2.scheduletimesorderby
end
------------------------------------------ARCHIVE--------------------------------------
else
if @database = 'Archive'
begin
select v1.*, v2.* from MARDemoArchive.dbo.mar_vw_rpt_orders_history v1
inner join
(Select orderid,hh,scheduletimesorderby,
Min(Case When scheduledday = 1 Then medadminstat End) As Day1,
Min(Case When scheduledday = 2 Then medadminstat End) As Day2,
Min(Case When scheduledday = 3 Then medadminstat End) As Day3,
Min(Case When scheduledday = 4 Then medadminstat End) As Day4,
Min(Case When scheduledday = 5 Then medadminstat End) As Day5,
Min(Case When scheduledday = 6 Then medadminstat End) As Day6,
Min(Case When scheduledday = 7 Then medadminstat End) As Day7,
Min(Case When scheduledday = 8 Then medadminstat End) As Day8,
Min(Case When scheduledday = 9 Then medadminstat End) As Day9,
Min(Case When scheduledday = 10 Then medadminstat End) As Day10,
Min(Case When scheduledday = 11 Then medadminstat End) As Day11,
Min(Case When scheduledday = 12 Then medadminstat End) As Day12,
Min(Case When scheduledday = 13 Then medadminstat End) As Day13,
Min(Case When scheduledday = 14 Then medadminstat End) As Day14,
Min(Case When scheduledday = 15 Then medadminstat End) As Day15,
Min(Case When scheduledday = 16 Then medadminstat End) As Day16,
Min(Case When scheduledday = 17 Then medadminstat End) As Day17,
Min(Case When scheduledday = 18 Then medadminstat End) As Day18,
Min(Case When scheduledday = 19 Then medadminstat End) As Day19,
Min(Case When scheduledday = 20 Then medadminstat End) As Day20,
Min(Case When scheduledday = 21 Then medadminstat End) As Day21,
Min(Case When scheduledday = 22 Then medadminstat End) As Day22,
Min(Case When scheduledday = 23 Then medadminstat End) As Day23,
Min(Case When scheduledday = 24 Then medadminstat End) As Day24,
Min(Case When scheduledday = 25 Then medadminstat End) As Day25,
Min(Case When scheduledday = 26 Then medadminstat End) As Day26,
Min(Case When scheduledday = 27 Then medadminstat End) As Day27,
Min(Case When scheduledday = 28 Then medadminstat End) As Day28,
Min(Case When scheduledday = 29 Then medadminstat End) As Day29,
Min(Case When scheduledday = 30 Then medadminstat End) As Day30,
Min(Case When scheduledday = 31 Then medadminstat End) As Day31
from MARDemoArchive.dbo.mar_vw_history_mo
where MARDemoArchive.dbo.mar_vw_history_mo.fullmedadmind ate
between convert(datetime,(convert(varchar(10),@begindate,1 01))) and convert(datetime,(convert(varchar(10),@enddate,101 )))
Group By orderid,scheduletimesorderby,hh) v2
on v1.orderid = v2.orderid
and v1.PatientCurrentSiteID = @siteid
and (@HousingID is null or v1.HousingID = @HousingID)