THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME. I am not good with SQL so please bare with me.
I will give a dumbed down table for us to work with:
timestamp - DATA1 - DATA2 - MAXVALUENEEDED - DATA3
2006/8/12 12:00 123 125 1289 121
2006/8/12 12:05 124 128 1291 123
2006/8/15 12:00 130 128 1190 124
2006/8/15 1:00 131 127 1196 123
The columns labeled DATA do not really matter. The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns.
What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY.
Example Output-
2006/8/12 124 128 1291 123
2006/8/15 131 127 1196 123
As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis.
My current SQL Query is: SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C,
CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0)
FROM Table_1 T1
WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp)
ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days. Can anyone help me correct this? Thanks in advance for your help.
I have two tables populated during the use of an application to loguser events and application states. They are named "EventTable" and"StateTable" and the structures follow:EventTable:ID EventTimeStep EventID-- ------------- ---------1 5 E12 22 E23 56 E3StateTableID StateTimeStep StateID-- ------------- -------1 1 S12 39 S2I want to perform a query that reports the StateID of the applicationat the time that each event was logged to the EventTable. The desiredoutput is:ID TimeStep EventID StateID-- -------- ------- -------1 5 E1 S12 22 E2 S13 56 E3 S2I have tried to create a query with an INNER JOIN where the value forthe StateID output field comes from the last row in the StateTableWHERE StateTable.TimeStep <= EventTable.TimeStep and where I use aGROUP_BY EventTable.ID to merge the following rows from the join:3 56 E3 S13 56 E3 S2However, the closest I can get is a query that gives me the wrongstate when applying the GROUP BY clause3 56 E3 S1I also think that the queries I have written is slow and inefficient.Is there a better way to perform this query or is my database designfatally flawed?Thanks,Adam Nemitoff
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
Anyway to figure this out? This is what I have so far. Sorry, but im in access 03.
SELECT tblCAESchedule.Name, tblCAESchedule.Title, tblCAESchedule.SunStart, tblCAESchedule.SunEnd, IIf(DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])>=1,DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend]),DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])+1440)/60 AS SunHours, tblSupSchedule.Name, tblSupSchedule.SunStart, tblSupSchedule.SunEnd, Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60) AS StartDif, Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS EndDif, Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60)+Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS ShiftDif FROM tblCAESchedule INNER JOIN tblSupSchedule ON tblCAESchedule.Skill = tblSupSchedule.Skill WHERE tblCAESchedule.Skill = "Sales"
I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:
Table X ID ------- 2,1,4, 2,5, 1, 3,1,2,4, ect... whereas the ID field in Table Y contains one ID in each row EX:
Table Y ID Color ------- ------- 1 Green 2 Blue 3 Red 4 Yellow 5 Orange ect...
Is there a way to join these two tables together? I need the output to be...
ID Color ------ ------- 2,1,4, Blue,Green,Yellow 2,5, Blue, Orange 1, Green 3,1,2,4, Red, Green, Blue, Yellow ect...
I have a query in which I have constructed a bunch of left outer joins, however I need to also join one of the tables on the "right" side of one of those joins with an entirely different table. What I have is as follows:
select F.*, FRL.findingRiskLevel, E.entityName, G.gName, FRS.findingStatus, FCA.findingControlArea, A.aYear, A.aDesc from Findings F
left outer join Audits A ON A.aID = F.auditID left outer join Entities E ON E.entityID = F.findingEntity left outer join Groups G ON G.gID = F.findingGroup left outer join FindingsReportingStatus FRS ON FRS.findingStatusID = F.findingRepStatus left outer join FindingsControlAreas FCA ON FCA.findingControlAreaID = F.findingControlArea left outer join FindingsRiskLevels FRL ON FRL.findingRiskLevelID = F.findingRiskLevel
So I also need to join Audits.aTypeID on AuditTypes.aTypeID and pull back AuditTypes.aType
Any tips appreciated! My attempts thus far have failed me :)
SELECT * FROM A INNER JOIN B ON A.AId = (SELECT B.BId FROM B WHERE (B.AId = 4))
I have two tables Table A has several columns and B has just two columns B.AId and B.BId.
I will pass B.AId as a parameter and that will return more than one B.BId values. Then I need to create a join where A.AId matches B.BId.
I tried the above SQL but it says subquery can't return more than one value. In my case it must return more than on value. How can I construct the SQL query. Please Help. Thanks
Ok, getting a little better at joins, but now need to take it two more steps.Main table tblEquipmentsFields: ID, EquipmentType, Location, StatusSecond Table tblEquipments_GroupFields: equipmentID, categoryID, subCategoryID (all are ints)I joined these two together and got the info back I needed:SELECT a.ID, a.Description, a.Status, a.Location, from tblEquipments ainner join tblEquipments_Group U on u.categoryID = a.EquipmentTypeand u.subCategoryID = @type Now, the a.status returns a number. What the status actually is is in tblStatus.Fields: statusID (int) , Status (varchar).So, the statusID matches a.Status.I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.The other one, location, is the same situation.I've been trying to do another join and am at an end on this.Suggestions?Thanks!!Zath
I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )� ). I have 7 left joins and where statements.
The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.
Can anyone help!
The full SQL is as follows:
SELECT DATE_DAY , SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , FORCE_STATUS , NUM_CREATED , NUM_OPEN , NUM_AWAITING_DISPATCH , NUM_OPEN_IN_DUE_DATE , NUM_DISPATCH_TO , DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO_G_L , MET_SLA_DISPATCH_TO , mdt.SLA AS SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L , C_NUM_DISPATCH_TO , C_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO_G_L , C_MET_SLA_DISPATCH_TO , mdt.SLA AS C_SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L , NUM_DISPATCH_FROM , DURATION_CREATE_TO_DISPATCH_FROM , NUM_DISPATCH_TO_TO_FROM , DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM_G_L , MET_SLA_DISPATCH_FROM , mdf.SLA AS SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L , C_NUM_DISPATCH_FROM , C_DURATION_CREATE_TO_DISPATCH_FROM , C_NUM_DISPATCH_TO_TO_FROM , C_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM_G_L , C_MET_SLA_DISPATCH_FROM , mdf.SLA AS C_SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L , NUM_CLOSED , DURATION_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE_G_L , MET_SLA_WIP_TO_RESOLVE , wip.SLA AS SLA_MET_WIP_TO_RESOLVE , wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L , DURATION_MTTR , SLA_MTTR , SLA_MTTR_G_L , MET_SLA_MTTR , mttr.SLA AS SLA_MET_MTTR , mttr.SLA_G_L AS SLA_MET_MTTR_G_L , NUM_RESOLVE_FD , rfd.SLA AS SLA_RESOLVE_FD , rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L , NUM_DISPATCH_NTF , ntf.SLA AS SLA_DISPATCH_NTF , ntf.SLA_G_L AS SLA_DISPATCH_NTF , AR_NUM_MET_DUE_DATE , ar.SLA AS AR_SLA_MET_DUE_DATE , ar.SLA_G_L AS AR_SLA_MET_DUE_DATE FROM ( SELECT DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS , sum( NUM_CREATED ) AS NUM_CREATED , sum( NUM_OPEN ) AS NUM_OPEN , sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH , sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE , sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO , sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L , sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO , sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO , sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L , sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO , sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM , sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM , sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM , sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L , sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM , sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM , sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM , sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM , sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L , sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM , sum( NUM_CLOSED ) AS NUM_CLOSED , sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L , sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE , sum( DURATION_MTTR ) AS DURATION_MTTR , max( SLA_MTTR ) AS SLA_MTTR , max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L , sum( MET_SLA_MTTR ) AS MET_SLA_MTTR , sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD , sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF , sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE FROM CTS.dbo.NDC_Scorecard_Summary_Table t WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime) AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime) AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' ) AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' ) GROUP BY DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END-- AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS ) t LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITY WHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL ) AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL ) AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL ) AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL ) AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL ) AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL ) AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )
I am trying to use the merge join example in the following link. To import new records only.
http://www.sqlis.com/311.aspx
The problem is that for some unkown reason the join is not woeking correctly. One of the records is incorrectly showing a NULL on the output. This would indicate that it would be a new record, but it is not it already exists in the new table.
I created a dummy table in SQL and executed the same join and I always get the right answer. What the heck could be wrong?
For example. Table A has 20 records Table B has 3 records. Table B has the new records I want to import into Table A. The package runs corectly the first time, only importing the 3 new records. Then the next time the package runs it shows 1 of the 3 records as being new still, and tries to import the record causing a PK error. Adding a watch to the MERGE output shows that the one record has a NULL on the join.
I have two employee tables called EmpA and EmpB.Each table has the same attributes of Employee ID and Email address.I do an inner join on email address like this:
select * from EmpA inner join EmpB on EmpA.email = EmpB.email where EmpB like '%@mydomain.com'
I now want to modify the above where I want to output rows such that
I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?
SQL with Left Outer join operator:
select CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.line#, WeeklyMetricsFormat.MetricsType, WeeklyMetricsFormat.Metrics, WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol ) else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end else convert (varchar, CurrentWeekFinMetrics.displayCol ) end from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#) where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen' order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#
SQL with *= operator select CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.line#, WeeklyMetricsFormat.MetricsType, WeeklyMetricsFormat.Metrics, WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol ) else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end else convert (varchar, CurrentWeekFinMetrics.displayCol ) end from WeeklyMetricsFormat , CurrentWeekFinMetrics where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen' AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)
For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.
I want to query a database through a ASP.NET page and I want to set the page up like an advanced search page would look. My question is, how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values. For instance, if someone wants to query by TRIP id and not vehicle ID then how do I write the where claue?
I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column: SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'. I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.
I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..
SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer FROM item, t15 WHERE item.row_id = t15.id AND t15.f167 > getDate() UNION ALL SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a > ' FROM item, t30 WHERE item.row_id = t30.id AND t30.f221 > getDate() ORDER BY start DESC
The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.
So the question is basically, how do I get the following to work:
'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'
Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !
Dear Expert,I have 2 tables as the following,Table : ProductColumn : ID, Code, NameTable : TrxColumn : Trx_ID, Trx_Date, Product_IDHow can perform the following query in MSSQL or is there any other wayto perform the same query ??? For you info I can do this in Sybase andit's work fine. Thank you.================================================== ==========Select Trx.Product_ID,(Select Produc.CodeFrom ProductWhere Product.ID = Trx.Product_ID) as Product_CodeFrom TrxWhere Product_Code = '0001'================================================== ==========Regards,Chen Fock Siong2004-05-27
I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.
Dbo.Products – This table holds product names and information.
Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID
Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.
Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID
Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.
Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are “required� (no product will be returned that does not have what is in the list).
I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.
If any of you know how to do the above with a subquery of some kind, please let me know.
I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:
Table has the fields: id, fruit, color
User enters: apple pear green
The query should return all records that contain apple AND pear AND green in any of the fields.
Now I do it like this:
SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')
this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.
Question: is there an easier way to achieve this?
(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)
SELECT tKlant.nKlant AS nKlant, MAX(sAchternaam) AS sAchternaam, MAX(sVoornaam) AS sVoornaam, MAX(sVoorletters) AS sVoorletters, MAX(sBedrijfsnaam) AS sBedrijfsnaam, MAX(sPlaats) AS sPlaats, MAX(sStraat + ' ' + sHuisnummer) AS sAdres, MAX(sPostcode) AS sPostcode, MAX(sCommunicatie) AS sCommunicatie, (CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig, MAX(sKlantGroep) AS sKlantGroep FROM tKlant LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%') AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%') AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%') GROUP BY tKlant.nKlant, DATALENGTH(mOverig) ORDER BY sAchternaam, sPlaats, sAdres;
KEYIDGROUP 1 1 a 2 1 b 3 2 a 4 2 b 5 3 a 6 3 b 7 4 a 8 5 a
This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.
query should reveal answer should be missing the group b KEYID 7 4 8 5
I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B
SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date,  @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable (  IDTable INT IDENTITY NOT NULL,  DateLog  DATE NOT NULL,  CountLog INT NOT NULL )
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01', @dateTo   ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
Id     MonthYear    Diff 1       Jan2014    5 2       Feb2014    3 3       March2014  0 4       April2014    5
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range  was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way. Â
I have 2 TableAuthorsID Name1 Clint2 Voke BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2 Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price. How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results. thanks,
This is a fairly detailed problem, so this will be a long post... I do appologize. I have been agonizing over this now for over a week and cannot find a viable solution. Hopefully one of you can help.
First off, I work for a realestate company, and this query will display a list of properties based on a number of different criteria and criteria types. There are multiple tables involved:
dbo.Prop: Property Database. Holds basic info about each property
dbo.Prop_Features: holds all the features (such as Pool, Carpet, Drapes etc) for each property. The only information stored in this table are PropID and FeatureID (PropID being the Identity of the Prop table, FeatureID being the Identity of the Features Table)
dbo.Features Holds information on each possable "feature" in the system.
dbo.Members Holds basic information and criteria for each of our members.
dbo.Members_Features This table holds the MemberID and FeatureID where members have chosen one ore more features to be used for criteria when searching for a property.
Ok, now... That said, here is my problem. The query I had written (by a professional hired thru Robert Half Technologies) takes over 30 seconds to execute. I will post a copy of that SP below. This is unacceptable. We have to process thousands of these per hour, and a 30 second process time is very bad. Can any of you give me a idea of how to better approach this problem?
In the code below, you will notice there are other tables I did not mention - they are not important. The Speed problem is surrounding a single function, which I will mention below.
CREATE PROCEDURE dbo.Member_Get_List ( @MemberID Int, @UpdatesOnly Bit
SET @PropCount = (SELECT Count(*) FROM Members_Features WHERE MemberID = @MemberID ) END
IF @PropCount = 0 BEGIN
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10), P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End as 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage
AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
CREATE TABLE #Prop ( PropID int, Bedrooms tinyint, Baths float, Garage float, DisplayText varchar(75), Rent float, Address varchar(100), Xstreets varchar(100), DateAvailable varchar(10), Lease tinyint, Features Varchar(3500), Deposit float, Phone1 varchar(12), AreaName Varchar(50), County Varchar(30), City Varchar(30), State Varchar(75) )
DECLARE curProp Cursor FORWARD_ONLY for
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10),P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features',
This function is the cause of the speed problem, methinks. When run alone, it takes 29 seconds to process with the same MemberID that takes about 33 seconds to process completely (the whole Proc). This function simply returns a list of comma delimited features for each property, for displaying on a customer list. Here is that function:
CREATE Function dbo.Prop_Get_Feature_List_Fun (@PropID int) RETURNS Varchar(3500)
AS BEGIN
Declare @FeatureList Varchar(3500)
select @FeatureList = Coalesce(@FeatureList + ', ', '' ) + F.FeatureName FROM Prop P
INNER JOIN Prop_Features PF
ON P.PropID = PF.PropID
INNER JOIN Features F
ON PF.FeatureID = F.FeatureID
WHERE
P.PropID = @PropID
ORDER BY
F.FeatureName
Set @FeatureList = isnull(@FeatureList,'Please call for features.')
RETURN @FeatureList END
Now, I know I probably gave lots more information then needed to solve this issue - but its better to have too much then not enough.
Any help at all in speeding up this function or describing another way to do this would be most appreciative.
I graduated from college about three years ago and have been working as a programmer using MS SQL 2000 and even though I've learned a TON in these past three years I know there is tons more I can learn.
Because my company uses Microsoft SQL Server 2000 I'd like to focus on that, but anything ANSI-SQL is perfectly fine.
So... what are some good books on SQL query optimization and other advanced SQL topics?
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
Hi I have a function which returns the periodId from the period table and the function is as follows: ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS int AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 1 AND 3 THEN 5 WHEN @month BETWEEN 4 AND 6 THEN 6 WHEN @month BETWEEN 7 AND 9 THEN 7 WHEN @month BETWEEN 10 AND 12 THEN 8 END END Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
