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
 General SQL Server Forums
 New to SQL Server Programming
 find something not there :(
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emailuser
Yak Posting Veteran

74 Posts

Posted - 02/20/2013 :  07:27:44  Show Profile  Reply with Quote
Hi everyone , i have an interesting scenario where i need to find something that is not there :)

Sql 2005

i have table called Audits in format id, CELL amd DATECOMPLETE , every time an audit is done in a cell a new line is written to the table as shown below, we have 50 cells and the expectation is that an audit is done every week , if this is true we would see 50 rows added to the table ( id, Cell,DateComplete the datecomplete column is a numeric column in format 201301 where 2013 is the year and 01 is the week )

In reality this is not the case

I can query who has done their audits and the date complete , is it possible to write a query which shows which cell has not completed an audit and thus there is no row written to the table pivoted by DateComplete .... hope this makes sense

The number of cells sometimes change , as we add more to the audits

any help greatly appreciated ...

id CELL DATECOMPLETE
1 BK 201301
2 Interface 201302

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 02/20/2013 :  07:39:03  Show Profile  Reply with Quote
This can be done, but couple of questions:

1. Do you have a list of cells somewhere, perhaps in another table?
2. Is there a specific day of the week that will be entered into the datecomplete column, or can it be any day of the week

The way to write the query would be to first cross join a calendar table and the table that has the list of cells. Then, you would do a left join with the audit table. If you can provide the info I asked for above, I or someone else on the forum can help you with writing the query.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/20/2013 :  07:45:46  Show Profile  Reply with Quote
Is there separate Table for storing Cell data ( i.e. Master table for 50 cells)... If yes, we can query as per your requirement

DECLARE @cell TABLE(CellId int, CellName VARCHAR(10))
INSERT INTO @cell VALUES(1, 'BK'), (2, 'ABC'),(3, 'Interface')

DECLARE @audit TABLE( id INT, CELLNme VARCHAR(10), DATECOMPLETE INT)
INSERT INTO @audit
SELECT 1, 'BK', 201301 union all
SELECT 2, 'Interface', 201302

SELECT *
FROM @cell c
WHERE NOT EXISTS (SELECT 1 FROM @audit WHERE c.CellName = CELLNme )



--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000