Sql-server – Select Query With In Statement

sql serversql-server-2008-r2t-sql

I know this is not valid syntax, but my select statement below shows what I am trying to accomplish. What do I need to alter in order to succesfully run this select query?

Declare @Data Table
(
nID varchar(100)
,name varchar(100)
,saletotals float
,saledate date
)

Declare @GiveMeSaleTotals Table
(
nID varchar(100)
,datetocheck date
)

Insert into @Data (nID, name, saletotals ,saledate) Values
('111eee', 'Blue', '13.22', '01/01/2016')
,('111eee', 'Blue', '44.11', '01/03/2016')
,('111eee', 'Blue', '22.11', '01/08/2016')
,('222ffff', 'Red', '11.11', '01/11/2016')
,('222ffff', 'Red', '22.10', '01/22/2016')
,('4444ffff', 'Red', '22.10', '12/05/2016')
,('4444ffff', 'Red', '21.10', '12/05/2016')

Insert Into @GiveMeSaleTotals (nID, datetocheck) Values
('1111eee', GetDate())

Select 
*
FROM @Data
WHERE nID IN (Select nID from @GiveMeSaleTotals where @Data.saledate = datetocheck)

Best Answer

It's not completely clear what you're trying to do, but this might help:

declare @Data table (
    nID varchar(16)
  , name varchar(16)
  , saletotals float
  , saledate date
)

declare @GiveMeSaleTotals table (
  nID varchar(16)
, datetocheck date
)

Insert into @Data (nID, name, saletotals ,saledate) Values
 ('111eee', 'Blue', '13.22', '01/01/2016')
,('111eee', 'Blue', '44.11', '01/03/2016')
,('111eee', 'Blue', '22.11', '01/08/2016')
,('222ffff', 'Red', '11.11', '01/11/2016')
,('222ffff', 'Red', '22.10', '01/22/2016')
,('4444ffff', 'Red', '22.10', '12/05/2016')
,('4444ffff', 'Red', '21.10', '12/05/2016')

insert into @GiveMeSaleTotals (nID, datetocheck) values ('4444ffff', convert(date,GetDate()))

select d.*
  from @Data d 
    inner join @GiveMeSaleTotals g on d.nid=g.nid and d.saledate=g.datetocheck

results in:

    nID              name             saletotals             saledate
---------------- ---------------- ---------------------- ----------
4444ffff         Red              22.1                   2016-12-05
4444ffff         Red              21.1                   2016-12-05

Rextester link: http://rextester.com/SPJQX46455

Related Question