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.
| Author |
Topic |
|
emailuser
Yak Posting Veteran
74 Posts |
 Posted - 2013-02-20 : 07:27:44
|
| 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 DATECOMPLETE1 BK 2013012 Interface 201302 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 07:39:03
|
| 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 weekThe 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. |
 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-20 : 07:45:46
|
| Is there separate Table for storing Cell data ( i.e. Master table for 50 cells)... If yes, we can query as per your requirementDECLARE @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 allSELECT 2, 'Interface', 201302SELECT *FROM @cell cWHERE NOT EXISTS (SELECT 1 FROM @audit WHERE c.CellName = CELLNme )--Chandu |
|
|
|
|
|
|
|
|
find something not there :(