desscartes desscartes - 12 days ago 9
SQL Question

How can I use count with INNER JOIN and date,where together on one SQL sentence

I have two sql tables below, I want to join two tables with inner join. And then I want to count alert number for device like Result table below. But I want to do this for some date range(for examle between date2- date6).

I try to write a sql sentence but it did not work. How can I do this?

First Table:

Message Date ID




Alert date1 1
Alert date2 2
Alert date3 3
Alert date4 1
Alert date5 1
Alert date6 2
Alert date7 3


Second Table:

Device ID




PC1 1
PC2 2
PC3 3
PC1 1
PC1 1
PC2 2
PC3 3


My Final Table:

Message Device Count




Alert PC1 2
Alert PC2 2
Alert PC3 1


My SQL Sentence:

select table1.message,table1.date, table2.device
Count (table2.device)
From table1 INNER JOIN table1
ON table1.id = table2.id
Where DATEDIFF(DAY,TimeStamp,GETDATE()) between date2 and date6
Group By table2.device

Answer
BEGIN TRAN
CREATE TABLE #Table(_Message VARCHAR(100), _Date VARCHAR(100),ID INT)
CREATE TABLE #Table1(_Device VARCHAR(100),ID INT)

INSERT INTO #Table(_Message , _Date ,ID )
SELECT 'Alert','date1',1 UNION ALL
SELECT 'Alert','date2',2 UNION ALL
SELECT 'Alert','date3',3 UNION ALL
SELECT 'Alert','date4',1 UNION ALL
SELECT 'Alert','date5',1 UNION ALL
SELECT 'Alert','date6',2 UNION ALL
SELECT 'Alert','date7',3

INSERT INTO #Table1(_Device ,ID )
SELECT 'PC1',1 UNION ALL
SELECT 'PC2',2 UNION ALL
SELECT 'PC3',3 UNION ALL
SELECT 'PC1',1 UNION ALL
SELECT 'PC1',1 UNION ALL
SELECT 'PC2',2 UNION ALL
SELECT 'PC3',3

SELECT _Message , _Device,COUNT(*) [COUNT]
FROM #Table
JOIN
(
SELECT ID,_Device
FROM #Table1
GROUP BY ID,_Device
)A ON A.ID = #Table.ID 
WHERE _Date BETWEEN 'date2' AND 'date6'
GROUP BY _Message , _Device

ROLLBACK TRAN