Gather Aggregate Data From Two Tables

Jun 13, 2006

I have three tables:

1) Contract - columns ContractNo and ContractName

2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value

3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost



How do I write a SELECT statement to produce rows containing:

ContractNo
ContractName
Sum of Value for ContractNo between @FromDate and @ToDate
Sum of Cost for ContractNo between @FromDate and @ToDate

Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.

I've written something like:

SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost

FROM Contract CT

INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo

INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo

WHERE (ST.InvoiceDate BETWEEN @FromDate AND @ToDate) AND (CS.TransactionDate BETWEEN @FromDate AND @ToDate)

GROUP BY CT.ContractNo, CT.ContractName

The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?

View 1 Replies


ADVERTISEMENT

Aggregate Data In Dimension Tables?

Jan 30, 2008

I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.

influent

View 1 Replies View Related

Gather Meta Data

Jul 23, 2005

Hi,I would like to prepare a data dictionary for my database (northwind).I have framed the below SQLSELECT'NAME ' = a.name,'DESCRIPTION' = b.value,'Type ' = type_name(a.xusertype),' ' AS 'Values','NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,' ' AS 'PK',' ' AS 'FK'FROMsyscolumns a,sysproperties bWHEREa.id = 2073058421 AND --- Customers Tablea.number = 0 ANDb.id = a.id ANDb.smallid = a.colidORDER BY a.colidand the output would be:NAME DESCRIPTION Type Values NULL PK FK-------------- --------------- ---------- ------ ----- ---- ----CustomerID Customer ID ncharCompanyName Company Name nvarcharContactName Contact Name nvarchar XContactTitle Contact Title nvarchar XAddress Address nvarchar XCity City nvarchar XRegion Region nvarchar XPostalCode Postal Code nvarchar XCountry Country nvarchar XPhone Phone # nvarchar XFax Fax # nvarchar XPK and FK is where I need to print whether the column is Primary Key orForeign Key.If CustomerId is defined as Primary Key then PK should have X printed.Thats the objective.How will I accomplish this ?Thanks in advance,Anu

View 3 Replies View Related

Best Way To Gather Large Quantities Of Data

Mar 30, 2008

Hi all,

I have a table which basically stores multiple users' responses to a questionnaire. I want to calculate certain statistics on this data (for example: how many users selected a specific answer to a question). If there are many questions and possible answers, then this can get really inefficient. I was wondering what would be the best way to go about doing this.

Currently, I was thinking of using what I believe are called crosstabs:


Code:

SELECT (SELECT COUNT(*) FROM tableName WHERE Q1answer='value1'), (SELECT COUNT(*) FROM tableName WHERE Q1answer='value2'), (SELECT COUNT(*) FROM tableName WHERE Q2answer='value1'), etc...



Is this the best way to go about this or is this really inefficient?

View 2 Replies View Related

Aggregate Functions In Multiple Tables

Jan 12, 2007

Hi, need help in this statement here. I have three tables here, i.e. Sales, SalesItem, & SalesPmt. I want to display a grid that shows the Total Bill and Total Payment amounts.
My try is like this: SELECT SalesNo, SUM(Price*Qty) AS TotalBill, SUM(Payment) AS TotalPayment FROM ... GROUP BY....
No syntax error or whatever found, but the result of the total amounts is incorrect.
Say the data of the respective table below:
SalesItem



No
Qty
Price

1
1
5.00

2
2
12.00

3
4
3.50
SalesPayment



No
Amount

1
10.00

2
5.00
But the result I get from the above query is:



TotalBill
TotalPayment

86.00
45.00
Total Bill should be 43.00 and Total Payment should be 15.00.
Apparently the problem is due to the fact that I and querying on multiple tables. The correct total payment amount was multiplied by the number of rows of sales items (15.00 x 3), while the correct total bill amount was multiplied by the number of rows of sale payments (43.00 x 2).
So, what is the better way of writing this query?

