SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Records not found in list
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

29 Posts

Posted - 01/28/2014 :  15:09:42  Show Profile  Reply with Quote
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

USA
93 Posts

Posted - 01/28/2014 :  16:36:28  Show Profile  Reply with Quote

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()))


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

WebKill
Starting Member

29 Posts

Posted - 01/28/2014 :  17:15:39  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 01/28/2014 :  17:37:08  Show Profile  Reply with Quote
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.
Go to Top of Page

WebKill
Starting Member

29 Posts

Posted - 01/28/2014 :  18:49:29  Show Profile  Reply with Quote
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

Edited by - WebKill on 01/29/2014 11:24:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2014 :  05:06:54  Show Profile  Reply with Quote
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
Go to Top of Page

WebKill
Starting Member

29 Posts

Posted - 02/12/2014 :  17:07:43  Show Profile  Reply with Quote
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.

Edited by - WebKill on 02/12/2014 18:02:42
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 02/12/2014 :  18:04:16  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000