Post reply

2 date queries and binding togthter to one

  • February 15, 2012 at 7:57 am

    #250753

    the thing is -:

    SELECT count(HolidayDate)as Count,Datename(month,HolidayDate) as Months

    FROM tblHoliday_Master

    where HolidayDate

    between ''+CONVERT(nvarchar, '2012-01-01', 110) +'' and ''+CONVERT(nvarchar, '2012-12-01', 110) +''

    GROUP BY Datename(month,HolidayDate) order by Months desc

    the data am getting is

    5-->jan

    2-->feb

    ;WITH

    MY_OTHER_CTE AS

    ( SELECT TOP (DATEDIFF(mm,

    DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month

    DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month

    N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)

    FROM Master.sys.All_Columns t1

    CROSS JOIN Master.sys.All_Columns t2

    )

    SELECT

    NumberOfDays = DATEPART (dd, DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1 )

    FROM MY_OTHER_CTE t

    this query gives me ..

    31-->january

    29--feb

    i wana substract 31 -5 to get

    26-->jan

    27-feb

    am binding both query

    any other way plz let me know ....

    4x4 photos

  • February 15, 2012 at 8:05 am

    #1447336

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1252488-392-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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