| Author |
 Topic  |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 10/03/2012 : 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
India
47157 Posts |
Posted - 10/03/2012 : 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/
|
 |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/03/2012 : 12:13:36
|
| all rows for that requestid if it meets the condition |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 10/03/2012 : 12:18:33
|
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/
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/03/2012 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 10/03/2012 : 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/
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/03/2012 : 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/
|
Edited by - jim_jim on 10/03/2012 14:17:11 |
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/03/2012 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 10/03/2012 : 14:41:15
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Selection Query help
