Fiscal year from July to June query based on month wise to fill template

Sameer · July 6, 2016, 6:47pm

Hi experts

I have no idea to write the fiscal year query month wise to fill my template .Please help me out

djj55 · July 6, 2016, 7:43pm

First you will need example data - input/expected output.
You also need your table definitions.
Then have a look at the pivot command.

Sameer · July 6, 2016, 9:43pm

djj55
Below is the sample out put result and table definitions.

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('1','Name1','TEST','EVENT','PRODUCT','2015-07-01 00:00','2015-07-01 00:00',1	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-07-02 01:00',	'2015-07-02 01:00',	1	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-08-01 02:00',	'2015-08-01 02:00',	2	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-08-31 03:00',	'2015-08-31 03:00',	3	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-09-05 04:00',	'2015-09-05 04:00',	4	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-10-04 05:00',	'2015-10-04 05:00',	5	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-11-03 06:00',	'2015-11-03 06:00',	6	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-11-06 07:00',	'2015-11-06 07:00',	7	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2015-12-10 08:00',	'2015-12-10 08:00',	8	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-01-01 09:00',	'2016-01-01 09:00',	9	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-02-27 10:00',	'2016-02-27 10:00',	10	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-02-28 11:00',	'2016-02-28 11:00',	11)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-03-01 12:00',	'2016-03-01 12:00',	12	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-04-17 13:00',	'2016-04-17 13:00',	13	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-05-10 13:00',	'2016-05-10 13:00',	14	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('1','Name1','TEST',	'EVENT' ,	'PRODUCT',	'2016-06-30 14:00',	'2016-06-30 14:00',	15	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1]) VALUES('2','Name2','TEST','EVENT','PRODUCT','2015-07-01 00:00','2015-07-01 00:00',19	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-07-02 01:00',	'2015-07-02 01:00',	1	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-08-01 02:00',	'2015-08-01 02:00',	10	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-08-31 03:00',	'2015-08-31 03:00',	50	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-09-05 04:00',	'2015-09-05 04:00',	33	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-10-04 05:00',	'2015-10-04 05:00',	74	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-11-03 06:00',	'2015-11-03 06:00',	663	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-11-06 07:00',	'2015-11-06 07:00',	74	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-12-10 08:00',	'2015-12-10 08:00',	88	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2015-12-17 08:00',	'2015-12-17 08:00',	88	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-01-01 09:00',	'2016-01-01 09:00',	99	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-02-27 10:00',	'2016-02-27 10:00',	1021	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-02-28 11:00',	'2016-02-28 11:00',	121)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-03-01 12:00',	'2016-03-01 12:00',	1	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-04-17 13:00',	'2016-04-17 13:00',	1369	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-05-10 13:00',	'2016-05-10 13:00',	142	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-05-10 13:00',	'2016-05-10 13:00',	15	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-06-30 14:00',	'2016-10-30 14:00',	17	)
INSERT [dbo].[TEST1] ([GROUP], [ITEM_ID], [DAILY], [TEST22],[PROD_NAME] , [START_DATETIME], [END_DATETIME], [COLUMN_1])  VALUES('2','Name2','TEST',	'EVENT' ,	'PRODUCT',	'2016-06-10 14:00',	'2016-06-30 14:00',	15	)


SELECT * FROM TEST1

bitsmed · July 6, 2016, 10:45pm

You could do:

declare @start date=cast('2015-07-01' as date);

select [group]
      ,item_id
	  ,sum(case when start_datetime<dateadd(month,1,@start)  and end_datetime>=@start                   then 1 else 0 end) as month01
	  ,sum(case when start_datetime<dateadd(month,2,@start)  and end_datetime>=dateadd(month,1,@start)  then 1 else 0 end) as month02
	  ,sum(case when start_datetime<dateadd(month,3,@start)  and end_datetime>=dateadd(month,2,@start)  then 1 else 0 end) as month03
	  ,sum(case when start_datetime<dateadd(month,4,@start)  and end_datetime>=dateadd(month,3,@start)  then 1 else 0 end) as month04
	  ,sum(case when start_datetime<dateadd(month,5,@start)  and end_datetime>=dateadd(month,4,@start)  then 1 else 0 end) as month05
	  ,sum(case when start_datetime<dateadd(month,6,@start)  and end_datetime>=dateadd(month,5,@start)  then 1 else 0 end) as month06
	  ,sum(case when start_datetime<dateadd(month,7,@start)  and end_datetime>=dateadd(month,6,@start)  then 1 else 0 end) as month07
	  ,sum(case when start_datetime<dateadd(month,8,@start)  and end_datetime>=dateadd(month,7,@start)  then 1 else 0 end) as month08
	  ,sum(case when start_datetime<dateadd(month,9,@start)  and end_datetime>=dateadd(month,8,@start)  then 1 else 0 end) as month09
	  ,sum(case when start_datetime<dateadd(month,10,@start) and end_datetime>=dateadd(month,9,@start)  then 1 else 0 end) as month10
	  ,sum(case when start_datetime<dateadd(month,11,@start) and end_datetime>=dateadd(month,10,@start) then 1 else 0 end) as month11
	  ,sum(case when start_datetime<dateadd(month,12,@start) and end_datetime>=dateadd(month,11,@start) then 1 else 0 end) as month12
  from dbo.test1
 where start_datetime<dateadd(year,1,@start)
   and end_datetime>=@start
 group by [group]
         ,item_id
;

Sameer · July 7, 2016, 1:30am

By using the above SP i am getting results like below which is not matching with actual results?
I missed the column_1 added still no luck for me.

bitsmed · July 7, 2016, 2:14am

My mistake, I thought you canted to Count occurrences, but you actually want to sum column_1. Try this:

declare @start date=cast('2015-07-01' as date);

select [group]
      ,item_id
	  ,sum(case when start_datetime<dateadd(month,1,@start)  and end_datetime>=@start                   then column_1 else 0 end) as month01
	  ,sum(case when start_datetime<dateadd(month,2,@start)  and end_datetime>=dateadd(month,1,@start)  then column_1 else 0 end) as month02
	  ,sum(case when start_datetime<dateadd(month,3,@start)  and end_datetime>=dateadd(month,2,@start)  then column_1 else 0 end) as month03
	  ,sum(case when start_datetime<dateadd(month,4,@start)  and end_datetime>=dateadd(month,3,@start)  then column_1 else 0 end) as month04
	  ,sum(case when start_datetime<dateadd(month,5,@start)  and end_datetime>=dateadd(month,4,@start)  then column_1 else 0 end) as month05
	  ,sum(case when start_datetime<dateadd(month,6,@start)  and end_datetime>=dateadd(month,5,@start)  then column_1 else 0 end) as month06
	  ,sum(case when start_datetime<dateadd(month,7,@start)  and end_datetime>=dateadd(month,6,@start)  then column_1 else 0 end) as month07
	  ,sum(case when start_datetime<dateadd(month,8,@start)  and end_datetime>=dateadd(month,7,@start)  then column_1 else 0 end) as month08
	  ,sum(case when start_datetime<dateadd(month,9,@start)  and end_datetime>=dateadd(month,8,@start)  then column_1 else 0 end) as month09
	  ,sum(case when start_datetime<dateadd(month,10,@start) and end_datetime>=dateadd(month,9,@start)  then column_1 else 0 end) as month10
	  ,sum(case when start_datetime<dateadd(month,11,@start) and end_datetime>=dateadd(month,10,@start) then column_1 else 0 end) as month11
	  ,sum(case when start_datetime<dateadd(month,12,@start) and end_datetime>=dateadd(month,11,@start) then column_1 else 0 end) as month12
  from dbo.test1
 where start_datetime<dateadd(year,1,@start)
   and end_datetime>=@start
 group by [group]
         ,item_id
;

Sameer · July 7, 2016, 3:44am

Hi bitsmed

Great worked like charm...
Thank you alot