I'm strugling with a string function.
I have a PRODUCT field and it contains 4 kind of values:
I want the values to look like this:
 if the length of PRODUCT without zeros is smaller then
7 the output is:
 if the length of PRODUCT without zeros is equal to 7 then output is:
 If the lengt of PRODUCT without zeros is equal to 9 then output is:
The If statements  and  are taken care of with af function that I call in my statement:
ALTER FUNCTION [dbo].[LPAD]
-- Add the parameters for the function here
-- Test with data as nvarchar like in Staging
@SourceString nvarchar(MAX), --Varchar(MAX),
RETURNS nvarchar(MAX) --Varchar(MAX) --<Function_Data_Type, ,Int>
RETURN --<@ResultVar, sysname, @Result>
(Select REPLICATE(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
, CAST(RTRIM(dbo.LPAD(SUBSTRING(dbo.SD.PRODUCT, PATINDEX('%[^0]%',dbo.SD.PRODUCT+ ''), LEN(dbo.SD.PRODUCT)), 7, 0))
ASnvarchar(255)) AS PRODUCT
So if PRODUCT is smaller then 7 chars I need a zero to make a maximum length of 7 chars. The first 11 zero's need to dissapear and thats taken care of with the above function and statement.
It's the third statement that gives me a headache, I also want the PRODUCT with 9 chars without any leading zero.
Does anyone know how I can integrate the 3rd if please ???
This one will work assuming the product codes are always numeric:
DECLARE@INPUTNVARCHAR(MAX) = 'your product code with or without leading zeros goes here'DECLARE@TMP_INPUTINT = CAST(@INPUTASINT)
SELECTCASEWHEN LEN(CAST(@TMP_INPUTASNVARCHAR)) < 7THEN SUBSTRING(@INPUT, 1, 7)
My advice is free, and you may get what you paid for.
STUDENTNO TOTALSCORE ENGLISH GEOGRAPHY MATHEMATICS BIOLOGY C.
20 240 50 60 70 60
now i wish to retrieve records in the format:
SUBJECTNAME Exam I Exam II Exam III Avg
ENGLISH 50 78 67
GEOGRAPHY 60 67
MATHEMATICS 70 56
BIOLOGY 60 90
Any idea on i can go about this?. any help will be appreciated
Unless you have omitted in your post something from your database-model, my first suggestion would be to rework your database-model a bit. You're missing some stuff there which would make a lot of sense:
Then you can get the marks with a query like this:
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
StudentId = ... /* however you want to narrow your query result */
ExamId = ...
SubjectId = ...
Edit: What you can read when following the link Peter Loew has posted in his answer is basically the reasoning for my suggested rework of your database-model.
I have a query and it takes too much time to fetch
How much time would be allowed?
There's no faster alternative to splitting a string, but it does count as a design-mistake. Each attribute in the tupel should be atomic. You should not need to separate the facts from a single field. They should have been two separate columns.
You "could" try to create an view that does the splitting, and making sure that those calculated columns are materialized. It would effectively move the extra processing required to when the view is created, rather then when the data is requested.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
I have an SSRS report which has two columns X and Y value but it is same category, so for example, Axis is the main column under this I have X and Y values, for showing it in more sensible way I want to have a a row in the header which has Axis and from the next row I want to have X, Y headers under these I want to display V values and Y Values, I could have done same by taking two columns and keeping the X Axis and Y-Axis as column headers but I want to show them in more readable format.
Can I do it by using SSRS, please help me but any suggestion, link or code snippet, it would be great help for me.
I have an SSRS report in which I want to change the name of the field from FundingModel to ClassFundingModel and I want to add a new field called "FundingModels", for this I have added the same in the ReportDataset and refreshed the fields, the new fields shows up in the query when I run it but when I copy this same report on to IIS folder and call it from Report Viewer, it gives me the following error. Please help me what should I do to make it working through report viewer also.
I tried to open the table1_Details_Group by right clicking to see if I can edit it at all it is using the old field name ie. ‘FundingModel’, but it doesn't show me any field names, where is it setting and how can I edit table1_Details_Group, if I can't or if I have done any mistake in editing the field names and in adding the new fields please help me with that, please help me any link, code snippet or advice is very helpful.
Thanks in advance.
The SortExpression expression for the grouping ‘table1_Details_Group’ refers to the field ‘FundingModel’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.
I'm working as devops in a SSAS project with SQL Server .
My final goal is automatise the deploy process of the olap cube (or SASS), I'm getting hard time try to do it.
Anyone have experience in this kind project?.
Can provide me information or material to study
What I need to do is find a way to do all the process by command line.
I have some parts of the overall process already resolved.
But what keep me frustrate is find a way to process the cube by command line
Background of the project
The datasource of the project is a virtual database,in consecuence, it's can read information for any data sources (other databases like SQL Server, Mysql or even documents like XML, spreadsheets ) and expose as single source to the cube.
more info in this link.
Before to start process, for performance reason it's necessary to stop any scheduled jobs in the cube.
When the process start, it's going to fetch the information from the vdb if there are 50 million records from legacy datasources. Then they are going to stored into the cube cannot do anything to avoid this
After finish to process, The final step is run some unit test made with NBI testing framework .
After finish to process i need to run some test made with NBI testing framework over the cube.
> > If the field fiscal_year exists in the date dimension
> > the query X is not doing a fullscan
more info in NBI codeplex site
Hello guys, this is my first message in codeproject.
My problem is the next, I have two databases in two different servers of SQL SERVER 2012, both, and I am using indexed views, functions that calling to that indexed views, Full-text catalogs ...
I need to have the same structure in both, I do change the database 1 and, I want to have the changes in the second without creating everything from the start. I have tried using scripts and tools like db comparer. I choose the elements or the execution of the script step by step, by order, for avoiding impact problem.
Well, I have the following error:
You can notuse the CONTAINSorFREETEXT predicate ontableor indexed view'dbo.vwProducts', because it isnot full-text indexed.
When I create the script, I select the option of creating "Full-text catalog", but definitely it doesn't create the Full-text catalog correctly. Is there any option of creating a script respecting the data from the database 2 and also incorporating "Full-text catalog" and other changes?
It is not possible to do a backup of full text catalog, so if you use it, make sure to have the tables and the model finished, or almost finished. Because, if you do changes (to add a column, create a new foreign key...), you will need to create the full text catalog and the indexes over and over again.
Despite to everything, I recommend the use of full text catalog in some cases, because his use increase the speed of the querys with filters, it is faster than the use of LIKE in most cases. But the maintenance is bigger.
I'm being asked to create a couple of SQL statements to retrieve information about a Luxury Hotel Database.
List all the rooms in each hotel that have never been reserved in order by hotel number.
I have the SQL statement retrieved the rooms what have been reserved but my problem is retrieving those that have not been reserved.
SELECT RoomNo FROM RESERVATION WHERE RoomNo ORDERBY HotelNo
List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.