Convert Excel Formula to SQL

  • andrewd.smith (2/19/2009)


    This is the line causing the error:

    select @result = ((1+@lf_group1/100)^(12/@lf_exponent)-1)*100

    The ^ 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]

  • 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!

  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply