SQL Help

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  August 30, 2020 at 7:06 pm

  #3784757

  I have a table A with InDate, OuTDate and weight column. I have another table B column which has CheckDate in it. Basically, table B has  calendar dates in it.

  Need to three things.

  1. Pull only the row from  table A if my CheckDate is in between InDate and OutDate.
  2. If muliple rows results from step 1 (i.e if my checkdate falls between multiple InDate and OutDate), pick the maxIndate and maxOutDate where my checkDate falls into. In Other words, I need to pull only one row.
  3. If my checkdate doesn't fall between InDate and Outdate, still pull one row with check date closest to minIndate and minOutDate.

  In all, i just need to return one row for the given set.

  CREATE TABLE [dbo].[test2]

  (

  [InDate] [datetime2](7) NULL,

  [OutDate] [datetime2](7) NULL,

  [weight] [float] NULL

  )

  insert into test2(InDate, OutDate, weight)

  values('2018-09-21 00:00:00.0000000', '2019-01-07 00:00:00.0000000', 1207.362172)

  insert into test2(InDate, OutDate, weight)

  values('2017-11-10 00:00:00.0000000', '2018-04-30 00:00:00.0000000', 1194.8)

  insert into test2(InDate, OutDate, weight)

  values('2017-04-17 00:00:00.0000000', '2017-08-14 00:00:00.0000000', 1003.2)

  insert into test2(InDate, OutDate, weight)

  values('2017-07-15 00:00:00.0000000', '2017-11-20 00:00:00.0000000', 1199.2)

  insert into test2(InDate, OutDate, weight)

  values('2017-10-20 00:00:00.0000000', '2018-04-22 00:00:00.0000000' 675)

  insert into test2(InDate, OutDate, weight)

  values('2017-01-01 00:00:00.0000000', '2017-04-22 00:00:00.0000000' 675)

   

  Create tableB (checkdate [OutDate] [datetime2](7) NULL)

  Insert table B values ('2020-01-02')

  Insert table B values ('2020-01-03')

  Insert table B values ('2017-01-01')

  Insert table B values ('2017-01-03')

  Insert table B values ('2018-01-03')

  Any help would be appreciated

  Attachments:

  You must be logged in to view attached files.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  August 30, 2020 at 7:56 pm

  #3784762

  I rewrote your create table and inserts, because they didn't run. You don't need DateTime2 if you're not using time.

  CREATE TABLE [test2]
  (
  [InDate] [date] NULL,
  [OutDate] [date] NULL,
  [weight] [float] NULL
  );
  
  Create TABLE tableB ([OutDate] date);
  go
  
  insert into test2(InDate, OutDate, weight)
  values('2018-09-21 00:00:00.0000000', '2019-01-07 00:00:00.0000000', 1207.362172)
  
  insert into test2(InDate, OutDate, weight)
  values('2017-11-10 00:00:00.0000000', '2018-04-30 00:00:00.0000000', 1194.8)
  
  insert into test2(InDate, OutDate, weight)
  values('2017-04-17 00:00:00.0000000', '2017-08-14 00:00:00.0000000', 1003.2)
  
  insert into test2(InDate, OutDate, weight)
  values('2017-07-15 00:00:00.0000000', '2017-11-20 00:00:00.0000000', 1199.2)
  
  insert into test2(InDate, OutDate, weight)
  values('2017-10-20 00:00:00.0000000', '2018-04-22 00:00:00.0000000', 675)
  
  insert into test2(InDate, OutDate, weight)
  values('2017-01-01 00:00:00.0000000', '2017-04-22 00:00:00.0000000', 675)
  
  
  Insert into tableB values ('2020-01-02');
  Insert into tableB values ('2020-01-03');
  Insert tableB values ('2017-01-01');
  Insert tableB values ('2017-01-03');
  Insert tableB values ('2018-01-03');
  

  What do these two tables refer to in the real world? it sounds like you're trying to get total weight on each date given visits that span a range of dates. But who knows? Would help a LOT if you chose descriptive names for your tables. If you want that, it's something like this:

  CREATE TABLE [Visit]
  (
  [InDate] [date] NULL,
  [OutDate] [date] NULL,
  [weight] [float] NULL
  );
  
  Create TABLE Calendar ([TheDate] date);
  go
  TRUNCATE TABLE Visit;
  insert into Visit(InDate, OutDate, weight)
  values('2018-01-01', '2018-01-07', 1207.362172)
  ,('2018-01-10', '2018-01-30', 1194.8)
  ,('2018-01-14', '2018-01-17', 1003.2)
  ,('2018-01-15', '2018-01-20', 1199.2)
  ,('2018-01-20', '2018-01-22', 675)
  ,('2018-01-01', '2018-01-22', 675);
  
  
  CREATE TABLE Calendar(TheDate DATE PRIMARY KEY);
  GO
  
  INSERT INTO Calendar(TheDate) 
  SELECT DATEADD(day,n-1,'2018-01-01')
  FROM Testdb.dbo.Tally t
  WHERE n <= (SELECT DATEDIFF(day,MIN(InDate),MAX(OutDate)) FROM Visit);
  
  SELECT c.TheDate, SUM([weight]) AS DailyWeight
  FROM Calendar c
  LEFT JOIN Visit v
  ON c.TheDate >= v.InDate AND c.TheDate <= v.OutDate
  GROUP BY c.TheDate;

  • This reply was modified 4 years, 2 months ago by  pietlinden.