Pls help with this query - 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 2000 Forums

Transact-SQL (2000)

Pls help with this query

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2008-01-16 : 07:29:53

Hi everyone

I'm having a huge headache with this query:

SELECT boxsource2k7.dbo.Containers.containercode AS ContainerCode, COUNT(boxsource2k7.dbo.indexdata.indexid) AS TotalItemsInBox,
SUM(CASE WHEN boxsource2k7.dbo.indexdata.dod <= '2008-01-01' AND boxsource2k7.dbo.indexdata.dod <> '1900-01-01' THEN 1 END)
AS Destroyable, MIN(boxsource2k7.dbo.indexdata.dod) AS LowestDOD, MAX(boxsource2k7.dbo.indexdata.dod) AS HighestDOD,
boxsource2k7.dbo.subco.subconame AS Company, isnull(rssqldb.dbo.rsitem.itemcode, '-') AS RSSQLContainer, isnull(rssqldb.dbo.rsitem.alternatecode, '-') as AlternateCode,
isnull(rssqldb.dbo.rslocation.locationcode, '-') AS Location
FROM boxsource2k7.dbo.Indexdata INNER JOIN
boxsource2k7.dbo.Containers ON boxsource2k7.dbo.Containers.containerid = boxsource2k7.dbo.Indexdata.containerid INNER JOIN
boxsource2k7.dbo.subco ON boxsource2k7.dbo.subco.subcoid = boxsource2k7.dbo.indexdata.subcoid LEFT OUTER JOIN
RSSQLDB.dbo.rsitem ON RSSQLDB.dbo.rsitem.itemcode = boxsource2k7.dbo.containers.containercode LEFT OUTER JOIN
rssqldb.dbo.rslocation ON rssqldb.dbo.rsitem.locationid = rssqldb.dbo.rslocation.locationid
WHERE (boxsource2k7.dbo.Indexdata.subcoid = 2)
GROUP BY boxsource2k7.dbo.containers.containercode, (CASE WHEN boxsource2k7.dbo.indexdata.dod <= '2008-01-01' AND
boxsource2k7.dbo.indexdata.dod <> '1900-01-01' THEN 1 END), boxsource2k7.dbo.subco.subconame, rssqldb.dbo.rsitem.itemcode,
rssqldb.dbo.rslocation.locationcode, rssqldb.dbo.rsitem.alternatecode
HAVING COUNT(boxsource2k7.dbo.indexdata.indexid) = sum(CASE WHEN boxsource2k7.dbo.indexdata.dod <= '2008-01-01' AND boxsource2k7.dbo.indexdata.dod <> '1900-01-01' THEN 1 END)
ORDER BY rssqldb.dbo.rslocation.locationcode

I have a list of boxes in table [containers] and a list of files inside the boxes in [indexdata]. Each record in [indexdata] has a date of destruction (dod). I want to match all boxes where the number of files inside the box is the same as the number of files inside the box that has reached its destruction date.

The query executes but it returns the number of destroyable files in the box as the 'total files in box' and as the 'total destroyable files'. When I do spot checks I see that the data is wrong, ie:
Box 123 has 20 files inside it. Only 18 can be destroyed, so my report says there are 18 files in the box of which 18 can be destroyed - I want it to only show boxes where all the files can be destroyed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 09:19:32

I didnt have a look at your query batch. But just following your explanation below, unless you dont have any specific reason to include other tables, i think your solution can be obtained as follows.

SELECT t.containerid
FROM
(
SELECT c.containerid,COUNT(i.indexid) AS TotalCount,
SUM(CASE WHEN i.dod<=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) THEN 1 ELSE 0 END) as DestructCount
FROM boxsource2k7.dbo.Containers c
INNER JOIN boxsource2k7.dbo.Indexdata i
ON i.containerid=c.containerid
GROUP BY c.containerid
)t
WHERE t.TotalCount=t.DestructCount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources