CODEWITHSUNDEEP
sqlsql-serverjoin
mbeasley
mbeasley

Reputation: 4874

SQL Query: Retrieving records based on criteria in multiple tables

The Setup

I'm using a SQL Server DB to track various cases in production at a manufacturing environment. The database has two tables:

  1. Status Table: this tracks the status of those cases, with one of the columns containing an integer code that represents the cases status. All cases with codes greater than 0 are "in production" while those with codes less than or equal to 0 are either "complete" or "canceled".
  2. Log Table: this tracks what changes have happened to a particular case and when those changes happened. The "what" aspect is captured both as a string and as a code. The "when" is a datetime field.

The Problem / Task / Question

I would like to query all cases that are in production AND any cases that have been completed or canceled within the past two days. In the log, I have a datetime column and a code specific to both cancelling or completing a case. I assume the query would maybe JOIN any cases from the log that have either of those codes and have datetimes within 48 hours prior of now. But I'm not quite sure how to go about doing this. The first part of my query (to get cases that are in production) is as follows:

SELECT * FROM test.dbo.status WHERE status > 0

Any help would be greatly appreciated. Thanks in advance!

UPDATE To clarify, two things:

  1. I would like the query to return all of the columns in the status table for records that match the criteria above, not just the ID or something.

  2. The table structures:

    CREATE TABLE [dbo].[status](
    [serial] [varchar](10) NOT NULL,
    [type] [varchar](50) NULL,
    [commit_date] [date] NULL,
    [sent_to_prod] [date] NULL,
    [target] [date] NULL,
    [sent_to_mfg] [date] NULL,
    [status] [int] NULL,
    [notes] [bit] NULL,
    [hold] [bit] NULL,
    [canceled] [bit] NULL,
    [priority] [bit] NULL,
    [vendors] [varchar](150) NULL,
    [rework] [varchar](50) NULL,
    [created_on] [datetime] NULL,
    [modified_on] [datetime] NULL
)

CREATE TABLE [dbo].[log](
    [serial] [varchar](13) NOT NULL,
    [action] [varchar](200) NOT NULL,
    [who] [varchar](80) NOT NULL,
    [time] [datetime] NOT NULL,
    [code] [varchar](20) NOT NULL
)

Upvotes: 0

Views: 2531

Answers (1)

OCary
OCary

Reputation: 3311

Given you didn't provide any structure, I'd start with something like.

Query 1: number of records returned will be the number of matching records from LogTable

SELECT StatusTable.ID, LogTable.When, etc etc etc.
FROM StatusTable INNER JOIN LogTable
  ON StatusTable.ID = LogTable.ID
WHERE StatusTable.status > 0
  OR (LogTable.WhatCode = 97   --Assumes 97 is your desired code.
  AND LogTable.When >= dateadd(hh, -48, GetDate()))

Added

Query 2: Use a sub query instead of a join to get desired IDs from LogTable. (you could also look at the EXISTS operator instead of IN)

SELECT StatusTable.ID, StatusTable.otherField, etc etc
FROM StatusTable
WHERE StatusTable.status > 0 
  OR StatusTable.ID IN (SELECT LogTable.ID FROM LogTable WHERE LogTable.WhatCode = 97   --Assumes 97 is your desired code.
      AND LogTable.When >= dateadd(hh, -48, GetDate()))

Upvotes: 2

Related Questions