View 5 Replies View Related

Aggregate SUM From Multiple Tables And Grouping

Oct 5, 2012

I have several tables that I need to summarize data from two tables based upon a dates passed in and group that data. I have attached my table layout, some sample data, and how I would like the results to look.

View 2 Replies View Related

View With Aggregate Function And Joined Tables

Mar 2, 2007

I have created the following view:
 
Create view vwOrderItemTotal2
AS
SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
 
When I present my data in a GridView, it works fine.  For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
 
26 Milk
 
Now I need to filter my data by OrderDate and Zipcode.  I created this new view:
 
Create view vwOrderItemTotal5
AS
SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems]
FROM Orders INNER JOIN OrderItems
ON Orders.OrderID = OrderItems.fkOrderID
GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
 
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
 
1 Milk
5 Milk
6 Milk
6 Milk
3 Milk
1 Milk
4 Milk
 
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view.  Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet). 
I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause.  Thanks for any help provided in solving this.
 

View 4 Replies View Related

SQL Server 2012 :: Using CTE To Aggregate Separate Tables?

Aug 20, 2014

I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.

My end result should look like the below example.

Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0

So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.

Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.

--create the temp table
Create table #SalesLine
(
Novarchar (50) not null
, LocationCodevarchar (50) not null
, QtyCommitint not null
)
create table #ItemLedgerEntry

[code]....

I am close to the desired results but can't find a way.

View 9 Replies View Related

How To Write Aggregate Functions For Tables And Lists

Apr 25, 2008

How to write Aggregate functions for tables and lists as If I can write them many problems in my reports will be solved. I tried writng it in the filters but I got an error saying Aggregate functions are not allowed for tables and lists. Can any one help me in this regard?????

View 4 Replies View Related

How To Gather SELECT Statistics?

May 17, 2004

Hi folks

I wonder if anyone has any pointers on how to gather statistics for SELECT queries? For instance, say 10 rows are returned by a query, is it possible to log which rows where returned?

TIA

Jarud

View 3 Replies View Related

Automate Process To Gather Server Name

Jun 20, 2001

Can anyone direct me to either some code or reading, that talk about gather all my server information. What I want to do is gather all the server Used and Free space size. I'm know a little sql-dmo, and I think I can do it that way, but not sure.

View 1 Replies View Related

Gather Format And Store - Right Or Wrong

May 4, 2006

The IT group that I work with has the habit of gathering data,formatting (i.e. in reports) and then storing the same formated data inthe same database.I think the practice is wrong. I think the activity is fundamentallywrong because we are storing the exact same data in a database in twodifferent locations. Somehow I have the impression that database designis about "oneness".I believe that collecting the data and then storing summerized data forreporting into a data warehouse would be the right solution.I am getting flack for my viewpoint.Am I all washed up?

View 1 Replies View Related

SQL 2012 :: Configure Replication Which Will Gather Logs From Publication Once In A Day?

Jan 21, 2015

I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.

I need to configure a replication which will gather logs from the publication once in a day.

Is there any possibility?

View 1 Replies View Related

Show Detail Data On A Matrix Rather Than Aggregate Data

Dec 12, 2007



Hi all experts,

I have a result set like:





Project

Milestone

Info


Project1

M1

Info1


Project1

M1

Info2


Project1

M2

Info3


Project2

M1

Info4


Project2

M2

Info5


Project3

M1

Info6


I need to create a report like:







M1

M2


Project1

Info1
Info2

Info3


Project2

Info4

Info5


Project3

Info6




But while I use matrix to build this report, I got the result like:







M1

M2


Project1

Info1

Info3


Project2

Info4

Info5


Project3

Info6




The report will not show the multiple records on the row group "Project" like item "Info2".

After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:









M1

M2


Project1

RowNumer

Info1






RowNumer

Info1






RowNumer



Info3


Project2

RowNumer

Info4






RowNumer



Info5


Project3

