i have a problem, i have a query which does a search based on a parameter, thing is i want to order them according to how relevant the results are such as 5 for beingan exact match, 0 for being no match. using a series of liek statements.
so that the most relevnat results are displayed at the top.
how do i achieve this - im a bit confused about this.
I have an existing query, which runs pretty good. However, I need to create a new report, in which I would require to alter the existing query to return two values from each of the subqueries back to the main query.
The following is the current query:
SELECT tbLogTimeValues.DateTimeStamp as DateTimeStamp ,tbLogTimeValues.FloatVALUE AS Value FROM tbLogTimeValues
[Code] ....
Need putting together a query that returns two values from the first sub query (DateTimeStampTemperature and ValueTemperature) and from the second query return (DateTimeStampHumidity and ValueHumidity).
I would simply have to change the Path LIKE '%' + 'Temperature Interval%' within the humidity subquery to read:
Path LIKE '%' + 'Humidity Interval%'
Obviously, I would like to keep the DateTimeStamp comparisons as part of the main query, as the customer has access to this via parameters, and should be the same for both subqueries.
I have tried this insert comand and it errors out telling me that icannot use subqueries this way. INSERT INTO tblPartLocation(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHEREPartName = 'test'))how would i insert a value from a query?thanks for any help
Hello, How can I do something like: Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost) Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3) Im getting an error: Subqueries are not allowed in this context. Only scalar expressions are allowed.
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
-- File: sp_GetProgramsWatchedByDateRange.sql -- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse. -- Note that some of that data used by this procedure are obtained from the RMS_EPG database -- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file. -- Auth: H Hunsaker -- Date: 11/07/2006
-- Example invocation -- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description -- 3. StartDate datetime First date of reporting period -- 4. EndDate datetime Last date of reporting period -- TerseMode bit Return all columns? (1 = no, 0 = yes) -- 5. AsXML bit Resultset format (0 = standard, 1 = XML) -- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange @StartDate datetime = NULL, @EndDate datetime = NULL, @TerseMode bit = 0, @AsXML bit = 0, @Debug bit = 0 AS -- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse. -- So I am going to the RMS_EPG database to obtain that information. -- We will have to ensure that the 2 databases are generated at the same or a matching time -- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing -- DECLARE @StartDate datetime -- DECLARE @EndDate datetime -- DECLARE @TerseMode bit
-- Basic program information tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db. tprogramId uniqueidentifier NULL, -- programID generated by IPTV tprogramTitle varchar(150) NULL, tprogramEpisodeTitle varchar(100) NULL, tprogramDescription varchar(500) NULL,
-- Flags ClosedCaption bit NULL, InStereo bit NULL, Repeats bit NULL, New bit NULL, Live bit NULL, Taped bit NULL, Subtitled bit NULL, SAP bit NULL, ThreeD bit NULL, Letterbox bit NULL, HDTV bit NULL, Dolby bit NULL, DVS bit NULL,
-- I store the program watching data in a temp table because -- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid. -- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system AND originTime BETWEEN @StartDate AND @EndDate ELSE INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId UPDATE #programWatched SET taccountId = (SELECT accountId FROM DW_BRDB_bm_device d WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId) -- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs UPDATE #programWatched SET tchannelCallName = (SELECT TOP 1 channelCallName FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags) UPDATE #programWatched SET programMPAARating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched SET programMPAARatingVal = CASE programMPAARating WHEN 'G' THEN 10 WHEN 'PG' THEN 25 WHEN 'PG-13' THEN 30 WHEN 'R' THEN 35 WHEN 'NC-17' THEN 50 WHEN 'NRAO' THEN 60 WHEN 'NR' THEN 0 ELSE 0 END
UPDATE #programWatched SET programVChipRating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched SET programVChipRatingVal = CASE programVChipRating WHEN 'TV-Y' THEN 10 WHEN 'TV-Y7' THEN 20 WHEN 'TV-G' THEN 35 WHEN 'TV-PG' THEN 40 WHEN 'TV-14' THEN 45 WHEN 'TV-MA' THEN 60 ELSE 0 END
-- Genre UPDATE #programWatched SET programGenre = (SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId WHERE tprogram = pc.programID)
-- Categories UPDATE #programWatched SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID)
UPDATE #programWatched SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles UPDATE #programWatched SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programDirectorLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programWriterLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programProducerLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1) UPDATE #programWatched SET programActor1LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor2LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor3LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor4LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor5LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor6LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor7LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched SET programActor8LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
-- Channel (provider) Call Letters, Display Name and Type -- Is this correct? Should we get the channelId from the schedule table? -- Is this efficient? View execution plan
UPDATE #programWatched SET tchannelId = (SELECT TOP 1 c.channelId FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram)
UPDATE #programWatched SET callLetters = (SELECT TOP 1 c.channelCallLetters FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET displayName = (SELECT TOP 1 c.channelDisplayName FROM RMS_EPG..channel c JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET type = (SELECT TOP 1 c.channelType FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0 SELECT * FROM #programWatched ORDER BY toriginDateTime ELSE -- Get only Genre, title, show date/time, rating, call letters SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName FROM #programWatched ORDER BY toriginDateTime
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer, meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
SELECT PW.DeviceID, PW.originTime AS 'When Watched', PW.programID, PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW ORDER BY DeviceId, programId, originTime
Hello I am building a survey application. I have 8 questions. Textbox - Call reference Dropdownmenu - choose Support method Radio button lists - Customer satisfaction questions 1-5 Multiline textbox - other comments. I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID. I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score. Please help me! Thanks Andrew
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Table #1 SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding FROM Employee_Hours WHERE ([Date] Between '1/1/04' AND '12/31/04') GROUP BY EID, DATEPART(wk,[Date]) HAVING MAX([Date]) = '1/24/04' --Pay period
Table #2 SELECT EID, DisplayName FROM Employee WHERE (Active=1)
I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.
I have the following sql statement: INSERT INTO prg_lvl VALUES ( 'Data Recovery', 'Data Recovery', 7, 'ENG', 0, 16, 'Menu=w_data_recovery_stk;', ( select max(menu_id) + 1 from prg_lvl ), 'K'); that works for Sybase and I wanna make it works for SQL Server.
The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).
Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem
I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.
For instance, I have the following code, which lists all orders for the month of November, 1996:
SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;
I have been asked to do the same thing, but using a subquery. I came up with the following:
SELECT CustomerID, CompanyName FROM Customers a WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;
However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?
Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)
Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon
Guys,Got this query...---------------------------SELECT TOP 5 Tbl_admin_hotels.HotelName,(SELECT COUNT(Tbl_marketing_history.HotelID)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS CountTotal,(SELECT MAX(DateSent)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS LastSentFROM Tbl_admin_hotelsWHERE NOT Tbl_admin_hotels.HotelID = 99ORDER BY CountTotal DESC---------------------------Within the table Tbl_marketing_history there is also a 'Subject' column thatI require, the row to grab this Subject column from should relate to the rowI'm selecting in the SubQuery to grab 'DateSent'.I've tried and tried to grab multiple columns but failed miserably? Cananyone help pleaseeee!Cheers, Ash
In this stored procedure, I want to grab all the information for Sales Representatives that made less then 6 visits. I have done that in the subquery. But I also want to be able to display their visits by Dates where any number of visits was made. Meaning, i want to be able to show Everyday where a visit was made, but i only want the records that are less then 6 visits. So I want nulls to show up for the dates where no visits were made. So I can track the people that didnt make visits, on days other made visits (which means they should have visits too)
So basically my report looks like this: 09-10-2007 09-11-2007 09-14-2007 Tom Smith 1 3 4 Janice Cooper 3 5 5 Troy Slater 5 4 2 Kelly Short 4 4 4
When i want it to look like this:
09-10-2007 09-11-2007 09-12-2007 09-13-2007 09-14-2007 Tom Smith 1 3 null null 4 Janice Cooper 3 5 null null 5 Troy Slater 5 4 null null 2 Kelly Short 4 4 null null 4
Its an exception report, so i want to show what was not done , on days that are supposed to have things done, i cant show this, if dates are not displayed before there were no visits less then 6. I want to display all relevant dates, where any number of visits was mad. But only show the visits that were less then 6.
Here's my stored procedure that already has the less then 6 visits showing. But doesnt always show the dates. What should i do?
Hey everyone, I'm running into an issue where I have a select statement that retrieves all employee records from a employee table. In this statement I'm displaying first and last name, titlte and the employees manager id(which is the employees id). What I want is a query that instead of displaying the manager's id, display the manager's name. The query that I'm using below doesn't work. I was hoping that someone could help me out: SELECT LastName, FirstName, Title, Extension, (SELECT FirstName, LastName FROM Employees WHERE (ReportsTo = EmployeeID)) AS Reports FROM Employees
Here's the data that I'm using: LastName, First Name Title Extension Reprots To
Davolio Nancy Sales Representative 5467 13
Fuller Andrew Vice President, Sales 3457 0
Leverling Janet Sales Representative 3355 12
When I run this query I get a error saying "Only one expression can be specified in the select list when the subquery is not introduced with Exists"
So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query. Anyways, in my select I have something like this: sum( case when code in (1,2,3,4) then 0 else 1 end ) as total which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails: sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total because apparently you can't use selects or aggregates within an aggregate. So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well. Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).
Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.
My SQL statement:
Select * FROM tblData WHERE LastName= (SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"
What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.
When there are multiple duplicate names then I run into an error.
When the statement is put into a string and executed in VBA I get this error:
"Run-time error '3021': No current record."
When the statement is put into a query and run against the DB I get this error:
"At most one record can be returned by this subquery"
So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.
I would like to know which of the following query is the fastest:
select * from customers C where exists (select 0 from ORDERS O where C.Name like 'A%' and O.Charged = 1)
select * from customers C where exists (select 0 from ORDERS O where O.Charged = 1) and C.Name like 'A%'
I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.
I would like to know which of the following query is the fastest:
select * from customers C where exists (select 0 from ORDERS O where C.Name like 'A%' and O.Charged = 1 and O.Customers_Id = C.Id)
select * from customers C where exists (select 0 from ORDERS O where O.Charged = 1 and O.Customers_Id = C.Id) and C.Name like 'A%'
Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?
I need to build a view that has 4+ columns that are "counts" of relationships with other tables.
for example:
Code Snippet select Name, Description, (select count(*) from PetOwner PO where PO.OwnerId = O.Id) as PetCount from O Owner
I need to add a few more counts to this query, but I'm thinking that is a bad idea since the number of joins is going to get out of control if I need more counts.
I've created a view with all the counts and it works fine.
I want to index the view so the counts aren't calculated everytime the query is run, but I can't index if I use a subquery like this.
In the end I want the result set to have these columns.
I have a table with two columns: OID and Cumulative (witch is the same type as OID) Each OID can have one or more Cumulatives.
Example of data: OID Cumulative 167 292 167 294 167 296 168 292 169 302 169 304
The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical). Example: 167->292->590 So the table would have on more row: OID Cumulative 295 505
I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure: OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.
How can I do the query? Is there a better way as my table with n columns?
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Dear experts, Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS. Well, actually i was using IN. I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed?? Really appreciate it if some expert can tell me. Thanks in advance
I wonder if somebody could give me advice on SQL Server7-specific handling of subqueries and backreferences...
What is a) more efficient, and b) faster:
1. IF (DATEDIFF(minute, ( SELECT tLastHitTime FROM ( SELECT * FROM tblSession WHERE tSessionID = 'abcd1234' ) as sess), getDate()) < 30) SELECT * FROM sess
or:
2. IF (DATEDIFF(minute, ( SELECT tLastHitTime FROM tblSession WHERE tSessionID = 'abcd1234' ) as sess), getDate()) < 30) SELECT * FROM tblSession WHERE tSessionID = 'abcd1234'
In the first example, 2 SELECT statements (line 2 & line 8) refer to derived table by its alias 'sess'. In the 2nd example, both queries are independant, yet identical...
I wonder if SQLServer is "smart" enough to execute identical queries only once, and then reuse the resulset (e.g. derived table), or it requires explicit references (such as alias) in order to do so...
hi there, I have a query that works on sybase and want to make it also works on SQL Server. The problem is that in this query I 'm using a subquery in an aggregate function. It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function. How can I overcome this problem.
To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work. Is it because the correlation must be back-to-back, without any levels that stand before the two?
If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):
---------------
select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,
count(date_picked) as wins
from printers as printersGlobal
left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company group by printersGlobal.company order by printersGlobal.company asc
----------------------
When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.
I would appreciate if anybody can point me in the right direction.
Hopefully someone can suggest a better solution than what I'm currently hobbling along with.
Basically, I've got a table that gets rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest values of each particular item.
I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or some FANCY item.
But for the time being, I've got a less-elegant query that returns the correct data, just using subqueries.
-- JUST DISPLAY THE TABLE FOR EXAMPLE SELECT * FROM @actionHistoryTable
-- THIS ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID SELECT aht.itemID ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '1' ORDER BY aht2.actionTime DESC ) as 'latest type 1 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '2' ORDER BY aht2.actionTime DESC ) as 'latest type 2 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '3' ORDER BY aht2.actionTime DESC ) as 'latest type 3 value' FROM @actionHistoryTable aht GROUP BY aht.itemID