Grouping Rows By Customer
Nov 8, 2006
my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Thanks
Vlad
View 4 Replies
ADVERTISEMENT
Oct 26, 2015
I have a table Customer with below column
CustomerNumber , FName,LName,DOB
I have either 2 or 1 row for each customer number I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.
example:
CustNumber Fanme Lname DOB
1 Sam tiller 08/26/1981
1 Joe timy 01/02/1986
2 jack niks 09/09/1990
Result I want:
CustNumber Fname1 Lname1 DOB1 Fname2 Lname2 DOB2
1 Sam Tiller 08/26/1981 Joe timy 01/02/1986
2 Jack niks 09/09/1990 null null null
View 7 Replies
View Related
Mar 2, 2001
I store data in a table using a column named InsertTimestamp which is a datetime format. I now want to report on rows by just the date. I have been able to do this by converting the datetime like such:
convert(varchar, inserttimestamp, 107)
This reurns the data correctly however is very ineffiecnet. DOes anyone know another easy way around this dilemna???
View 1 Replies
View Related
Dec 14, 2004
Hi
I have a table strcutre as follows.This is like a many to many relationship between category and Article. Now i need to pick 3 recrord from each category which has a relavancy 1 sorted by Article date Desc. ie.. from the recent articles..
I can fire 4 diff queries and restrict it using top keyword..
Can i do this in a Single query or in a better way..can anyone help me plz......
Rgds
jinu
Category
---------
catID - PK
CatName
Article
-------
ArticleID - PK
Name
Date
Category_Article
----------------
ArticleID - Composite Key(ArticleID,CatId)
CatID
Relavancy
View 8 Replies
View Related
Aug 24, 2013
I have a table with following sample data:
**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265
[code]....
I need to know :
1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)
Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.
View 2 Replies
View Related
Jan 24, 2007
Hi folks. Hope all the gurus including Brett,Pat Phalen, RdjBarov, r937 are fine. ;)
been so long to ask stupid question. Here's the question and i really need help on this.
i have data that tracks patterns of bus stops from one point to another.
like point a, to point b, point b to point c forms one pattern.
point a to point c , point c to point b should be a different pattern.
create table #journeypatterns (patternid int ,points varchar(100))
go
insert #journeypatterns
select 1 ,'a' union all select 1 ,'b' union all select 1,'c'
union all select 2,'a' union all select 2,'c' union all select 2,'b'
union all select 3 ,'a' union all select 3 ,'b' union all select 3,'c'
select * from #journeypatterns
patternid points
1 a
1 b
1 c
2 a
2 c
2 b
3 a
3 b
3 c
what i want is to get unique pattern value of sequence of points by grouping on patternid. if the sequence of points change, i need a unique value against that pattern.
like for patternid 1, sequence of points a,bc for example should be abc.
for patternid 2, sequence of points a,c,b for example should be acb.
again patternid 3, sequence of points a,bc for example should be abc.
i tried CHECKSUM_AGG which brutally failed in production because the checksum values for each single point when summed produce SAME result for different patterns.
select checksum_agg(binary_checksum(points)) ,patternid
from #journeypatterns
group by patternid
961
962
963
here patternid 2 should be different because sequence is acb. i know checksum is not the right approach for what i need.
I NEED A GENERIC FUNCTION, that marks the pattern differences, my ultimate goal was to create a procedure, whom a patternid should be passed, and it would result the NEXT patternid in the table which has the SAME ORDER OF point sequences.
now folks, i can do this holding all data into a temp table and write a cursor to traverse through each patternid and concatenate the sequence of points.
BUT, using this approach is the ugliest, as it has slow down the process badly and boss is not happy with the performance. the table holds a lot of data.
I NEED a query rather than a cursor on the fly to resolve this.
Here's the query i am using to get the current sequence of a pattern and then i have to search all sequences similarly against it.
declare @patternid int
set @patternid =1
declare @sequence [varchar] (100)
declare @id varchar(10)
declare cr_sequence cursor fast_forward for select points from #journeypatterns where patternid=@patternid
open cr_sequence
fetch next from cr_sequence into @id
while @@fetch_status = 0
begin
select @sequence = isnull(@sequence,'')+@id
fetch next from cr_sequence into @id
end
print @sequence -- next i have code to find the similar sequence for another patternid.... which is not mentioned here but is similar
View 5 Replies
View Related
Feb 14, 2012
i've to generate a notepad using this query in vb.net :
strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _
[Code]....
In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.
Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.
how to group the "d.amount" without having repetition in the "d.shareholderid" ??
View 5 Replies
View Related
Feb 21, 2013
SQL Server 2008.
From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.
Part of the table:
logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413
[Code] ....
So as long the field LocationID is the same in the next row, it needs to be grouped.
I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp
By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.
View 12 Replies
View Related
Sep 23, 2015
I've unpivoted some data and stored it in a temp table variable
idNumFreqDtFreqrn
16100120120101M2
16100120120101M3
16100120100101M4
16100120100101M5
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
Using the above data, if two rows have the same FreqDt, I want to see the record with the lowest row number.
So it should look like the below
idNumFreqDtFreqrn
16100120120101M2
16100120100101M4
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
I've used the below code to accomplish it
SELECT DISTINCT
CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END,
CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END,
CASE WHEN t2.freq is null then t1.freq else t2.freq end
FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2
ON t1.idNum = T2.idNum
AND t1.FreqDt = t2.FreqDt
AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
idNumFreqDtFreq
16100120060101M
16100120000929Q
16100119990101A
16100119970101M
answer is below:
SELECT T1.*
FROM @t as t1 LEFT JOIN @t as t2
ON t1.idnum = T2.idnum
AND t1.freq = t2.freq
AND t1.rn = (t2.rn-1)
WHERE t2.idnum IS NULL
View 5 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
Oct 26, 2007
Hi ALl,
I am using SQl server 2005,
I have the following data
customer order_id order_code complete
1328004 3462 18 1
1328004 3463 18 1
1554477 3689 18 1
1554477 4123 18 0
Here I need results like this,
customer order_id order_code complete
1328004 3462 18 1
1554477 3689 18 1
the first row for each customer and order_code, so far I haven't been able to come up with the correct query.
select top 1 a.customer,a.order_id,a.order_code,a.complete
from order a
order by a.customer,a.order_id
My query only returns the top 1 row .Please point me in the right direction.
Thanks in advance
View 4 Replies
View Related
Apr 13, 2008
Hi, is there a way to modify the way an identity column counts? for example, I want the counter to reset every month, I store the number of the month in another colum, so I just need the identity to take the month in to account, so the first day of each month it would start from 1 again.
View 4 Replies
View Related
Feb 13, 2007
I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)
so to get the invoice_customer address for the customer account - 13301
SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'
i want to add the above InvoiceAddress to the query below:
select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'
How would I put the 2 togtheer...
Thanks in advance!!!
View 2 Replies
View Related
Jul 18, 2007
Hello everyone -
Is there a pro / con reason why a corporation would create a new database for each customer??
I can understand the obvious reason - to keep the companies apart,
but other than that single reason are there any other ones?
thanks
tony
View 7 Replies
View Related
May 10, 2007
Good day.
Am new with SQL server and i use SQL Server 2005
i have 3 tables (1 = customers, 2 = orders, 3 = keys) both these tables are linked by the key table
I wanna be able to "view" (as am only allowed to view) the last three orders for every customer.
I tried using the top predicate/ clause with a group by & count but unfortunately am getting no where.
Please kindly help
View 14 Replies
View Related
Mar 12, 2008
What statements can be used to grab the most recent customer record?
View 3 Replies
View Related
Jul 23, 2005
I am aware of the problems with using identity. I would like a bitmore information on implementation. Concurency is a concern becausethis is a net application.Do I do this at the application level, in a trigger, or using anothermethod?I want to make sure that the method I choose is robust and maintainsthe integrity of the data.[color=blue]>From what I know, in this case not much, it would seem that the[/color]trigger is the best bet.If I were to do a table that stores the unique number counter, I'mthinking TableName and NextNumber fields could be created and thenused for all tables that need this requirement. Then my triggerwould look up the next number based on its table name, save thenumber as the ID and then increment the value by 1 or whatever stepI choose. Is this thread safe?Thanks,Greg
View 4 Replies
View Related
Mar 4, 2006
Hi there, I am a little confused with some data ideas in .NET 2.0.
First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions?
Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself.
If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say?
Thank you very much!
Tristan
View 3 Replies
View Related
Jan 26, 2007
I have given a Zip file with mdf + ldf file to a customer and he cannot attach it to his server
what can be the problem ?
any rights ?
thank you
View 4 Replies
View Related
Dec 17, 2014
If i want to replicate data only for a particular customer code using SQL 2012 replication - is it possible. I believe that either the entire database or few tables can be replicated using SQL 2012.
View 2 Replies
View Related
Jan 22, 2014
I have three SQL tables that I am trying to join. One is Called Customer and the other Orders.
Customer contains these fields that I need
CustomerID,Email,FirstName,LastName,
Orders has these that i need
OrderNumber,CustomerID,OrderTotal,OrderSubTotal
OrdersShoppingCart has these that I need
OrderNumber,CustomerID,OrderedProductSKU,OrderedProductName,OrderedProductPrice
What I would like to try to export is this
CustomerID
FirstName
LastName
Order ID
SKU
Order Total
This would be grouped for each customer for all total orders. Do you think this is possible?
View 9 Replies
View Related
May 16, 2014
I have this table below with customers having loans. Nou I want to write a query which retrieves all customers with exactly the same set of Loans.
Rules:
If I give C1 then I should get C2 (same set of loans)
If I give C2 then I should get C1 (same set of loans)
If I give C3 then I should get C4 (same set of loans)
If I give C4 then I should get C3 (same set of loans)
If I give C5 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C6 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C7 then I should NOT get any row because there is no other Customer with the same set of loans.
Table LoanCustomer:
[KEY] [LOAN] [Customer]
1L1C5
2L2C1
3L2C2
4L3C1
5L3C2
6L4C3
7L4C4
8L5C6
9L5C7
10L6C7
I tried this query below but it doesn't give me always the same right results.
SELECT t1.LOAN, t1.CUSTOMER
FROM LoanCustomer t1
WHERE EXISTS (
SELECT t2.LOAN, t2.CUSTOMER
FROM LoanCustomer t2
WHERE CUSTOMER= 'C1'
and t1.CUSTOMER != t2.CUSTOMER and t1.LOAN = t2.LOAN
)
View 3 Replies
View Related
Jan 31, 2007
Hello Guys,
I really need you help to debug this query.
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress,
from
(
select
MIN(CAST(customer_guid AS varchar(36))) as Customer_GUID,
Transaction_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()
and Terminate_dm is null
and customer_guid
not
in
(
select CAST(customer_guid AS varchar(36)) as Customer_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()
and Terminate_dm is null
)
group by transaction_guid, customer_guid
)z
inner
jointblTransaction a
onz.Transaction_GUID = a.Transaction_GUID
View 8 Replies
View Related
Mar 22, 2007
I am fairly new to sql. Reading a table I need to show amount for each customer. Also I need to add amount for each customer that has more than one entry. What would the sql statement look like? Thank you.
Records in file:
Company Amount
Customer1 24.000
Customer2 36.000
Customer3 72.000
Customer1 20.000
Customer3 15.000
Desired results:
Company Amount
Customer1 44.000
Customer2 36.000
Customer3 87.000
View 1 Replies
View Related
Mar 3, 2008
Hi,
I have two tables customers and orders. customerID is the foreign key for order table. If I pass customername I need to get information about each customer how many orders holding?
View 3 Replies
View Related
Sep 17, 2006
i have table:
customer(customerid, age,sex....)
orderdata(orderid, customerid,day)
orderdetails(orderid, productid, quantity)
products(productid, productname,...)
now, i want to show some product for customer when i now him age and sex.
e.ct: if he is a man and age =20 i show product : ball, pull, sport close....... if man is a woman , i show lips, babara, t_shirt, skirt....
if man is a chirdren, i will show joy, story for chidren....
how i create my mining model. and how i query for result in DTS
View 1 Replies
View Related
Oct 30, 2006
i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.
Userid, product id, createddate
1034 2000788 2006-09-01 14:50:19.880
1034 383002 7 2005-09-07 20:50:19.880
1034 4493903 2006-09-01 20:00:19.880
I am therefore making a query for getting the data.How can i get only one record for each customer?
Sincerely
wan.
View 3 Replies
View Related
Jul 25, 2006
Hi
Pl any one tell me which algorithm is better for Customer retention Using SQL server 2005 analysis services
It will be great if some one can give the same with example of data model with key column , and rest
Thanks in Advance
Rajesh Ladda
View 3 Replies
View Related
Feb 8, 2007
I have a contact table and a customer table. The two tables will contain columns like
First name
Last Name,
Date of Birth
Post Code,
House Number
Street Name
etc.
I would like to find the different combinations in which I can relate the customer and contact data.
Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me.
From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?
I am very new to Data Mining and would like to have some direction as to how to progress with this.
View 1 Replies
View Related
Oct 15, 2007
I have to retrieve first and last record of each customer according to the Date. Each customer has 10 - 15 records in the table and there are 3000 customers.
how can I retrieve this data.
regards
View 7 Replies
View Related
Apr 18, 2003
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables in SQL dbs which hold a lot of date consisting of statistics, errors, logs etc.
They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
Thanks for any input
mipo
View 1 Replies
View Related
Sep 26, 2012
I want to update the orders that has invoice number empty when doing a change in the customer table Nm.
My order table is named order and customer is named customer, they both have a column named CustId and Nm (name).
This works fine and shows in my case column Nm in 4 rows (for orders);
select
Order.Nm
from
Order
Join Customer
on Order.CustId=Customer.CustId
where
Order.CustId=@variable and InvoNo=''
But the update statement gets error;
Update
Order
set
Order.Nm=Customer.Nm
from
Order
inner join Customer
on
Order.CustId=Customer.CustId
where
Ord.CustId=@Variable and InvoNo=''
I use SQL 2008....
View 13 Replies
View Related
Oct 12, 2014
I would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,
[Code] ....
View 2 Replies
View Related
