Combining 3 tables

Transact-SQL
1

I have 3 tables - Production, History & Lookup. Production & History are identical and the History table contains any data over a month old from the Production table. The lookup table contains description information pertaining to data in the other table.

Tables -
HISTORY -
CREATE TABLE [dbo].[History](
[DTStamp] [datetime] NOT NULL,
[ProductionOrder] varchar NOT NULL,
[ItemNo] varchar NOT NULL,
[Quantity] varchar NOT NULL,
[LastModified] [datetime] NOT NULL
) ON [PRIMARY]

DATA -
CREATE TABLE [dbo].[Production](
[DTStamp] [datetime] NOT NULL,
[ProductionOrder] varchar NOT NULL,
[ItemNo] varchar NOT NULL,
[Quantity] varchar NOT NULL,
[LastModified] [datetime] NOT NULL
) ON [PRIMARY]

LOOKUP -
CREATE TABLE [dbo].[Lookup](
[ItemNo] varchar NULL,
[Description] varchar NULL,
[Description2] varchar NULL
) ON [PRIMARY]

I want to create a sql to output data from the either the Production or History table depending upon the user dates. The History table contains all data from more than 30 days from todays date. EG - Anything earlier than 09\10\2016 is stored in History, the newer data is logged into Production. If the user wants a range of dates, then the SQL must fetch the data from both the tables and get the corresponding description from the lookup table.

DATA in the tables -

HISTORY -

DTStamp PO ItemNo QTY LastModified
2016-06-22 17:45:03.517 P025439 8731-T499-XLXL 1 2016-06-22 13:46:00.327
2016-06-22 17:45:05.720 P025303 8730-T499-M 1 2016-06-22 13:46:00.327
2016-06-22 17:45:06.987 P025439 8731-T499-XLXL 1 2016-06-22 13:46:00.327
2016-06-27 17:45:08.347 P025303 8730-T499-M 1 2016-06-27 13:46:00.327
2016-06-27 17:45:11.547 P025439 8731-T499-XLXL 1 2016-06-27 13:46:00.327

PRODUCTION -

DTStamp PO ItemNo QTY LastModified
2016-10-02 21:31:10.837 P025564 8730-T499-S 1 2016-10-02 17:32:02.190
2016-10-02 21:31:13.557 P025564 8730-T499-S 1 2016-10-02 17:32:02.190
2016-10-03 21:31:16.213 P025564 8730-T499-S 1 2016-10-03 17:32:02.190
2016-10-04 21:31:18.417 P025564 8730-T499-S 1 2016-10-04 17:32:02.190

LOOKUP -

ItemNo Description Description2
8730-T499-S TEST DESC 1 8415016411719
8730-T499-M TEST DESC 2 8415016411717
8730-T499-XL TEST DESC 3 8415016411751
8731-T499-XLXL TEST DESC 4 8415016410916
8735-T499-S TEST DESC 5 8415016411719
8734-T499-L TEST DESC 6 8415015801355
8735-T499-SS TEST DESC 7 8415016411717

I need an output for a date range of 2016-06-27 to 2016-10-02 [The dates can vary] like -

DTStamp PO ItemNo QTY LastModified Description

2016-06-27 17:45:08.347 P025303 8730-T499-M 1 2016-06-27 13:46:00.327 TEST DESC 2
2016-06-27 17:45:11.547 P025439 8731-T499-XLXL 1 2016-06-27 13:46:00.327 TEST DESC 4
2016-10-02 21:31:10.837 P025564 8730-T499-S 1 2016-10-02 17:32:02.190 TEST DESC 1
2016-10-02 21:31:13.557 P025564 8730-T499-S 1 2016-10-02 17:32:02.190 TEST DESC 1

I am a newbie at this. Any help will be welcome. If there is a better way to do the history between 2 tables and the description, I will be happy to learn. The front end selects 2 dates - From and TO for the SQL.

Thanks,
MM.

2 
DECLARE @start_date date
DECLARE @end_date date
 
SET @start_date = ' 20160627'
SET @end_date = '20161002'

SELECT p.DTStamp, p.ProductionOrder AS PO, p.ItemNo, p.Quantity AS QTY, 
    p.LastModified, l.Description
FROM dbo.Production p
WHERE p.DTStamp >= @start_date AND p.DTStamp < DATEADD(DAY, 1, @end_date)
UNION ALL
SELECT h.DTStamp, h.ProductionOrder AS PO, h.ItemNo, h.Quantity AS QTY, h.LastModified, 
    l.Description
FROM dbo.History h
WHERE h.DTStamp >= @start_date AND h.DTStamp < DATEADD(DAY, 1, @end_date)
--ORDER BY ...
3

Thanks Scott.

I haven't implemented the above solution in its required place ,I will get to it shortly, but I used your example for another solution. But I have a problem in the new one -

I modified your query to -

