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
;
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!