Retrieve data on condition base on May to Sep and Oct to April

[Sameer]

Hi Experts!
I need to retrieve historical + Current data based on condition.
suppose if months are May to September (Summer) than show data in datapoint1 else months are October to April (Winter) than show show data datapoint2.

[yosiasz]

As always

Daily ,Weekly, Monthly ,Quartely, Yearly, Accumulative Query

We need a lot more information. table definition example table data expected output what you have tried

[Sameer]

Hi Experts!
Please find below the test table and data and what i have tried but not able to right path .The result required available in excel screenshot.

Drop Table TEST1
CREATE TABLE [dbo].[TEST1](
	[GROUP] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[DAILY] [varchar](14) NULL,
	[TEST22] [varchar](20) NULL,
	[PROD_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[COLUMN_1] [numeric](28, 12) NULL
)

GO

INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1]) VALUES('10','Name1','TEST','EVENT','PRODUCT','2015-01-01 00:00','2015-07-01 11:59',10	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('20','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-02-02 01:00',	'2015-02-02 11:59',	20	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('30','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-03-01 02:00',	'2015-03-01 11:59',	30	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('40','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-04-30 03:00',	'2015-04-30 11:59',	40	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('50','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-05-05 04:00',	'2015-05-05 11:59',	50	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('60','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-06-04 05:00',	'2015-06-04 11:59',	60	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('70','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-07-03 06:00',	'2015-07-03 11:59',	70	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('80','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-08-06 07:00',	'2015-08-06 11:59',	80	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('90','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-09-10 08:00',	'2015-09-10 11:59',	90	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('100','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-10-01 09:00',	'2015-10-01 11:59',	100	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('110','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-11-27 10:00','2015-11-27 11:59',	110	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('120','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-12-28 11:00',	'2015-12-28 11:59',	120)


GO


SELECT * FROM TEST1   


--- What i tried but not got to right path
declare @start date=cast('2015-12-28' as date);

select [GROUP]
      ,[ITEM_ID]
	  ,START_DATETIME,

	[MayToSep] = case when START_DATETIME<=dateadd(month,5,@start) and START_DATETIME<=dateadd(month,6,@start)  and  START_DATETIME<=dateadd(month,7,@start) and  START_DATETIME<=dateadd(month,8,@start) 
	  and  START_DATETIME<=dateadd(month,9,@start) then COLUMN_1 else 0 end, 


	[OctToApril] = case when START_DATETIME<=dateadd(month,10,@start) and START_DATETIME<=dateadd(month,11,@start)  and  START_DATETIME<=dateadd(month,12,@start) and  START_DATETIME<=dateadd(month,1,@start) 
	  and  START_DATETIME<=dateadd(month,2,@start) and  START_DATETIME<=dateadd(month,3,@start) and  START_DATETIME<=dateadd(month,4,@start)  then COLUMN_1 else 0 end


  from TEST1  
 where START_DATETIME<=@start

;

[jeffw8713]

This is not tested - but I think this is what you want:

, CASE WHEN month(START_DATETIME) BETWEEN 5 AND 9 THEN COLUMN1 END AS MayToSep
, CASE WHEN month(START_DATETIME) < 5 OR month(START_DATETIME) > 9 THEN COLUMN1 END AS OctToApril

[Sameer]

Exactly Sir ... It is working perfectly thank you!