Select OPCMachine, OPCPassedScanData, Count(OPCPassedScanData) As QTY, P.Description, P.Description2
From WareHouseData W INNER JOIN Lookup P
On W.OPCPassedScanData = P.Description2
Where LEFT(CONVERT(VARCHAR, W.DTStamp, 120), 10) >= '2016-10-01'
And LEFT(CONVERT(VARCHAR, W.DTStamp, 120), 10) <= '2016-10-29'
Group By OPCMachine, OPCPassedScanData, P.Description, P.Description2
UNION ALL
Select OPCMachine, OPCPassedScanData, Count(OPCPassedScanData) As QTY, P.Description, P.Description2
From WareHouseHistoryData H INNER JOIN Lookup P
On H.OPCPassedScanData = P.Description2
Where LEFT(CONVERT(VARCHAR, H.DTStamp, 120), 10) >= '2016-10-01'
And LEFT(CONVERT(VARCHAR, H.DTStamp, 120), 10) <= '2016-10-29'
Group By OPCMachine, OPCPassedScanData, P.Description, P.Description2
Order by OPCMachine, OPCPassedScanData, P.Description

This gives an output -

OPCMachine OPCPassedScanData QTY Description Description2
10 8415015801355 2 JKT,FLC,TAN 499,GEN 3-V2,L 8415015801355
10 8415015801355 1 JKT,FLC,TAN 499,GEN III-V2,L 8415015801355
10 8415016411717 2 SHT LTWT ECWCS ARMY TAN G3 8415016411717
20 8415015801355 2 JKT,FLC,TAN 499,GEN 3-V2,L 8415015801355
20 8415015801355 1 JKT,FLC,TAN 499,GEN III-V2,L 8415015801355
20 8415016411717 2 SHT LTWT ECWCS ARMY TAN G3 8415016411717

This is wrong !! Can you help me fix where I have erred ?

The existing data is -
WareHouseData Table :

SELECT TOP 1000 [warehousedata_ndx]
,[OPCMachine]
,[OPCPassedScanData]
,[OPCTrigger]
,[OPCServerAck]
,[DTStamp]
FROM [DBData].[dbo].[WareHouseData]

warehousedata_ndx OPCMachine OPCPassedScanData OPCTrigger OPCServerAck DTStamp
432011 10 8415015801355 1 0 2016-10-15 12:03:08.747
432012 10 8415016411717 1 0 2016-10-15 12:05:17.807
432013 20 8415016411717 1 0 2016-10-15 12:06:16.340
432014 20 8415015801355 1 0 2016-10-15 12:06:39.507

WareHouseHistoryDataTable :
SELECT TOP 1000 [WarehouseHistorydata_ndx]
,[OPCMachine]
,[OPCPassedScanData]
,[OPCTrigger]
,[OPCServerAck]
,[DTStamp]
,[LastModified]
FROM [DBData].[dbo].[WareHouseHistoryData]

WarehouseHistorydata_ndx OPCMachine OPCPassedScanData OPCTrigger OPCServerAck DTStamp LastModified
8332 15 8415015386747 1 0 2016-01-21 14:06:18.387 2016-10-15 00:39:32.043
8333 10 8415016411751 1 0 2016-01-21 14:06:21.693 2016-10-15 00:39:32.043
8334 25 8415015386747 1 0 2016-01-21 14:06:23.397 2016-10-15 00:39:32.043
8335 15 8415015386747 1 0 2016-01-21 14:06:24.400 2016-10-15 00:39:32.043
8336 25 8415015386747 1 0 2016-01-21 14:06:28.407 2016-10-15 00:39:32.043

LookUp Table :

SELECT TOP 1000 [No_]
,[Description]
,[Description2]
FROM [DBData].[dbo].[Lookup]

No_ Description Description2
8730-T499-S SHT LTWT ECWCS ARMY TAN G3 8415016411719
8730-T499-SS SHT LTWT ECWCS ARMY TAN G3 8415016411717
8730-T499-XL SHT LTWT ECWCS ARMY TAN G3 8415016411751
8731-T499-XLL DWR LTWT ECWCS ARMY TAN G3 8415016410916
8735-T499-S SHT LTWT ECWCS ARMY TAN G3 8415016411719
8734-T499-L JKT,FLC,TAN 499,GEN III-V2,L 8415015801355
8735-T499-SS SHT LTWT ECWCS ARMY TAN G3 8415016411717
8735-T499-XL SHT LTWT ECWCS ARMY TAN G3 8415016411751
8736-T499-XLL DWR LTWT ECWCS ARMY TAN G3 8415016410916
8738-T499-S DWR MDWT ECWCS ARMY TAN G3 8415016411232
8749-GSA-T499-L JKT,FLC,TAN 499,GEN 3-V2,L 8415015801355
8749-T499-L JKT,FLC,TAN 499,GEN 3-V2,L 8415015801355
8734-T499-M Test 1 Test 1

Thanks' for your help !!

MM