I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:
Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:
=First(Fields!MyField.Value, "dataset_B")
OR
=First(Fields!MyField.Value, "dataset_C")
This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.
Any advice you have for me would be greatly appreciated!
I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever.
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.
Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
Hi This may seem amazing and a stupid question but:
Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML SET @XMLVAR = '<?xml version="1.0"?> <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
I have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
SELECT * FROM dotable WHERE dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121) AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error: SELECT * FROM dotable WHERE dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121) AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.
Product O is added according to:
for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501 for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150
SQL:
--Existing Data --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with CREATE TABLE #Table1 ( product char(100), yrmnth varchar(6), en_count int,
[Code] ....
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with CREATE TABLE #Table2 ( product char(100), yrmnth varchar(6),
i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.
I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.
Hi people My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
I have an OLE DB Source and i want to transform the data type fields of the table before i export the table in an OLE DB Destination. Is there a way to transform numeric value to float, and numeric to nvchar?
This doesn't necessarily belong in this forum, but I'm starting here in the hopes of getting some direction.
Business users have asked me to "map" a spreadsheet to our Datawarehouse. The spreadsheet contains a lot of calculations.
I created a first pass, but it was difficult to map the Analysis Services cube data to the spreadsheet data and in the process I had to hard code a lot of things that will make the spreadsheet less flexible for additional data.
So my question is, where is the best place to put calculated fields. In my SQL Statements, in ths SSIS transformations, or in the Analysis Cube?
Any help, or pointers to more information, would be greatly appreciated.
I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?
I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,break_tue, lunch_tue, dinner_tue, ent_tue, .....etcI want to output the sum of the four monday columns as well as the sum ofthe four tuesday columns.I did this withSELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,break_tue+lunch_tue+dinner_tue+ent_tue AS tue_totFROM expense_reportI now want to add mon_tot and tue_tot AS total, but SQL is telling me thatit can not find mon_tot. Is there a way to do this? Thanks!DarrenMCP
I have created a calculated data field for one of my data set. I use it for a table in my report. But when I try to preview it, visual studio crashes and I get the following error message.
"An error occured during local report processing. An internal error occured on the report server.see the error log for more details."
I have not installd servers on my machine(only the sql tools). So I can not see error logs either.
Time columns go up to Dec 07. But I did not show all the columns.
The values are calculated as follows. Field value for Jan07 = (No of Loans pass due in Jan07 / Total No of loans disbursed in Jan07) Repayment due date is 5th of every month. if smbody does not pay on 5th, its considedred as pass due.
Source data tables look like this.
LoanTable(Disbursed date, userID, Amount, Status)
CreditTier table (Credit Tier, Rate) - Seems like no relationship with Loan table
we can identify pass due loans from status field in loan table. Status appears as 'Deliquency'.
I have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute ,ApplicationName ,COUNT(*) as Requests FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute WHERE EventClass IN (10,12) GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null. Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?
Help! I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
use Northwind Go select dbo.Orders.OrderID ,Cast(dbo.Orders.OrderDate As DATE)Order_Date , dbo.Customers.CustomerID , dbo.Customers.CompanyName , dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
I am new to SQL Server and am migrating another database
In my original database I have a default(constant) type field and a calculated field both of which call the same user defined function: GetMyUID()
My Function GetMyUID() returns the current date, time and users initials, i.e. "20080522T09:31:15.250LSG"
When a record is first created both fields have identical values
As the record is updated over time my constant field stays constant and my calculated field reflects the time the record was last updated and the initials of that person. So my first field is called 'Created' and my second is called 'Updated'
I would have thought that something like this would be a pretty bog standard and very straightforward requirement in any database
However in SQL I am getting error messages about the return value being non deterministic
I searched the web and found advice that to sort the problem I need to use WITH SCHEMABINDING in my function definition
Unfortunately I am still getting the same 'non deterministic' error
I wonder if (in the quest to not have an overlong field) by looking up the persons initials from a 'STAFF' file rather than leaving the username in full tacked on to the end that this is causing the problem?
I can't imagine that what I am trying to achieve is rocket science but unfortunately have not been able to find any resource on the web that solves this issue for me
In desperation I turn to you
Please help (preferably by letting me have a few lines of code that return the current date/time followed by the username lookup of a Username's initials, here is a snippet of my code...
Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.
