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
 General SQL Server Forums
 New to SQL Server Programming
 Selection Query help

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-03 : 11:57:06
Hi All

I have a table called OUTSTANDING_ITEMS for all the requests which stores information about all the outstanding items that are needed before we can complete a request
The DML for the table is below


SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OUTSTANDING_ITEMS](
[requestid] [numeric](18, 0) NOT NULL,
[triageitemid] [int] NOT NULL,
[needdt] [datetime] NULL,
[cmpltdt] [datetime] NULL,
[nadt] [datetime] NULL,
[notestext] [varchar](1000) NULL,
CONSTRAINT [PK__TRIAGE_CHECK_LIST_ITEM] PRIMARY KEY CLUSTERED
(
[requestid] ASC,
[triageitemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO


Note:for every request we have 11 outstanding items that we check and we track the dates using the three dates(needdt,cmpltdt,nadt)

My selection Query criteria

Retreive requests only from the OUTSTANDING_ITEMS table only if cmpltdt or nadt dates are populated with real dates for each triageitemid.
if needdt along with cmpltdt or nadt is populated then also the request qualifies
If any request has just the needdt populated for any triageitemid then do not retreive that request




below is sample data for one request where it can be omitted from the query because for triageitemid 4,8,11,15 only the needdt is populated as a real date which means we still need those items before we can complete the request

requestid triageitemid needdt cmpltdt nadt notestext
149030 1 1/1/00 10/3/12 1/1/00 test
149030 2 10/3/12 1/1/00 10/3/12
149030 3 10/3/12 10/3/12 1/1/00 test
149030 4 10/3/12 1/1/00 1/1/00
149030 5 10/3/12 10/3/12 1/1/00 test
149030 7 10/3/12 1/1/00 10/3/12
149030 8 10/3/12 1/1/00 1/1/00
149030 11 10/3/12 1/1/00 1/1/00
149030 15 10/3/12 1/1/00 1/1/00
149030 16 1/1/00 1/1/00 10/3/12
149030 19 1/1/00 1/1/00 10/3/12


Below is a sample request where I want this to be retrieved in my select query because no triageitemid has just the needdt populated with the real date

requestid triageitemid needdt cmpltdt nadt notestext
149031 1 1/1/00 10/3/12 1/1/00 test
149031 2 10/3/12 1/1/00 10/3/12
149031 3 10/3/12 10/3/12 1/1/00 test
149031 4 10/3/12 10/3/12 1/1/00 test
149031 5 10/3/12 10/3/12 1/1/00 test
149031 7 10/3/12 1/1/00 10/3/12
149031 8 10/3/12 1/1/00 10/3/12
149031 11 10/3/12 1/1/00 10/3/12
149031 15 10/3/12 10/3/12 1/1/00 test
149031 16 1/1/00 1/1/00 10/3/12
149031 19 1/1/00 1/1/00 10/3/12



Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:02:42
you want to retrieve only those rows with real date or all rows belonging to same requestid if it follows above conditions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-03 : 12:13:36
all rows for that requestid if it meets the condition
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:18:33
[code]
SELECT o.*
FROM [dbo].[OUTSTANDING_ITEMS] o
INNER JOIN (SELECT requestid
FROM [dbo].[OUTSTANDING_ITEMS]
WHERE triageitemid IN (4,8,11,15)
GROUP BY requestid
HAVING SUM(CASE WHEN needdt <> '1/1/00' THEN 1 ELSE 0 END) >0
)r
ON r.requestid = o.requestid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-03 : 12:31:56
Visakh
Iam still not getting results as i expected
Below is one requestid which is retreived from the query
but this request has outstanding triageitemid 2,4 which has just the needdt populated


requestid triageitemid needdt cmpltdt nadt notestext
147864 1 2/29/12 1/1/00 1/1/00
147864 2 2/29/12 1/1/00 1/1/00
147864 3 1/1/00 1/1/00 2/29/12
147864 4 2/29/12 1/1/00 1/1/00
147864 5 1/1/00 1/1/00 2/29/12
147864 7 1/1/00 1/1/00 2/29/12
147864 8 1/1/00 1/1/00 2/29/12
147864 11 1/1/00 1/1/00 2/29/12
147864 15 2/29/12 1/1/00 1/1/00
147864 16 2/29/12 1/1/00 1/1/00
149031 19 1/1/00 1/1/00 10/3/12



quote:
Originally posted by visakh16


SELECT o.*
FROM [dbo].[OUTSTANDING_ITEMS] o
INNER JOIN (SELECT requestid
FROM [dbo].[OUTSTANDING_ITEMS]
WHERE triageitemid IN (4,8,11,15)
GROUP BY requestid
HAVING SUM(CASE WHEN needdt <> '1/1/00' THEN 1 ELSE 0 END) >0
)r
ON r.requestid = o.requestid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:36:10
so you wanted once which doesnt have those dates? then tweak as below


SELECT o.*
FROM [dbo].[OUTSTANDING_ITEMS] o
INNER JOIN (SELECT requestid
FROM [dbo].[OUTSTANDING_ITEMS]
WHERE triageitemid IN (4,8,11,15)
GROUP BY requestid
HAVING SUM(CASE WHEN needdt <> '1/1/00' THEN 1 ELSE 0 END) =0
)r
ON r.requestid = o.requestid





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-03 : 13:37:21
I'm editing my previous reply as i have youq query working
Will let you know after doing some testing

Thanks

quote:
Originally posted by visakh16

so you wanted once which doesnt have those dates? then tweak as below


SELECT o.*
FROM [dbo].[OUTSTANDING_ITEMS] o
INNER JOIN (SELECT requestid
FROM [dbo].[OUTSTANDING_ITEMS]
WHERE triageitemid IN (4,8,11,15)
GROUP BY requestid
HAVING SUM(CASE WHEN needdt <> '1/1/00' THEN 1 ELSE 0 END) =0
)r
ON r.requestid = o.requestid





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-10-03 : 14:33:04
Thank You.this works great

quote:
Originally posted by visakh16

so you wanted once which doesnt have those dates? then tweak as below


SELECT o.*
FROM [dbo].[OUTSTANDING_ITEMS] o
INNER JOIN (SELECT requestid
FROM [dbo].[OUTSTANDING_ITEMS]
WHERE triageitemid IN (4,8,11,15)
GROUP BY requestid
HAVING SUM(CASE WHEN needdt <> '1/1/00' THEN 1 ELSE 0 END) =0
)r
ON r.requestid = o.requestid





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 14:41:15
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -