select wo_id, date_assigned, bl_id, description from wo where time_closed is null order by date_assigned

select user_info, user_name, user_pwd from afm_users

select * from afm_users where user_name = 'a9984'
select user_pwd from afm_users where user_name = 'afm'

select * from afm_users

select * from wr
select * from afm_users where user_name = 'A9984'
select eq_std, eq_id, bl_id from eq

select distinct eq_std from eq order by eq_std

select distinct address1 from bl where address1 is not null order by address1 

select address1, bl_id  from bl


select top 1 bl_id from bl where address1 = '5595 Smoke Tree'



-- try this
select 
	bl_id,
	address1
from 
	bl 
where
	( select distinct address1 from bl where address1 is not null )
	
-- end

SELECT * FROM AC where bl_id is not null

select * from afm_users where bl_id_list is not null

select * from afm_groups


select * from afm_roles

select * from afm_cats

create table #tempuser1( 
	rowid int IDENTITY(1,1) PRIMARY KEY,
	address varchar(50)
	)

insert into #tempuser1 ( address ) 
 select distinct address1 from bl where address1 is not null  

drop table #tempuser1
select * from #tempuser1

drop table #tempuser2
select * from #tempuser2


CREATE TABLE #tempuser2(
	rowid int IDENTITY(1,1) PRIMARY KEY,
	bl_id char(8) )

insert into #tempuser2 ( bl_id )
	select bl.bl_id from bl, #tempuser1 where #tempuser1.address = bl.address1
		

select top 1 address1, bl_id from bl where address1 = '5595 Smoke Tree'

select #tempuser2.bl_id from #tempuser2, bl where #tempuser1.address = bl.address1

select bl.address1
from bl
inner 

select 
	distinct address1 
from 
	bl 
where 
	address1 is not null
and
	( select #tempusers2.bl_id from #tempusers2, bl where bl.address1 )


select address1, MAX(bl_id) as bl_id from bl group by address1

INSERT INTO wr (bl_id, 
		date_requested, 
		description, 
		eq_id, 
		phone, 
		priority, 
		prob_type, requestor, status, time_requested) 
VALUES ('COL506', getdate(), 'durn thing is all over the place!', 'ZXZ506_ALARM', '555-1212', 99, 'CUSTODIAL_NO_CHG', 'a9984', 'R', getdate() ) 

select * from eq where eq_id = 'AC_PGE_3_5T'


select * from wr
select * from fl


select distinct eq_std, MAX(eq_id) as eq_id from eq group by eq_std

INSERT INTO wr (bl_id, 
		date_requested, 
		description, 
		eq_id, fl_id, 
		phone, priority, 
		prob_type, requestor, 
		rm_id, status, time_requested) 

VALUES ('SAB042', getdate(), 'This is the time for all good men to come to the aid of their country.', 'ZXZ506_ALARM', '1', 'x80536', 75, 'GROUNDS_CHG', 'a9984', '1', 'R', getdate()) 


INSERT INTO wr (bl_id, date_requested, description, eq_id, phone, priority, prob_type, requestor, status, time_requested) VALUES ('CRS028', getdate(), 'this is a test item', 'ZXZ509_FOUN', '555-1212', 50, 'MAINT_CHG', 'a9984', 'R', getdate() ) 

INSERT INTO wr (bl_id, date_requested, description, eq_id, phone, priority, prob_type, requestor, status, time_requested) VALUES ('CRS028', getdate(), 'this is a test item', 'ZXZ509_FOUN', '555-1212', 50, 'MAINT_CHG', 'a9984', 'R', getdate() ) 

INSERT INTO wr (bl_id, date_requested, description, eq_id, phone, priority, prob_type, requestor, status, time_requested) VALUES ('SAB042', getdate(), 'this is really nothing special', 'ZXZ506_LIGHT', '555-8989', 50, 'MAINT_CHG', 'a9984', 'R', getdate() ) 




select 
	wr_id, priority, 
	( SELECT CONVERT(CHAR(10),date_requested,111) ) as date_requested, 
	( SELECT CONVERT(CHAR(8),time_requested,108) ) AS time_requested, 
	description, 
	( select address1 from bl where bl.bl_id = wr.bl_id ) as building,
	phone,
	( select afm_users.user_info from afm_users where afm_users.user_name = wr.requestor ) as requestor

from 
	wr 
where 
	description is not null 
and 
	date_completed is null

order by building


select * from afm_users
select * from wr