Records not found in list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Records not found in list

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2014-01-28 : 15:09:42

I currently have a left join query set up, I am trying to identify items that where listed last week but not this week. The problem is that this will be automatically ran, and I don't want it to think all items from last week are not there this week just because the data hasn't been loaded yet. So far the way around this I have found is to loop through the client list to see if any data has been loaded for the week, if so then run the left join query.

Take this example of data

Date, Client, ID1, ID2
2014-01-21, Alpha, 1, A
2014-01-21, Alpha, 2, B
2014-01-28, Alpha, 1, A
2014-01-21, Bravo, 1, C

If I do the left join query if lists the item for client bravo because there is no match with a later date, but that is only because Bravo information has not been loaded yet.

select PriorData.* from DataTable as PriorData
left join DataTable as CurrentData
on (
PriorData.ID1 = CurrentData.ID1
and PriorData.ID2 = CurrentData.ID2
and PriorData.Client = CurrentData.Client
and CurrentData.Date > PriorData.Date
and CurrentData.Date > dateadd(day,-5,getdate())
)
WHERE
PriorData.Date < dateadd(day,-5,getdate())
PriorData.Date > dateadd(day,-12,getdate())
and CurrentData.ID1 is null

This query probably looks pretty dumb, but it's all I could come up with so far. Somehow I need it to know that if there is no 1/28 data then not to list items.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 16:36:28

[code]
DECLARE @DataTable TABLE
(
[Date] DATE,
Client VARCHAR(10),
ID1 INT,
ID2 CHAR(1)
)
INSERT INTO @DataTable VALUES('2014-01-21', 'Alpha', 1, 'A'), ('2014-01-21', 'Alpha', 2, 'B')
,('2014-01-28', 'Alpha', 1, 'A'), ('2014-01-21', 'Bravo', 1, 'C')

SELECT Client, ID1, ID2 FROM @DataTable a
WHERE [Date] = CONVERT([Date],DATEADD(WEEK, -1, GETDATE()))
AND NOT EXISTS (SELECT * FROM @DataTable b WHERE a.Client = b.Client AND a.ID1 = b.ID1 AND a.ID2 = b.ID2
AND b.[Date] = CONVERT([Date], GETDATE()))
[/code]

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

WebKill
Starting Member

32 Posts

Posted - 2014-01-28 : 17:15:39

Thanks for the quick reply, but your query does the same thing mine does. I should have specified the output I am looking for.

Given the table:

Date, Client, ID1, ID2
2014-01-21, Alpha, 1, A
2014-01-21, Alpha, 2, B
2014-01-28, Alpha, 1, A
2014-01-21, Bravo, 1, C

I would want to see only:

Date, Client, ID1, ID2
2014-01-21, Alpha, 2, B

Normally, as both of our queries do, the last line with Bravo client shows up because there is no match for him, but because there is no current week data for that client, I don't want to look at it.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 17:37:08

Use this instead...


;WITH CTE AS
(
SELECT *
FROM @DataTable
WHERE [Date] = CONVERT([Date],DATEADD(WEEK, -1, GETDATE()))
)
, CTE_A AS (SELECT * FROM @DataTable WHERE [Date] = CONVERT([Date], GETDATE()))
SELECT a.* 
FROM CTE a
INNER JOIN CTE_A b ON a.Client = b.Client AND a.ID1 <> B.ID1 AND a.ID2 <> b.ID2

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

WebKill
Starting Member

32 Posts

Posted - 2014-01-28 : 18:49:29

Awesome, it works on the exmaple data! I am trying to apply this to my data and I'm having a little trouble, I know there are 5 records that should come up, but nothing is displaying.

Here is what I have, I had to rename certain things in order to post. I assumed that the first WHERE is last weeks data, the data is always loaded on Monday or Tuesday, so I gave it range so that if it runs any day during the current week it should be the right data. Then assuming the second WHERE is the current data, I set it to anything > sunday.

