datetime column, Day of Week with count, how to add month/yr grouping

  • SELECT count(*)

    ,DATENAME(dw, [myDateTime]) as DOW

    FROM [myDatabase].[dbo].[myTable]

    group by DATENAME(dw, [myDateTime])

    this gets me what i need for like a total for day of week but what would i need to do in order to get like a grouping by month, DOW, Count and a grpuping of Year, DOW, count

    currently i get

    DOW, Count but would also like to get

    Month, DOW, Count and

    Year, DOW, Count

     

     

  • You didn't provide any sample data, so I can't test my code, but something like this should do what you need:

    SELECT COUNT(*) AS DayCount

    ,YEAR(myDateTime) AS Year
    ,MONTH(myDateTime) AS Month
    ,DATENAME(dw, [myDateTime]) as DOW

    FROM [myDatabase].[dbo].[myTable]

    GROUP BY DATENAME(dw, [myDateTime]), YEAR(myDateTime), MONTH(myDateTime) WITH ROLLUP

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Works like a charm. thank you

    I have follow up question though. (we always do) i made a minor change to get the month name but basically same results.

    why am i getting the year,null and null,null records

    SELECT COUNT(*) AS DayCount

    ,YEAR(myDateTime) AS Year

    ,DATENAME(month, [myDateTime]) AS Monthly

    ,DATENAME(dw, [myDateTime]) as DOW

    FROM [myDatabase].[dbo].[myTable]

    GROUP BY DATENAME(dw, [myDateTime]), YEAR([myDateTime]),DATENAME(month, [myDateTime]) WITH ROLLUP

     

    DayCount Year Monthly DOW

    631 2021 July Friday

    173 2021 June Friday

    3 2021 May Friday

    807 2021 NULL Friday

    807 NULL NULL Friday

    1 2021 April Monday

    493 2021 July Monday

    229 2021 June Monday

    2 2021 March Monday

    1 2021 May Monday

    726 2021 NULL Monday

    726 NULL NULL Monday

    39 2021 July Saturday

    13 2021 June Saturday

    52 2021 NULL Saturday

    52 NULL NULL Saturday

    761 2021 July Sunday

    266 2021 June Sunday

    1027 2021 NULL Sunday

    1027 NULL NULL Sunday

    1 2021 January Thursday

    743 2021 July Thursday

    181 2021 June Thursday

    2 2021 March Thursday

    927 2021 NULL Thursday

    927 NULL NULL Thursday

    1 2021 April Tuesday

    679 2021 July Tuesday

    229 2021 June Tuesday

    1 2021 May Tuesday

    910 2021 NULL Tuesday

    910 NULL NULL Tuesday

    2 2021 April Wednesday

    554 2021 July Wednesday

    377 2021 June Wednesday

    2 2021 May Wednesday

    935 2021 NULL Wednesday

    935 NULL NULL Wednesday

    5384 NULL NULL NULL

  • That's from the ROLLUP.

    The one with NULL, NULL is the global dayname total, so all Mondays, Tuesdays, etc., regardless of year, month.

    The one with NULL, YEAR is supposed to be monthly total, but it doesn't look like it's working.  If you can post sample data, I'll correct the code.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • customer, myDateTime

    1854

    2020-06-17 11:09:04.000

    262

    2020-08-14 06:20:39.000

    157

    2020-08-21 11:05:44.000

    319

    2020-08-27 08:39:22.000

    262

    2020-08-28 04:52:56.000

    1217

    2020-09-03 17:11:48.000

    262

    2020-09-04 05:50:05.000

    157

    2020-09-04 16:59:15.000

    157

    2020-09-08 08:13:37.000

    262

    2020-09-11 06:35:46.000

    262

    2020-09-18 07:02:56.000

    1640

    2020-09-22 06:36:18.000

    2376

    2020-09-23 07:33:50.000

    2376

    2020-09-23 07:40:58.000

    2376

    2020-09-23 07:56:15.000

    334

    2020-09-23 17:03:36.000

    1701

    2020-09-24 07:50:25.000

    1701

    2020-09-24 07:51:56.000

    122

    2020-09-24 16:55:29.000

    72

    2020-09-27 09:27:27.000

    510

    2020-09-27 23:59:38.000

    80

    2020-09-28 12:27:36.000

    2014

    2020-09-28 13:42:17.000

    1854

    2020-09-28 14:51:29.000

    733

    2020-09-28 15:07:27.000

    733

    2020-09-28 15:08:52.000

    334

    2020-09-28 17:03:46.000

    1898

    2020-09-29 07:52:32.000

    1898

    2020-09-29 07:55:02.000

    282

    2020-09-29 10:55:37.000

    114

    2020-09-29 13:48:00.000

    827

    2020-09-30 08:26:14.000

    661

    2020-09-30 22:55:12.000

    346

    2020-10-01 10:27:23.000

    1181

    2020-10-01 17:30:01.000

    1279

    2020-10-05 11:29:15.000

    968

    2020-10-05 21:09:56.000

    303

    2020-10-06 17:40:59.000

    1506

    2020-10-06 17:43:31.000

    1506

    2020-10-06 19:28:13.000

    1712

    2020-10-07 06:22:33.000

    734

    2020-10-08 07:46:38.000

    185

    2020-10-08 14:14:03.000

    185

    2020-10-08 14:20:43.000

    1293

    2020-10-08 16:03:19.000

    481

    2020-10-08 17:08:56.000

    1691

    2020-10-09 07:37:46.000

    1279

    2020-10-09 08:00:22.000

    734

    2020-10-09 17:21:07.000

    734

    2020-10-09 17:22:44.000

    1488

    2020-10-09 17:24:13.000

    72

    2020-10-10 10:43:41.000

    1712

    2020-10-12 05:56:42.000

    133

    2020-10-12 16:59:06.000

    133

    2020-10-13 17:04:26.000

    157

    2020-10-14 16:46:04.000

    133

    2020-10-15 17:01:53.000

    185

    2020-10-16 06:44:43.000

    1599

    2020-10-16 08:12:31.000

    157

    2020-10-16 08:14:17.000

    1599

    2020-10-19 10:38:32.000

    242

    2020-10-19 17:13:11.000

    1657

    2020-10-20 07:14:19.000

    1599

    2020-10-20 08:44:22.000

    1985

    2020-10-20 17:27:33.000

    157

    2020-10-21 08:28:51.000

    1477

    2020-10-21 15:33:41.000

    1455

    2020-10-21 17:03:38.000

    1899

    2020-10-21 21:24:20.000

    157

    2020-10-22 08:02:38.000

    202

    2020-10-23 12:35:47.000

    157

    2020-10-23 16:46:05.000

    610

    2020-10-23 17:20:06.000

    157

    2020-10-26 07:35:00.000

    133

    2020-10-26 17:02:04.000

    1150

    2020-10-28 06:57:11.000

    1599

    2020-10-28 08:26:40.000

    612

    2020-10-28 16:16:35.000

    746

    2020-10-28 16:33:18.000

    1150

    2020-10-28 17:01:55.000

    1599

    2020-10-29 08:24:01.000

    1150

    2020-10-29 10:42:03.000

    1150

    2020-10-29 13:38:17.000

    846

    2020-10-29 15:35:46.000

    846

    2020-10-29 15:36:29.000

    454

    2020-10-29 16:06:04.000

    1130

    2020-10-29 16:11:14.000

    1130

    2020-10-29 16:16:41.000

    1130

    2020-10-29 16:18:45.000

    1625

    2020-10-29 16:25:18.000

    612

    2020-10-29 16:26:13.000

    1139

    2020-10-29 16:33:21.000

    1937

    2020-10-29 16:39:59.000

    1870

    2020-10-29 16:40:09.000

    1077

    2020-10-29 16:41:59.000

    1139

    2020-10-29 16:42:34.000

    1937

    2020-10-29 16:48:04.000

    1284

    2020-10-29 16:50:10.000

    490

    2020-10-29 16:50:18.000

    1311

    2020-10-29 16:52:01.000

    554

    2020-10-29 16:58:00.000

    359

    2020-10-29 17:00:48.000

    605

    2020-10-29 17:01:45.000

    699

    2020-10-29 17:02:17.000

    393

    2020-10-29 17:06:29.000

    439

    2020-10-29 17:12:19.000

    129

    2020-10-29 17:14:33.000

    1470

    2020-10-29 17:16:07.000

    193

    2020-10-29 17:16:41.000

    507

    2020-10-29 17:16:55.000

    1532

    2020-10-29 17:21:46.000

    1870

    2020-10-29 17:24:02.000

    1351

    2020-10-29 17:48:51.000

    1883

    2020-10-29 17:50:31.000

    533

    2020-10-29 17:55:57.000

    243

    2020-10-29 17:59:01.000

    714

    2020-10-29 18:01:50.000

    501

    2020-10-29 18:04:04.000

    104

    2020-10-29 18:05:39.000

    758

    2020-10-29 18:30:16.000

    345

    2020-10-29 18:49:43.000

    895

    2020-10-29 18:54:23.000

    1651

    2020-10-29 19:43:51.000

    213

    2020-10-29 19:47:10.000

    627

    2020-10-29 20:28:38.000

    1307

    2020-10-30 02:22:15.000

    2733

    2020-10-30 02:23:35.000

    2350

    2020-10-30 02:26:59.000

    2336

    2020-10-30 02:27:04.000

    2693

    2020-10-30 02:27:33.000

    2715

    2020-10-30 02:27:47.000

    2257

    2020-10-30 02:28:52.000

    2275

    2020-10-30 02:32:06.000

    2393

    2020-10-30 02:32:34.000

    2036

    2020-10-30 02:33:05.000

    2686

    2020-10-30 02:35:46.000

    2746

    2020-10-30 02:35:46.000

    2676

    2020-10-30 02:35:46.000

    2271

    2020-10-30 02:40:16.000

    2436

    2020-10-30 02:40:23.000

    2398

    2020-10-30 02:42:05.000

    2413

    2020-10-30 02:42:12.000

    752

    2020-10-30 02:42:41.000

    2406

    2020-10-30 02:42:49.000

    856

    2020-10-30 05:00:14.000

    127

    2020-10-30 05:08:59.000

    4

    2020-10-30 05:18:02.000

    1265

    2020-10-30 05:18:29.000

    1960

    2020-10-30 05:19:28.000

    1745

    2020-10-30 05:20:08.000

    1074

    2020-10-30 05:20:49.000

    2099

    2020-10-30 05:20:52.000

    1991

    2020-10-30 05:21:28.000

    1822

    2020-10-30 05:23:38.000

    882

    2020-10-30 05:24:50.000

    1271

    2020-10-30 05:27:56.000

    119

    2020-10-30 05:31:10.000

    58

    2020-10-30 05:32:01.000

    2106

    2020-10-30 05:32:12.000

    2269

    2020-10-30 05:32:21.000

    2134

    2020-10-30 05:32:23.000

    2035

    2020-10-30 05:32:38.000

    134

    2020-10-30 05:32:45.000

    2166

    2020-10-30 06:00:42.000

    1544

    2020-10-30 06:10:48.000

    794

    2020-10-30 06:21:31.000

    638

    2020-10-30 06:23:02.000

    1121

    2020-10-30 06:26:10.000

    2315

    2020-10-30 06:28:40.000

    441

    2020-10-30 06:31:32.000

    475

    2020-10-30 06:35:54.000

    220

    2020-10-30 06:40:01.000

    185

    2020-10-30 06:46:56.000

    370

    2020-10-30 06:47:34.000

    600

    2020-10-30 06:58:07.000

    593

    2020-10-30 07:00:28.000

    230

    2020-10-30 07:02:37.000

    665

    2020-10-30 07:04:35.000

    1559

    2020-10-30 07:05:28.000

    128

    2020-10-30 07:06:05.000

    1986

    2020-10-30 07:06:57.000

    774

    2020-10-30 07:08:04.000

    415

    2020-10-30 07:08:31.000

    341

    2020-10-30 07:11:25.000

    549

    2020-10-30 07:13:41.000

    1484

    2020-10-30 07:16:06.000

    1685

    2020-10-30 07:16:21.000

    371

    2020-10-30 07:16:35.000

    2414

    2020-10-30 07:19:43.000

    186

    2020-10-30 07:21:09.000

    684

    2020-10-30 07:21:47.000

    317

    2020-10-30 07:23:33.000

    232

    2020-10-30 07:25:23.000

    1599

    2020-10-30 07:28:18.000

    294

    2020-10-30 07:29:33.000

    2228

    2020-10-30 07:32:18.000

    250

    2020-10-30 07:32:48.000

    1010

    2020-10-30 07:35:43.000

    2043

    2020-10-30 07:37:25.000

    254

    2020-10-30 07:44:16.000

  • Hmm, decent start, but I can't query against it.

    I need an actual INSERT statement, that works, like this:

    CREATE TABLE #data ( customer int NOT NULL, myDateTime datetime NULL );

    INSERT INTO #data VALUES

    (1854,'2020-06-17 11:09:04.000'),

    (262, '2020-08-14 06:20:39.000'),

    ...,

    ...,

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • sorry had a meeting to attend

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[myTable](

    [customer] [int] NOT NULL,

    [myDateTime] [datetime] NOT NULL,

    CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED

    (

    [customer] ASC,

    [myDateTime] 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

    there is create will gather up insert

     

  • INSERT INTO [dbo].[myTable]

    ([customer]

    ,[myDateTime]

    VALUES

    (1 ,'2020-11-20 17:29:56')

    (1 ,'2021-01-04 17:27:34')

    (1 ,'2021-01-08 17:33:16')

    (1 ,'2021-02-04 17:34:35')

    (1 ,'2021-02-11 17:19:47')

    (1 ,'2021-02-12 07:13:43')

    (1 ,'2021-02-12 07:16:53')

    (1 ,'2021-03-09 17:27:57')

    (1 ,'2021-03-30 17:26:59')

    (1 ,'2021-04-09 17:13:36')

    (1 ,'2021-04-30 17:37:40')

    (1 ,'2021-06-29 17:51:59')

    (1 ,'2021-07-12 17:24:59')

    (1 ,'2021-07-22 13:42:57')

    (2 ,'2020-11-09 18:31:15')

    (2 ,'2021-01-10 14:13:26')

    (2 ,'2021-02-03 11:15:14')

    (2 ,'2021-03-12 18:44:32')

    (2 ,'2021-05-19 17:51:05')

    (2 ,'2021-06-07 15:35:39')

    (2 ,'2021-06-08 13:29:20')

    (2 ,'2021-06-08 13:34:16')

    (2 ,'2021-06-08 13:39:00')

    (2 ,'2021-06-08 13:41:24')

    (2 ,'2021-06-08 13:53:14')

    (4 ,'2020-10-30 05:18:02')

    (4 ,'2020-11-01 03:03:31')

    (4 ,'2020-11-08 03:05:29')

    (4 ,'2020-11-08 14:44:41')

    (4 ,'2020-11-15 03:03:49')

    (4 ,'2020-11-22 03:03:57')

    (4 ,'2020-11-29 03:03:47')

    (4 ,'2020-11-29 14:52:51')

    (4 ,'2020-12-27 03:03:23')

    (4 ,'2021-01-03 03:03:54')

    (4 ,'2021-01-10 15:35:26')

    (4 ,'2021-01-24 17:11:20')

    (4 ,'2021-01-28 21:13:06')

    (4 ,'2021-01-29 00:02:30')

    (4 ,'2021-01-31 03:03:16')

    (4 ,'2021-02-07 03:07:04')

    (4 ,'2021-02-14 03:03:20')

    (4 ,'2021-02-21 03:05:04')

    (4 ,'2021-02-28 03:03:41')

    (4 ,'2021-03-07 03:03:15')

    (4 ,'2021-03-14 03:04:05')

    (4 ,'2021-03-21 03:04:06')

    (4 ,'2021-03-28 03:03:01')

    (4 ,'2021-03-30 20:54:50')

    (4 ,'2021-04-04 03:03:04')

    (4 ,'2021-04-11 03:03:11')

    (4 ,'2021-04-18 03:04:18')

    (4 ,'2021-04-25 03:03:14')

    (4 ,'2021-04-26 21:19:02')

    (4 ,'2021-05-02 03:03:29')

    (4 ,'2021-05-09 03:03:32')

    (4 ,'2021-05-16 03:03:23')

    (4 ,'2021-05-23 03:03:04')

    (4 ,'2021-05-30 03:03:04')

    (4 ,'2021-06-06 03:02:53')

    (4 ,'2021-06-13 03:02:58')

    (4 ,'2021-06-20 03:03:04')

    (4 ,'2021-06-27 03:03:06')

    (4 ,'2021-07-04 03:03:10')

    (4 ,'2021-07-11 03:03:27')

    (4 ,'2021-07-18 03:03:08')

    (5 ,'2020-11-01 03:05:40')

    (5 ,'2020-11-08 03:04:15')

    (5 ,'2020-11-08 14:32:24')

    (5 ,'2020-11-15 03:03:13')

    (5 ,'2020-11-22 03:03:16')

    (5 ,'2020-11-29 03:04:05')

    (5 ,'2020-11-29 14:22:11')

    (5 ,'2020-12-27 03:04:41')

    (5 ,'2021-01-24 03:04:59')

    (5 ,'2021-01-24 14:56:55')

    (5 ,'2021-01-24 17:06:31')

    (5 ,'2021-01-28 21:07:49')

    (5 ,'2021-01-28 22:29:45')

    (5 ,'2021-01-31 03:03:12')

    (5 ,'2021-02-07 03:08:21')

    (5 ,'2021-02-14 03:03:02')

    (5 ,'2021-02-21 03:03:41')

    (5 ,'2021-02-28 03:03:07')

    (5 ,'2021-03-07 03:22:17')

    (5 ,'2021-03-14 03:15:06')

    (5 ,'2021-03-21 04:02:24')

    (5 ,'2021-03-28 03:14:32')

    (5 ,'2021-03-30 21:00:47')

    (5 ,'2021-03-30 21:49:28')

    (5 ,'2021-04-04 03:15:01')

    (5 ,'2021-04-11 03:13:11')

    (5 ,'2021-04-18 03:45:45')

    (5 ,'2021-04-25 03:23:32')

    (5 ,'2021-04-26 21:44:52')

    (5 ,'2021-05-02 03:03:32')

    (5 ,'2021-05-09 03:05:52')

    (5 ,'2021-05-23 03:43:47')

    (5 ,'2021-05-30 03:02:54')

    (5 ,'2021-06-06 03:04:51')

    (5 ,'2021-06-13 03:02:56')

    (5 ,'2021-06-20 03:02:36')

    (5 ,'2021-06-27 03:02:41')

    (5 ,'2021-07-04 03:03:20')

    (5 ,'2021-07-11 03:02:39')

    (5 ,'2021-07-18 03:03:07')

    (6 ,'2020-11-17 17:46:54')

    (6 ,'2021-01-28 16:38:48')

    (6 ,'2021-02-02 17:02:18')

    (6 ,'2021-02-03 17:15:32')

    (6 ,'2021-02-08 15:44:16')

    (6 ,'2021-02-09 17:23:38')

    (6 ,'2021-02-24 17:30:50')

    (6 ,'2021-03-03 17:25:59')

    (6 ,'2021-03-26 17:10:56')

    (6 ,'2021-03-26 17:51:54')

    (6 ,'2021-03-31 16:19:12')

    (6 ,'2021-04-01 07:35:42')

    (6 ,'2021-04-05 10:50:49')

    (6 ,'2021-04-09 17:20:22')

    (6 ,'2021-04-09 17:20:50')

    (6 ,'2021-04-10 07:26:56')

    (6 ,'2021-04-15 11:03:36')

    (6 ,'2021-05-07 14:58:46')

    (6 ,'2021-05-13 17:47:21')

    (6 ,'2021-05-13 17:47:50')

    (6 ,'2021-05-28 17:13:50')

    (6 ,'2021-06-04 17:19:39')

    (6 ,'2021-06-04 17:19:46')

    (6 ,'2021-06-22 11:07:52')

    (6 ,'2021-07-07 17:26:58')

    (6 ,'2021-07-09 17:13:53')

    (6 ,'2021-07-15 13:40:28')

    (6 ,'2021-07-20 17:47:08')

    (7 ,'2020-11-13 07:21:39')

    (7 ,'2021-01-25 07:51:27')

    (7 ,'2021-01-29 07:39:20')

    (7 ,'2021-02-24 17:22:00')

    (7 ,'2021-03-01 07:24:17')

    (7 ,'2021-03-30 07:30:48')

    (7 ,'2021-03-31 08:57:02')

    (7 ,'2021-03-31 08:57:10')

    (7 ,'2021-04-08 07:17:24')

    (7 ,'2021-04-08 07:19:44')

    (7 ,'2021-04-14 07:09:45')

    (7 ,'2021-04-26 07:16:34')

    (7 ,'2021-05-03 07:34:41')

    (7 ,'2021-05-04 07:31:05')

    (7 ,'2021-05-07 06:59:33')

    (7 ,'2021-05-19 07:29:41')

    (7 ,'2021-05-20 13:39:14')

    (7 ,'2021-06-08 06:53:54')

    (7 ,'2021-06-08 06:54:01')

    (7 ,'2021-06-08 06:58:13')

    (7 ,'2021-07-02 07:21:20')

    (8 ,'2020-11-03 18:39:27')

    (8 ,'2020-11-03 18:40:50')

    (8 ,'2020-11-03 18:42:34')

    (8 ,'2020-11-03 18:42:42')

    (8 ,'2021-02-01 08:09:32')

    (8 ,'2021-02-01 08:39:24')

    (8 ,'2021-02-01 10:17:45')

    (8 ,'2021-02-02 07:54:48')

    (8 ,'2021-02-02 07:57:43')

    (8 ,'2021-02-02 07:57:55')

    (8 ,'2021-02-02 08:45:20')

    (8 ,'2021-04-13 20:15:59')

    (8 ,'2021-04-19 08:04:21')

    (8 ,'2021-04-19 10:32:59')

    (8 ,'2021-04-19 10:43:10')

    (8 ,'2021-04-19 10:45:57')

    (8 ,'2021-04-19 10:50:22')

    (8 ,'2021-04-19 10:54:41')

    (8 ,'2021-04-19 11:09:42')

    (8 ,'2021-04-26 08:40:55')

    (8 ,'2021-04-29 13:32:23')

    (8 ,'2021-04-29 14:33:24')

    (8 ,'2021-04-29 14:33:41')

    (8 ,'2021-04-29 14:55:13')

    (8 ,'2021-04-29 15:14:10')

    (8 ,'2021-04-29 15:15:56')

    (8 ,'2021-05-25 21:53:34')

    (8 ,'2021-06-16 13:06:24')

    (8 ,'2021-06-16 18:17:16')

    (9 ,'2020-11-04 12:49:46')

    (9 ,'2020-11-04 12:49:59')

    (10 ,'2021-02-22 10:24:43')

    (10 ,'2021-02-22 10:29:30')

    (10 ,'2021-02-22 15:46:09')

    (10 ,'2021-02-22 16:03:06')

    (10 ,'2021-03-15 07:53:38')

    (10 ,'2021-04-16 07:55:21')

    (10 ,'2021-06-04 07:54:20')

    (10 ,'2021-07-21 07:50:50')

    (11 ,'2020-10-30 07:59:40')

    (11 ,'2020-11-09 08:04:17')

    (11 ,'2020-11-12 08:01:01')

    (11 ,'2020-11-16 07:58:31')

    (11 ,'2020-11-17 08:04:33')

    (11 ,'2020-11-30 08:07:52')

    GO

  • Thanks for the test data!  Sorry if I seemed difficult, but I help on up to dozens qs a day, and I just don't have to do data prep for all the qs.

    See if this gives you something better:

    SELECT 
    COUNT(*) AS DayCount
    ,ISNULL(myYear, 'All') AS Year
    ,ISNULL(myMonth, 'All') AS Month
    ,DOW
    FROM dbo.MyTable

    CROSS APPLY (
    SELECT CAST(YEAR(myDateTime) AS varchar(5)) AS myYear,
    CAST(MONTH(myDateTime) AS varchar(5)) AS myMonth,
    DATENAME(dw, [myDateTime]) as DOW
    ) AS ca1

    GROUP BY DOW, myYear, myMonth WITH ROLLUP
    HAVING GROUPING(DOW) = 0
    ORDER BY DOW, myYear, myMonth

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Personally I think I'd rather see the days as columns in the same row, i.e. a column for Monday, a column for Tuesday, etc.., with one per month and one row per year.  But that's just my personal preference, of course, so use whatever format you like/need.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • as for the format of this data i am going to be using it for a bar/line chart.

    basically 3 different ways.

    bar chart with just sunday thru saturday and totals for each day.

    then another line chart with left axis as months of the year (no year needed) and again bottom axis as sun - sat

    next chart is left axis years and bottom axis sun - sat.

    our clients can run a particular process whenever they want and we gather that info back and mgmt was asking for when does this occur the most during the week and if it was different for various months/years

    by the way that newest query gives me all the info i need now i can just tweek it as needed. so thank you very much

  • ... and using that same datetime column i am going to do same with the time of day part grouping into 0 - 24 hour 1 hour increments by DOW, Month, Years

    basically they are looking at any trends by clients so we can best determine when we should be making any changes to our ftp server or when not to try anything.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply