update

select T_draft1.customer_name,T_draft1.mannum,syupin_irai2.kaitori_no
from [dbo].[T_draft1] T_draft1,[dbo].[syupin_irai2] syupin_irai2
where T_draft1.touroku_date <='2017-11-03'
and status_cd = 1
and syupin_irai2.kaitori_no = T_draft1.kaitori_no
and syupin_irai2.location_no = ''
and not (T_draft1.kaitori_no='')
and T_draft1.kaitori_no is not null
group by T_draft1.customer_name,T_draft1.mannum,syupin_irai2.kaitori_no,T_draft1.touroku_date
order by T_draft1.touroku_date asc






select T_draft1.mannum,T_draft1.customer_name,syupin_irai2.kaitori_no
from [dbo].[T_draft1] T_draft1,[dbo].[syupin_irai2] syupin_irai2,[dbo].[web_payment] web_payment
where T_draft1.touroku_date <=dateadd(day,-19,getdate())
and status_cd = 1
and syupin_irai2.kaitori_no = T_draft1.kaitori_no
and syupin_irai2.location_no = ''
and not (T_draft1.kaitori_no='')
and T_draft1.kaitori_no is not null
and syupin_irai2.kaitori_no = web_payment.kaitori_no
group by T_draft1.customer_name,T_draft1.mannum,syupin_irai2.kaitori_no,T_draft1.touroku_date
order by T_draft1.touroku_date asc




--<\distinct用\>--
select distinct T_draft1.mannum,T_draft1.customer_name,syupin_irai2.kaitori_no
from [dbo].[T_draft1] T_draft1,[dbo].[syupin_irai2] syupin_irai2
where status_cd = 1
and syupin_irai2.kaitori_no = T_draft1.kaitori_no
and syupin_irai2.location_no = ''
order by T_draft1.mannum asc










select *
from  [dbo].[T_draft1] 
where auto_no in ('999999','1000000')
and customer_name like '顔%'








select *
from  [dbo].[T_draft1] 
where auto_no in ('999999','1000000')


select *
from  [dbo].[T_draft1] 
where customer_name = '顔試験用'




update [dbo].[T_draft1] 
set status_cd=1
where auto_no in ('999999','1000000')
























and syupin_irai2.location_no = ''
and not (T_draft1.kaitori_no='')








select kaitori_no 
from web_payment
where kaitori_no = ''








select count(*) 
from web_payment
where kaitori_no='8889'




-----update　status


select * 
from 買取下書き
where 買取下書き.ステータス=1
and 買取下書き.その他='d21704'
and 買取下書き.出品者='顔建霖123'




update 買取下書き
set 買取下書き.ステータス=1
where 買取下書き.ステータス=2
and 買取下書き.その他='d21704'
and 買取下書き.出品者='顔建霖123'
-----


select * 
from 買取下書き
where 買取下書き.ステータス=1
and 買取下書き.その他='zidn576326'
and 買取下書き.出品者='顔建霖123'


update 買取下書き
set 買取下書き.ステータス=1
where 買取下書き.ステータス=2
and 買取下書き.その他='zidn576326'
and 買取下書き.出品者='顔建霖123'
------


select * 
from 買取下書き
where 買取下書き.ステータス=1
and 買取下書き.その他='zidn576326'
and 買取下書き.出品者='笹本奈緒子'


update 買取下書き
set 買取下書き.ステータス=1
where 買取下書き.ステータス=2
and 買取下書き.その他='zidn576326'
and 買取下書き.出品者='笹本奈緒子'






-----#############


select *
from [dbo].[ikkatutorihikikakutei_mail]
where kaitori_no = 'd21704'




delete 
from [dbo].[ikkatutorihikikakutei_mail]
where kaitori_no = 'd21704'






-------###########


select *
from firikomi
where mannum=971928
and irai_date='2017/11/29'




select *
from firikomi
where mannum=971928
and irai_date < dateadd(day,1,getdate())
and irai_date > dateadd(day,-1,getdate())




select *
from firikomi
where irai_date < dateadd(day,1,getdate())
and irai_date > dateadd(day,-1,getdate())




delete from firikomi
where irai_date < dateadd(day,1,getdate())
and irai_date > dateadd(day,-1,getdate())








--
--SELECT date(NOW());




select GETDATE();




SELECT CONVERT(nvarchar(8), GETDATE(), 112)


SELECT LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', '/'), 10)
SELECT CONVERT(VARCHAR(10), GETDATE(), 111)










update T_draft1
set T_draft1.status_cd=2
where T_draft1.auto_no=1




INSERT 
INTO [dbo].[ikkatutorihikikakutei_mail] ( 
  [kaitori_no]
  , [mannum]
  , [touroku_date]
  , [mail_send_date]
  , [syupinsya]
) 
VALUES ( 
  1111111,
  1111111,
   cast(now() as date),
  '',
  '出品者'
)












INSERT 
INTO [dbo].[ikkatutorihikikakutei_mail] ( 
  [kaitori_no]
  , [mannum]
  , [touroku_date]
  , [mail_send_date]
  , [syupinsya]
) 
VALUES ( 
  1111112,
  1111111,
  CONVERT(VARCHAR(10), GETDATE(), 111),
  '',
  '出品者'
)




select *
from ikkatutorihikikakutei_mail
where ikkatutorihikikakutei_mail.kaitori_no='1111112'





INSERT 
INTO [dbo].[firikomi] ( 
  [irai_no]
  , [irai_date]
  , [furikomi_date]
  , [furikomi_price]
  , [mannum]
  , [kaitori_form]
) 
VALUES ( 
  '振込み依頼番号'
  ,'振込み依頼日'
  ,'振込み日'
  ,'振込み金額'
  , '出品者番号'
  , '買取フォーム'
) 






select *
from usr 
where usr.ユーザID=508