WITH CTE AS
(
SELECT * from DataTable
where Type1 in ('This', 'That')
and Type2 = 'Other'
and RunDate between dateadd(day,2-datepart(dw,getdate())-8,getdate())
and dateadd(day,2-datepart(dw,getdate())-2,getdate())
and CompletionDate is null
)
, CTE_A AS (SELECT * FROM DataTable
WHERE Type1 in ('This', 'That')
and Type2 = 'Other'
and RunDate > dateadd(day,1-datepart(dw,getdate()),getdate()))SELECT a.*
FROM CTE a
INNER JOIN CTE_A b ON a.ClientID = b.ClientID
AND a.ID1 <> b.ID1
AND a.ID2 <> b.ID2
AND a.ID3 <> b.ID3
AND a.ID4 <> b.ID4
AND a.ID5 <> b.ID5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 05:06:54

quote:
Originally posted by WebKill

Thanks for the quick reply, but your query does the same thing mine does. I should have specified the output I am looking for.

Given the table:

Date, Client, ID1, ID2
2014-01-21, Alpha, 1, A
2014-01-21, Alpha, 2, B
2014-01-28, Alpha, 1, A
2014-01-21, Bravo, 1, C

I would want to see only:

Date, Client, ID1, ID2
2014-01-21, Alpha, 2, B

Normally, as both of our queries do, the last line with Bravo client shows up because there is no match for him, but because there is no current week data for that client, I don't want to look at it.


SELECT [Date],
Client,
ID1,
ID2
FROM
(
SELECT *,SUM(CASE WHEN DATEDIFF(wk,0,[Date]) = DATEDIFF(wk,0,GETDATE()) THEN 1 ELSE 0 END) OVER (PARTITION BY Client) AS Cnt,
SUM(CASE WHEN DATEDIFF(wk,0,[Date]) = DATEDIFF(wk,0,GETDATE()) THEN 1 ELSE 0 END) OVER (PARTITION BY Client,ID1) AS Cnt1
FROM Table
)t
WHERE Cnt > 0
AND Cnt1=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

WebKill
Starting Member

32 Posts

Posted - 2014-02-12 : 17:07:43

visakh16, I was able to get your method to work, but wanted to try the other way as well, but still havn't been able to get it to work, perhaps someone could show me what I am doing wrong?

