0

I am creating the report in which I want to date range from "June,2015 or 36 month before current month report was ran, whichever is later"

I have script for which stared from June,2016 and go get 36 back month. AS Below 

SELECT  (QUOTENAME(RTRIM(LTRIM(cast(datename(month, [dates]) as char(15))))+',' + RTRIM(LTRIM(cast(year([dates]) as char(20))))) + ',') FROM efoxsfc.dbo.FTX_FA_Calender 
                WHERE 1=1 
                AND CAST(dates AS DATETIME) >= DATEADD(mm, -35 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
                AND dates <= DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)



SELECT  (QUOTENAME(RTRIM(LTRIM(cast(datename(month, [dates]) as char(15))))+',' + RTRIM(LTRIM(cast(year([dates]) as char(20))))) + ',') FROM efoxsfc.dbo.FTX_FA_Calender 
                WHERE 1=1 
                AND CAST(dates AS DATETIME) >= DATEADD(mm, -5 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
                AND dates <= DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)

How i will connect this two script ?

sony921
  • 109
  • 13
  • use UNION ALL or UNION – Adish Nov 04 '15 at 04:10
  • Please choose a correct answer if there is one, otherwise provide follow up information that is not being answered. – red6 Nov 04 '15 at 19:46
  • I am looking for the data range "June,2015 or 36 month before current month report was ran, whichever is later" for example if i am running report today it will shows from June,2015 as June,2015 is more than Nov12(36 month back). Now consider i am running same report in Nov 2018 so it will show me Nov 2015 because Nov 2015 is more than June,2015. Does this make sence to you? – sony921 Nov 06 '15 at 04:08

2 Answers2

0
    SELECT  (QUOTENAME(RTRIM(LTRIM(cast(datename(month, [dates]) as char(15))))+',' + RTRIM(LTRIM(cast(year([dates]) as char(20))))) + ',') FROM efoxsfc.dbo.FTX_FA_Calender 
            WHERE 1=1 
            AND CAST(dates AS DATETIME) >= DATEADD(mm, -35 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
            AND dates <= DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)
UNION ALL
    SELECT  (QUOTENAME(RTRIM(LTRIM(cast(datename(month, [dates]) as char(15))))+',' + RTRIM(LTRIM(cast(year([dates]) as char(20))))) + ',') FROM efoxsfc.dbo.FTX_FA_Calender 
            WHERE 1=1 
            AND CAST(dates AS DATETIME) >= DATEADD(mm, -5 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
            AND dates <= DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)

or combine the where clause

SELECT  (QUOTENAME(RTRIM(LTRIM(cast(datename(month, [dates]) as char(15))))+',' + RTRIM(LTRIM(cast(year([dates]) as char(20))))) + ',') FROM efoxsfc.dbo.FTX_FA_Calender 
            WHERE 1=1 
            AND (
                CAST(dates AS DATETIME) >= DATEADD(mm, -35 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
                OR
                CAST(dates AS DATETIME) >= DATEADD(mm, -5 ,DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)) 
                )
            AND dates <= DATEADD(m, DATEDIFF(m, 0,GETDATE()), 0)
Adish
  • 709
  • 4
  • 12
0

A UNION or UNION ALL if you're trying to return these data as the results of a single query.

UNION ALL will return all results, whereas UNION will perform a DISTINCT on the data set.

This is discussed more here.

Community
  • 1
  • 1
red6
  • 102
  • 2
  • 11