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
 Selection Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
302 Posts

Posted - 10/03/2012 :  11:57:06  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/03/2012 :  12:02:42  Show Profile  Reply with Quote
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

USA
302 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:18:33  Show Profile  Reply with Quote

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

USA
302 Posts

Posted - 10/03/2012 :  12:31:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/03/2012 :  12:36:10  Show Profile  Reply with Quote
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

USA
302 Posts

Posted - 10/03/2012 :  13:37:21  Show Profile  Reply with Quote
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/




Edited by - jim_jim on 10/03/2012 14:17:11
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
302 Posts

Posted - 10/03/2012 :  14:33:04  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/03/2012 :  14:41:15  Show Profile  Reply with Quote
welcome

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

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