DECLARE @DataTable TABLE
(
[Date] DATETIME,
Client varchar(10),
ItemType1 varchar(10),
ItemType2 varchar(10),
ID1 varchar(10),
ID2 varchar(10),
ID3 varchar(10),
ID4 varchar(10),
ID5 varchar(10)
)
INSERT INTO @DataTable VALUES
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '12345', '111', 'ABC', 'A1', 'Test1'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '23456', '111', 'BCD', 'B1', 'Test2'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '34567', '111', 'CDE', 'C1', 'Test3'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '45678', '111', 'DEF', 'D1', 'Test4'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '56789', '111', 'FGH', 'E1', 'Test5'),
('2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '12345', '111', 'ABC', 'A1', 'Test1'),
('2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '23456', '111', 'BCD', 'B1', 'Test2'),
('2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '34567', '111', 'CDE', 'C1', 'Test3'),
('2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '45678', '111', 'DEF', 'D1', 'Test4'),
('2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '56789', '111', 'FGH', 'E1', 'Test5'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '98765', '111', 'SDF', 'Z1', 'Test9'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '87654', '111', 'WER', 'W1', 'Test8'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '76543', '111', 'FHJ', 'Y1', 'Test7'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '65432', '111', 'ERT', 'X1', 'Test6'),
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '54321', '111', 'UKG', 'U1', 'Test0'),
('2014-02-03 13:25:57.000', 'Bravo', 'P', 'E', '54321', '111', 'UKG', 'U1', 'Test0')

;WITH CTE AS
(
SELECT * from @DataTable
where ItemType1 in ('C', 'P')
and ItemType2 = 'E'
and [Date] between CONVERT(CHAR(10),dateadd(day,2-datepart(dw,getdate())-8,getdate()),120) and CONVERT(CHAR(10),dateadd(day,2-datepart(dw,getdate())-2,getdate()),120)
)
, CTE_A AS (SELECT * FROM @DataTable
WHERE ItemType1 in ('C', 'P')
and ItemType2 = 'E'
and [Date] > CONVERT(CHAR(10),dateadd(day,1-datepart(dw,getdate()),getdate()),120))

SELECT a.* FROM CTE a
INNER JOIN CTE_A b ON a.Client = b.Client and a.ItemType1 = b.ItemType1
AND a.ID1 <> b.ID1
AND a.ID2 <> b.ID2
AND a.ID3 <> b.ID3
AND a.ID4 <> b.ID4
AND a.ID5 <> b.ID5

The data I would be looking for is:

('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '98765', '111', 'SDF', 'Z1', 'Test9')
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '87654', '111', 'WER', 'W1', 'Test8')
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '76543', '111', 'FHJ', 'Y1', 'Test7')
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '65432', '111', 'ERT', 'X1', 'Test6')
('2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '54321', '111', 'UKG', 'U1', 'Test0')

This is because those items do not have a 2/10 match, the last item in the list with Client Bravo should not appear either because there is no 2/10 data for Client Bravo so it is assumed it has not been loaded yet.

shilpash
Posting Yak Master

103 Posts

Posted - 2014-02-12 : 18:04:16

DECLARE @DataTable TABLE
(
[Date] DATETIME ,
Client VARCHAR(10) ,
ItemType1 VARCHAR(10) ,
ItemType2 VARCHAR(10) ,
ID1 VARCHAR(10) ,
ID2 VARCHAR(10) ,
ID3 VARCHAR(10) ,
ID4 VARCHAR(10) ,
ID5 VARCHAR(10)
)
INSERT INTO @DataTable
VALUES ( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '12345', '111', 'ABC',
'A1', 'Test1' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '23456', '111', 'BCD',
'B1', 'Test2' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '34567', '111', 'CDE',
'C1', 'Test3' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '45678', '111', 'DEF',
'D1', 'Test4' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '56789', '111', 'FGH',
'E1', 'Test5' ),
( '2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '12345', '111', 'ABC',
'A1', 'Test1' ),
( '2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '23456', '111', 'BCD',
'B1', 'Test2' ),
( '2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '34567', '111', 'CDE',
'C1', 'Test3' ),
( '2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '45678', '111', 'DEF',
'D1', 'Test4' ),
( '2014-02-10 13:25:57.000', 'Alpha', 'P', 'E', '56789', '111', 'FGH',
'E1', 'Test5' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '98765', '111', 'SDF',
'Z1', 'Test9' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '87654', '111', 'WER',
'W1', 'Test8' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '76543', '111', 'FHJ',
'Y1', 'Test7' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '65432', '111', 'ERT',
'X1', 'Test6' ),
( '2014-02-03 13:25:57.000', 'Alpha', 'P', 'E', '54321', '111', 'UKG',
'U1', 'Test0' ),
( '2014-02-03 13:25:57.000', 'Bravo', 'P', 'E', '54321', '111', 'UKG',
'U1', 'Test0' );

--SELECT * FROM @DataTable

WITH CTE
AS ( SELECT *
FROM @DataTable
WHERE ItemType1 IN ( 'C', 'P' )
AND ItemType2 = 'E'
AND [Date] BETWEEN CONVERT(CHAR(10), DATEADD(day,
2 - DATEPART(dw,
GETDATE()) - 8,
GETDATE()), 120)
AND CONVERT(CHAR(10), DATEADD(day,
2 - DATEPART(dw,
GETDATE()) - 2,
GETDATE()), 120)
),

--SELECT * FROM cte
CTE_A
AS ( SELECT *
FROM @DataTable
WHERE ItemType1 IN ( 'C', 'P' )
AND ItemType2 = 'E'
AND [Date] > CONVERT(CHAR(10), DATEADD(day,
1 - DATEPART(dw,
GETDATE()),
GETDATE()), 120)
)

--SELECT * FROM CTE_A
SELECT DISTINCT a.*
FROM CTE a
INNER JOIN CTE_A b ON a.Client = b.Client
AND a.ItemType1 = b.ItemType1
AND a.ID1 NOT IN (SELECT ID1 FROM CTE_A)
--WHERE a.id1='98765'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources