February 13, 2002 at 6:30 am
I need to select all records from a big huge database and locate everything that has no trafic base on a period of month.
Ex: we have Pax lines,Crew Lines and Admin Lines, if there is NO trafic on any of them
bring that info up.
The question is : it's possible do that
using a SQL statement? we are using SQL 2000
February 13, 2002 at 6:51 am
Almost anything is possible in queries, just give us some detail on the tables and how they work so we can help.
February 13, 2002 at 9:45 am
Well the result of the querie got be that way
Vessel1 Pax = 0
Vessel1 Admin = 0
Vessel1 Crew = 0
and so far for each vessel
Because they want to know wich vessel don't have trafic on those lines
February 13, 2002 at 5:44 pm
well, table has alot of fields like
Passenger calls . fieldname = Pax
Crew Calls . fieldname = Crew
Admin Calls . fieldname = Admin
Vessel Name
Cruise Line Name
I need select by those fields and report anything with NO TRAFIC.
Edited by - nelsonVere on 02/13/2002 5:44:16 PM
February 13, 2002 at 6:02 pm
What datatype are Pax, Crew, and Admin and can they be NULL? What will the final report be done in and look like? And does no traffic have to apply to all three lines or not? Then I should have enough to help.
February 15, 2002 at 2:22 pm
They are varchar but I don't have NULL for those ( can be null )but if don't have trafic don't have record, I need select all that don't have record (trafic)
and the report gotta to be that way
vessel 1 - Pax "Do not have traffic"
vessel 1 - Admin "Do not have traffic"
vessel 1 - Crew "Do not have traffic"
February 15, 2002 at 6:25 pm
Ok so last thing, you say that if there is no value for Pax, admin, or crew then there is no row. Then do you have a table with all available vessels.
If so then something like
SELECT
vesselname,
'Pax has no traffic' as WhatYouWantFldToBe FROM
tblWithPaxCrewAdmin
RIGHT JOIN
tblWithVesselName
ON
tblWithPaxCrewAdmin.Vessel1 = tblWithVesselName.FldWithVesselName
WHERE
tblWithPaxCrewAdmin.Vessel1 IS NULL
UNION ALL
SELECT
vesselname,
'Crew has no traffic' as WhatYouWantFldToBe FROM
tblWithPaxCrewAdmin
RIGHT JOIN
tblWithVesselName
ON
tblWithPaxCrewAdmin.Vessel1 = tblWithVesselName.FldWithVesselName
WHERE
tblWithPaxCrewAdmin.Vessel1 IS NULL
UNION ALL
SELECT
vesselname,
'Admin has no traffic' as WhatYouWantFldToBe FROM
tblWithPaxCrewAdmin
RIGHT JOIN
tblWithVesselName
ON
tblWithPaxCrewAdmin.Vessel1 = tblWithVesselName.FldWithVesselName
WHERE
tblWithPaxCrewAdmin.Vessel1 IS NULL
This is a rough idea of what should work for you if there is a table with all vessel names and you can link like that. If not then I need to know how I can get a full listing of all vessel names to know which records are missing.
Hope this helps thou.
February 22, 2002 at 2:47 pm
well...thanks but not work good
what I need is something like that
select distinct siteid,callid from billable_transactions where (callid='Pax' or Callid='Crew' or callid='Admin')
and siteid='AMSTERDAM'
this query returns
AMSTERDAM Admin
AMSTERDAM Pax
and don't say Amsterdam Crew because do not
have crew calls...so I need that info say "NO DATA" for crew call on that example
How I do that?
thanks
February 22, 2002 at 4:51 pm
based on what I have read about your problem I think this might work for you:
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'crew' AND siteid='AMSTERDAM') IS NULL THEN 'Crew No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = 'AMSTERDAM'
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'Pax' AND siteid='AMSTERDAM') IS NULL THEN 'Pax No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = 'AMSTERDAM'
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'Admin' AND siteid='AMSTERDAM') IS NULL THEN 'Admin No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = 'AMSTERDAM'
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 22, 2002 at 6:07 pm
Or this which is a little different but could potentially run faster and you might want to test both a few times to see how they run.
SELECT
billable_transactions.siteid,
(CASE
WHEN Callid IS NULL THEN BaseX + ' No Traffic'
ELSE Callid
END) AS Callid
FROM
billable_transactions
RIGHT JOIN
(
SELECT DISTINCT siteid, BaseX FROM billable_transactions
CROSS JOIN
(
SELECT 'pax' AS BaseX
UNION ALL
SELECT 'admin'
UNION ALL
SELECT 'crew'
) AS BaseList
WHERE
billable_transactions.siteid = 'AMSTERDAM'
) AS BaseItems
ON
billable_transactions.siteid = BaseList.siteid AND
billable_transactions.Callid = BaseList.BaseX
WHERE
billable_transactions.siteid = 'AMSTERDAM'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 9:27 am
well...I tried this one
SELECT DISTINCT siteid,callid,
CASE
WHEN (SELECT siteid FROM billable_transactions where callid = 'Crew' AND siteid='') IS NULL THEN 'Crew No Traffic'
ELSE callid
END as 'NoData'
FROM billable_transactions
order by siteid
and I'm getting this result
AMSTERDAM Admin Crew No Traffic
AMSTERDAM Pax Crew No Traffic
ARCADIA Admin Crew No Traffic
ARCADIA Crew Crew No Traffic
ARCADIA Pax Crew No Traffic
BARUNA JAY Admin Crew No Traffic
CAPE MAYLIGHT Admin Crew No Traffic
as you can see works for AMSTERDAM because there is NO trafic on crew lines, but
Do not work for ARCADIA, Arcadia has traffic on crew lines and it still says NO TRAFFIC
February 25, 2002 at 9:42 am
That is because in
CASE
WHEN (SELECT siteid FROM billable_transactions where callid = 'Crew' AND siteid='') IS NULL
you have to have the ship name in that siteid as well so for ARCADIA you have to do
CASE
WHEN
(SELECT siteid FROM billable_transactions where callid = 'Crew' AND siteid='ARCADIA') IS NULL
which will work only for ARCADIA. I kinda figured you may go that route so if you try my code post you should find it will work.
Like this
SELECT
billable_transactions.siteid,
(CASE
WHEN Callid IS NULL THEN BaseX + ' No Traffic'
ELSE Callid
END) AS Callid
FROM
billable_transactions
RIGHT JOIN
(
SELECT DISTINCT siteid, BaseX FROM billable_transactions
CROSS JOIN
(
SELECT 'pax' AS BaseX
UNION ALL
SELECT 'admin'
UNION ALL
SELECT 'crew'
) AS BaseList
) AS BaseItems
ON
billable_transactions.siteid = BaseList.siteid AND
billable_transactions.Callid = BaseList.BaseX
Will output all but if you want a particular siteid
SELECT
billable_transactions.siteid,
(CASE
WHEN Callid IS NULL THEN BaseX + ' No Traffic'
ELSE Callid
END) AS Callid
FROM
billable_transactions
RIGHT JOIN
(
SELECT DISTINCT siteid, BaseX FROM billable_transactions
CROSS JOIN
(
SELECT 'pax' AS BaseX
UNION ALL
SELECT 'admin'
UNION ALL
SELECT 'crew'
) AS BaseList
WHERE
billable_transactions.siteid = 'AMSTERDAM' /* I do this only to speed up the subquery, it is not required just bennificial.*/
) AS BaseItems
ON
billable_transactions.siteid = BaseList.siteid AND
billable_transactions.Callid = BaseList.BaseX
WHERE
billable_transactions.siteid = 'AMSTERDAM'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 10:08 am
To make the solution I offered work you will have to use a cursor:
CREATE TABLE #report (siteid varchar(20), callid varchar(20))
DECLARE @SiteID varchar(30)
DECLARE csrSites CURSOR FOR
SELECT DISTINCT siteid
FROM billable_transactions
ORDER BY siteid
OPEN csrSites
FETCH NEXT FROM csrSites INTO @SiteID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #report (siteid, callid)
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'crew' AND siteid=@SiteID) IS NULL THEN 'Crew No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'Pax' AND siteid=@SiteID) IS NULL THEN 'Pax No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
UNION
SELECT DISTINCT siteid, CASE WHEN (SELECT siteid FROM billable_transactions where callid = 'Admin' AND siteid=@SiteID) IS NULL THEN 'Admin No Traffic' ELSE callid END AS callid
FROM billable_transactions
WHERE siteid = @SiteID
FETCH NEXT FROM csrSites INTO @SiteID
END
SELECT *
FROM #report
CLOSE csrSites
DEALLOCATE csrSites
DROP TABLE #report
Since any other solution is better than using a cursor, I would try the solution James offered first. This is because a cursor can be very intensive because it looks at rows one at a time. However, if you find nothing else that works I think this will. It did on the test table I created.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 25, 2002 at 10:51 am
Not to step on Robert here, but just a little detail, even though his soultion will work you are required a cursor and a temp table both of which cause overhead extra on the server. My solution is tested and will accomplish the same thing without the need for a cursor or generating a temp table (outside of what SQL Server itself does with the subquery).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 2:05 pm
I really apprecciate your help guys but it's not working.
the query with the cursor don't work at all
give a bunch of errors
the commom error is
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried the other query from Antares...I stop the query after one hour running...don't know how long will take so I stop...
All I need is by siteid result something like this
AMSTERDAM Crew no traffic
AMSTERDAM Admin There is trafic
AMSTERDAM Pax There is traffic
thanks for your help guys
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply