I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum
I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?
This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.
This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).
CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset ( -- paging @startRowIndex INT = 1, @maximumRows INT = 999999,
-- set the unique key used to ensure the rows are sorted deterministically SET @uniqueKey = 'title_id'
-- build the FROM table source used throughout this procedure SET @tableSource = 'titles t inner join publishers p on t.pub_id = p.pub_id'
-- build the WHERE search condition used to control filtering throughout this procedure
SET @searchCondition = '(1 = 1)'
IF @title IS NOT NULL SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')' IF @type IS NOT NULL SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')' IF @price IS NOT NULL SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'
-- build the ORDER BY expression used to control the sorting throughout this procedure
SET @orderByExpression = @sortColumn + ' ' + @sortDirection
-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique IF @sortColumn <> @uniqueKey SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection
-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order
SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey + ' FROM ' + @tableSource + ' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression
-- add sql to filter the results based on criteria passed in as parameters SET @sql = 'SET ROWCOUNT @rowcount; ' + 'SELECT t.title_id, t.title, t.price, t.type, p.pub_name, p.city, p.state, p.country FROM ' + @tableSource + ' WHERE (' + @searchCondition + ') AND '
-- add sql to control the starting row IF @sortDirection = 'ASC' SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' + @sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )' ELSE SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' + @sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'
-- add sql to control the ordering of everything SET @sql = @sql + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
I have the following table (which is an import from another system Ican't mod):-CREATE TABLE [tbl_wsg_maternity_observations] ([documentname] [varchar] (40),[clientguid] [decimal](16, 0) ,[docguid] [decimal](16, 0) ,[displayname] [varchar] (80),[valuetext] [varchar] (255) ,[valuenum] [float] NULL) ON [PRIMARY]GOWheredocumentname is the name of the documentclientguid is the unique identifier for my patientdocguid is the unique id for the documentdisplayname is the dataitem (e.g. diagnosis)valuetext is the "answer" (e.g. kidney failure)valuenum is used instead if the valuetext is an integer (e.g.number of toes)I am trying to split/change this table so that I have a different tableper document, with one row per patient occurance with the displaynamesas columns.I have been using the following but it is slow and for large tablestakes hours (literally) to run:-SELECT distinctclientguid,(SELECT DISTINCT case when t2.[ValueText] is null thencast(t2.[Valuenum] as varchar(10)) else t2.[ValueText]end FROMtbl_wsg_maternity_observations t2 WHERE 'How many vessels present incord' = t2.[Displayname] AND t1.ClientGUID = t2.ClientGUID ANDt1.docGUID = t2.docGUID) as [How many vessels present in cord],<SNIP...more identical lines, one per dataitem>INTOtbl_wsg_baby_delivery_detailsFROMtbl_wsg_maternity_observations t1WHEREdocumentname = 'Mat Baby Delivery Details'Does anyone have any ideas how to do this faster and preferably moresimply?Will
Has anyone ever looked at the way the grid data viewer sorts it data when clicking on a column header? If you click on a column header, something happens to the sorting of the data in the viewer, but it's not always clear to me _what_ is happening. It appears the data is sorted ascending on the column you clicked on. If you click again the sort order seems to inverse to descending. However, if you look closely, it turns out that the data is not always sorted correctly, especially when you click on an integer valued column.
I found this when doing a demo on the AdventureWorks demo extracting data from the SalesOrderDetail table. If you sort on the OrderQty column, data is correctly sorted in ascending quantities. However, if you click the column again, orders with an order quantity of 2 are displayed on top (while there are orders with a much higher order quantity) and if you scroll down the list, you notice that there is no clear sorting anymore. The same happens with other columns.
Is this supposed to work as I would expect it to do or is there a logical explanation for the behaviour I see?
-- Best regards,
Hans Geurtsen Docent Kenniscentrum
Info Support De Smalle Zijde 39 3903 LM Veenendaal The Netherlands www.infosupport.nl
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-
Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport]) Days Remaining (x8 columns) (named as "TS_" + Credential)
I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-
SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass], [Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND
[code]....
I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.
We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.
e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.
Site %Risk Alfa Beta Gama
X 2 1 2 3
Y 10 4 5 6 However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.
Site %Risk Gama Alfa Beta
X 2 3 1 2
Y 10 6 4 5
Any thoughts on ways to achieve this in SSRS matrix report would really help.
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
Name Description Date Question Answer Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A Profile Assessment 01/01/2015 The total value of assets? Less than GBP 1 million
Customer A Profile Assessment 01/01/2015 The volume of transactions undertaken? Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name Description Date How complex is the structure? The total value of assets? The volume of transactions undertaken? How was the client introduced? Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
I am writing a query to list all users in 2 countries but exclude users which their name starts with _inactive_5#4$66899, holder_....
I wrote
Select position, subsidiaryName from position where subsidiaryname = 'country one' OR subsidiaryname= 'country 2'
I get a result but there are some names which are fake accounts
eg. names start with inactive_#@$%&*ijkgfhg
or with holder_uhgfjgj
how can I do write into the query so that only the proper names without the rows that are fake showing up. In other words i want all results for those 2 countries except ones starting with inactive or with holder......?
I do not want results with combinations like this:
d.workphone is null and m.workphone = '0'
m.workphone is null and d.workphone = '0'
but my query below still brining in records like this, how can I fix this:
select m.number, m.workphone as master_workphone, d.workphone as debtor_workphone
FROM master m
INNER JOIN debtors d ON d.number = m.number
where (d.workphone <> m.workphone) AND NOT (d.workphone = '' AND m.workphone = '') AND NOT ((d.workphone = '0' AND m.workphone = '') OR (d.workphone = '' AND m.workphone = '0'))
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
I have a big presentation tomorrow and I have a query I'm trying to make a little more presentable. query text itself formatted (filtering data)
Tables: --tpeople --tsynclog
The query:
select Firstname+' '+Lastname as [FirstName LastName],synctime as [Nestle synctime],sum(DurationInMinutes) from tpeople join tsynclog on people_id = id where synctime > '2006-05-05' group by tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime having sum(DurationInMinutes) > 100 order by synctime desc
As you can see I need some alias added and maybe the whole thing formatted a little different. Can anyone help? Thanks.
I am new to SQL,I wrting a query which I will at the later stage create a report but my query is creating duplicate records,I have tried the group by and the sub selects but this still bring all uneccessary columns I do not need,
I have a query that goes
Select name Date from date from date to summary Estimated value
I know in this database i only have 1 record containing that information but immediately when i ass the estimated value field a lot of duplicate,I have tried using group by the same thing happens,or even when i tried having count then all the columns disappear.
I have three tables: one that holds users, on that holds products, and one that holds votes on products (fields are userid, productid, and the vote they made (1-5)). I am trying to come up with a query that orders the product result set by number of votes both descending and ascending for a particular user. If a user has not voted, then there will not be anything in the vote table for that user. I am not even sure where to begin on this as it is way out of my SQL experience.
This is a normal query I use to just pull them out ordered by name.
SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, USERS.U_USERNAME FROM [PRODUCTS] INNER JOIN [USERS] ON USERS.USER_ID = PRODUCTS.USER_ID WHERE STATUS_ID = 1 ORDER BY PRODUCTS.P_NAME DESC
Can anyone help or point me in the right direction?
Hi,I am attempting to write a complex query to group sets of data. I have myquery working correctly, but i ran into a problem with sorting.I want to sort my query by a string field.Is there a way around sorting using a field other than numeric of a querycontaining a union?Thanks,Karen
I have a SQL script which runs perfectly on my test database, but fails to run on the (identical) production server. After some research, I found out that the field "Remark" (nvartext type of 800 chars) is the cause of the problem. Error message: "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Taken this error message into account in the script, I changed it to the following: "CASE WHEN dbo.User_Updates.Remark IS NULL THEN '<no comment>' ELSE (dbo.User_Updates.Remark) END AS Remarks"
This also works on the test server, but still refuses to run on the production server. Does anyone know what I can do to solve this?
I'm using txtParentID as the FK, which matches with txtID, to create the recursion.
This is my current Query
This is my current Query:
Code Snippet WITH tree (data, id, level, pathstr, numSortID, memContent) AS (SELECT txtTitle, txtid, 0, CAST('' AS NVARCHAR(MAX)), numSortID, memContent FROM CTLG WHERE txtParentID IS NULL UNION ALL SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent FROM CTLG V INNER JOIN tree t ON t.id = V.txtParentID) SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent FROM tree
I have a query i have been optimizing. Now runs in about 15 minutes but was wondering if there is any way tr educe the SORT cost.
Currently the high costs left are the Table insert which is 58% and the Sort cost of 36%
The inner query below is around 400million rows and aggregates to around 15,000,000 rows)
SELECT@1 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -1, GETDATE() -1) as DATE) SELECT@2 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -2, GETDATE() -1) as DATE) SELECT@3 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -3, GETDATE() -1) as DATE) SELECT@4 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -4, GETDATE() -1) as DATE) SELECT@5 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -5, GETDATE() -1) as DATE)
Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query. Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff @filter1 varchar(100) = '', @filter2 varchar(100) = '' AS BEGIN SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS FILTER( ([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]), [Measures].[Sales] >= 10000 AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
I need to remove all records except a short list from two related tables (related by uniqueidentifiers). I'm working with CE 3.5.
First I tried a coworker's suggestion:
Code Block delete from [SYSTEM_USER] su, SU_PERMISSION where su.SU_USER_NAME IN ('friendlyName1','friendlyName2')
CE doesn't like multiple tables in a delete.
Then I tried a join to remove the foreign key rows:
Code Blockdelete from SU_PERMISSION sp join [SYSTEM_USER] su on sp.SUP_SU_ID = su.SU_ID where su.SU_USER_NAME IN ('friendlyName1','friendlyName2')
CE doesn't like a join in a delete
Then I queried SU_PERMISSIONS to get the GUIDs associated with the usernames, and built my query (I manually did a SELECT subquery, which I know CE does not support):
Code Block delete from SU_PERMISSION where SUP_SU_ID not in ('ea6b9b05-e1b1-b86a-e072-d6c8af80b051','0a102ce9-bca5-8df4-e062-d6c8af804072')
CE removed every row in the table, including the ones with these GUIDs.
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
