question

SimonThompson avatar image
SimonThompson asked

Counting number of Records with a Particular Value

I have a table with many columns and rows, each row has a date (Not the Key) and 24 columns representing the hour of the day. In each field there is a code (N,C,Y,CT etc). I want to know how many of each code exists in a specified day. The code to generate the sample data is here: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tbl_DutyDetails] ( [DD_ID] [int] IDENTITY(1, 1) NOT NULL , [A_ID] [int] NULL , [DD_Date] [date] NULL , [H0] [nvarchar](2) NULL , [H1] [nvarchar](2) NULL , [H2] [nvarchar](2) NULL , [H3] [nvarchar](2) NULL , [H4] [nvarchar](2) NULL , [H5] [nvarchar](2) NULL , [H6] [nvarchar](2) NULL , [H7] [nvarchar](2) NULL , [H8] [nvarchar](2) NULL , [H9] [nvarchar](2) NULL , [H10] [nvarchar](2) NULL , [H11] [nvarchar](2) NULL , [H12] [nvarchar](2) NULL , [H13] [nvarchar](2) NULL , [H14] [nvarchar](2) NULL , [H15] [nvarchar](2) NULL , [H16] [nvarchar](2) NULL , [H17] [nvarchar](2) NULL , [H18] [nvarchar](2) NULL , [H19] [nvarchar](2) NULL , [H20] [nvarchar](2) NULL , [H21] [nvarchar](2) NULL , [H22] [nvarchar](2) NULL , [H23] [nvarchar](2) NULL , [H24] [nvarchar](2) NULL , [AllocatedBlock] [nvarchar](50) NULL , [UpdatedBy] [nvarchar](50) NULL , [UpdatedDate] [datetime] NULL , [Comments] [nvarchar](250) NULL , [Overtime] [decimal](10, 3) NULL , [PBlock] [nvarchar](50) NULL , [SBlock] [nvarchar](50) NULL , [TBlock] [nvarchar](50) NULL , [BlockName] [nvarchar](30) NULL , [DD_UserID] [int] NULL , [Std_Start] [datetime] NULL , [STd_End] [datetime] NULL , CONSTRAINT [PK_Tbl_DutyDetails] PRIMARY KEY CLUSTERED ( [DD_ID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[Tbl_DutyDetails] ON INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 1 , 238 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 453 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 2 , 319 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 463 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 3 , 400 , CAST(0x22380B00 AS DATE) , N'C' , N'C' , N'C' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'C' , N'C' , N'C' , N'C' , N'M' , N'C' , N'C' , NULL , N'C' , NULL , NULL , NULL , NULL , N'C' , NULL , NULL , N'City' , 635 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 4 , 481 , CAST(0x22380B00 AS DATE) , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 947 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 5 , 561 , CAST(0x22380B00 AS DATE) , N'C' , N'C' , N'M' , N'C' , N'C' , N'C' , N'C' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'C' , N'C' , NULL , N'C' , NULL , NULL , NULL , NULL , N'C' , NULL , NULL , N'City' , 1005 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 6 , 640 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'C' , N'C' , N'C' , N'M' , N'C' , N'C' , N'C' , N'C' , N'C' , N'C' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , N'C' , NULL , NULL , NULL , NULL , N'C' , N'N' , NULL , N'City' , 1111 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 7 , 955 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 1643 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 8 , 1035 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'H' , N'H' , N'H' , N'M' , N'H' , N'H' , N'H' , N'H' , N'H' , N'' , N'' , N'' , N'' , N'' , NULL , N'H' , NULL , NULL , NULL , NULL , N'H' , N'S' , NULL , N'Headquarter' , 1926 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 9 , 1115 , CAST(0x22380B00 AS DATE) , N'B' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'B' , N'B' , N'B' , N'M' , N'B' , N'B' , N'B' , N'B' , NULL , N'B' , NULL , NULL , NULL , NULL , N'B' , NULL , NULL , N'Switchboard' , 2222 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 10 , 1196 , CAST(0x22380B00 AS DATE) , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 3333 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 11 , 1276 , CAST(0x22380B00 AS DATE) , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , N'H' , NULL , N'County' , 4022 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 12 , 1355 , CAST(0x22380B00 AS DATE) , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 4043 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 13 , 1433 , CAST(0x22380B00 AS DATE) , N'C' , N'C' , N'M' , N'C' , N'C' , N'C' , N'C' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'C' , N'C' , NULL , N'C' , NULL , NULL , NULL , NULL , N'C' , NULL , NULL , N'City' , 4075 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 14 , 1820 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , NULL , NULL , N'M' , NULL , NULL , NULL , NULL , NULL , NULL , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , N'Unknown' , 4444 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 15 , 1900 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , NULL , NULL , N'M' , NULL , NULL , NULL , NULL , NULL , N'' , N'' , N'' , N'' , N'' , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , N'Unknown' , 5555 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 16 , 1980 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'T' , N'T' , N'T' , N'T' , N'M' , N'T' , N'T' , N'T' , N'T' , N'' , N'' , N'' , N'' , N'' , NULL , N'T' , NULL , NULL , NULL , NULL , N'T' , N'Z' , NULL , N'Unknown' , 7003 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 17 , 2060 , CAST(0x22380B00 AS DATE) , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , N'N' , N'N' , N'M' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 7006 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 18 , 2139 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'H' , N'H' , N'H' , N'H' , N'M' , N'H' , N'H' , N'H' , N'H' , N'' , N'' , NULL , N'H' , NULL , NULL , NULL , NULL , N'H' , N'S' , NULL , N'Headquarter' , 7007 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 19 , 2217 , CAST(0x22380B00 AS DATE) , N'N' , N'N' , N'M' , N'N' , N'N' , N'N' , N'N' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'N' , N'N' , NULL , N'N' , NULL , NULL , NULL , NULL , N'N' , NULL , NULL , N'County' , 7012 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 20 , 2294 , CAST(0x22380B00 AS DATE) , N'T' , N'T' , N'M' , N'T' , N'T' , N'T' , N'T' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'T' , N'T' , NULL , N'T' , NULL , NULL , NULL , NULL , N'T' , N'T' , NULL , N'Unknown' , 7020 , NULL , NULL ) INSERT [dbo].[Tbl_DutyDetails] ( [DD_ID] , [A_ID] , [DD_Date] , [H0] , [H1] , [H2] , [H3] , [H4] , [H5] , [H6] , [H7] , [H8] , [H9] , [H10] , [H11] , [H12] , [H13] , [H14] , [H15] , [H16] , [H17] , [H18] , [H19] , [H20] , [H21] , [H22] , [H23] , [H24] , [AllocatedBlock] , [UpdatedBy] , [UpdatedDate] , [Comments] , [Overtime] , [PBlock] , [SBlock] , [TBlock] , [BlockName] , [DD_UserID] , [Std_Start] , [STd_End] ) VALUES ( 21 , 2605 , CAST(0x22380B00 AS DATE) , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'T' , N'T' , N'T' , N'T' , N'M' , N'T' , N'T' , N'T' , N'T' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , N'' , NULL , N'T' , NULL , NULL , NULL , NULL , N'T' , N'T' , NULL , N'Unknown' , 7039 , NULL , NULL ) SET IDENTITY_INSERT [dbo].[Tbl_DutyDetails] OFF The table had to be structured this way. I would like to achieve an output like this: > Letter H0 H1 H2 H3 H4 H5 H6.... > 04/02/2014 C 123 456 33 22 111 223 555.... > 04/02/2014 T 44 56 56 33 556 566 333.... How can I do this efficiently please? Thank you
sql-server-2008t-sqlaggregates
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm sorry, but I don't understand what you are trying to get out of this. The expected output is different to the test data..... Are you wanting the sum of occurrences of each letter in each column? e.g. (using the test data and only the H0 and H1 columns) : Date | Letter | H0 | H1 '2014-02-03' | C | 3 | 3
1 Like 1 ·

1 Answer

· Write an Answer
svtr avatar image
svtr answered
Well, this ran in 10s for 10 million rows on my local machine, without any indexing. Depending on how many different codes you have, this might work for you. DECLARE @testData TABLE (h0 CHAR(1), h1 CHAR(1), h2 CHAR(1), dt datetime) INSERT INTO @testData VALUES ('c','L','', '20140101') INSERT INTO @testData VALUES ('','D','C','20140101') INSERT INTO @testData VALUES ('','','Q','20140101') INSERT INTO @testData VALUES ('L','C','D','20140102') INSERT INTO @testData VALUES ('L','C','D','20140102') INSERT INTO @testData VALUES ('D','D','C','20140102') INSERT INTO @testData VALUES ('D','','L','20140102') INSERT INTO @testData VALUES ('','C','L','20140102') INSERT INTO @testData ( h0, h1, h2, dt ) SELECT TOP 10000000 TD.* FROM @testData TD CROSS JOIN @testData td2 CROSS join @testData td3 CROSS JOIN @testData TD4 CROSS JOIN @testData TD5 CROSS JOIN @testData TD6 DECLARE @letters TABLE (letter CHAR(1)) insert INTO @letters SELECT ('C') UNION SELECT ('L') UNION SELECT ('D') UNION SELECT ('Q') SELECT L.letter , t.dt , h0 = SUM(CASE WHEN t.h0 = L.letter THEN 1 ELSE 0 END) , h1 = SUM(CASE WHEN t.h1 = L.letter THEN 1 ELSE 0 END) , h2 = SUM(CASE WHEN t.h2 = L.letter THEN 1 ELSE 0 END) FROM @letters L CROSS JOIN @testData t GROUP BY L.letter ,t.dt
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.