Transact SQL :: Multiple Occurrences Of Same Product And Same Customer Filtered By Region
Nov 18, 2015
I successfully used the query below to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. Now I need to only pull the purchases from a particular region (client_cd = '593') that purchased a particular product on different dates. How can I do this?
select distinct T.* from db1 T1
where exists (select 1 from db1 T2 where T2.CustomerId = T1.CustomerId and T2.ProductId = T1.ProductId and T2.PurchaseDate
<> T1.PurchaseDate) and T1.ProductId = '123'<o:p></o:p>
View 3 Replies
ADVERTISEMENT
Nov 5, 2015
I am trying to build a query to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. All of the information needed is in the same db. How do I do this?
Select db1.customer_id,
db1.product_id,
db1.purchase_date
From db1
View 20 Replies
View Related
Oct 22, 2015
I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.
createtableProduct2
(
[product] [varchar](255),
[Region] [varchar](15),
[Subregion] [varchar](25),
[Code] ....
View 5 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
Nov 18, 2015
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
View 2 Replies
View Related
Dec 21, 2012
I have a sales order table that has customer, sales, product and sales date. I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer
Product
Sales Date
Sales
First Bike Date
Jon
Bike
12/4/2011
$10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Can I use function to complete? I have a datedim table as well related to the sales date.
View 3 Replies
View Related
Aug 30, 2015
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT)
INSERT INTO @Hierarchy VALUES (123, 234)
INSERT INTO @Hierarchy VALUES (234, 456)
INSERT INTO @Hierarchy VALUES (456, 678)
INSERT INTO @Hierarchy VALUES (678, 891)
INSERT INTO @Hierarchy VALUES (891, NULL)
[Code] .....
View 3 Replies
View Related
Oct 19, 2015
create the column "Region" like this Picture?.
View 26 Replies
View Related
Oct 14, 2015
How to create column "Region" like this Picture.
View 16 Replies
View Related
Jun 19, 2007
If I want to return records where a column has similar data and occurs more than once what is the function?
Ex. If a database contains:
Column 1 Column 2 Column 3
1 Dog White
2 Cat Brown
3 Dog Black
4 Mouse Black
5 Cat White
and I want to show all records where the 2nd column has more than one occurrences so that I get both records where Dog appeared and both records where Cat appeared (since each appeared more than 1 time) - what do i need to write as my function?
View 4 Replies
View Related
Sep 9, 2015
I am working with a pretty complicated xml document but I am only trying to grab specific information.
The issue I am having is that the info I am after is in a node>parent>sibling where there are similar repeating siblings.
<ClinicalDocument xmlns="urn:hl7-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="1.2.840.114350.1.72.1.51693" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<templateId root="2.16.840.1.113883.10.20.22.1.2" />
<id assigningAuthorityName="EPC" root="1.2.840.114350.1.13.291.2.7.8.688883.87504" />
[code].....
Here is my query:
WITH XMLNAMESPACES(DEFAULT 'urn:hl7-org:v3')
SELECT t.document_id,
t.person_id,pref.value('title[1]', 'varchar(255)') AS Title,
[Code] ....
This almost gives me what I need but I am only concerned with, in this case, the first sibling component but it is also picking up Information from the second. in this case it is picking up the caption containing "Strep A Antigen Scrn, Cult if Indicated (09/07/2015 6:35 PM EDT)" Also the number of components siblings change from document to document and although in this example I am trying to get the first component sibling, in actuality the component sibling is more towards the bottom.
Is there a way to only grab the info under the <title> in the component sibling I am after?
It will always be <title>Visit Diagnoses</title>. Is there a way to pinpoint this in the above query? Or am I going at it all wrong?
View 2 Replies
View Related
Oct 21, 2015
How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
View 6 Replies
View Related
Nov 12, 2015
I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around
SELECT MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net]
FROM dbo.tblSalbatTxn
WHERE ([sbt::typ] = 13 OR [sbt::typ] = 17)
Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.
I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.
SELECT TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM dbo.tblSalAccounts AS a INNER JOIN
dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE (b.[sbt::dte] =
(SELECT MAX([sbt::dte]) AS Expr1
FROM dbo.tblSalbatTxn
WHERE (b.[sbt::typ] = 11 OR
b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code])))
View 8 Replies
View Related
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
Aug 6, 2015
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
View 5 Replies
View Related
Sep 29, 2015
I need to find the last purchase price for each product. If I run the following code, I correctly get 1 result for each productID and the last purchase order number.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article
Now I need to add in the price for that product on that orderID. I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article
How can I get it to simply add the price to the first query?
View 10 Replies
View Related
Aug 26, 2006
Hello, im brand new to these forums, scrolled through a bit of the forum(mainly trying to figure out how to go about what im about to ask) and it seems like a good forum...anyways onto my question.
i am in need of ideas/know-how on how to structure a database like such:
its an online motorcycle accessories website, and this is the information i need to store in the db(basically these will be my tables in the db)
bike manufacturer
bike name
products
now my main question is how would i structure those tables so i can get this effect.
each product in the product table needs to have a bike associated to it, 99% of the time it will be more than one bike associated to it, so how would i go about doing that?
originally i had set it up with the bikes name being different columns in the products table with a bit type set to 1 if that product was available for that bike and 0 if it wasnt available. however as you are probably already thinking that isnt the best way to do that.
so what is the best way?
any info at all would be greatly appreciated.
thanks in advance
View 2 Replies
View Related
Sep 29, 2015
I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
Table 1:Order
Order_Id Order_No Customer_Id
2401 1234567 23
2402 1246001 24
2403 1246002 25
Table 2 : OrderedItems
OrderItem_Id Order_Id Item_Id Sequence
1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1
Table 3: ItemMaster
Item_Id Item_Name confidentCode
1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8
Out put for 1st grid
**Note :** Logged-in user have confidentiality code 6
Order No Customer
1234567 23
1246001 24
3rd order is not displayed in the grid
After user selects the 1st order in the grid then the items present in that 1st order should be displayed as
1001 Rice
the second item not displayed because that having confidentiality code greater than user.
After user selects the 2nd order in the grid then the items present in that order should displays
1005 Badham
1007 Oil
I need the query to display the order details in 1st grid.
View 3 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Jul 19, 2007
I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.
I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:
Y Cnt=4, N Cnt=2, V Cnt=1
My assumption was that this will work (white spaces added for readability:
=iif(
Fields!myCol.Value = "Y",
"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "N",
"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "V",
"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
"NULL"
)
)
)
Please help.
View 3 Replies
View Related
Jul 25, 2006
Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate
I have a details table where I capture the following details
CustID, DateofPurchase, PurchaseDetails
I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.
Thanks for your help in advance.
Regards
Dinesh
View 2 Replies
View Related
Jan 30, 2008
Hi,
I have a table1 which points to a dataset1
I was wondering how I use the 'data region' dropdown selection which is present when i click the properties of a cell in table1, it always appear to be blank.
I would like to know how this works as i feel it will be useful in specifying various data regions within a table, I cant seem to find any relevant information on it, can someone tell me how this works ?
View 3 Replies
View Related
Oct 4, 2012
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
View 9 Replies
View Related
Sep 23, 2015
I'm trying to figure out how to do the following:
Number of People receiving their second speeding ticket during this time frame
4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12
The table would contain historical data and look something like this
CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
GO
View 9 Replies
View Related
Jun 1, 2008
I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success
What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:
SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME
Thank you. The above query actually targets a result table from another query, but that should not matter.
View 11 Replies
View Related
Aug 18, 2013
I want to retrieve staff who attend less than 80% for a meeting type assuming we have 10 meetings per list.
Meeting Table:
staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21
* 1 - Dan
* 2 - Jane
When the staffID occur 3 times (70%), query will Output:
staffName list Participation%
------------------------
Dan A 70
SELECT a.staffName, b.list,
(100 - ((COUNT(c.staffID) * 100) / 10)) AS 'Participation%'
from Staff AS a, listType AS b, Meeting AS c
where a.staffID = c.staffID AND
b.list = c.list
GROUP BY a.staffName, b.list
HAVING COUNT(c.staffID) > 2
View 2 Replies
View Related
Feb 14, 2007
can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,
Stud Name Fee Name Fee Amt. Amt Paid
John Doe Registration 1,000 1,000
Tuition Fee 5,000 3,500
1,500
Jane Bake Tuition Fee 5,000 5,000
SubTotal 16,000 11,000
when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.
Thanks...
View 1 Replies
View Related
May 5, 2008
Hi Everyone. How could I configure data region ( table, matrix ) to be repeated on each report's page?
View 8 Replies
View Related
Jul 31, 2007
I have a report that has several headings for users to click on to toggle visibility of the data regions that the headings represent. Several of the columns in the data tables have interactive sort enabled. However, every time the user clicks the sort indicator, the region collapses again and is hidden. So, ultimately for users the interactive sort becomes a two-click action, after which they have to visually re-orient.
I tried putting the table into a rectangle and toggling visibility of the rectangle instead of the table, but it exhibited the same behavior.
How can I get it so the regions do not get hidden just because the user sorts?
Thanks,
Joel
View 1 Replies
View Related
Oct 27, 2003
Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter
declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1
pk occurrences
----------- -----------
2 2
pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.
View 7 Replies
View Related
Jan 24, 2008
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
View 7 Replies
View Related
