SQLTeam.com | Weblogs | Forums

Counting total orders based on min snapshot for the month


#1

Hi,

I am trying to calculate an aggregated column called [Order Pop] as total count using the following fields as partitioning [Accnt_Num], [Customer],[Location] and [Snap_Shot] from the table. I am selecting ONLY the count for the minimum order date for that month. I created this query below to archive this please advise if this approach is correct or there is an alternative way. I have included also below creating the table and inserting date using SQL Server 2008

Thanks

  1. Aggregated column " [Order Pop] "

SELECT [Accnt_Num], [Month],[Year], SUM([Order Pop]) [Order Pop] FROM
(SELECT [Accnt_Num], [Customer],[Location],[Snap_Shot],B.[Month],B.[Year], SUM([Order Pop]) [Order Pop] FROM
(
SELECT [Accnt_Num], [Customer],[Location],
DATENAME(dd, CONVERT(DATE, [Snap_Shot], 121)) AS Day,
DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)) AS Month,
DATENAME(yyyy, CONVERT(DATE, [Snap_Shot], 121)) AS Year,
CONVERT(DATE, [Snap_Shot], 121) [Snap_Shot],
COUNT(*) [Order Pop]
FROM [???].[dbo].[Order]
WHERE DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)) IS NOT NULL
GROUP BY
[Accnt_Num], [Customer],[Location],
DATENAME(dd, CONVERT(DATE, [Snap_Shot], 121)),
DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)),
DATENAME(yyyy, CONVERT(DATE, [Snap_Shot], 121)),
CONVERT(DATE, [Snap_Shot], 121)
) A
INNER JOIN
(SELECT MIN(Day) Day, Month, Year FROM (
SELECT
DATENAME(dd, CONVERT(DATE, [Snap_Shot], 121)) AS Day,
DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)) AS Month,
DATENAME(yyyy, CONVERT(DATE, [Snap_Shot], 121)) AS Year
FROM [???].[dbo].[Order]
WHERE DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)) IS NOT NULL
GROUP BY DATENAME(dd, CONVERT(DATE, [Snap_Shot], 121)),
DATENAME(mm, CONVERT(DATE, [Snap_Shot], 121)),
DATENAME(yyyy, CONVERT(DATE, [Snap_Shot], 121))
)x
group by Month, Year
) B
ON A.[Day] = B.[Day]
AND A.[Month] = B.[Month]
AND A.[Year] = B.[Year]
GROUP BY [Accnt_Num], [Customer],[Location], [Snap_Shot], B.[Month], B.[Year]
) y
group by [Accnt_Num], [Month],[Year]

  1. creating table

CREATE TABLE [dbo].[Order](
[Transactional_Id] [float] NOT NULL,
[Customer] nvarchar NULL,
[Location] nvarchar NULL,
[Accnt_Num] nvarchar NULL,
[Snap_Shot] nvarchar NULL
) ON [PRIMARY]

GO

  1. inserting into the table

INSERT INTO [dbo].[Order]
([Transactional_Id]
,[Customer]
,[Location]
,[Accnt_Num]
,[Snap_Shot])
VALUES
( 4833, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4834, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4835, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4836, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4837, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4838, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4839, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4840, 'Hammersmith', 'Central', '1/BFL901009/4', '20170517'),
( 4841, 'Hammersmith', 'Central', 'MDU137949/1', '20170517'),
( 4842, 'Hammersmith', 'South', 'UnMnown', '20170508'),
( 4843, 'Hammersmith', 'South', 'BMG980336/7', '20170508'),
( 4844, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4845, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4846, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4847, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4848, 'Hammersmith', 'South', 'BMG980336/7', '20170508'),
( 4849, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4850, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4851, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4852, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4853, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4854, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4855, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4856, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4857, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4858, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4859, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4860, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4861, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4862, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4863, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4864, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4865, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4866, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 4867, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4868, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4869, 'Hammersmith', 'South', 'BGM1361006/3', '20170508'),
( 4870, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4871, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4872, 'Hammersmith', 'South', 'BMG980336/7', '20170508'),
( 4873, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 4874, 'Hammersmith', 'Central', 'A90TTMAV700C', '20170517'),
( 4875, 'Hammersmith', 'Central', 'A90TTMAV700C', '20170517'),
( 4876, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4877, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4878, 'Hammersmith', 'South', 'BMR910427/1', '20170508'),
( 4879, 'Hammersmith', 'South', 'ABC60119/1', '20170508'),
( 4880, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4881, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4882, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4883, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4884, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4885, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4886, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 4887, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 4888, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4889, 'Hammersmith', 'South', 'UnMnown', '20170508'),
( 4890, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4891, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4892, 'Hammersmith', 'Central', 'NCD90141/1', '20170523'),
( 4893, 'Hammersmith', 'Central', 'NCD90156/1', '20170523'),
( 4894, 'Hammersmith', 'Central', 'MDU137624/11', '20170517'),
( 4895, 'Hammersmith', 'Central', 'MRC161349/2', '20170517'),
( 4896, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4897, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4898, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4899, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4900, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 4901, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 4902, 'Hammersmith', 'South', 'CSSRET-200', '20170508'),
( 4903, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 4904, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4905, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4906, 'Hammersmith', 'South', 'MRC161290/1', '20170508'),
( 4907, 'Hammersmith', 'South', 'ABC60117/1', '20170508'),
( 4908, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4909, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4910, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4911, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 4912, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4913, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4914, 'Hammersmith', 'South', 'MRC161320/1', '20170508'),
( 4915, 'Hammersmith', 'South', 'MDU137925/31', '20170508'),
( 4916, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4917, 'Hammersmith', 'South', 'MDU137949/1', '20170508'),
( 4918, 'Hammersmith', 'Central', 'MDU137624/1', '20170517'),
( 4919, 'Hammersmith', 'Central', 'MDU137624/11', '20170517'),
( 4920, 'Hammersmith', 'Central', 'NCD90141/1', '20170517'),
( 4921, 'Hammersmith', 'Central', 'MDU137949/1', '20170517'),
( 4922, 'Hammersmith', 'Central', 'MDU137624/11', '20170517'),
( 4923, 'Hammersmith', 'Central', '1/BFL901009/4', '20170517'),
( 4924, 'Hammersmith', 'Central', 'MDU137949/1', '20170517'),
( 4925, 'Hammersmith', 'Central', 'MDU137624/11', '20170517'),
( 4926, 'Hammersmith', 'Central', 'NCD90141/1', '20170517'),
( 4927, 'Hammersmith', 'Central', 'MDU137624/1', '20170517'),
( 4928, 'Hammersmith', 'Central', 'MDU137949/1', '20170517'),
( 4929, 'Hammersmith', 'Central', 'MRC161349/2', '20170517'),
( 4930, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4931, 'Hammersmith', 'South', 'MDU137949/1', '20170508'),
( 4932, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4933, 'Hammersmith', 'Central', 'MRC161254/2', '20170517'),
( 4934, 'Hammersmith', 'Central', 'MDU137624/1', '20170517'),
( 4935, 'Hammersmith', 'Central', 'MDU137624/11', '20170517'),
( 4936, 'Hammersmith', 'Central', 'MRC161349/2', '20170517'),
( 4937, 'Hammersmith', 'Central', 'MRC161349/2', '20170517'),
( 4938, 'Hammersmith', 'Central', 'MRC161349/2', '20170517'),
( 4939, 'Hammersmith', 'South', '16911699', '20170508'),
( 4940, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4941, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4942, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4943, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4944, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4945, 'Hammersmith', 'South', 'MDCU-G0000', '20170508'),
( 4946, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4947, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4948, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4949, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4950, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4951, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4952, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4953, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4954, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4955, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4956, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4959, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4960, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4961, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4962, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4963, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4964, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4965, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4966, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 4967, 'Hammersmith', 'South', 'MRC161290/1', '20170508'),
( 4968, 'Hammersmith', 'South', 'MRC161290/1', '20170508'),
( 4969, 'Hammersmith', 'South', 'MRC161290/1', '20170508'),
( 4970, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4971, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4972, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4973, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 4974, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4975, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4976, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 4977, 'Hammersmith', 'West', 'MRC11856/1', '20170603'),
( 4978, 'Hammersmith', 'West', 'SBNHH-1D45C', '20170528'),
( 4979, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4980, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4981, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4982, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4983, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4984, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4985, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4986, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4987, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4988, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4989, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4990, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4991, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4992, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4993, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4994, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4995, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 4996, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 4997, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4998, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 4999, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5000, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5001, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5002, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5003, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5004, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5005, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5006, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5007, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 5008, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5009, 'Hammersmith', 'South', 'BGM136106/1', '20170508'),
( 5010, 'Hammersmith', 'South', 'BMG980336/7', '20170508'),
( 5011, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 5012, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 5013, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 5014, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5015, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5016, 'Hammersmith', 'South', 'MRC161299/2', '20170508'),
( 5017, 'Hammersmith', 'South', 'CSSRET-200', '20170508'),
( 5018, 'Hammersmith', 'South', 'UnMnown', '20170508'),
( 5019, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5020, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5021, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5022, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5023, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5024, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5025, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5026, 'Hammersmith', 'West', 'MRC161553/1', '20170523'),
( 5027, 'Hammersmith', 'South', 'MDCU-G0000', '20170508'),
( 5028, 'Hammersmith', 'South', 'MDCU-G0000', '20170508'),
( 5029, 'Hammersmith', 'West', 'MRC161456/1', '20170528'),
( 5030, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 5031, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5032, 'Hammersmith', 'South', 'NCD90156/1', '20170508'),
( 5033, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5034, 'Hammersmith', 'West', '1/BFL901009/4', '20170523'),
( 5035, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 5036, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5037, 'Hammersmith', 'South', 'MDU137533/4', '20170508'),
( 5038, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5039, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5040, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5041, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5042, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5043, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5044, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5045, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5046, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5047, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5048, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5049, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5050, 'Hammersmith', 'South', '16910526', '20170508'),
( 5051, 'Hammersmith', 'South', '16910526', '20170508'),
( 5052, 'Hammersmith', 'South', '16910526', '20170508'),
( 5053, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5054, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5055, 'Hammersmith', 'South', '16910526', '20170508'),
( 5056, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5057, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5058, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5059, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5060, 'Hammersmith', 'South', '16910526', '20170508'),
( 5061, 'Hammersmith', 'South', '16910526', '20170508'),
( 5062, 'Hammersmith', 'South', '16910526', '20170508'),
( 5063, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5064, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5065, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5066, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5067, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5068, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5069, 'Hammersmith', 'South', '16910526', '20170508'),
( 5070, 'Hammersmith', 'South', '16910526', '20170508'),
( 5071, 'Hammersmith', 'South', '16910526', '20170508'),
( 5072, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5073, 'Hammersmith', 'South', '16910526', '20170508'),
( 5074, 'Hammersmith', 'West', 'MRC11856/1', '20170603'),
( 5075, 'Hammersmith', 'West', 'MRC11856/1', '20170603'),
( 5076, 'Hammersmith', 'West', 'RETU-CG01', '20170603'),
( 5077, 'Hammersmith', 'West', 'BMR910427/1', '20170603'),
( 5078, 'Hammersmith', 'South', 'MRD901044/1', '20170508'),
( 5079, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5080, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5081, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5082, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5083, 'Hammersmith', 'West', 'BMR910427/1', '20170603'),
( 5084, 'Hammersmith', 'West', 'BMG980336/4', '20170603'),
( 5085, 'Hammersmith', 'West', 'MRC161298/1', '20170603'),
( 5086, 'Hammersmith', 'West', 'MRC161298/1', '20170603'),
( 5087, 'Hammersmith', 'West', 'MRC11856/1', '20170603'),
( 5088, 'Hammersmith', 'West', 'BMG980336/4', '20170603'),
( 5089, 'Hammersmith', 'Central', 'MDCU-G0000', '20170517'),
( 5090, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5091, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5092, 'Hammersmith', 'South', '16910526', '20170508'),
( 5093, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5094, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5095, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5096, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5097, 'Hammersmith', 'South', 'A90TTMAV700C', '20170508'),
( 5098, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5099, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5100, 'Hammersmith', 'South', 'MDCU-G0000', '20170508'),
( 5101, 'Hammersmith', 'South', 'MDCU-G0000', '20170508'),
( 5102, 'Hammersmith', 'West', 'MRC161286/1', '20170528'),
( 5103, 'Hammersmith', 'West', 'MRC161299/2', '20170528'),
( 5104, 'Hammersmith', 'West', 'MRC161349/2', '20170528'),
( 5105, 'Hammersmith', 'West', 'SA-DC0301', '20170528'),
( 5106, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5107, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5108, 'Hammersmith', 'South', 'MRC118050/1', '20170508'),
( 5109, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5110, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5111, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5112, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5113, 'Hammersmith', 'South', 'MRC118050/1', '20170508'),
( 5114, 'Hammersmith', 'South', 'MRD901044/1', '20170508'),
( 5115, 'Hammersmith', 'South', '16911699', '20170508'),
( 5116, 'Hammersmith', 'South', '16911699', '20170508'),
( 5117, 'Hammersmith', 'South', '16911699', '20170508'),
( 5118, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5119, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5120, 'Hammersmith', 'Central', 'MRC161349/2', '20170523'),
( 5121, 'Hammersmith', 'West', 'MRC161286/1', '20170523'),
( 5122, 'Hammersmith', 'West', 'MRC161299/2', '20170523'),
( 5123, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5124, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5125, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5126, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5127, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5128, 'Hammersmith', 'South', 'UnMnown', '20170508'),
( 5129, 'Hammersmith', 'South', 'MDU137925/31', '20170508'),
( 5130, 'Hammersmith', 'South', 'MRC161464/1', '20170508'),
( 5132, 'Hammersmith', 'North', 'MRC11856/1', '20170517'),
( 5133, 'Hammersmith', 'North', 'MRC11856/1', '20170517'),
( 5134, 'Hammersmith', 'West', 'MDDU-G01G11', '20170524'),
( 5135, 'Hammersmith', 'South', 'SBNHH-1D65C', '20170508'),
( 5136, 'Hammersmith', 'South', 'A90TTMAV700C', '20170508'),
( 5138, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5139, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5140, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 5141, 'Hammersmith', 'South', '86010153', '20170508'),
( 5142, 'Hammersmith', 'South', '86010153', '20170508'),
( 5143, 'Hammersmith', 'South', '86010153', '20170508'),
( 5144, 'Hammersmith', 'South', '86010153', '20170508'),
( 5145, 'Hammersmith', 'South', '86010153', '20170508'),
( 5146, 'Hammersmith', 'South', '86010153', '20170508'),
( 5147, 'Hammersmith', 'South', '86010153', '20170508'),
( 5148, 'Hammersmith', 'South', '86010153', '20170508'),
( 5149, 'Hammersmith', 'South', '86010153', '20170508'),
( 5150, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5151, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5152, 'Hammersmith', 'South', 'SBNHH-1D65B', '20170508'),
( 5153, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5154, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5155, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5156, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5157, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5158, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5159, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5160, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5161, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5162, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5163, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5164, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5165, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5166, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5167, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5168, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5169, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5170, 'Hammersmith', 'South', '16910526', '20170508'),
( 5171, 'Hammersmith', 'South', '16910526', '20170508'),
( 5172, 'Hammersmith', 'South', '16910526', '20170508'),
( 5173, 'Hammersmith', 'South', '16910526', '20170508'),
( 5174, 'Hammersmith', 'South', '16910526', '20170508'),
( 5175, 'Hammersmith', 'South', '16910526', '20170508'),
( 5176, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 5177, 'Hammersmith', 'North', 'MDCU-G0000', '20170517'),
( 5178, 'Hammersmith', 'North', 'MDCU-G0000', '20170517'),
( 5179, 'Hammersmith', 'North', 'MRC118050/1', '20170517'),
( 5180, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5181, 'Hammersmith', 'South', '16910526', '20170508'),
( 5182, 'Hammersmith', 'South', '16910526', '20170508'),
( 5183, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5184, 'Hammersmith', 'South', 'BMG980336/7', '20170508'),
( 5185, 'Hammersmith', 'South', 'MRC161456/1', '20170508'),
( 5186, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5187, 'Hammersmith', 'South', 'NCD90141/1', '20170508'),
( 5188, 'Hammersmith', 'South', '1/BFL901009/4', '20170508'),
( 5189, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5190, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5191, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5192, 'Hammersmith', 'South', '16910526', '20170508'),
( 5193, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5194, 'Hammersmith', 'South', 'BMG980336/4', '20170508'),
( 5195, 'Hammersmith', 'South', 'MRC118050/1', '20170508'),
( 5196, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5197, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5198, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5199, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5200, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5201, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5202, 'Hammersmith', 'South', '16910526', '20170508'),
( 5203, 'Hammersmith', 'South', '16910526', '20170508'),
( 5204, 'Hammersmith', 'South', '16910526', '20170508'),
( 5205, 'Hammersmith', 'West', 'MRC161299/2', '20170524'),
( 5206, 'Hammersmith', 'West', 'MRC161299/2', '20170524'),
( 5207, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5208, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5209, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5210, 'Hammersmith', 'South', '16910526', '20170508'),
( 5211, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5212, 'Hammersmith', 'South', '16910526', '20170508'),
( 5213, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5214, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5215, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 5216, 'Hammersmith', 'South', 'ATM3', '20170508'),
( 5217, 'Hammersmith', 'South', 'MRC161349/2', '20170508'),
( 5218, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5219, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5220, 'Hammersmith', 'South', '16910526', '20170508'),
( 5222, 'Hammersmith', 'West', 'MRC161328/3', '20170528'),
( 5223, 'Hammersmith', 'West', 'MRC161328/3', '20170528'),
( 5224, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5225, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5226, 'Hammersmith', 'South', '16910526', '20170508'),
( 5227, 'Hammersmith', 'South', '16910526', '20170508'),
( 5228, 'Hammersmith', 'South', 'MRC118050/1', '20170508'),
( 5229, 'Hammersmith', 'South', 'MRD901044/1', '20170508'),
( 5230, 'Hammersmith', 'South', 'MRD901044/1', '20170508'),
( 5231, 'Hammersmith', 'South', '16911699', '20170508'),
( 5232, 'Hammersmith', 'South', '16911699', '20170508'),
( 5233, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5234, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5235, 'Hammersmith', 'South', 'MRC118034/1', '20170508'),
( 5236, 'Hammersmith', 'South', 'MRC118046/1', '20170508'),
( 5237, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5238, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5239, 'Hammersmith', 'North', 'ATM3', '20170517'),
( 5240, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5241, 'Hammersmith', 'South', 'CSSRET-200____', '20170508'),
( 5242, 'Hammersmith', 'South', 'UnMnown', '20170508'),
( 5243, 'Hammersmith', 'South', 'MDU137624/1', '20170508'),
( 5244, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5245, 'Hammersmith', 'South', 'A90TTMAV700C', '20170508'),
( 5246, 'Hammersmith', 'North', 'ATM3', '20170517'),
( 5247, 'Hammersmith', 'North', 'BMG980336/4', '20170517'),
( 5248, 'Hammersmith', 'North', 'MRC131145/1', '20170517'),
( 5249, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5250, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5251, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5252, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5253, 'Hammersmith', 'South', 'MRC11856/1', '20170508'),
( 5254, 'Hammersmith', 'South', 'MRD901044/1', '20170508'),
( 5255, 'Hammersmith', 'South', '16911699', '20170508'),
( 5256, 'Hammersmith', 'South', '16911699', '20170508'),
( 5257, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5258, 'Hammersmith', 'South', 'MDU137624/11', '20170508'),
( 5259, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5260, 'Hammersmith', 'South', 'BGM136106/1', '20170508'),
( 5261, 'Hammersmith', 'South', 'NFS89973/5', '20170508'),
( 5262, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5265, 'Hammersmith', 'Central', 'BML161174/1', '20170517'),
( 5266, 'Hammersmith', 'North', 'MRC161456/1', '20170508'),
( 5268, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5269, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5270, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5271, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5272, 'Hammersmith', 'North', 'SBNHH-1D65C', '20170508'),
( 5273, 'Hammersmith', 'North', 'MRC11859/2', '20170508'),
( 5274, 'Hammersmith', 'North', 'MRC11859/2', '20170508'),
( 5275, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5276, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5277, 'Hammersmith', 'North', 'BMG980336/4', '20170508'),
( 5278, 'Hammersmith', 'North', 'MRC161286/1', '20170508'),
( 5279, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5280, 'Hammersmith', 'North', 'MRC161299/2', '20170508'),
( 5281, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5282, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5283, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5284, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5285, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5286, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5287, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5288, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5289, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5290, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5291, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5292, 'Hammersmith', 'North', 'MRC161299/2', '20170508'),
( 5293, 'Hammersmith', 'North', 'MRC161299/2', '20170508'),
( 5294, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5295, 'Hammersmith', 'North', 'BMG980336/4', '20170508'),
( 5296, 'Hammersmith', 'North', 'BMG980336/4', '20170508'),
( 5297, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5298, 'Hammersmith', 'North', 'MDCU-G0000', '20170508'),
( 5299, 'Hammersmith', 'North', 'MRC161299/2', '20170508'),
( 5300, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5301, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5302, 'Hammersmith', 'North', 'MRC161299/2', '20170508'),
( 5303, 'Hammersmith', 'North', 'BMG980336/4', '20170508'),
( 5304, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5305, 'Hammersmith', 'North', 'MDU137925/31', '20170508'),
( 5306, 'Hammersmith', 'North', 'BMG980336/4', '20170508'),
( 5307, 'Hammersmith', 'North', 'BMG980336/7', '20170508'),
( 5308, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5309, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5310, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5311, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5312, 'Hammersmith', 'North', 'MRC131145/1', '20170508'),
( 5313, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5314, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5315, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5316, 'Hammersmith', 'North', 'MRY901309/1', '20170508'),
( 5317, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5318, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5319, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5320, 'Hammersmith', 'North', 'ATM3', '20170508'),
( 5321, 'Hammersmith', 'North', 'ATM3', '20170508'),
( 5322, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5323, 'Hammersmith', 'North', 'MRC161349/2', '20170508'),
( 5324, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5325, 'Hammersmith', 'North', 'MRC11856/1', '20170508'),
( 5326, 'Hammersmith', 'North', 'MRC161290/1', '20170508'),
( 5327, 'Hammersmith', 'North', 'BMR910427/1', '20170508'),
( 5328, 'Hammersmith', 'North', 'BMR910427/1', '20170508'),
( 5329, 'Hammersmith', 'North', 'MDU137624/1', '20170508'),
( 5330, 'Hammersmith', 'North', 'MRC11856/1', '20170508')

GO


#2

Try this:

select top(1) with ties
       accnt_num
      ,datename(month,cast(snap_shot as date)) as [month]
      ,substring(snap_shot,1,4) as [year]
      ,count(*) as [order pop]
  from dbo.[order]
 group by customer
         ,[location]
         ,accnt_num
         ,snap_shot
 order by row_number() over(partition by accnt_num
                                        ,substring(snap_shot,1,6)
                                order by snap_shot
                           )
;

#3

Thanks