February 21, 2009 at 9:39 pm
andrewd.smith (2/19/2009)
This is the line causing the error:
select @result = ((1+@lf_group1/100)^(12/@lf_exponent)-1)*100The ^ character represents the XOR operator in TSQL, not the exponential operator that it represents in VB. In TSQL, you need to use the POWER function instead.
Heh, you know I had a very devious Question Of The Day, based on this common oversight: http://www.sqlservercentral.com/questions/TSQL/63632/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2009 at 2:05 pm
Sergiy (2/21/2009)
nathanb (2/19/2009)
Also realised my Broken Drop Table Statement was because I had the Schema (Dbo) included. On just doing a straight select * on information.Schema I realised what it should look like (in case anyone goes to fix that for me):IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dbo' and table_name = 'Fund') DROP TABLE Fund
I use
IF Object_ID('dbo.Fund') IS NOT NULL
Much less typing. 🙂
Thanks Sergiy!
August 19, 2015 at 7:38 am
I have data concerning sale of several products, columns are number of customer, date of purchase and name of product.
I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products
Sur Excel :
The formula for duration in D2 is : IF(AND(A1=A2;$E$1>=C2);B2-B1;SI(AND(A1=A2;$E$1<B2);"";IF(AND(A1<>A2;$E$1<B2);"";$E$1-B2)))
The formula in G4 for average duration : AVERAGEIFS($D:$D;$D:$D;"<>""";$C:$C;$F4)
Thanks a lot in advance
August 19, 2015 at 8:07 am
aziznet (8/19/2015)
I have data concerning sale of several products, columns are number of customer, date of purchase and name of product.I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products
Sur Excel :
The formula for duration in D2 is : IF(AND(A1=A2;$E$1>=C2);B2-B1;SI(AND(A1=A2;$E$1<B2);"";IF(AND(A1<>A2;$E$1<B2);"";$E$1-B2)))
The formula in G4 for average duration : AVERAGEIFS($D:$D;$D:$D;"<>""";$C:$C;$F4)
Thanks a lot in advance
Please create a new thread which includes DDL and sample data in the form of insert statements so we can work on it.
August 20, 2015 at 1:27 am
Hi,
sorry for my language, in fact I am not Sufficiently well in english especialy in the forum so I ask if I can write in french may be sommebody can help ? and this can help me well.
thanks a lot
August 20, 2015 at 7:11 am
Hi,
I want just to rewrite my question in other way, so the exemple is the same but there is a macro may be someone can understand what I am asking.
The macro named Duration is :
Sub Duration()
'
' Duration Macro
'
Range("D2:D20").FormulaR1C1 = _
"=IF(AND(RC[-3]=R[1]C[-3],R1C5>=R[1]C[-2]),R[1]C[-2]-RC[-2],IF(AND(RC[-3]=R[1]C[-3],R1C5<R[1]C[-2]),"""",IF(AND(RC[-3]<>R[1]C[-3],R1C5<=RC[-2]),"""",R1C5-RC[-2])))"
Range("G4:G6").FormulaR1C1 = "=AVERAGEIFS(C4,C4,""<>"""""",C3,RC6)"
Range("E1").Select
ActiveWorkbook.Save
End Sub
So if it is understood and possible I want the macro converted into SQL.
Thanks a lot
August 21, 2015 at 2:02 pm
aziznet (8/20/2015)
Hi,sorry for my language, in fact I am not Sufficiently well in english especialy in the forum so I ask if I can write in french may be sommebody can help ? and this can help me well.
thanks a lot
I do not know how other members of this forum would react to seeing a foreign language here but I am open to do the translation if you want to try it.
August 21, 2015 at 2:26 pm
What version of SQL Server are you using? If you have 2012 or later then you can use Windowing functions.
For the benefit of those trying to help, here are the scripts to reproduce your data... (The Sales table should probably have some indexing, but it's a small dataset...)
CREATE TABLE Sales(
CustomerID INT,
Product VARCHAR(8),
PurchaseDate DATE
);
GO
INSERT INTO Sales (CustomerID, PurchaseDate,Product)
VALUES (957623232, '9/2/2014','Product1'),
(57623233, '9/2/2014','Product1'),
(957623255, '4/6/2014','Product1'),
(957623256, '4/6/2014','Product1'),
(957623257, '6/25/2014','Product1'),
(957623259, '4/10/2014','Product1'),
(957623260, '4/7/2014','Product1'),
(957623282, '4/15/2014','Product1'),
(957623417, '4/15/2014','Product1'),
(957623419, '5/13/2014','Product1'),
(957623482, '5/22/2014','Product2'),
(957623483, '4/9/2014','Product2'),
(957623484, '4/9/2014','Product2'),
(957623486, '4/8/2014','Product2'),
(957623487, '5/21/2014','Product2'),
(957623492, '5/17/2014','Product2'),
(957623493, '4/10/2014','Product2'),
(957623494, '4/10/2014','Product2'),
(957623495, '4/12/2014','Product2'),
(957623498, '6/15/2014','Product2'),
(957623570, '4/27/2014','Product3'),
(957623571, '6/11/2014','Product3'),
(957623657, '4/5/2014','Product3'),
(957623658, '4/8/2014','Product3'),
(957623659, '4/12/2014','Product3'),
(957623660, '5/25/2014','Product3'),
(957623685, '4/28/2014','Product3'),
(957623686, '4/22/2014','Product3'),
(957623690, '4/8/2014','Product3'),
(957623691, '4/7/2014','Product3'),
(957623692, '4/7/2014','Product3'),
(957623696, '5/7/2014','Product3'),
(957623697, '5/11/2014','Product3');
If you can use windowing functions, this will work:
SELECT Product
, PurchaseDate
, LAG(PurchaseDate,1) OVER (PARTITION BY Product ORDER BY PurchaseDate) AS PrevPurchaseDate
, DATEDIFF(day,LAG(PurchaseDate,1) OVER (PARTITION BY Product ORDER BY PurchaseDate), PurchaseDate) AS DaysBetween
FROM Sales
ORDER BY Product, PurchaseDate;
Otherwise, it might require some craftiness to simulate LAG. Here[/url]'s an article from SQLAuthority
August 23, 2015 at 2:51 am
Hi everybody,
Hi SSC-Enthusiastic
Thanks SSC-Enthusiastic for your proposal and thanks for all of you :
En réalité le nombre de lignes à présent ça remonte au plus d'un million d'enregistrements ce qui dépassera le nombre de lignes existant sur Excel.
Un client peut effectuer plusieurs achats d'un même produit donc son Num_customer se répète autant de fois et le nombre de produits vendus est 15.
L'ordre des enregistrements premièrement selon le Num_customer et deuxièmement selon Purchase_day.
Thanks again
August 23, 2015 at 5:31 am
Hi pietlinden,
Thanks for your help,
As in example one customer can buy the same product many times, so the " Num_Customer " we will see it repeted several times and also the " purchase_day " for the same customer. Thus, the columns for the lags would be more than one time, and we will stop these columns (lags) until the sum of the last column egal to " 0 " as I think.
Thanks a lot for your effort and help.
September 8, 2015 at 8:24 am
Hi everybody,
Here is an ambitious try as I am not developer.
SELECT Name_product, Purchase_Day
CASE
when cur.Name_product = prev.Name_product
AND cur.Purchase_Day <= '31/05/2014' then cur.Purchase_Day - prev.Purchase_Day as Durarion
when cur.Name_product <> prev.Name_product
AND cur.Purchase_Day > '31/05/2014' then "" as Duration
else '31/05/2014' - prev.purchase_Day as Duration
END
FROM Sales
ORDER BY Name_product, Purchase_Day
Of course it is false but it's a try, this is without Average duration.
https://onedrive.live.com/redir?resi...nt=file%2cxlsx
Thanks
September 8, 2015 at 8:28 am
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply