Need help in writing small logic for given data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help in writing small logic for given data.

Author

Topic

Ravi0435
Starting Member

47 Posts

Posted - 2011-01-13 : 17:21:47

Hi...
2 items per month are allowed. In the Span of 3 months 6 items are allowed, after 6 items which is 3 month's supply if any items are there next 2 months then those are Errors..Example below check the first 3 lines. 2nd and 3rd lines are wrong as 2 items were supplied on 10/29 & 4 items on 11/26 after 6 items of first line which already received 6 items enough for 3 months.

Need to write a logic to span thru such data and when found change G to Error --> 'E'

fromdate thrudate items status
20081022 20090121 6 G
20081029 20090126 2 G --> E
20081126 20090225 4 G --> E
20090122 20090421 6 G
20090211 20090511 4 G --> E
20090303 20090531 4 G --> E
20090614 20090913 6 G
20090910 20091208 6 G
20090914 20091213 6 G --> E
20091113 20100210 6 G --> E
20091212 20100311 6 G
20091215 20100314 4 G --> E
20100326 20100623 6 G
20100610 20100909 4 G
20100910 20101209 4 G
20101210 20110309 4 G

any suggestion is appreciated.

ravi.

Nothing much that i can do..!!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-13 : 17:34:25

Define Month. Is it a calendar month (e.g., January vs February) or is it any 30 day period of time or other?

Also, is the second line necessarily wrong? Consider the case where the second line items are shipped AFTER the first period has ended.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Ravi0435
Starting Member

47 Posts

Posted - 2011-01-13 : 17:44:32

Thanks Bustaz Kool

Either way its fine...calender or 30day
E --> Since these are additional units during 3 months span as they already recieved that month's supply previously.

fromdate thrudate items status
20081022 20090121 6 G
20081029 20090126 2 G --> E
20081126 20090225 4 G --> E

20090122 20090421 6 G
20090211 20090511 4 G --> E
20090303 20090531 4 G --> E

20090614 20090913 6 G
20090910 20091208 6 G
20090914 20091213 6 G --> E
20091113 20100210 6 G --> E
20091212 20100311 6 G
20091215 20100314 4 G --> E
20100326 20100623 6 G
20100610 20100909 4 G
20100910 20101209 4 G
20101210 20110309 4 G

Nothing much that i can do..!!

Ravi0435
Starting Member

47 Posts

Posted - 2011-01-26 : 17:34:38

Any suggestions please..??

Nothing much that i can do..!!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-26 : 19:07:01

Where is the items table with a single calendar date for the events?

My first thought is to set up a reporting periods table with the 2-month and 6-month time frames

CREATE TABLE Report_Periods
(period_name CHAR(10) NOT NULL PRIMARY KEY,
period_type SMALLINT NOT NULL -- not needed but handy
CHECK (period_type IN (2, 6)),
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date ));

WITH AggregatedEvents (item_name, period_name, two_cnt, six_cnt)
AS
(SELECT E.item_name, R.period_name,
SUM(CASE WHEN period_type = 2 THEN 1 ELSE 0 END) AS two_cnt,
SUM(CASE WHEN period_type = 6 THEN 1 ELSE 0 END) AS six_cnt
FROM Report_Periods AS R, Events AS E
WHERE E.event_date BETWEEN R.period_start_date AND R. period_end_date
GROUP BY E.item_name, R.period_name)

SELECT item_name, period_name,
CASE WHEN two_cnt <= 2 THEN 'G' ELSE 'E'
WHEN six_cnt <= 6 THEN 'G' ELSE 'E'
END AS something_status
FROM AggregatedEvents;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources