Computing The Queried Output

Mar 11, 2008

hi please help me,
i have a table queried using this sql,
  select name,(select count(*) from myTable a where a.name = r.name ) as Total,
(select count(*) from myTable b where b.name = r.name and dnum > '1') as Used,
(select count(*) from myTable c where c.name = r.name and dnum < '1') as remaining
from myTable r
group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150%  this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks 

View 4 Replies


ADVERTISEMENT

Min Output Size For Queried Field

Oct 12, 2007



I have a select statement that is being processed through oSql on Sql Server 2000. There are 2 fields in the select statement that are defined in the dateabase as nvarchar(1). When I perform my select statement, they show up in the output as 4 char fields. See dataset below for example.



493575545493575545003753404A 20070805000000002007080520070805 131307269009426800000000000000000000000
493575545493575545003753404A 00000000000000000000000020010410S 131307270009426800000000000000000000000
493575545493575545003753410A 20070805000000002007080520070805 131307271009426800000000000000000000000

How do I get rid of the extra spaces in the output? I have tried using ltrim(rtrim(fielde)) to no avail. Fieldg (the S) is a nullable field and is being processed using an isnull(filedg, ' ').

The general statement is:

Select fielda, fieldb, fieldc, ltrim(rtrim(fieldd)), fielde, fieldf, isnull(fieldg, ' '), filedh from mytable

The functioality can be replicated using:

Select 'a', 'b'

---- ----
a b
(1 row(s) affected)


Any Ideas?

Thanks in advance.

Aaron

View 2 Replies View Related

Queried Parameter Based On A Queried Parameter

Nov 9, 2007

FOA, I am using the webservices of the RS2005.

I created a report with same parameters. Two of them a based on a query. The first query contains a simple select * from ... the result will be stored in the parameter @param1. The second parameter contains select * from ... where ID = @param1. So far...
If a pass the reportparameters of the report i will get a valuelist for the first query, but nothing for the second one.

How can I manage that the second parameters also gets a valuelist?

regs

View 5 Replies View Related

Knowing What Has Been Queried???

Sep 16, 2006

Thank you for your help.

I run a website which uses SQL 2000 and VB ASP. I would like to add a section to the site which posts the most popular data being queried from my SQL server. I'm sure this is possible, but I don't know where to start. Please let me know if any of you need specifics regarding my data and set up.

Thanks again for the help! :)

JAC

View 6 Replies View Related

Show The Name Of The DB Being Queried

Jan 6, 2007

Hello. I work on a proprietary software package at work that has a SQL query engine. I'm able to query tables that I know exist but I would like to know what other tables are there in the db. Unfortunately I don't know the name of the database. Is there a SQL statement that will show me the name of the database that I'm running my query on?

Thanks for your help.

View 2 Replies View Related

Storing A Value, Vs Computing A Value

Sep 5, 2007

Hi,

I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.

We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".

I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?

Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.

Thanks for any input !

Mike123

View 3 Replies View Related

Grid Computing

Jul 20, 2005

Has anyone here heard or come across an article or write up about GridComputing in SQL Server 2000?Bharathihttp://www.vkinfotek.com

View 2 Replies View Related

Non-queried Parameters

Oct 5, 2007



Quick question guys, I remember running into this before but can't remember if I was able to complete it or not.
What is the syntax format in the 'Report Parameters' dialog box selecting 'Non-queried' in the "value " field I want to select multiple value parameters ie 001, 002, 003, 004 for one "label" Every combination I tried it's not taking. Thanks in advance

View 8 Replies View Related

How To Sent Queried Data To Variables

Jan 14, 2008

I have a problem about calculating data from the table. I want to sent a row to the local variables and calculate them by C#. Thank you in advance for every suggestion.

View 3 Replies View Related

Querying A Queried Result...

Jan 28, 2005

Hi, is there such a thing as querying a queried result?

Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B

If there is, could you kindly direct me to a website with the appropiate article on this topic?



Thanks,
-Gabian-

View 2 Replies View Related

Computing 'SUM' On 'DATETIME' Datatype

Jan 5, 2006

hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.

View 5 Replies View Related

Computing Business Hours

Mar 16, 2004

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')


Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advance

