选择数年 [英] select with count for years

问题描述

我有一个像这样的皮卡桌

I have pickup table which looks like this

create table Pickup
(
PickupID int IDENTITY,
ClientID int ,
PickupDate date ,
PickupProxy  varchar (200) ,
PickupHispanic bit default 0,
EthnCode varchar(5) ,
CategCode varchar (2) ,
AgencyID int,
Primary Key (PickupID),
);

，其中包含客户的取件 我需要根据此表创建报告，该表应如下所示

and it containe pickups for clients I need to create report based on this table which should looks like this

我知道我需要使用CASE，但真的不知道如何计算年份和计算 每年的平均皮卡数量.以及如何计算特定年份的皮卡.到目前为止，我只有这个

I know i need to use CASE but really do not know how to put years and calculate average pickups for each year. and how to count pickups for specific year.so far i have only this

SELECT
DATEPART(YEAR, PickupDate)as 'Month'

FROM dbo.Pickup 
group by DATEPART(YEAR, PickupDate)
WITH ROLLUP

有什么想法吗?

推荐答案

构造查询的最佳方法是使用索引表.理想情况下，请在您的主数据库中创建此文件，以便它对服务器上的所有数据库都可用，但也可以在本地数据库中创建.

Best way to structure a query like this is to use an Index table. Ideally create this in your master database so it is available to all dbs on the server but can be created in the local db too.

您可以创建一个这样的

create table IndexTable
(
IndexID int NOT NULL,
Primary Key (IndexID),
);

然后用数字1-n填充，其中n足够大，例如1,000,000.

Then fill it with the numbers 1 - n where n is big enough, say 1,000,000.

赞

INSERT IndexTable
VALUES (1)

WHILE (SELECT MAX(IndexID) FROM IndexTable) < 1000000
INSERT IndexTable
SELECT IndexID + (SELECT MAX(IndexID) FROM IndexTable)
FROM IndexTable 

您的查询然后使用此表将月份视为整数

Your query then uses this table to treat months as integers

SELECT DATEADD(month, 0, i.IndexID) Months
      ,COUNT(p.PickupDate)
      ,AVERAGE(*Whatever*)
FROM Pickup p
     INNER JOIN
     IndexTable i ON DATEDIFF(month, p.PickupDate, 0) = i.IndexID
GROUP BY i.IndexID
WITH ROLLUP

这篇关于选择数年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！