RowNumer

Info6



It would be much appreciated if anyone could give me some hints to complete the report I need.

Thanks in advance,
Steve Wang 2007/12/12

View 5 Replies View Related

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.

Oct 19, 2007

Can any1 tell me why i am getting an error


SELECT DISTINCT

--p.voucher,

--p.amount,

p.siteID,

b.siteID,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers

FROM

BillingTotal b,

Payment p

--WHERE

-- s.sitename=@cmb1

--AND p.siteid = s.siteid

-- p.voucher = 0

-- p.voucher = 1

GROUP BY p.siteID,b.siteID



Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

View 8 Replies View Related

Analysis :: Calculated Member From Aggregate And Non-aggregate Factors

Sep 22, 2015

I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named

This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.

The first factor,  BillableLastYear is a number, aggregated (sum) from child units.

The second factor,  BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.

How can I calculate This_Year_Billable_Objective_Count for each unit?

\ able 1
SELECT [UnitKey]
      ,[UnitID]
      ,[UnitName]
      ,[Unit2Name]
      ,[Unit3Name]
      ,[Unit4Name]

[Code] .....

View 6 Replies View Related

SQL Query - Aggregate Data

Jul 23, 2005

I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?

View 2 Replies View Related

Aggregate Data Grouped Lists

Apr 15, 2008

Hello,

I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).

Now I want to aggregate some numbers of the table on each page (year) of the list.

The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.

Can anyone help?

Thanks a lot -

Markus

View 2 Replies View Related

Adding Columns Of Data In An Aggregate Query

Mar 14, 2001

Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.

Here's what I'm trying:

select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id

but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).

I'm new to SQL and could really use the help.

Thanks

View 1 Replies View Related

Aggregate Operation Cannot Take A Nvarchar Data Type

Oct 5, 2002

I recently upsized my acess 2000 db to SQL 2000, but I am now having errors . . .

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a nvarchar data type as an argument.
/data_count.asp, line 4


The code within the asp file:
<% dataSQL = "SELECT SUM(filesize) FROM DL where show=1"
Set datacount = my_Conn.Execute(dataSQL)
anBytes = datacount(0)
%>

Can someone point me to a solution?
Thanks!

View 3 Replies View Related

T-SQL (SS2K8) :: How To Aggregate Interval Data To Hourly Values

May 12, 2014

I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?

SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId

Current Output

MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000

View 7 Replies View Related

How To Generate Condition Based Aggregate/summary Data

Jun 14, 2006

Hi

I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns.

I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table.

What all the steps i need to create in SSIS to get this results ?

Thanks

Kumaran

View 2 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

Data Warehousing :: Copy Data From Staging Tables To Other Instance Master Tables?

Aug 14, 2015

I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?

View 3 Replies View Related

The Sum Or Average Aggregate Operation Cannot Take A Nvarchar Data Type As An Argument.

Feb 6, 2007

how to solve this problem ....
I want to select te sum of nvarchar column and this error always happen
 
what is the solution this is my query
 
"SELECT Datein AS Date,SUM(Durm) AS DurationMin, COUNT (*) AS 'Number of Hits' FROM Rayyan_Log WHERE (DNIS = '"+DropDownList1.SelectedItem.Text +"') AND (Datein between '"+TextBox2.Text+"' and '"+TextBox3.Text+"') GROUP BY Datein ORDER BY Datein"

View 2 Replies View Related

Aggregate Function In Chart Data Field When Connecting To Cube

Oct 11, 2007

SSRS/SSAS. I'm using aggregate function no problem in a Table, but when I try to use same function in data field of a chart I get no results. I know that "aggregate" has only been supported since SP2, is there a known problem with using it in charts?

Thanks
Richard

View 1 Replies View Related

Error Message: Report Item Expressions Can Only Refer To Fields Within The Current Data Set Scope Or, If Inside An Aggregate, T

May 14, 2007

Hi



I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:



[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.





Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.



Regards,

Karen



View 3 Replies View Related

How To Aggregate A Column That Was Itself An Aggregate

Jul 15, 2005

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

View 9 Replies View Related

How To Set User Who Can't Modify Any StoredProc And Data In Tables But Can Read Data In Tables And Any StoredProc ?

Jul 13, 2007

Please give me advise ครับ
 

View 1 Replies View Related

Exporting Data From Excel Tables To SQL Server Tables

Dec 9, 2007

Hi all,
 I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
 
How can it be done?
 
 
Thanks, Michael.

View 1 Replies View Related

Joining Tables - But Data Not Always In All Tables

Aug 18, 2007

I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.

My scripts are as follows:
Main View
SELECT
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER,
--TotalPayable
IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal
WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalPayable,
--NetPremium
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS NetPremium,
--IPT
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS IPT,
--Fee
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS Fee,
--TotalCommission
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalCommission

FROM
ACCOUNTS_CLIENT_TRAN_LINK
INNER JOIN ACCOUNTS_TRANSACTION
ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID
INNER JOIN ACCOUNTS_TRAN_BREAKDOWN
ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID
INNER JOIN LIST_TRAN_BREAKDOWN_TYPE
ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID
INNER JOIN CUSTOMER_POLICY_DETAILS
ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND
CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID
INNER JOIN SYSTEM_INSURER
ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID
INNER JOIN SYSTEM_SCHEME_NAME
ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID
INNER JOIN CUSTOMER_INSURED_PARTY
ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND
ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID
WHERE
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'

GROUP BY
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
ACCOUNTS_TRANSACTION.Transaction_ID,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER

Add on View 1
CREATE VIEW TOPCARDPA AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'

Add on View 2
CREATE VIEW TOPCARDRESC AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'

Join Result Script
SELECT
TOPCARD.AUTHORISATIONUSER,
TOPCARD.AUTHORISATIONDATE,
TOPCARD.TRANSACTION_CODE_ID,
TOPCARD.SURNAME,
TOPCARD.INSURER_DEBUG,
TOPCARD.SCHEMENAME,
TOPCARD.POLICYNUMBER,
TOPCARD.TotalPayable,
TOPCARD.NetPremium,
TOPCARD.IPT,
TOPCARD.Fee,
TOPCARD.TotalCommission,
TOPCARDPA.SELECTED,
TOPCARDRESC.SELECTED
FROM
dbo.TOPCARD TOPCARD
INNER JOIN dbo.TOPCARDPA TOPCARDPA
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND
TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID
INNER JOIN dbo.TOPCARDRESC TOPCARDRESC
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID
AND
TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID

I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.

View 2 Replies View Related

Reporting Services :: Keep Multiple Data Tables Having Fixed Size Data In Same Page On Runtime?

Jul 31, 2015

I have a report where in I have a combination of matrix ,table data regions.

The problem what I am facing is that the data tables don't remain fixed in their position and they tend to move down.

E.g. table 1 and table 2  are on the same page in design time side by side (right and left)however during the runtime the table1 is pushed down and table2 is at its position .

Now how can I keep them all fixed in their same position. Most of the tables have fixed size rows  and some who have high size of rows have been put at the end . What settings we can set?

View 6 Replies View Related

Archiving Data From Some Tables - Timestamp Data Type?

Aug 21, 2015

I'm working on archiving data from some tables. I've duplicated the data structure, with the exception of not including the IDENTITY specifier on INT columns, so that the archive table will keep the value that was generated in the original table. This is all going well, until I tried to copy the data over where the column is specified as a timestamp data type. I've looked this up and found a couple of things. First, documentation for SQL 2000 says,

Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

And then documentation for the soon to be released SQL 2016 on the rowversion data type says,

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

and

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.

OK, I've read the descriptions, but I don't get it. Why have a timestamp/rowversion data type?

View 9 Replies View Related







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