SQLTeam.com | Weblogs | Forums

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

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.

As always

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

;

image

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
1 Like

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