Beräkna försäljningsstatistik

Sql Server 2005

Skulle behöva lite försäljningsstatistik från nedan tabeller.

1. Antal sålda för en viss produkt per dag mellan två datum.
2. Antal sålda för en viss produkt per månad mellan två datum.
3. Antal sålda för en viss produkt per år mellan två datum.

Antal = [Orderitem.Quantity]
Produkt = [Orderitem.ProductId = @productid]
Order status = [Order.Status = 600]
Mellan datum = [Order.CreateDate BETWEEN @date1 AND @date2]

Exempel:
2004 - 101
2005 - 54
2006 - 0
2007 - 133

Om det då inte finns något för ett visst år/månad/dag så skall värdet vara 0.

CREATE TABLE [Order] (
	[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[Status] [int] NOT NULL ,
	[CreateDate] [smalldatetime] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [OrderItem] (
	[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[OrderId] [int] NOT NULL ,
	[ProductId] [int] NOT NULL ,
	[Quantity] [int] NOT NULL ,
	[RowTotal] [money] NOT NULL ,
	CONSTRAINT [PK_OrderItem] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Ok, har nu skapat en Kalender tabell.

CREATE TABLE dbo.Calendar  
(  
    dt SMALLDATETIME NOT NULL 
        PRIMARY KEY CLUSTERED
) 
GO

Och fyllt den med några datum.

SET NOCOUNT ON 
DECLARE @dt SMALLDATETIME 
SET @dt = '20070101' 
WHILE @dt < '20270101' 
BEGIN 
    INSERT dbo.Calendar(dt) SELECT @dt 
    SET @dt = @dt + 1 
END

Och kan med förljande kod ta ut per dag.

SELECT	c.dt as [Date], COALESCE(SUM(oi.Quantity),0) as Quantity
FROM	dbo.Calendar as c LEFT OUTER JOIN 
		dbo.[Order] as o ON o.CreateDate >= c.dt
		AND o.CreateDate < dateadd(day,1,c.dt) INNER JOIN 
			dbo.[OrderItem] as oi ON o.Id = oi.OrderId
WHERE	(oi.ProductId = @productid) AND c.dt BETWEEN @startdate AND @enddate
GROUP BY c.dt
ORDER BY c.dt;

Men skulle behöva hjälp med att ta ut per månad och per år.

Per månad

SELECT	year(c.dt), month(c.dt), COALESCE(SUM(oi.Quantity),0) as Quantity
FROM	dbo.Calendar as c LEFT OUTER JOIN 
		dbo.[Order] as o ON o.CreateDate >= c.dt
		AND o.CreateDate < dateadd(day,1,c.dt) INNER JOIN 
			dbo.[OrderItem] as oi ON o.Id = oi.OrderId
WHERE	(oi.ProductId = @productid) AND c.dt BETWEEN @startdate AND @enddate
GROUP BY year(c.dt), month(c.dt)
ORDER BY year(c.dt), month(c.dt)

Och utan month() för per år då.

Men är det möjligt att sätta per månad till bara två kolumner.
Typ:
2007-10 | 22
2007-11 | 4

Ok, lyckades få ihop det med.

SELECT (cast(year(c.dt) as varchar(4)) + '-' + cast(month(c.dt)as varchar(2))), COALESCE(SUM(oi.Quantity),0) as Quantity

Tackar för hjälpen.