I inherited a table with this structure:
Value a Value b
x date a
x date b
x date c
y date d
z date e
z date e
z date f
Value a fields are one to many. The objective is to obtain the maximum date value for each unique a value.
I have a requirement to provide data in a denormalized form from normalized tables. Working in SSIS.
I have two tables: EmployeeCountry and Country.
EmployeeCountry
EmployeeId (PK)(FK)
CountryId(PK)(FK)
Country
CountryId (PK)
CountryName
There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
Employee
EmployeeId
CountryId1
CountryId2
CountryId3
I have a de-normalized table that I need to export to XML using For XML, but put all of the related rows under the same node.The table is alot more complicated than the example below, but for proof of concept purposes, i'll keep it really simple:
Campaign, Price
C1, 4.00
C1, 6.00
C1, 10.00
C2, 1.00
C2, 13.00
C3, 20.00
If I have a table of campaigns and prices, I would like to output it as XML like the following:
<Campaign name="C1">
<Price value="4.00"></Price>
<Price value="6.00"></Price>
<Price value="10.00"></Price>
</Campaign>
<Campaign name="C2">
<Price value="1.00"></Price>
etc
The default behaviour gives me this:
<Campaign name="C1" price="4.00"/>
<Campaign name="C1" price="6.00"/>
<Campaign name="C1" price="10.00"/>
Is there an existing option that deals with this automatically, or do I essentially need to do a group by to output the campaign element, and then union an ungrouped select to output the price element?
Hello,I am currently working on a monthly load process with a datamart. Ioriginally designed the tables in a normalized fashion with the ideathat I would denormalize as needed once I got an idea of what theperformance was like. There were some performance problems, so thedecision was made to denormalize. Now the users are happy with thequery performance, but loading the tables is much more difficult.Specifically...There were two main tables, a header table and a line item table. Thesehave been combined into one table. For my loads I still receive them asseparate files though. The problem is that I might receive a line itemfor a header that began two months ago. When this happens I don't get aheader record in the current month's file - I just get the record inthe line items file. So now I have to join the header and line itemtables in my staging database to get the denormalized rows, but I alsomay have to get header information from my main denormalized table(~150 million rows). For simplicity I will only include the primarykeys and one other column to represent the rest of the row below. Thetables are actually very wide.Staging database:CREATE TABLE dbo.Claims (CLM_ID BIGINT NOT NULL,CLM_DATA VARCHAR(100) NULL )CREATE TABLE dbo.Claim_Lines (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_LN_DATA VARCHAR(100) NULL )Target database:CREATE TABLE dbo.Target (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_DATA VARCHAR(100) NULL,CLM_LN_DATA VARCHAR(100) NULL )I can either pull back all of the necessary header rows from the targettable to the claims table and then do one insert using a join betweenclaims and claim lines into the target table OR I can do one insertwith a join between claims and claim lines and then a second insertwith a join between claim lines and target for those lines that weren'talready added.Some things that I've tried:INSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLGROUP BY T.CLM_ID, T.CLM_DATAINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Target.dbo.Target TINNER JOIN (SELECT CL.CLM_IDFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID =CL.CLM_IDWHERE C.CLM_ID IS NULL) SQ ON SQ.CLM_ID = T.CLM_IDI've also used EXISTS and IN in various queries. No matter which methodI use, the query plans tend to want to do a clustered index scan on thetarget table (actually a partitioned view partitioned by year). Thenumber of headers that were in the target but not the header file thismonth was about 42K out of 1M.So.... any other ideas on how I can set up a query to get the distinctheaders from the denormalized table? Right now I'm considering usingworktables if I can't figure anything else out, but I don't know ifthat will really help me much either.I'm not looking for a definitive answer here, just some ideas that Ican try.Thanks,-Tom.
View 2 Replies View RelatedI have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
Thanks for reading.
This is pretty long, hopefully it isn't rambling.
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example
(my denormalized tables are 20 - 30 colums wide)
denormalized table:
===================
name, address, city, state, cellphone, homephone
normalized tables:
==================
tblPerson [PK_person, name, age, height, weight]
tblAddress [PK_address, FK_person, street, city, state, zip, addressType]
tblContact [PK_contact, FK_person, data, contactType]
I'm breaking up the denormalized tables like this (*UNTESTED*):
=================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR
FAST_FORWARD FOR
SELECT name, address, city, state, cellphone, homephone
FROM _DNT_myWideTable
INTO
WHILE @@Fetch_Status = 0
BEGIN
-- grab the next row from the wide table
FETCH NEXT FROM myCursor
INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY
INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables
INSERT INTO tblAddress (FK_person, address, city, state, addressType)
VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @homephone, 'HOMEPHONE')
END
Hello,
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
TIA
-Brian
I have a problem selecting maximum values inside a data flow, when the field is varchar. For example, I could have an incoming text file with following rows (the real life problem is a bit more complicated, but the idea is the same)
ID Desc
1 Car
1 Truck
1 Bicycle
2 Horse
2 Cow
Now I would like to group by ID, and have the maximum value from the Desc field, resulting
ID Desc
1 Truck
2 Horse
I tried to sort the data flow first with ID ascending and Desc descending, followed by sort with ID ascending and "Remove duplicate sort values" turned on. However, the sort does not seem to select the first Desc value it gets, because the result is.
ID Desc
1 Bicycle
2 Horse
If you have any hints, how to tackle this problem, please help!
How many nodes can you have in a cluster with SQL 2012 alwaysOn.
I understand that availability groups are limited to 5 nodes but if you had a 10 node cluster and decided to create multiple availability groups using various nodes within the 10 nodes but never exceeding 5, is that possible?
Or is there a counter or some validation from SQL AlwaysOn that actually hard limits to a grand total of 5 nodes in a cluster?
I have the following script -
SELECTDISTINCTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
[Code] ....
Above is a small example.
As you can see 13 Alamein Avenue is listed twice. This is because it pulls back the former and the current tenancy. I don't want to exclude former tenancies - so I want to pull back the most recent Tenancy Reference for each Place Reference.
I have a code I wrote and I'm required to get Product Name and Quantity with the maximum quantity sold per day. My issue is with the "per day" part, here's my code below.
SELECT
t.SalesOrder
,t.OrderQuantity
,t.OrderTotal
,t.OrderDate
,p.ProductName
FROM Transactions t
INNER JOIN SalesPerson sp
ON t.SalesPersonID = sp.SalesPersonID
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
Now from this code I need to get only Product Name and Quantity with the maximum quantity sold per day, here's what I tried;
SELECT
SUM(t.OrderQuantity) AS Order_Quantity
,p.ProductName
FROM Transactions t
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
GROUP BY p.ProductName, t.OrderQuantity
HAVING MAX(t.OrderQuantity)
I have a dataset that I need to search for a maximum value of a specified index.
Example of dataset:
Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126
I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.
Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.
Could I use an array?
I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement.
View 2 Replies View RelatedI have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
What is the maximum no.of global temporary tables can create in sql server
View 4 Replies View RelatedMy ERP system (SAP Business One) does not have a "payment type" field, instead it has a field for the amount of each type of payment. It makes for annoying reporting.
If I have the following data in the Incoming Payments table (ORCT)
DocEntryCashSumCreditSumCheckSumTrsfrSumDocTotal
0100000100
1010000100
2001000100
3000100100
What is an efficient SQL query to return something like this? (It is okay if the type returns an integer code instead of a string description):
DocEntryTypeTotal
0Cash100
1Credit100
2Check100
3Transfer100
I am currently using:
SELECTT0.DocEntry,
CASE
WHEN T0.CashSum <> 0 THEN 'Cash'
WHEN T0.CreditSum <> 0 THEN 'Credit'
WHEN T0.CheckSum <> 0 THEN 'Check'
WHEN T0.TrsfrSum <> 0 THEN 'Transfer'
END AS [Type],
CASE
WHEN T0.CashSum <> 0 THEN T0.CashSum
WHEN T0.CreditSum <> 0 THEN T0.CreditSum
WHEN T0.CheckSum <> 0 THEN T0.CheckSum
WHEN T0.TrsfrSum <> 0 THEN T0.TrsfrSum
END AS [Total]
FROMORCT T0
I think I've just been staring at this too long and wanted some fresh eyes to think outside of the box on this one.
I am using this to report on our customer's monthly statements so that I can display "12/13/06 Wire Transfer $100". There are many more complications to this query such as currencies and which document number or reference to display, but I have figured out most of those on my own.
Thank you,
Mike
I have just inherited a new project consisting of data imported into sql 2005 from a multi-dimensional database. After finding the correct ODBC and importing the data I believed that I was done, but after reviewing the resulting structure I discovered why this was called a ��multi-dimensional�? database. The resulting imported data is completely de-normalized and resembles an excel spreadsheet more than a relational database. An example of this structure is the persons table. The table has multiple columns, some of which contain the multi-dimensional fields. These fields contain multiple values which are separated with a tilde, ��~�?. I need to parse out the data and normalize it. In the specific sample of data below I attempting to take the personid, associates, and assocattrib and insert them into a sql table, associates. This table references the persons table where the personid and the associates references the personid in the persons table.
Code Snippet
CREATE TABLE [dbo].[persons](
[namepkey] [int] PRIMARY KEY NOT NULL,
[nameid] [varchar](6) NOT NULL,
[lastname] [varchar](41) NULL,
[firstname] [varchar](50) NULL,
[mname] [varchar](50) NULL,
[sex] [char](1) NULL,
[race] [varchar](55) NULL,
[dob] [varchar](10) NULL,
[address] [varchar](28) NULL,
[city] [varchar](32) NULL,
[state] [varchar](25) NULL,
[zip] [varchar](127) NULL,
[hphone] [varchar](10) NULL,
[busphone] [varchar](50) NULL,
[profession] [varchar](28) NULL,
[employer] [varchar](42) NULL,
[eyecolor] [varchar](23) NULL,
[build] [varchar](14) NULL,
[complexion] [varchar](26) NULL,
[haircolor] [varchar](26) NULL,
[dlnumber] [varchar](36) NULL,
[dlstate] [varchar](27) NULL,
[jacketnumber] [varchar](130) NULL,
[height] [varchar](4) NULL,
[weight] [varchar](50) NULL,
[ethnicity] [varchar](25) NULL,
)
CREATE TABLE [dbo].[associates](
[associd] [int] NOT NULL REFERENCES persons(personid),
[namepkey] [int] NOT NULL REFERENCES persons(personid),
[assocattribute] [varchar](20) NULL
)
The purpose of normalizing this data will be to show the realationship(s) between people as it has been documented in the previous data structure, i.e. person 1 is an associate of person 336 and the attribute is WIT.
My problem lies in attempting to parse out the associates and assocattrib columns and relate them to the appropriate personid. The personid relates to each associate and assocattrib and the tilde, ~, separates the values ordinal position which, in sql, would be separate rows. For example the single row:
personid associates assocattrib
58201 252427~252427~252427 VICT/SUSP~WIT~RP
Should be:
58201 252427 VICT/SUSP
58201 252427 WIT
58201 252427 RP
The imported data can have no associates:
personid associates assocattrib
152683 NULL NULL
or up to 69 associates, I am not even going to try to paste all of that here.
There are over 400,000 rows that I am looking at parsing, so any way of doing this in t-sql or the clr would be GREAT. This data is stored in SQL 2005 standard SP2. The specific machine is our test/reporting server, so I am not necessarily concerned with the best performing solution, I am leaning more towards providing some free time for me.
Any help or suggestions, including better ideas on the table structure, would be greatly appreciated.
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
- What are your thoughts on adding clustered index on datetime (createdDate , native GUID) column. The data will be be physically organized in the clustered index allowing range operations to perform its duties. But will the GUID column make any impact ( drawbacks) should it be made part of the clustered key ?
The GUID column will provide the lookup with the required indexes to support.
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year
FROM
EMP_DATE DTE join
EMP_INFO EMP on EMP.ID = DTE.ID join
YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
DATA SAMPLE:
EMP
ID NAME
1 JOHN
EMP_INFO
ID STARTDATE ENDDATE
12013-10-24 2014-03-11
12014-06-13 2014-03-11
12014-06-15 2014-03-11
12014-09-08 2014-03-11
12014-09-12 2014-03-11
12014-09-14 2014-03-11
12014-01-13 2014-05-17
12014-05-14 2014-06-09
12014-06-10 2014-06-16
12014-08-31 2014-09-04
12014-09-05 2014-09-06
12014-09-07 2014-10-23
YEAR
Year
2012
2013
2014
2015
I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
[code]....
where @reasons and @departure_code can be multiple.
Dear Experts,
i've one database with around 1400 tables.
is there any possibilities to know at a time what is the count(*) in each table? actually i need tables which are having maximum data.
my expected result is like this
table num_rows
table1 20000
table2 10000
like this
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
How do I set the maximum vertical size of a table? I would like to set it based on total vertical size in inches or a maximum number of rows returned.
Thanks for any help.
Brad
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
Suppose I have string like
@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'
and table contains two columns having rows like,
ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx
how can I search a ID's which are exist in my string.
result should be,
3 abcd Oh
6 abcd O
I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?
select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded
i have a table with productID and OrderID. For ech product there are orders.
So for each productID there are lot Of OrderID's are present.
My data like
ProductID OrderID
1 12012
1 12447
1 12478
2 24154
2 21457 etc.......
so, i need to get the maximum count for a product.
i can do by using Temporary tables. but i need without using temporary tables.
please help me out.
thanks
-Praveen.
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I need to extract a price from a package solution table that was current on a certain date.
Columns are:
Product_Reference int,
Change_Date int, -- Note that this is in yyyymmdd format
Price decimal
So for one items you would get
Product_ReferenceChange_DatePrice
100014200401281.59
100014200605131.75
100014200802121.99
100014200906252.35
100014201002242.50
100014201107151.10
100014201205151.15
The challenge is to return the price of the item on 20070906. In this example 1.75.
I've tried joining it to itself
SELECT DISTINCT p1.[Product_Reference]
,p1.[Change_Date]
,p1.[Price]
FROM PHistory p1
INNER JOIN PHistory p2
ON p1.Product_Reference = p2.Product_Reference
WHERE p1.Product_Reference = 100014
AND p1.Change_Date >= 20070906
and p2.Change_Date < 20070906
But it returns the price above and below that date.
writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?
View 1 Replies View RelatedHello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies View Related