Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SELECT: Прирост продаж за последний месяц  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Уважаемые форумчане, был недавно на собеседовании, дали следующее задание:

Необходимо спроектирвоать схему БД и один запрос для вычисления прироста продаж за последний месяц.
(отношение продаж текущего месяца к прошлому)

вот что я наваял:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
DROP TABLE [dbo].[Orders]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Models]') AND type in (N'U'))
DROP TABLE [dbo].[Models]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Filials]') AND type in (N'U'))
DROP TABLE [dbo].[Filials]


CREATE TABLE [dbo].[Filials] (Id INT IDENTITY(1,1) PRIMARY KEY, [Name] VARCHAR(MAX))
CREATE TABLE [dbo].[Models] (Id INT IDENTITY(1,1) PRIMARY KEY, [Name] VARCHAR(MAX),[Price] INT)
CREATE TABLE [dbo].[Orders] (Id INT IDENTITY(1,1) PRIMARY KEY, [FilialId] INT REFERENCES [dbo].[Filials](Id),[ModelId] INT REFERENCES [dbo].[Models](Id), Date DATETIME)


INSERT INTO [dbo].[Filials] ([Name])
SELECT 'Минск'
UNION
SELECT 'Брест'


INSERT INTO [dbo].[Models] ([Name],Price)
SELECT 'Model1',100
UNION
SELECT 'Model2',200

INSERT INTO [dbo].[Orders] ([FilialId],[ModelId],[Date])
SELECT 1,1,'20120417'
UNION
SELECT 1,1,'20120317'
UNION
SELECT 1,2,'20120318'
UNION
SELECT 2,2,'20120318'
UNION
SELECT 2,1,'20120418'



DECLARE @Month INT
SET @Month = 4


SELECT p1.Id,p1.[Name], p1.Profit, p2.Profit, CAST(p1.Profit AS FLOAT)/p2.Profit AS Growth
FROM
(SELECT f.Id, f.[Name], IsNull(SUM(m1.Price),0) Profit
FROM dbo.Filials f
LEFT JOIN [dbo].[Orders] o1 ON f.Id=o1.FilialId AND MONTH(o1.Date) = @Month
LEFT JOIN Models m1 ON o1.[ModelId]= m1.Id
GROUP BY f.Id, f.[Name]) p1
JOIN
(SELECT f.Id,f.[Name], IsNull(SUM(m2.Price),0) Profit
FROM dbo.Filials f
LEFT JOIN [dbo].[Orders] o2 ON f.Id=o2.FilialId  AND MONTH(o2.Date) = @Month-1
LEFT JOIN Models m2 ON o2.[ModelId]= m2.Id 
GROUP BY f.Id,f.[Name] ) p2 ON p1.Id = p2.Id


пожалуйста покритикуйте, предложите лучшее решение.
17 апр 12, 02:04    [12424981]     Ответить | Цитировать Сообщить модератору
 Re: SELECT: Прирост продаж за последний месяц  [new]
aleks2
Guest
design21,

DECLARE @Month datetime
SET @Month = '20120301' -- месяцы в разные годы нам же считать не надо?

with
curr_month as
( select o.FilialId, sum(m.Price) Price
	FROM
	Orders o inner join Models m on o.ModelId=m.Id
  WHERE @Month<=o.Date AND o.Date<DATEADD(month, 1, @Month)
  GROUP BY o.FilialId
)
prev_month as
( select o.FilialId, sum(m.Price) Price
	FROM
	Orders o inner join Models m on o.ModelId=m.Id
  WHERE DATEADD(month, -1, @Month)<=o.Date AND o.Date<@Month
  GROUP BY o.FilialId
)
select cm.FilialId, @Month Month, cm.Price/pm.Price Growth
FROM curr_month cm INNER JOIN prev_month pm on cm.FilialId=pm.FilialId
-- и вот тока тут, если вам хоцца имя филиала присобачьте dbo.Filials

-- окромя того, хранение цен у вас кривое. Намекаю: цена - она менятся со временем...
17 апр 12, 06:58    [12425084]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить