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

1

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.

2

As always

3

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

1 Like
4

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
2 Likes
5

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

6

To retrieve historical and current data based on the condition you've specified, you'll need to provide more specific details about the data source and how you plan to access it. You'll need a way to determine the current month in your programming language or environment of choice. This can usually be done using date and time functions. Based on the current month, you'll define the condition to determine whether it's summer (May to September) or winter (October to April). Depending on your data source and retrieval method, you will query or access the data based on the condition you defined. This may involve database queries, API requests, or file reading, among other methods.