September 5, 2007 at 8:44 am
Hi all i am trying to get the completed field in the following table tble3215 to return the appropriate requirement numbers that are equal to 0. The query pulls from 3 seperate tables. All i need it to do is on tbl3215.completed =0 return those items to me.
here is the statement
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)
I have tried the following:
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and tbl3215.completed = 0
and
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and (tbl3215.completed = 0)
neither of these work, can i get an assist. Please let me know if you need more information
Thanks, Joe
September 5, 2007 at 8:48 am
Joe
When you say they don't work, do you mean they return an error, or you don't get the result set you expect?
What is the significance of the first statement?
Some sample data and table DDL would help us to help you.
John
September 5, 2007 at 8:49 am
Have you tried this.
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where ((itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)) and (tbl3215.completed = 0)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 5, 2007 at 9:02 am
OK, newbie alert...what is DDL?
September 5, 2007 at 9:10 am
Joe
Sorry - DDL is data definition language, in other words the CREATE TABLE statements for your tables. Sample data in the form of INSERT statements is easiest for us to use as well. There is a paragraph on this site somewhere that gives advice like this to new posters such as yourself, but I couldn't find it just now when I looked.
John
September 5, 2007 at 9:29 am
John, thanks for the kind response, some folks get in a tizzy
here you go:
DDL from tbl3215:
INSERT INTO [SCX_3215].[dbo].[tbl3215]
([id]
,[IttNumber]
,[IttTitle]
,[IttDescription]
,[IttJustification]
,[TechSolution]
,[DateReceived]
,[DateRequired]
,[Customer]
,[OrgID]
,[Funded]
,[HBApID]
,[Disapproved]
,[DisReason]
,[Completed]
,[CompletionDate]
,[Priority])
VALUES
(<id, int,>
,<IttNumber, decimal(18,0),>
,<IttTitle, nvarchar(max),>
,<IttDescription, nvarchar(max),>
,<IttJustification, nvarchar(max),>
,<TechSolution, nvarchar(max),>
,<DateReceived, datetime,>
,<DateRequired, datetime,>
,<Customer, nvarchar(max),>
,<OrgID, nvarchar(max),>
,<Funded, nvarchar(max),>
,<HBApID, nvarchar(max),>
,<Disapproved, nvarchar(max),>
,<DisReason, nvarchar(max),>
,<Completed, int,>
,<CompletionDate, datetime,>
,<Priority, nvarchar(max),>
DDL from tblassignment:
INSERT INTO [SCX_3215].[dbo].[tblassignment]
([id]
,[IttNumber]
,[AssFltWrkID]
,[DateAss]
,[DateCmplt]
,[RecordID]
,[OnHold])
VALUES
(<id, int,>
,<IttNumber, decimal(18,0),>
,<AssFltWrkID, varchar,>
,<DateAss, datetime,>
,<DateCmplt, datetime,>
,<RecordID, int,>
,<OnHold, int,>
DDL from tblfltwrkcnter
INSERT INTO [SCX_3215].[dbo].[tblfltwrkcntr]
([id]
,[FltWkCntrID]
,[Flt]
,[WrkCntr]
,[WrkCntrEmail]
,[Deactivated])
VALUES
(<id, float,>
,<FltWkCntrID, float,>
,<Flt, nvarchar(255),>
,<WrkCntr, nvarchar(255),>
,<WrkCntrEmail, nvarchar(255),>
,<Deactivated, float,>
here is some sample data from tbl3215
8 | 20060109 | 2 Laptops/2 Printers | Two Dell Latitude D610 Laptops\r\nTwo HP 2600 Laserjet Printers | Required to support essential needs in support of OPS. | 10/13/2005 12:00:00 AM | 11/1/2005 12:00:00 AM | 745 SAS/379 EOG\r\n Sharod Allen\r\nXXX-5716 | 2 | 0 | 99 | Approved | 1 | 12/11/2005 12:00:00 AM | C |
here is some from tblassignment
49 | 20050017 | 6 | 3/14/2005 12:00:00 AM | 4/5/2005 12:00:00 AM | 1430 | 0 |
here is some from tblfltwrkcntr
8 | 25 | SCM | SCMPO | myemail@youremail.com | 0 |
i hope this suffices?
September 5, 2007 at 9:35 am
Yes i tried that as well.
there is something that i am missing and i cant quite put my finger on it. I got it to work, but it does not return all the correct information and some show up under totally different workcenters.
My disclaimer is this: the database started out as an ACCESS DB, then ported to PHP, and now ported to SQL hopefully for the last port..LOL. I feel sorry for the next person that may have to fix this! A lot of datatype incompatabilities and the like.
I dont have a lot of SQL experience and i seem to bang my head against my desk at least 4 times a day, but figuring it out is fun. I do a victory dance when i get something to work!! I have definitely learned how NOT to design a DB
September 5, 2007 at 10:05 am
You may want to consider shortening your nvarchar(max) columns.
The following may work:
SELECT
T.ittnumber
,COALESCE(T.itttitle, T.ittdescription) AS itttitle
,T.daterequired
FROM dbo.tbl3215 T
WHERE T.completed = 0
AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL
AND EXISTS (
SELECT *
FROM dbo.tblassignment A
WHERE A.ittnumber = T.ittnumber
AND EXISTS (
SELECT *
FROM dbo.tblfltwrkcntr W
WHERE W.fltwkcntrid = A.assfltwrkid
AND W.wrkcntr = @WrkCntr
)
)
September 5, 2007 at 12:26 pm
Hi Ken, thanks for the help. I will try this tomorrow when i get into work.
You mentioned shortening nvarchar(max) columns....im sure there is a good reason, to increase data access speed i presume?
a few questions though so that i understand it. Why the T.ittnumber instead of just ittnumber? and what does coalesce do?
thank you
September 5, 2007 at 10:44 pm
ok ken, i tried that and it appears to do the same thing as this one
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr) and (tbl3215.completed = 0)
i get the exact same result set.
But if i use this statement
select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and (dbo.tblfltwrkcntr.wrkcntr = @WrkCntr)
i get every requirement no matter the value in the tbl3215.comlpeted column. Does the order of the AND statements matter?
September 6, 2007 at 1:24 am
all, i think i have identified the issue. Now i ask for your help how to solve it.
within each 3 letter designator there is a sub org if you will. For example,
SCB
SCBN
SCBM
SCBT
so, when i change the statement to represent an OPEN status (the completed column in tbl3215 is equal to 0) i only get SCB, when i select SCBN, SCBM, or SCBT nothing is returned.
To test this theory, i changed the tbl3215 = 2 (on hold status) and the same thing happened. I verified this through the live database (the PHP one) checking open and on hold requirements and they were there.
So now the question becomes, how to i get it to see the 4 letter disgnators instead of only pulling the 3 letter workcenter designator?
Thank You
September 6, 2007 at 5:58 am
Maybe:
SELECT
T.ittnumber
,COALESCE(T.itttitle, T.ittdescription) AS itttitle
,T.daterequired
FROM dbo.tbl3215 T
WHERE T.completed = 0
AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL
AND EXISTS (
SELECT *
FROM dbo.tblassignment A
WHERE A.ittnumber = T.ittnumber
AND EXISTS (
SELECT *
FROM dbo.tblfltwrkcntr W
WHERE W.fltwkcntrid = A.assfltwrkid
AND W.wrkcntr LIKE @WrkCntr + '%'
)
)
September 6, 2007 at 8:04 am
Ken, interesting. It partially worked but only for one workcenter.
We have another section called SCM, with subsections
SCM
SCMC (this works)
SCMPO (does Not)
SCMP (does Not)
SCMCI (does Not)
SCB
SCBM (does Not)
SCBN (does Not)
SCBT (does not)
..etc
cinfused yet? i am trying to determine the commonality of SCM to SCMC. I will keep the thread posted. Thank You
September 6, 2007 at 8:44 am
Maybe you should post some correct DDL, some sensible sample data, the results you get and the results you want.
(If you do this you may even be able to work it out yourself!)
Something like:
CREATE TABLE #T3215
(
ittnumber int NOT NULL
,itttitle nvarchar(30) NULL
,ittdescription nvarchar(30) NULL
,daterequired datetime NULL
,completed int NOT NULL
)
-- Look up INSERT INTO in Books Online (BOL)
INSERT INTO #T3215 VALUES (...)
INSERT INTO #T3215 VALUES (...)
INSERT INTO #T3215 VALUES (...)
-- etc
CREATE TABLE #Assignment
(
ittnumber int NOT NULL
,assfltwrkid int NOT NULL
)
INSERT INTO #Assignment VALUES (...)
INSERT INTO #Assignment VALUES (...)
INSERT INTO #Assignment VALUES (...)
-- etc
CREATE TABLE #WrkCntr
(
assfltwrkid int NOT NULL
,wrkcntr varchar(10) NOT NULL
)
INSERT INTO #WrkCntr VALUES (...)
INSERT INTO #WrkCntrt VALUES (...)
INSERT INTO #WrkCntr VALUES (...)
-- etc
-- Your Query
SELECT ...
-- Show the results you get.
ittnumber itttitle daterequired
========== ========== ============
...
-- Show the results you want.
ittnumber itttitle daterequired
========== ========== ============
...
September 6, 2007 at 8:45 am
maybe this will help
here is the SCMC row from tblfltwrkcntr
9 | 29 | SCM | SCMC | myemail@youremail.com | 0 |
here is an SCB
10 | 30 | SCB | SCBN | youremail@sqlisconfusing.com | 0 |
another thing that i found is it appears that under the columns under fltwrkcntrID are out of order and it looks like through the years that records have been deleted, added re added however there are no duplicates.
here is a sample of that data:
4 | 4 | SCB | SCB | myemail@youremail.com | 0 |
5 | 21 | SCB | SCBH | myemail@youremail.com | 0 |
6 | 22 | SCB | SCBM | myemail@youremail.com | 0 |
7 | 24 | SCM | SCMR | myemail@youremail.com | 0 |
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply