Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-03 : 11:57:06
|
Hi AllI 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 requestThe DML for the table is belowSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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 criteriaRetreive 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 qualifiesIf any request has just the needdt populated for any triageitemid then do not retreive that requestbelow 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 requestrequestid triageitemid needdt cmpltdt nadt notestext149030 1 1/1/00 10/3/12 1/1/00 test149030 2 10/3/12 1/1/00 10/3/12 149030 3 10/3/12 10/3/12 1/1/00 test149030 4 10/3/12 1/1/00 1/1/00 149030 5 10/3/12 10/3/12 1/1/00 test149030 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 daterequestid triageitemid needdt cmpltdt nadt notestext149031 1 1/1/00 10/3/12 1/1/00 test149031 2 10/3/12 1/1/00 10/3/12 149031 3 10/3/12 10/3/12 1/1/00 test149031 4 10/3/12 10/3/12 1/1/00 test149031 5 10/3/12 10/3/12 1/1/00 test149031 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 test149031 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 12:18:33
|
[code]SELECT o.*FROM [dbo].[OUTSTANDING_ITEMS] oINNER 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 )rON r.requestid = o.requestid[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-03 : 12:31:56
|
VisakhIam still not getting results as i expectedBelow is one requestid which is retreived from the query but this request has outstanding triageitemid 2,4 which has just the needdt populatedrequestid triageitemid needdt cmpltdt nadt notestext147864 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] oINNER 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 )rON r.requestid = o.requestid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
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 belowSELECT o.*FROM [dbo].[OUTSTANDING_ITEMS] oINNER 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 )rON r.requestid = o.requestid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 workingWill let you know after doing some testingThanksquote: Originally posted by visakh16 so you wanted once which doesnt have those dates? then tweak as belowSELECT o.*FROM [dbo].[OUTSTANDING_ITEMS] oINNER 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 )rON r.requestid = o.requestid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-03 : 14:33:04
|
Thank You.this works greatquote: Originally posted by visakh16 so you wanted once which doesnt have those dates? then tweak as belowSELECT o.*FROM [dbo].[OUTSTANDING_ITEMS] oINNER 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 )rON r.requestid = o.requestid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 14:41:15
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|