Hi:
I have three tables in my db. One is clients, other calls, and other visits.
I want to get all the calls and visits of all clients in my db every row in each table separate in one row in the results table.
How can I do it?
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.
The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.
My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.
I'm very new at using SQL and my knowledge of the query syntax is very basic.
Thanks for any help in advance!
~Lacy
I need to run a query which will pull data from two tables and appendit as one when it displays the result. The data are in two tables. Butthe result set will be identical in terms of number of columns. I wantto display it one set below the other.This is for pay history. From 2003 we have a new payroll system. Till2002 we used to have a different system. But I need to run a querywhich will pull the data for an employee for the last one year. So theinformation is spread out between these two tables. Both these tablesare in SQL Server databases.I want to write a Stored Procedure. I can use Shape/Append but I thinkit doesnt work on QA/Stored Proc. It needs an OLEDB.How can I write the query. I dont want to use temporary tables and doinserts.ThanksGIRISH
View 1 Replies View RelatedDear FriendsI need to get the data in the required format. Please help----------------------Table TripSheet----------------------Id,TSNo.,JourneyDate(ddmmyy),CustName,RateType,VehicleNo., BillId, journey hours, journey kmDATA
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------Table BillTripSheet-----------------------------Id,BillId,VehicleNo., TotalJourneyHours,TotalJourneyKM,SlabApplied, MinAmt, ExtAmtDATA
1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 502, 234, Vehi99, 13hrs, 250km, SlabA, 500, 50
I need to display data to be displayed in the following format in report(grouping based on vehicle no.)
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 50---------------------------------------------------------------------------------------------------------
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------2, 234, Vehi99, 13hrs, 250km, SlabA, 500, 50----------------------------------------------------------------------------------------------------------
The Calculation logic is lengthly and the number of records involved is huge.also, only the manager has authority to generate the Bill and rest of the team can view the bill report
So i cannot do the calculations while fetching the records, Bill has to be generated and stored. How do i write the query to fetch the data from the two tables "TripSheet" and "BillTripSheet" ? Please Help
ReagardsSara
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
View 4 Replies View RelatedI want to retrieve the data from table "Document" and i need to check the below condition using 3 tables.
Document.ID=Project.ID=Group.ID
Here Project and Group is an another table.
Query : Select Document.Name from Document, Group, Project where
Document.ID = Group.ID and Document.ID= Project.ID.
is this right a way?
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------
I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),
[Code] ....
I want to knew the sql query to return differences between two tables from two different database.
View 3 Replies View RelatedI have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
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 RelatedHow I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View RelatedI have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
Please give me advise ครับ
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.
Greetings,
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Thanks in advance for your replies.
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.
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?
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?
Everytime I run the load packages that I have built into the MDS staging the tables, in MDS_Staging, get cleared down and then repopulated. When the MDS database gets new data it appends it and does not clear down the data that is already in the database and is there fore hold duplicate rows.
Is there a way of truncating the tables in the MDS database before populating again with the new data from the MDS_Staging database so as to not hold duplicate rows?
hi,I have two tables, t1 t2col1 col1 col2 1 1 0 2 1 0 3 1 1 4 2 0 5 2 0 6 3 1 7 3 1 8 4 1 9 4 1 10 4 1 11 4 1t2.col1 is the key from t1.col1I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"the result should be: 3, 4I try:select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...???.)any help?
View 8 Replies View RelatedHello all,
If I write this query :SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes the return is good.
But If I the query is :SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes I have an error "Invalid column name.
The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table.
How to make a good query with an aliased column without error ?
Thanks for reply.
Regards.
PAB.
I have two tables: 1 with data, the other 3 columns from the first table with rows filled with data that needs to be ommitted when a query is made on the first table.
For example, say I have a column named "meat" and there are rows in it with "beef" as the data. In the other table there is also a column with meat and beef as the data. I would need to query both tables to first get the data to be ommitted and then use that result to query the first table. I hope I'm making sense, any help would be appreciated
Hi
I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)
To get the data I need - I need to query three tables.
Stockitem - This has the column "itemid"
Stockitemmemo - This has the column "itemid" and "timeanddatecreated"
Binitem - This has the column "itemid" and "binname"
My results must be based on the following criteria......
All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.
I do have two questions based on the above....
1. Does it make sense what I need?
2. Is it possible
:confused:
Any help would be gratefully received.
Thanks
Simba
I want to get the list of tables which an SQL query is using. It could be through some query or parser written in jruby or R or any other language,any thrd party tool(freeware)or any scripts.
View 1 Replies View RelatedHi,
I know it is simple query but I am confused.
I need to make a query from 3 tables in the same database. There are no relations between these tables.
Here is result I need.
KE LE JE
15 775.5 398
18 192.23 399
Please help me.
Sep
I'm doing a query from two tables. the table "pr" and the table "prre" with the following data: pr.no, pr.nome, pr.data, pr.recibo, pr.ettsuj, pr.ettdesc, prre.cr pr.eliquido, prre.rqtt , prre.ervu, prre.ere
I'm doing the following query:
select cast(pr.no as varchar) as 'Numero',pr.nome as 'Nome',pr.data as 'Data',cast(pr.recibo as varchar) as 'Recibo',
cast(prre.rqtt as integer) as 'Qt Dias', prre.ervu as 'valor unitario',prre.ere as 'Total Subsidio',
pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido'
from pr
inner join prre on prre.prstamp=pr.prstamp
for this query is a list of salaries, with the fields, official number, expiration, name, date, receipt number, net total, the total gross, days subsidy amount, subsisidio.
I've got a problem with my query : [URL] ....
I want to say:
Pull all users whos status is New and they have at least one Completed delivery
The problem is, I am getting multiple delivery records form one patient, but I would like to see only newest one. So instead of this result:
2014-05-10 00:00:00.000CompletedReed BrewerNew830
2014-06-10 00:00:00.000CompletedReed BrewerNew830
2014-07-10 00:00:00.000CompletedReed BrewerNew830
2014-07-02 00:00:00.000CompletedColton DukeNew920
I would like to get this:
2014-07-10 00:00:00.000CompletedReed BrewerNew830
2014-07-02 00:00:00.000CompletedColton DukeNew920
I have tried to use Max , but it does not work ....
select
Max(delivery.deliveryDate),
delivery.deliveryStatus,
patient.userName,
patient.userStatus,
delivery.accountid
[Code] ....