View 13 Replies View Related

Computing Difference Between Two Rows

Jul 6, 2013

I encountered a tricky problem. The original data, say, table_o, is shown below:

IdsStatusDate
ID1402-May-13
ID1310-May-13
ID1216-May-13
ID1120-May-13
ID2308-May-13
ID2210-May-13
ID2119-May-13

The final resulting table, e.g., table_f, is:

Ids4->3 3->2 2->1
ID18 6 4
ID2NULL 2 9

The values in the final table are the days used by each ID transferring from status i to status i-1. E.g., ID uses 8 days (10-May-13 - 2-May-13) to go to status 3 from status 4.

It is hard for me to come up with a table like the final table, although I know that the difference between two adjacent rows can be computed by using self-join and timediff().

View 3 Replies View Related

Computing New Variable From Other Variables

Sep 6, 2007

Dear All.
I'm a fairly new SQL programmer so apologies if this is a silly question.

I'm trying to create a new column/variable from 3 other variables where the new column = column 1 unless column 1 is blank, then = column 2, unless column 2 is blank, then = column 3.

But I don't know where in my query to begin building this. Should I build it in a subquery? Thanks in advance for any replies.

View 2 Replies View Related

Computing The Sum Of Multiple Columns

May 17, 2008

Hi,

I want to create a view where I can calculate the sum of a couple of bit value columns,
aswell as keeping track of the total number of bits set to true.

Here is an example:





dbo.Band


BandID int
Name nvarchar(50)
Country nvarchar(50)




dbo.Record


ID int
Name nvarchar(50)
BandID int
Label nvarchar(50)
InProduction bit
InSkodne bit
From these tables I created this view:





dbo.TestView


SELECT dbo.Band.Name, dbo.Band.Country, dbo.Record.Name AS Recordname, dbo.Record.Label, CONVERT(int, dbo.Record.InProduction) AS InProduction,
CONVERT(int, dbo.Record.InSkodne) AS InSkodne, CONVERT(int, dbo.Record.InProduction) + CONVERT(int, dbo.Record.InSkodne) AS Total
FROM dbo.Band INNER JOIN
dbo.Record ON dbo.Band.BandID = dbo.Record.BandID
I use the convert function to be able to use SUM() across my bit columns, which works fine. Problems is I´m not sure that the way I´m creating the TOTAL column is the best way to go. Any other ideas?
I´m having some problems using this view and the TOTAL column in particular when referencing this view from applications outside SQL Server...

View 9 Replies View Related

Computing Values From Two Tables

May 11, 2008

Hello all!

I´m currently devoloping an application where users can register errors related to recieved purchase orders.
I store these values in i table where the purchase order id i PK, and the possible errors that
can exist are stored as bit.

Now I want to be able to put a price on these errors.
I´m thinking about adding another table, containing all possible errors as columns, and then storing the cost of each error as an integer, and probably also a datetime for keeping track of when the costs was last updated.

I´m pretty sure this problem has been solved alot of times before, so I don´t wanna do something stupid here :-)

I´m also wondering about how it would be best to show the computed values?
Should I use a view for this?

For example:

SELECT (dbo.Orders.QuantityError * dbo.Costs.QuantityError )
FROM dbo.Costs CROSS JOIN
dbo.Record

assuming now that the Costs table only contains one row.

Is this the right way to go, or can you guys give me hints to a better solution?

Regards

Daniel

View 4 Replies View Related

Computing Full Record Row Size

Dec 19, 2001

Is there a quick easy way to calculate rowsize for a record in a table that has data in each column?

View 5 Replies View Related

Problems With Computing A Primary Key Column

Sep 13, 2006

hi !

Just started playing around with SQL 2000 and I createda sample table called 'actor' which has 4 columns

1. actorID
(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3))
2. actorFirst
varchar(20) NOT NULL
3. actorLast
varchar(20) NOT NULL
4. actorName
(formula = [actorFirst] + ' ' + [actorLast]

Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....

I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....

What am I doing wrong ? Please help ....

View 5 Replies View Related

Is Oracle 10g Really Oriented For Grid Computing?

May 8, 2007

Hello all, I known this is a SQL Server forum, but some people maybe have worked with oracle and can help me.



Microsoft's article "http://www.microsoft.com/sql/prodinfo/compare/oracle/mythandreality.mspx :"

reads as follows:



"Oracle's purported Grid enablement in 10g is based on its Oracle Real Application Clusters (RAC) technology that is no more than a local cluster. RAC is a local cluster of computers with no geographic distribution capabilities. This marketing campaign relabeled existing features to exploit current industry trends. "



My question is how can I support the above paragraph? I would like to known more reasons about why oracle grid is a local cluster instead of grid computring oriented.



Thanks in advance.

Hernán.

View 1 Replies View Related

Adjusting Column Width In Queried Dataset

Apr 17, 2007



I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.



Thanks in advance.



Mark.

View 3 Replies View Related

Transact SQL :: Computing Column Formatted To Decimal (12, 2)

Oct 19, 2015

I have the following a computing column

(isnull(TotalProductSaleCost,0) * 7) / 100

I would like the output to be formatted to decimal (12, 2) not sure how to achieve this?

View 4 Replies View Related

Monthly Parameter Expressions [Formerly:Queried Parameters]

Jun 18, 2007

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!



SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

View 6 Replies View Related

Computing And Assigning A Value To A Textbox From Other Data Regions

May 18, 2007

I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).



somethig like.... txtTotal.Value = FormatCurrency(txtSalesTotal.Value) - txtDiscount.Value));



Any ideas??



DNG.

View 6 Replies View Related

Best Practice For Formulating A Default Value For A Queried Parameters

Jun 6, 2007



In general, what is the most appropriate way to specify a "data driven" default for a report parameter. Seems to me like options are:



1. construct an expression which operates on one of the existing report datasets (possibly the same data set used to populate the defaults dropdown)

2. construct a new dataset which returns a single value which is the desired default



what's the "normal" approach?



For example, I have an MDX dataset which contains the list of dates for which data is available. There is generally some data available for today, but normally the user is interested in the most recent day with data prior to today.



So which would be the better option out of the two above (or a third option if I'm missing something!) to tell my parameter to set itself to:



"The most recent day prior to today for which data exists".

View 3 Replies View Related

Computing Basic Totals In A Proportional Line Graph

Apr 22, 2008

Hello,I am in the process of making a very simple stats page that will show us how many tasks we've completed.  Here is what I have so far:     Here is the SQL that makes it work:  SelectCommand="SELECT                            Count(TicketID),                           Category                                               FROM                              Tickets                                               GROUP BY                              Category                                                ORDER BY                                Count(TicketID)                             DESC  ">My problem is with how the totals seem to go on forever.  Instead of being in proportion to each other as a percentage of a the total amount of tickets.. they just increase in size with each additonal entry.  Can someone help me restructure this so that I can calculate the totals individually and as a whole and then apply the totals to create a proportional bar graph?Thank you greatly for your help,Mark 

View 2 Replies View Related

Multi-value, Non-queried, Report Parameter Expression Problem

Nov 30, 2007

I need to use a non-queried report parameter to filter a dataset for a report.

The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.

The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.

Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."

Which sucks.

Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.

I've tried eveything, researched everywhere online and I can't find any guidance anywhere.

View 10 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

Default Non-queried Report Parameter Not Updated When Project Is Deployed

Feb 22, 2007

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked
Available values = "From Query"
Dataset = [create a dataset that returns a table w/ a Id and Description column]
Value field = [the Id field from the table]
Label field=[the Description column from the table]
Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:


SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

View 8 Replies View Related

Transact SQL :: Sending Auto Email With Queried Data (On INSERT And On Month Ends)

Oct 22, 2015

I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file 

I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.

USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]

[Code] ....

View 8 Replies View Related

Differences Between Mined Data And Queried Data : Association Rule

Sep 22, 2006

Dear all,

I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.

The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.

I queried the table with this query

SELECT DISTINCT calling_number
FROM call_records
WHERE called_number = '07786000815'
AND target_operator = 'INDOSAT';

It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?

Thank you,

Bernaridho

View 8 Replies View Related

Computing The CPU Usage ,memory Usage For An Inserted Record

Nov 2, 2007




I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .

Can anybody help me with this?


View 6 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved