Calculation Of A Degree Of Similarity Of Phrases
May 19, 2004
Hi!
i make The Extended stored procedure for MS SQL Server which
Calculation of a degree of similarity of phrases.
Purpose:
One of the most complex and important problems for the developer and the operator of a database is maintenance of uniqueness names in the most important references of system.
Offered function can be used in SQL inquiry as criterion of sorting of the directory according to similarity with a required phrase.
Features:
· incredibly high speed of data processing
· Unique algorithm analyzing similarity of phrases even at significant divergences in required phrases
· is not required installation of additional libraries to each client - library DLL must benn installed only on a server.
· Result is all the table sorted in decreasing order phonetic similarity (probably use of operator TOP for sample only the limited quantity of the most similar variants)
· Use of user server function supposes use in Stored procedures, Views and any SQL expressions
· Spends a minimum of server memory
--------------------------------------------------------------------------
If the decision of the given problem is interesting to you and there is an opportunity desire and an opportunity to test http://kozin1.narod.ru (http://kozin1.narod.ru/newsite/index.html?english.htm)
Dll and sample scripts in rar archive (2,5 KB)
I thank in advance
View 2 Replies
ADVERTISEMENT
Sep 20, 2005
Hi
Is it possible to find records that contain the string "cyber-shot" when the value for search is "cybershot"?? (This is an example and I need a dynamic solution)
Thanks,
Inon.
View 2 Replies
View Related
Nov 8, 2007
This started in one thread, but since it was for beginners, I didn't want anyones brain to melt...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92096
I said this:
quote:
This is getting into a deeper topic beyond just sounding alike.
I am trying to create a similarity search so I can clean up our database... it isn't going to be easy, that's for sure.
Here in lies my data problem, an example:
Let's take something simple, like, gee, a university. A good one is UCLA - University of California, Los Angeles. Do you have any idea how many variations of that can exist on a database? A LOT! UCLA, U.C.L.A., UC,LA, Univ. Cal. LA., the list is literally endless. The same applies for Penn State... PSU, Penn State, P-State... not as many, but you get the idea.
The goal is to be able to bring those similar names back together.
I have been researching the Smith-Waterman algorithm, but they are all rather slow... and prepping a database with a gram based index is just painful... it could take hours to put that together.
For those that don't know, gram is a part of a word. For example, "University," would be stored as UN, NI, IV, and so on... that's a 2 byte gram, a 3 byte gram would be UNI, NIV, IVE, etc.
Think of all the databits that need to be compiled... and then maintained!
Here is a good example of how a search can work:
You all watch CSI, right? Great show. Good fun, serious leaps of faith. They run fingerprint searches, and on their computers, it's these trick graphics with images that are matched. That is how the human mind matches prints, but even then, we use the same basic data the computer uses - reference points on the print.
Take a quick look at a finger, and you will see swirls and junctions and things of that nature (I am not going to get to tech on that, but I used to work in identity theft investigations for a police department on the east coast). These points become part of a mathmatical equation that is then converted to a number. That number is stored.
Whenever a new print is to be tested, it is manually referenced for it's points and then the number is created and compared to everything in that index. Bam, 15 seconds later you have a 99.9% match. Very impressive. It will also preset a top 10 match list.
I do have a question: what is the nature of the similarity searching in SQL 2005?
To save from reading that other thread I hi-jacked, Kristen was kind enough to reply with this:
quote:
Our clients use organisations that specialise in de-duping data for this kind of fuzzy-matching
Which I replied with:
quote:
yea, I was afraid of that.
We are out to create a model that will allow for data quality in real time. For example, a new client comes into our system, let's say from a trade show.
They fill out an info card and we take the contact data and add it to our CRM.
For example, they fill out the card with this info:
John Smith
Some Huge Technical Company
123 Main Street
Anytown, NY 17999 USA
Well... Our search model would take that info and pass it by our RDBMS and return a top ten hit list of matches.
Some Huge Technical Company can become SHTC or Some Huge Tech Co., or any other combo. But because it looks similar, we can then apply the new contact, Mr. Smith, to the right firm, instead of creating three or four (or more) versions of Some Huge Technical Company.
That kinda mass fuzzy matching is easy, we need something that keeps us from needing to do mass updates on a regular basis.
So far, we have determined that a separate database server will be required. It would contain the metrics needed to narrow the search down to the point where the search won't take 30 seconds. We are resisting the idea of grams, simply because of the overhead needed... a single address could create 400 entries in a database table.
I have found a couple of products, but I am terrorified about pricing... I think coding will be my best solution. Amazon listed a couple of very interesting looking books, pricy, yes, but I suspect cheaper than buying the technology.
And finally, Kristen's final reply:
quote:
I did a fair amount of work to just try to match "new accounts".
We looked at Telephone number (unique, so a high indicator if it matches, but then we found lots of addresses had the same "agency phone number" )
Then ZIP code (in the UK our PostCode generally relates to < 20 properties), then lines of address - mixing them around to try to get State / Street matches even if they were switched around in the Address fields, or someone entered an extra address line - like "4th floor" as the first address line.
We had a copy of the "accounts" table that we had cleaned up a bit. Removed all trailing spaces and punctuation. Also all embedded punctuation converted to "space" and adjacent spaces removed, so:
"10, The High Street,"
"10 The High Street"
We made abbreviations consistent:
"10 The High St"
"10 The High Street"
and we probably took out and "the" and other noise words.
Then we tried matching based on that "sanitised" version.
But it took DAYS AND DAYS - of iterative processing - "Gee, look, here's yet-another-variation-of-rule-X" ...
That really tee-d be off, I don't have the stomach for any single job that takes "days" ...
Just in case any of that gives you any ideas
Kristen
The gist:
I need to know what SQL Server 2005 offers in regards to SimSearching. Also, are there any OTC products that will interface with VB.NET 2005 - and not cost a mint.
Thanks!!!
View 2 Replies
View Related
Dec 10, 2007
Hi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?
Regards.
View 1 Replies
View Related
Jan 31, 2008
Hello, I hope you can help!Database: SQL Server 2005Code: ASP.net 2.0 / C#I have implemented full text search that works fine with single word searches but I receive a syntax error when the search string contains a space. The way the code works is a user selects a "Category" in ListBox3 and search results (company names) are displayed in ListBox4.
Here is an error example:Search on "Absorbers, Nuclear Radiation"Error:Syntax error near ',' in the full-text search condition 'Absorbers, Nuclear Radiation'. My Code for Selecting (ListBox4):
<asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:XYZ%>"
SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], @Category)" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox3" Name="Category" PropertyName="SelectedValue"
Type="String"/></SelectParameters>
</asp:SqlDataSource>
<asp:ListBox ID="ListBox4" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource6"DataTextField="SupplierName" DataValueField="SupplierName" Style="z-index: 103;
left: 26px; position: absolute; top: 302px" Width="90%" Height="200px"></asp:ListBox>
Here is where I assign the value to the parameter @Category (ListBox3):
<asp:ListBox ID="ListBox3" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource5"
DataTextField="Category" DataValueField="Category" ></asp:ListBox>
<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:XYZ%>"
SelectCommand="SELECT [Category] FROM [Categories] ORDER BY [Category]">
</asp:SqlDataSource>
Typical data format in the Categories database field. Note: Each entry in the field is separated by a semicolon.Abrasives;Absorbers, Nuclear Radiation;Accelerators & Accessories;Adhesives;
I read somewhere you could possibly add quotations to the search string and it could possibly work but I do not know how to do that??Thanks for your help!
View 2 Replies
View Related
Nov 16, 2007
You know how you can go:
Code Block
where control_id in (11111,22222,33333,44444)
or
Code Block
where TextName in ('11111','22222','33333','44444')
and you can do this:
Code Block
where TextName like '11%' or TextName like '22%'
well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.
where TextName like in ('11%','22%',... and so on)? is anything like that possible?
or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )
can you imagine the dynamics if that syntax actually worked?
Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')
View 13 Replies
View Related
Dec 15, 2006
hi,i've set 'max degree of parallelism' to 1 because some sql request hanged.Now when i connect, how can i set the parallelism to 4 for a session.Is there a command like this :'alter session set max degree of parallelism 4' ?ThanksPaul
View 6 Replies
View Related
Jul 28, 2006
Hi,
how can I efficiently find out the shortest connection in the following scenario:
there's a table which has two colums, both of the same type.
two items in the same row means that they are connected. (there is no direction of the connection so that a connection shows up in both ways in a table for query performance issues)
ie. item1 is connected with item2 and item2 is connected with item3, then the table looks like:
row1: item1 , item2
row2: item2 , item1
row3: item2 , item3
row4: item3 , item2
so the connection item1 to item3 would be item1 - item2 - item3.
degree of seperation is 2 in this case. how can I implement this in general with sql?
making an exhaustive search would cost too much time. thanks for any hints!
View 1 Replies
View Related
Sep 2, 2015
I have SQL Server Version:-
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Â Jun 28 2012 08:36:30 Â Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Â
This is just an UAT server which has OS and hardware detail below:-
OS :- Windows Server 2008 R2 Standard
SP:- SP1
Processor :- Intel(R) Xeon(R) CPU Â X5650 @2.67GHz 2.66 GHz
RAM : - 4 GB
Bit - 64 bit
I want to set the value to max degree of parallelism, what value should i configure for the same?
Below is the snap property of SQL instance >> Processor
View 3 Replies
View Related
Mar 26, 2008
I have come across something on Fuzzy Lookup and dont know am I doing something wrong or is that the behaviour we are expected to get from Fuzzy Lookup.
I have a Test table as shown below with couple of sample rows.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
DROP TABLE [dbo].[Test]
GO
CREATE TABLE [dbo].[Test](
[Code] [varchar](4) NOT NULL,
[Name] [varchar](50) NULL,
[Server] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [Test] ([Code],[Name],[Server])VALUES('PQR','CONTROL GEAR (GROUP) LTD','ELPS122')
GO
INSERT INTO [Test] ([Code],[Name],[Server])VALUES('PQR','CONTROL GEAR (GROUP)','ELPS122')
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTest]'))
DROP VIEW [dbo].[vwTest]
GO
CREATE VIEW [dbo].[vwTest]
AS
SELECT Code, [Name]
FROM Test
GO
OLE DB Data Source - I read the data from Test Table.
Fuzzy Lookup - vwTest is used as Reference Table Name. Joined by Code & Name. Maximum No of matches to output per lookup is set to 5.
Row Count - Data Viewer between Fuzzy Lookup and RowCount
The results as shown below:
Name Name (1) _Similarity_Name
CONTROL GEAR (GROUP) LTD CONTROL GEAR (GROUP) LTD 1
CONTROL GEAR (GROUP) LTD CONTROL GEAR (GROUP) 0.6
CONTROL GEAR (GROUP) CONTROL GEAR (GROUP) 1
CONTROL GEAR (GROUP) CONTROL GEAR (GROUP) LTD 0.8
The result produced by Fuzzy Lookup has shown above.
My question is are we expected to get same similarity value or not. It doesnt produce same similarity value during my testing.
I was expecting same similarity score if I do the following two statements.
Is "CONTROL GEAR (GROUP) LTD" same as "CONTROL GEAR (GROUP)"
Is "CONTROL GEAR (GROUP)" same as "CONTROL GEAR (GROUP) LTD"
I think I know the answer, but I would like to know why though?
Thanks
Sutha
View 7 Replies
View Related
Apr 30, 2008
Hi all,
My question is how to calculate the similarity by using SQL query, example LIKE % , order by.....? Now i'm doing a function same like fuzzy grouping but i do not know how to get the answer, mean how they get match with those selected row of data.
Hope my question is clear. How to write the correct query? What should i do? I 'm newbie in Integration Services, so i need ur explaination in step by step if there hv correction.
I am looking forward to hearing from you shortly and thanks a lot in advance.
Thanks!
rgds,
xuenly
View 3 Replies
View Related
Sep 17, 2015
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
View 2 Replies
View Related
Jul 19, 2007
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
View 5 Replies
View Related
Mar 6, 2015
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
View 2 Replies
View Related
Jan 28, 2008
Hi
I have a Full Text index on a table with an image field that is successfully indexing .doc, .pdf and .rtf files.
Keyword searching this is no problem.
What i want to be able to do is perform a similarity search. by this i mean pass in a Key_ID (documentID) and have the database return a list of Key_IDs (documents) which are similar.
By similar i mean contain mostly the same keywords in roughly the same quantities
Thanks
View 3 Replies
View Related
Apr 16, 2008
Hi,
I want to put a Degree sign in a SQL server field. Can anyone tell me how to do this please (preferably on the data entry screen rather than by coding it in, as I need it freqently).
Many thanks.
View 4 Replies
View Related
Jun 12, 2015
I have got a question on max degree of parallelism and CPU cores.
If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?
If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)
Unable to create a relationship between this parameters.
View 3 Replies
View Related
May 29, 2014
I am running a query on SQL 2012 Server with the Resource governer setup for my account to have Max DOP option set to 1.
The query still runs in about 1 minute and the execution plan still considers parallelism.
When I explicitly mention the OPTION (MAXDOP 1) , the query runs in 6 seconds.
How can i tell by querying DMV's whether my query is using parallelism or not?
View 2 Replies
View Related
May 4, 2008
Hi,
My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
My manager has handed me a task of converting an excel file she uses in to a web aplication.
While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.
The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).
So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
Since these calculations are required a produce a result in an online environment, what will be faster?
I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.
Any views that you can offer will be very helpful.
Thanks in advance.
PMA
View 6 Replies
View Related
Aug 3, 2007
I need to calculate the overall GPA for a student in a particular class.
YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000
The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.
Here's my sql:
select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA
from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725
order by
school.schname,
student,
grade,
class
View 3 Replies
View Related
May 28, 2008
Dimensions:
DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode
Period = 1
Product = 1
Status Code = InStock
Period = 1
Product = 2
Status Code = InStock
Period = 2
Product = 1
Status Code = OutOfStock
Period = 2
Product = 2
Status Code = InStock
In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)
The measure = 1 / 2 or 50%. TIA
View 2 Replies
View Related
Oct 9, 2007
This is a smple data in table1
sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...
I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value
select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1
thanks
View 3 Replies
View Related
Apr 2, 2008
My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.
There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)
A 1300
B 3000
C 400
So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)
A 1000
B 2600
C 400
So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2
I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.
The design for creating the above sample tables is below
/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)
CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)
insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')
insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/
View 5 Replies
View Related
Feb 19, 2008
I´m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:
Code Snippet
WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month
When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).
View 11 Replies
View Related
Mar 1, 2007
Hello Friends
I have 3 tables
1) Product Id, ShortName
2) IncomingStockId, ProductId, Quantity, InDate
3) OutGoingStock Id, OutDate, ProductId, Quantity
I need to get the results like thisProduct name, quantity in stock today
the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)
Thank you for your timeSara
View 1 Replies
View Related
Mar 3, 2008
Hi.
i have the code :
cmd = New SqlCommand("SELECT sales,country,year FROM salesTable WHERE (country = " & (CountryBlk) & " AND branch = " & (NameSnif) & " AND datepart(yyyy,year)=" & (YearBlk) & ") order by datepart(mm,year) ", cnn) cnn.Open() rdr = cmd.ExecuteReader( _ CommandBehavior.CloseConnection)
While x < 12 If rdr.Read = Nothing Then Exit While End If varcount(x) = rdr("sales")
TempMonth = rdr("year") MonthNumber(x)=datepart(DateInterval.Month,TempMonth) x = x + 1 End While
i want to calculate and put into the varcount(x) value all sales of the same month
thanks
View 6 Replies
View Related
Oct 12, 2004
I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
View 4 Replies
View Related
Mar 14, 2001
hello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks
View 1 Replies
View Related
Nov 12, 1999
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
View 1 Replies
View Related
Aug 18, 2003
I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi
View 3 Replies
View Related
Oct 7, 2003
HI,
I have the below logic for age calcuation for more than a year.
But I need age calculation for lessthan year.
Note for MAK: As per our previous post that day calucations didn't work.
DECLARE @birthday datetime, @d datetime
SELECT @birthday = '12/31/1998', @d = '12/30/1999'
SELECT datediff(yy, @birthday, @d) -
(case WHEN (datepart(m, @birthday) > datepart(m, @d)) OR
(datepart(m, @birthday) = datepart(m, @d) AND
datepart(d, @birthday) > datepart(d, @d))
THEN 1
ELSE 0
end) AS Age1
View 6 Replies
View Related
Jan 10, 2006
I need to use the first day of previous month, can anybody help me with that please?
Thanks in advance!!
View 6 Replies
View Related
Oct 5, 2004
HI,
getdate() gives me today's date. I have a sql query which returns me a date. I want to see if the difference between today's date and the date returned by sql query is 12 months(1 year) or less. If yes I want to print it.
(If the difference is more than 12 months or 1 year i don't want to consider that record.)
Example:
how to do calculate:
10/05/2004-10/3/2003
View 3 Replies
View Related
