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'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',
[Code] ....
I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes. Which method of design would you chose for this scenario:Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joiningSo second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records. ThanksBen
View 3 Replies View RelatedI have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.
I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
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?
regards.
Julio Diaz.
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?
View 6 Replies View RelatedI 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
View 4 Replies View Related
Hi ,
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.
Does any one know why this happenes?
Thanks
I'm supposed to create a report like shown below.
Credit Tier
Jan-07
Feb-07
Mar-07
AA
0.00%
0.00%
0.00%
A
0.00%
0.00%
0.00%
B
0.00%
0.00%
0.00%
C
0.00%
0.00%
0.00%
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'.
Please Can any one help me to create this report?
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
View 1 Replies View RelatedI 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.
Hi all
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.
Thanks
Hi everyone:
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?
Barry
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?
Thanks.
I have a simple report with a row grouping on teh Date and record_id from an sql query. I would like to find the row total.
The expression I have on the row is:
=IIF(Max(Fields!closing.Value) > 0, Microsoft.VisualBasic.Interaction.IIF(Max(Fields!opening.Value) > 0, IIF(Max(Fields!closing.Value) > Max(Fields!opening.Value), Max(Fields!closing.Value) - Max(Fields!opening.Value), Nothing), Nothing), Nothing)
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.
Hi,
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:
=iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C"))
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!
Thanks.
Hi
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...
RETURN (Convert(VarChar(8),@DateTimeNow,112)+ Right(Convert(VarChar(30),@DateTimeNow,126),13)+dbo.myInitials())
Where the dbo.myInitials() calls:
RETURN (SELECT STAFF.Code from dbo.STAFF where STAFF.Login = dbo.myLogin())
and dbo.myLogin() calls
return UPPER(Right(System_User,PATINDEX('%\%',System_User)))
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.
Quote:
This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?
View 5 Replies View Related
Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)
I tried to do that. But I got a error message.
"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Please can some one help me out?
I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.
So it reports for example how much we billed in May 2015, 2014 and 2013.
I have got the table to display this when reporting on one dataset by grouping by year
The datasets both look similar to this
SELECT
Practice.ibvSalesByJob.JobBilledExVAT
,Practice.ibvSalesByJob.[Year]
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType
[Code] ...
The calculated field is
=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")
The field I am looking to add together is below
=Sum(Fields!JobBilledExVAT.Value)
If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.
I tried this
=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")
However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.
I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.
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.
View 3 Replies View RelatedSetup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,
[Code] ....
Does boolean data type exists in Sequel if y how??? if N y???
How can i opt for boolean ( nearest ) ?
I've used Bit with not null
i want 2 display column of bit datatype into Checkbox .. Is is possible??
Whenver i bind the datatype of bit for checkbox it is throwing an error??
HELP ME HELP ME
I am trying to find boolean data type in SQL Server. but i didn't find.
is this data type available or not.
I want to make a column true or false
Thanks.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)
[Code] ....
Im used to using access and now im gonna switch to sql server 2005 express. My program is coded in Visual basic 2005 where all connection is made to the access database by oledb.
Now when im creating the database in management studio express, ive noticed that there is no boolean datatype, but instead there is a bit datatype.
Is this the one to use, and would the bit datatype accept true/false values when sending sql commands? In visual basic2005 i think that it uses 0 for true and -1 for false or something like that, so when switchin database i would have to recode quite a bit.
Or is simpler than this?
Hello. A question please. To define a column of a table in SqlServer, Does it exist a boolean data type ?
Thanks...
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
[code]...
what I need to change in the WHERE clause to rectify this error ?