Forum Replies Created

Viewing 15 posts - 31 through 45 (of 70 total)

  • RE: Multiple count() in a single SQL

    I agree that my code is little bit heavier on execution

    but there are a few reasons

    1) I come from a windows programming background and making the execution plan fast as...

  • RE: Multiple count() in a single SQL

    #aTabale should be dataTable

    my find and repalce gave that problem

    here is the corrected code

    CREATE TABLE #a (id INT PRIMARY KEY, name VARCHAR(20), igroup INT)

    INSERT #a VALUES (1, 'Johan', 1)

    INSERT...

  • RE: Multiple count() in a single SQL

    This would be my solution

    CREATE TABLE #a (id INT PRIMARY KEY, name VARCHAR(20), igroup INT)

    INSERT #a VALUES (1, 'Johan', 1)

    INSERT #a VALUES (2, 'David', 1)

    INSERT #a VALUES (3, 'Fredrik', 2)

    INSERT...

  • RE: how to sum of two tables value

    Sorry one typo

    CREATE TABLE t1(

    [MonthNo] [int],

    [Amount] [money]

    ) ON [PRIMARY]

    CREATE TABLE t2(

    [MonthNo] [int],

    [Amount] [money]

    ) ON [PRIMARY]

    Insert into t1 values (1,111)

    Insert into t1 values (2,211)

    Insert into t1 values (3,311)

    Insert into t2 values...

  • RE: how to sum of two tables value

    This uses CTE and UNION

    CREATE TABLE t1(

    [MonthNo] [int],

    [Amount] [money]

    ) ON [PRIMARY]

    CREATE TABLE t2(

    [MonthNo] [int],

    [Amount] [money]

    ) ON [PRIMARY]

    Insert into t1 values (1,111)

    Insert into t1 values (2,211)

    Insert into t1 values (3,311)

    Insert into...

  • RE: num of days in week

    I thought I will give it a go

    --Thanks

  • RE: num of days in week

    Maybe not the complete answer to the original question

    but it gives you a table from you can get the answer to the original question

    I am new to this

    can someone...

  • RE: num of days in week

    I do this in all my applications. That is why I know this. It is known as Calendar Tables

    very useful for analysisng anything which has dates by just joining it...

  • RE: week range

    I have this problem in all my apps like accounting, Billing,TimeSheets etc

    I keep a table called calendar which is always in the system ( Date, WeekNo, PeriodNo) etc

    then all my...

  • RE: How to display months across like crosstab?

    Sorry. I have been looking at the wrong code

    I should have been looking at Abishalt code

  • RE: How to display months across like crosstab?

    SELECT Customer,

    Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

    FROM

    (

    Select

    left(datename(month,SalesDate ),3) as mn

    ,Sales,Customer

    from

    #Sales

    ) AS SourceTable

    PIVOT

    (

    sum(Sales)

    FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

    ) AS PivotTable;

    1) The Data mn,Sales,Cusomer is being Pivoted here. Is there a some other command called PIVOT if this...

  • RE: How to display months across like crosstab?

    I am not sure where you can use variables in the Pivot Statment so as to move the12 month period forward month by month

  • RE: How to display months across like crosstab?

    I agree livingstones method works.

    However you have write the script everytime you want to Execute it becaus the 12 months will change from

    jan,feb,mar, etc

    feb,Mar,Apr after a month etc

    What I...

  • RE: How to display months across like crosstab?

    The Pivot Table solution given by Livingstone works

    we actually have a table of dates for say from yesr 2000 to 2020 with bank holidays, period number, week number etc etc

    we...

  • RE: SQL Server Licencing

    You are wrong about the KNAPP / MS Alliance

    There is no such thing. When KNAPP comes into your comapany and quote you for your system all they are looking for...

Viewing 15 posts - 31 through 45 (of 70 total)