Hi all,
I am a newbie to .NET and would appreciate all your valuble
suggestions. I have and issue were I am trying to import data from a
few selected columns MS Access and a couple of columns in SQL
Server Table Y and trying to populate another table X . Both
tables X and Y are in the same Database . I am wondering if I could
design a custom package for this task.
Ananth
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
I have a text file which contains the data that has to be inserted into multiple tables.The columnames of table 1 form the H1 follwed by Details D1,D1,D1...
The column names of table two form the H2 followed by details D2,D2,D2 so on and similarly for Table 3.
Am using a link server to the file directory and schema.ini which defines the column names fofr the text file
Is there any way of defining column names for more than one table through the schema.ini? or is there any other way through I can parse the text file contents to multiple tables?
Sample text file:
H1,JobDate,JobNumber,FileName,
D1,13/02/2008,asdf123,text1.txt
D1,13/02/2008,asdf123,text2.txt
D1,13/02/2008,asdf123,text3.txt
H2,PagesUsed,PagesPrinted,Pages emailed
D2,10,10,9
D2,1000,100,99
D2,50,22,93
Schema.Ini - defined for the first table
[LogConfig.txt]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeaders=true
Col1=JobDateText Width 20
Col2=JobNumberText Width 20
Col3=FileName Text Width 100
Hoe do i define the column names for the second table. All these contents are in a single text file and need to be parsed only thru sql.
Any help/suggestions are welcome..
Thanks a lot for taking time to read this.
I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....
I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.
The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.
The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.
It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
Is there a way to import multiple csv files from a directory into sql2005? The situation I have right now is that I have a folder withmultiple csv files that i need to import into sql 2005. I can do itwith the import wizard but it takes to long. The files will be updatedmonthly. The first row in the files contains all the header informationwhich may change monthy. What I am looking to do is import all of thesecsv into tables. One csv file into for one table. Ideally I would liketo use the name of the csv file as the name of the table. Any bump inthe right direction would be apprecieted
View 1 Replies View RelatedHi,
I am using DTS in SQL server2k
What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.
eg.
CSV record
(ID, param1, param2, param3)
ParentTable(ID as PK)
Param1Table(ID as FK in ParentTable, param1)
Param2Table(ID as FK in ParentTable, param2)
Param3Table(ID as FK in ParentTable, param3)
Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.
Thanks and regards,
Henry
We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.
Thank you very much,
Regards,
Srinivas Raveendra
We just upgraded from SQL Server 2000 to 2005. In the past, when I ran the import/export wizard to copy multiple tables from one database to another with SQL Server 2000, I had no problem. Now when I used the import/export wizard to copy multiple tables with SQL Server 2005, I kept getting an error. For example, when copied three tables, the first table might be copied fine and I got an error with the second table and the whole thing stop. Sometimes I could copy two tables. However, when I ran the import/export wizard to copy each table one at a time, it worked.
The error that I got was "Cannot insert duplicate key in object..." I selected the options to "Delete rows in existing destination tables", and "Enable identity insert". What am I doing wrong?
R. Jiwungkul
I 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.
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
Hi, I am trying to import an XML file to SQL tables using SSIS, Data Flow Task --> XML Source --> OLEDB destination.
I have both the XML file and its xsd file.
The extraction of the XML file output 5 tables (mc, r, i, bc and c, have relationship among them). My problem is that one of the tables is empty (table mc). I tried to add a "root" node to the XML file (the xsd file was modified too), and all 5 tables were populated correctly. Is there any other way to fix this problem without manually changing the XML file?
The XML file looks like this:
<mc MajCmd="Atlantic">
<r Region="East">
<i Installation_UIC="M00146" Installation_Name="Cherry Point">
<bc BC="111" BC_Title="Airways">
<c Restoration_Cost_C3="0.0000" Comment_Type="" Override_C_Rating="" Comment="" />
</bc>
</i>
</r>
</mc>
The xsd file looks like this:
<?xml version="1.0" encoding="utf-8"?>
<xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
<xs:element name="mc">
<xs:complexType>
<xsequence>
<xs:element name="r" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="i" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="bc" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="c" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
</xs:complexType>
</xs:element>
..........
</xsequence>
<xs:attribute name="BC" type="xstring" />
<xs:attribute name="BC_Title" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Installation_UIC" type="xstring" />
<xs:attribute name="Installation_Name" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Region" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="MajCmd" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="mc" />
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
Thanks!
ChaInn
at first let me specify my requirement.
a) i have an excel file with more than one sheets
b) i want to import data from that excel file into sqlserver 2000 using asp.net & c#
NOW
i need a program that automatically realize total sheets of excel file
AND
insert into seperate table
please help me
Hi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
Hi All,
I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis
IBM DB2 UDB for isereis IBMDA400 OLE DB Provider
Regds,
Anu
I'm pulling data from XML into tables, but I'm unsure how to link the data after it's imported. This example has names and tasks, and I can pull the data into two tables, but I can't find any way to link the task to the appropriate person. My person and task tables populate without issue, but there's nothing I can find to link the rows together. So in this example Test 1 would go to the first two Tasks and Test 2 would go to the second two work items.
DECLARE @XML TABLE (XMLData XML);
DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));
DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));
INSERT INTO @XML SELECT '
<process>
<header>
<Person><Name>Test1</Name><Address>123 main street</Address></Person>
[Code] .....
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 have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!).
Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task.
I have 3 tables
say table1 , table2 and table3
table1 have three columns say name,lastname, id2
table2 have 3 columns say details,id2,id3
table3 have 3 columns say payment,id3 ,session
I have to get data as
select * from table1
then
select * from table2 where id2=table1.id
then
select * from table3 where id3=table2.id3 and session=something say 4
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it?
i think; it should be implemented through transaction statements but not much aware about these
hi. can anyone help me, please.
i am using vwd2005 express edition and sql server 2005 express. i want to insert data in multiple tables at once. the tales are linked to each other through primary key and foreign keys. for example i have one table with a primary key. when i add data to it, i have to retrieve the id of the newly inserted record and then introduce this id in another table as a foreign key. i have 10 tables linked in this way, and only one form to add data to the database.
can you help me, please? i'd be very greatful. thanks.
I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.Thanks!Vic
View 7 Replies View RelatedI have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. This is the query that i used
SELECT date(readingtime), max(energy)-min(energy)as total FROM lights_1 WHERE readingtime between 20140407000000 and 20140409235959 Group by date(readingtime) LIMIT 0,30;
This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!
Hi there!!! Trying to figure something out, I have searched this forum but no answer to my dilemma.
I have three tables on a database that I have to insert new data and update the old one. The structure of the tables is like this:
Table1
custid int primary key,identity
fname varchar(30)
lname varchar(30)
Table2
fileid int primary key, identity
ssn varchar(11)
custid int foreign key
Table3
statusid int primary key,identity
title varchar(18)
fileid int foreign key
This is very general but that's the idea. Now I receive a text file with the necessary info, I've already parse and break down the file into the correct fields.
Now for my question, how I insert the relevant data onto the tables from this one parsed file? and also how I go about inserting the primary keys from the different tables onto the foreign keys of each tables?
I tried relationships and key indexes, but it just spew a bunch of errors, that I'm investigating.
Any ideas, pointers, tuts, something I'm missing?
BTW I'm using sql 2005 express and VB.net
Thanks in advance for any help.
_José
Hi there,I have tables with such structuretransaction_YYMM(idx,date,company_id,value)where YYMM stands for 2digits year and monthI want to define query (maybe view, procedure):select * from [???] where date>='2007-01-01' and date<='2007-04-30'which will grab data fromtransaction_0701transaction_0702transaction_0703transaction_0704and return all as onebest regardsRafal
View 3 Replies View RelatedHi,
I have a number of related tables:
RGData is related to RGCrossReference
RCPPositionData is related to RCPCrossReference
RGCrossReference is also related to RCPCrossReference.
The data is returned correctly from these tables.
However, I also want to return data from another table - RComments.
How do I do this?
RComments is related to either RGData or RCPPositionData only.
Thanks.
Code Snippet
SELECT cm.CommentImage AS ViewComment, gd.PositionID AS GPositionID, cd.UniquePositionID AS CPPositionID
FROM RGData gd
INNER JOIN
RGCrossReference g
ON g.GPositionID = gd.PositionID
INNER JOIN
RCPCrossReference c
ON c.GMatchID = g.GMatchID
INNER JOIN
RCPPositionData cd
ON cd.UniquePositionID = c.CPPositionID
left outer JOIN
RComments cm
ON ((cm.CPPositionID = cd.UniquePositionID) or (cm.GPositionID = gd.PositionID))
AND cm.CommentsDate =
(SELECT MAX(CommentsDate) AS Expr1
FROM RComments
WHERE (GPositionID = g.GPositionID))
WHERE
(cd.Quantity != gd.Quantity
OR
cd.Currency != gd.Currency)
AND g.ForcedMatch = 'no';
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
Can someone show me the command string required to write data out to 2 or 3 tables at once? How about how to write 2 entries at once? Write now my solution writes 1 record to a table and then I write somewhere around 40-200 records to a second child . Write now I'm writing like this:Open --> Add 1 record in table #1 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeand I'm just wondering if there is a better approach, such as writing all the data at once. Something like:Open --> Add 1 record in table #1 --> Add 5 records in table #2 --> close
View 1 Replies View RelatedHello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View 5 Replies View RelatedI have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.
I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.
All,
i am looking for a query which fetches data from column 'managerid' which spread across 20 tables,
Managerid colums is spread across 20 tables and i need only managerid data from all the tables available, data for managerid column will be different in each table.
can anyone help me regarding the same.
I've been struggling with this for about 2 weeks now and can't seem to get any further.I have two tables: orders and orders_extended. They can be joined by the common orderid field (example SELECT * FROM orders JOIN orders_extended ON orders.order = orders_extended.orderid WHERE 1=1)I need to create a report that sums the following fields:
SELECT
CONVERT(VARCHAR(12), orderdate, 101) As orderdate
, COALESCE (
CASE WHEN orders_extended.productprice < 0 THEN 'DISCOUNT' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%AB%' THEN 'PRODUCTGROUPAB' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%CD%' THEN 'PRODUCTGROUPCD' ELSE NULL END
[code]...
What I'm trying to accomplish is to get the total dolloar amount of sales for each day for each payment type on one line per productgroup.
Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q
Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001
Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011
I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011
May be some duplicate entry of same record is available in Table 1.