You must Sign In to post a response.
  • Category: SQL Server

    How to calculate quarter depends on month in mssql2000


    Are you looking for a way to calculate quarter depends on month in sql ? then read this thread to know more about it



    Hi Friends,



    I ve the sample table like


    create table test
    (
    Item varchar(500),
    uom char(200),
    qty float(50),
    price numeric(5,2),
    bill_date datetime
    )



    insert into test values ('pudina','kgs','5','300','2014-04-05')
    insert into test values ('karpoor','kgs','15','300','2014-06-15')
    insert into test values ('Garlic','kgs','25','300','2014-08-05')
    insert into test values ('Oil','Ltr','05','300','2014-11-05')


    like i ve the raw materials purchased on different dates



    now my clients expecting the o/p to show quarter depends on month

    QUARTER:

    1=(apr,may,june),
    2=(july,aug,sep)
    3=(oct,nov,dec)
    4=(jan,feb,march)


    Expect O/p:

    item UOM QTY Quarter Month
    pudina KGS 5 1 apr
    karpoor KGS 15 1 june
    Garlic KGS 25 2 Aug
    Oil Ltr 5 3 NOv




    How to make query in Mssql 2000
  • #752763
    Hi,

    Try below SQL query. In sql we have quarter property to get the quarter name form date.


    SELECT item, UOM, QTY, DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), bill_date) AS DATETIME)) as [quarter], DATENAME(MONTH, bill_date) from test


    Let me know if this is OK or you are looking for any other query.


    Regards,
    Asheej T K

  • #752774
    Refer the following script,

    SELECT item,
    uom,
    qty,
    CASE
    WHEN ( Datename(quarter, Cast(CONVERT(VARCHAR(8), bill_date) AS
    DATETIME)) - 1
    ) = 0 THEN 4
    ELSE Datename(quarter, Cast(CONVERT(VARCHAR(8), bill_date) AS DATETIME)
    ) - 1
    END AS [quarter],
    Datename(month, bill_date)
    FROM test


  • Sign In to post your comments