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

Откуда:
Сообщений: 166
Добрый день необходимо реализовать на MSSQL вот какую штуку:

Есть таблица dbo.Time в которой забиты все временные характеристики на N лет вперед

CREATE TABLE [dbo].[Time](
	[TheDate] [datetime] NOT NULL,
	[sDate] [nvarchar](6) NULL,
	[TheYear] [int] NULL,
	[theQuarter] [nvarchar](5) NULL,
	[theMonth] [int] NULL,
	[KodeMonth] [nvarchar](5) NULL,
	[NameMonth] [nvarchar](20) NULL,
	[DayOfMonth] [int] NULL,
	[NameDayOfWeek] [nvarchar](20) NULL,
	[theWeek] [int] NULL,
	[KodeWeek] [nvarchar](5) NULL,
	[DayOfWeek] [int] NULL,
	[theWsWeek] [smallint] NULL,
	[KodeWsWeek] [nvarchar](6) NULL,
	[WeekEnd] [nvarchar](1) NULL,
	[Holiday] [nvarchar](1) NULL,
	[Date_char] [nvarchar](10) NULL,
 CONSTRAINT [Time_pk_Time] PRIMARY KEY CLUSTERED 
(
	[TheDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Набор данных на текущую неделю таков:

TheDatesDateTheYeartheQuartertheMonthKodeMonthNameMonthDayOfMonthNameDayOfWeektheWeekKodeWeekDayOfWeektheWsWeekKodeWsWeekWeekEndHolidayDate_char
04.06.201240612201212Q02612M06June4Monday2312W2312412W24NN04.06.2012
05.06.201250612201212Q02612M06June5Tuesday2312W2322412W24NN05.06.2012
06.06.201260612201212Q02612M06June6Wednesday2312W2332412W24NN06.06.2012
07.06.201270612201212Q02612M06June7Thursday2312W2342412W24NN07.06.2012
08.06.201280612201212Q02612M06June8Friday2312W2352412W24NN08.06.2012
09.06.201290612201212Q02612M06June9Saturday2312W2362412W24YN09.06.2012
10.06.2012100612201212Q02612M06June10Sunday2312W2372412W24YN10.06.2012



Но мне необходимо ввести новую временную характеристику. Буду ее называть Period (период)
Период - это конкретная дата. Период определяется для каждой недели. Для одной недели определяется 6 периодов.
Например для текущей и прошлой недели:

theWeekKodeWeekPeriodThedate
2312W23P-107.06.2012
2312W23P-204.06.2012
2312W23P-331.05.2012
2312W23P-428.05.2012
2312W23P-524.05.2012
2312W23P-621.05.2012
2212W22P-131.05.2012
2212W22P-228.05.2012
2212W22P-324.05.2012
2212W22P-421.05.2012
2212W22P-517.05.2012
2212W22P-614.05.2012


Расшифровываю для текущей недели:
P-1 это дата Четверга на текущей неделе, 07.06.2012
P-2 это дата Понедельника на текущей неделе, 07.06.2012 - 3 = 04.06.2012
P-3 это дата Четверга на прошлой неделе, 04.06.2012 - 4 = 31.05.2012
P-4 это дата Понедельника на прошлой неделе,31.05.2012 - 3 = 28.05.2012
P-5 это дата Четверга на позапрошлой неделе, 28.05.2012 - 4 = 24.05.2012
P-6 это дата Понедельника на позапрошлой неделе, 24.05.2012 - 3 = 21.05.2012

И по этой дате отображать данные.
Хочу получить от вас совет. Просчитывать эту характеристику в запросе при выводе данных.
Или завести таблицу такого плана и автозаполнить ее:

CREATE TABLE [forecast].[FA_WPeriods](
	[Period] [int] NULL,
	[Kodeweek] [varchar](5) NULL,
	[TheDate] [datetime] NULL
) ON [PRIMARY]


И если автозаполнить, то попросил бы помощи в написании скрипта заполнения...
Спасибо
7 июн 12, 16:14    [12683177]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
zanderman,

Под определнием в запросе подразумеваю, что то вроде такого

SELECT 
...
CASE Period = 'P-1' WHEN thedate = (select thedate from dbo.time where kodeweek = @kodeweek and NameDayOfWeek = 'Thursday') AS 'P-1' ELSE
CASE Period = 'P-2' WHEN thedate = (select thedate from dbo.time where kodeweek = @kodeweek and NameDayOfWeek = 'Thursday') -3 AS 'P-2'
END END
...    


Где @kodeweek - параметр который будет мне передаваться от приложения.
7 июн 12, 16:32    [12683317]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5124
имхо, однозначно рассчитать заранее (по возможности упихав в ту же таблицу dbo.Time)
sql12:
declare
	@year int=2012
	,@week int=23

select
dateadd(dd,-6,dateadd(ww,@week,datefromparts(@year,1,1))), dateadd(dd,-3,dateadd(ww,@week,datefromparts(@year,1,1))) --текущая неделя
,dateadd(dd,-6,dateadd(ww,@week-1,datefromparts(@year,1,1))), dateadd(dd,-3,dateadd(ww,@week-1,datefromparts(@year,1,1))) --прошлая
,dateadd(dd,-6,dateadd(ww,@week-2,datefromparts(@year,1,1))), dateadd(dd,-3,dateadd(ww,@week-2,datefromparts(@year,1,1))) --позапрошлая
7 июн 12, 17:08    [12683547]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
Дедушка,

спасибо за совет, тоже был склонен к этому, но все же в ту же таблицу запихнуть будет неправильно
по-крайней мере из понятия нормализации данных

Одной неделе соответствует 6 периодов(а может и 100, но не в моей задаче) и они ни есть календарные характеристиками
Я бы их назвал искусственным.
А dbo.Time - это календарь.

Проще сделать отдельную табличку и привязываться по коду недели. То есть для такой то недели - такие то периоды...

Запрос который вы прислали я так понимаю надо выполнять в цикле по всем неделям... еще и параллельно присваивая - Периоды. Я правильно понимаю?
7 июн 12, 17:45    [12683795]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
Забыл, еще конечно будет табличка

CREATE TABLE [forecast].[FA_Periods](
	[Periodid] [int] NULL,
	[Period] [varchar](10) NULL
) ON [PRIMARY]


где

forecast.FA_Periods.Periodid = forecast.FA_WPeriods.Period


cвязь с FA_WPeriods

CREATE TABLE [forecast].[FA_WPeriods](
	[Period] [int] NULL,
	[Kodeweek] [varchar](5) NULL,
	[TheDate] [datetime] NULL
) ON [PRIMARY]
7 июн 12, 17:52    [12683838]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5124
как то так
select
t.theWeek, t.KodeWeek,
case
when DayOfWeek=1 then 'P-1'
when DayOfWeek=2 then 'P-2'
...
end as Period,
case
when DayOfWeek=1 then dateadd(dd,-6,dateadd(ww,theWeek,datefromparts(TheYear,1,1)))
when DayOfWeek=2 then dateadd(dd,-3,dateadd(ww,theWeek,datefromparts(TheYear,1,1)))
...
end as TheDate
from
(
select distinct
TheYear, KodeWeek, theWeek, DayOfWeek
dbo.Time
where
DayOfWeek<7
)t
7 июн 12, 18:00    [12683901]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
Дедушка,

а можно немного подробнее, что вы имели ввиду последним запросом?


и спасибо за консультацию, до идеала уже сам доведу)
7 июн 12, 18:29    [12684012]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5124
zanderman
а можно немного подробнее, что вы имели ввиду последним запросом?
какая часть из него не понятна?
8 июн 12, 10:12    [12685673]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
Дедушка,

Мы сошлись на том, что лучше делать автозаполнение таблицы и брать инфу оттуда.
Надо заполнить таблицу


CREATE TABLE [forecast].[FA_WPeriods](
	[Period] [int] NULL,
	[Kodeweek] [varchar](5) NULL,
	[TheDate] [datetime] NULL
) ON [PRIMARY]


Тоесть для каждого Kodeweek проставить 1-6 Period, и для каждого из них надо определить TheDate по расчету, который описан.
8 июн 12, 11:54    [12686584]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
В запросе разобрался...

Выполняю для теста:

select
t.theWeek, t.KodeWeek,
case
when DayOfWeek=1 then 'P-1'
when DayOfWeek=2 then 'P-2'
end as Period,
case
when DayOfWeek=1 then dateadd(dd,-6,dateadd(ww,theWeek,datefromparts(TheYear,1,1)))
when DayOfWeek=2 then dateadd(dd,-3,dateadd(ww,theWeek,datefromparts(TheYear,1,1)))
end as TheDate
from
(
select distinct
TheYear, KodeWeek, theWeek, DayOfWeek from 
dbo.Time 
where
DayOfWeek<7 and kodeweek = '12W21'
)t


Выдает:


Msg 195, Level 15, State 10, Line 9
'datefromparts' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 't'.


По этому поводу есть линк:

http://www.xpode.com/ShowArticle.aspx?ArticleId=585

Но данное решение не помогло... Подскажите?
11 июн 12, 12:12    [12698828]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
zanderman,

что такое datefromparts?
Вам 1 января нужно что ли? Читайте это: http://msdn.microsoft.com/ru-ru/library/ms180878(v=SQL.100).aspx#UnseparatedStringFormat

Внимательно прочитали? Тогда Вам будет всё понятно:
select
t.theWeek, t.KodeWeek,
case
when DayOfWeek=1 then 'P-1'
when DayOfWeek=2 then 'P-2'
end as Period,
case
when DayOfWeek=1 then dateadd(dd,-6,dateadd(ww,theWeek,str(TheYear)))
when DayOfWeek=2 then dateadd(dd,-3,dateadd(ww,theWeek,str(TheYear)))
end as TheDate
from
(
select distinct
TheYear, KodeWeek, theWeek, DayOfWeek from 
dbo.Time 
where
DayOfWeek<7 and kodeweek = '12W21'
)t
Не совсем понимаю, что такое theWeek и DayOfWeek.
Если Вы - американец, то это одно, а если живёте в России, то другое.
Про номер недели ISO 8601 вообще молчу...
11 июн 12, 12:31    [12698904]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
iap,

по поводу ошибки разобрался, пользуюсь же Sql Serv 2008R2

iap
что такое datefromparts?
Вам 1 января нужно что ли?


Это конструкция вернет, конкретную неделю конкретного года, а не 1 января

dateadd(ww,theWeek,datefromparts(TheYear,1,1))


И отталкиваясь от нее можно найти нужную дату

dateadd(dd,-3,dateadd(ww,theWeek,datefromparts(TheYear,1,1)))


Проблема заключалась только в использовании функции datefromparts в Sql Serv 2008R2

iap
theWeek и DayOfWeek


Я приводил исходный набор данных, что не понятного...номер недели и номер дня недели...

iap
Про номер недели ISO 8601 вообще молчу...


Что вам не нравиться?
11 июн 12, 12:54    [12698956]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
zanderman,

После автозаполнения таблицы периода возникает вопрос, каким образом связать ее с таблицей Тime, а затем с таблице фактов.

Чтобы при выборе конкретной недели, пользователю отображались нужные периоды.

В таблице Time Уникальна - Дата (TheDate), в таблице же FAW_Period - Дата уникальна для конкретной недели...
11 июн 12, 13:30    [12699048]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
zanderman
iap,

по поводу ошибки разобрался, пользуюсь же Sql Serv 2008R2

iap
что такое datefromparts?
Вам 1 января нужно что ли?


Это конструкция вернет, конкретную неделю конкретного года, а не 1 января
В SQL 2008R2 функции datefromparts нет или я ошибаюсь? Она есть только в SQL 2012.
В любом случае в Вашем скрипте вычисляется именно 1 января.
zanderman
iap
theWeek и DayOfWeek


Я приводил исходный набор данных, что не понятного...номер недели и номер дня недели...

iap
Про номер недели ISO 8601 вообще молчу...


Что вам не нравиться?
Если год начинается с воскресенья, то 2 января - это какая неделя - 1-я или 2-я?
Мне не нравится неопределённая постановка задачи.
Неряшливость в этом деле очень дорого стоит.
Например, налицо опасная зависимомть от @@DATEFIRST и @@LANGUAGE.
11 июн 12, 14:42    [12699306]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
iap,

автор
В SQL 2008R2 функции datefromparts нет или я ошибаюсь? Она есть только в SQL 2012.
11 июн 12, 15:06    [12699388]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в решении задачи  [new]
zanderman
Member

Откуда:
Сообщений: 166
iap
В SQL 2008R2 функции datefromparts нет или я ошибаюсь? Она есть только в SQL 2012.


Вы абсолютно правы, ее ввели только в 2012 версии

iap
Если год начинается с воскресенья, то 2 января - это какая неделя - 1-я или 2-я?


Если год начинается с воскресенья (например рубеж 2011 и 2012 года)
То для 1-го января Kodeweek = '11W52' Kodemonth = '12M01'
Это действительно проблемная дата.

iap
Мне не нравится неопределённая постановка задачи.
Неряшливость в этом деле очень дорого стоит.

Задачe я постарался описать максимально точно... насколько возможно...

iap
Например, налицо опасная зависимомть от @@DATEFIRST и @@LANGUAGE.


Я же специально веду таблицу Time, тем самым независим от @@DATEFIRST

Меня интересует такой вопрос, для моей задачи построена и заполнена таблица

CREATE TABLE [forecast].[FA_WPeriods](
	[Period] [int] NULL,
	[Kodeweek] [varchar](5) NULL,
	[TheDate] [datetime] NULL
) ON [PRIMARY]


В которой заложено понятие Period, описанного выше. То есть конкретной дате может соответствовать 1 из 3х Периодов в зависимости от выбранной недели.

Но если у меня есть таблица продаж (фактов), например:

CREATE TABLE dbo.Sales
    (regionid int,
     productid int,
     date datetime,
     dollars money,  
     );


Дата в ней уникальна, относительно regionid и productid.
И в запросе:

SELECT s.thedate,f.kodeweek,f.id
FROM 
(SELECT DISTINCT thedate FROM  dbo.Sales
) s
LEFT JOIN forecast.FA_WPeriods f
ON
s.thedate = f.thedate 


Результирующий набор будет в 3 раза больше чем исходные данные таблицы Sales.
Как этого избежать?
11 июн 12, 15:25    [12699472]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить