Finding The Difference Between Two Column From Two Different Datasets

Nov 21, 2007

Hi

I always seem to come back to dataset issues. Nonetheless, here is my problem:

I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.

Pretty simple so far.

I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.

The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.

The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.

Anyone have any ideas as to how I could achieve this?

View 1 Replies


ADVERTISEMENT

Finding Time Difference

Jan 15, 2003

I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?

View 2 Replies View Related

Finding Difference Between Two Date Columns And Deleting It

Apr 14, 2008

I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx 

View 2 Replies View Related

One Column Missing From Datasets Even After Refresh?

Sep 20, 2007

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

View 3 Replies View Related

Join Two Datasets From A Similar Column

Apr 23, 2008

Hi, I was wondering wich is teh easiest way to solve this:


I have one data set that comes from a sql query with many columns.

I have another data set that comes from a txt file that I am reading.

Both data set have one column in common (for example ID).

In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.

So, which is the easiest way to solve this?
Thanks!

View 6 Replies View Related

Reporting Services :: Tabular Report Datasets - Grouping On Column

Jun 23, 2015

I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .

I would like to create a tabular  report  datasets on the report like shown below with grouping on col1

like
col1  some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------

Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.

View 2 Replies View Related

Reporting Services :: Count Of Lookup Expression Column In Report With Multiple Datasets?

Sep 27, 2015

My report has two data sets that hold inventory from two different departments.    

ds_DeptA and ds_DeptB

I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine.  Typically means DeptB does not have the record yet.   I need to count these empty (null) feilds and populate it in a Text box outside of the table.
 
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset. 

table: ds_DeptA
fields:
ID
Name 
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")

View 3 Replies View Related

Finding A Column ??????

May 2, 2008

Hi,
How to find if a column exists in Database Table ?

thanx in advance

View 6 Replies View Related

Finding Tables Having A Particular Column

May 19, 2008

Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?

Cheers,
Mathi
India.

View 6 Replies View Related

Finding Column Name Associated With View

Apr 30, 2007

I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.

TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --

___________________________________________________
--just example

go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee

go

go
create view v_PersonalD
as
select SSN from Employee

View 5 Replies View Related

Finding A Column In A Database

Jul 23, 2005

Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno

View 2 Replies View Related

Finding All References To A Column

Jul 20, 2005

I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?

View 1 Replies View Related

Difference Between Dates In The Same Column

Jun 23, 2007

i was working on a appication and just got stuck with this.
I have a column on a table which is a date column and i need to get the days differenct and copy it in another column
e.g

date days between
27-02-2005 1
28-02-2005 0
28-02-2005 0
28-02-2005 1
01-03-2005 0
01-03-2005

View 4 Replies View Related

Error Finding Identity Column

Jan 13, 2008

I'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus,   C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale  FROM syscolumns C   JOIN sys.tables O ON C.id = O.object_id  JOIN systypes   U ON C.xusertype = U.xusertype    WHERE O.name =  'Fred' AND C.status = 128  ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus,   C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale  FROM syscolumns C   JOIN sys.tables O ON C.id = O.object_id  JOIN systypes   U ON C.xusertype = U.xusertype    WHERE O.name =  'Products' AND C.status = 128  ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?

View 3 Replies View Related

Finding Max Date And Populate New Column

Jan 7, 2014

Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"

Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011

Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105

My query is

select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc

View 3 Replies View Related

Finding Dashes/hyphens In A Column.

Jul 23, 2005

Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard

View 5 Replies View Related

Data Access :: Finding Min Value Corresponding To Other Column

Aug 12, 2015

Query :-

 SELECT
 MAX(TIME_VAL)  AS  Duration1 ,
 MIN(TIME_VAL)  AS  Duration2 , 
 --  VALUE OF LATITUDE CORRSPONDING TO  MAX(TIME_VAL)
 --  VALUE OF LATITUDE CORRSPONDING TO  MIN(TIME_VAL)
  (Select LONGITUDE from (select LONGITUDE ,  row_number()
   
[Code] ....

I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.

View 3 Replies View Related

Finding Max/Min Value In A Column When Row Data Repeat

Feb 13, 2008



I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.

Thank you for your help






SELECT DISTINCT mx.stu_num,

CASE WHEN er.STU_NUM = ep.STU_NUM

THEN er.enterdater

ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM

THEN ea.enterdatea

ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM

THEN ep.enterdatep

ELSE eb.enterdateb

END

END

END AS entrydate


FROM dbo.mx AS mx LEFT OUTER JOIN

dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN

dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN

dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN

dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num

View 1 Replies View Related

Finding UNIQUE Values In Only One Column

Jan 28, 2008

I am using Access 2007 and I have 10 columns of data.

I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.

I have used this

SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];

But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.

I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.

I know that I am missing something basic but well, I can't figure it out.

Thanks

Bob

View 4 Replies View Related

Finding Matching Data In One Column That Does Not Match In Another

Nov 18, 2005

I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.

I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.

I have tried this, but it does not seem to want to work.


Code:



Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)




I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.

These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P

Any ideas?

View 2 Replies View Related

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Rows From CSV Column With CSV Parameter

Nov 2, 2015

I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.

I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema

CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

[code]....

View 9 Replies View Related

Finding Stored Procs That Ref/Update A Column

Jul 23, 2005

Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.

View 5 Replies View Related

Finding The Highest Or Largest Number In A Column Problem

Dec 19, 2006

Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list. 
 When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
 The largest number comes back as CA997
 
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
 The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
 The largest comes back as CA1278
What am I doing wrong?

View 3 Replies View Related

Difference Between 2 Columns Key And A Third-party Column With Identity

Jul 22, 2007

Hi all,



Just a silly wonder I had a few days ago:



I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).



Which one should I choose and why? Would i gain anything by choosing one over the other?



Thanks in advance,

iTaY.

View 6 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

TSQL Script For Finding Column Names In Stored Procedures

Feb 23, 1999

Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?

View 1 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2012 :: New Column Calculation Based On Running Difference

Aug 15, 2014

New column calculation

CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,

[Code] .....

---OUTPUT
--=======

INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another

Mar 20, 2015

I have a query which provides the below result set:

1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013

I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.

1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80

[code]...

View 5 Replies View Related

SQL Server 2008 :: Get Time Difference Of Char Datatype Column Value

May 29, 2015

How can I get time difference of the following record :

STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM

I have tried it by below query,

SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER

but it gives following error message

[color=red]Operand data type time is invalid for subtract operator.[/color]

View 6 Replies View Related

Reporting Services :: Calculating Difference Between Columns Within Column Group

Jun 15, 2015

Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.

Sample report:

View 4 Replies View Related







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