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 2005 Forums
 Transact-SQL (2005)
 Best ship date selection question

Author  Topic 

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 12:12:41
Bonjour beautiful people,

Given the following set of data, FromDate ToDate represents valid ship dates (Mon-Fri). This is considered a ship week. What I want to do is if in a given ship week PYesNo is true (1) give me the date with the smallest (MIN) PMinutes. But if even one day in that week has PYesNo False (0) I do not want that week. In other words I only want a week that has all PYesNo = 1, if so give me the ToDate with the smallest PMinutes (process Minutes)

FromDate	ToDate	      AvlMin	PMinutes      InclYesNo  PYesNo
4/20/2009	4/20/2009	846	114.646	       1	1
4/20/2009	4/21/2009	1808	116.29	       1	1
4/20/2009	4/22/2009	2770	117.385	       1	1
4/20/2009	4/23/2009	3732	120.668	       1	1
4/20/2009	4/24/2009	4694	238.028	       1	1
4/20/2009	4/20/2009	846	111.904	       0	1
4/20/2009	4/21/2009	1808	116.29	       1	1
4/20/2009	4/22/2009	2770	117.385	       1	1
4/20/2009	4/23/2009	3732	120.668	       1	1
4/20/2009	4/24/2009	4694	238.028	       1	1
4/20/2009	4/20/2009	846	111.904	       0	1
4/20/2009	4/21/2009	1808	113.549	       0	1
4/20/2009	4/22/2009	2770	117.385	       1	1
4/20/2009	4/23/2009	3732	120.668	       1	1
4/20/2009	4/24/2009	4694	238.028	       1	1
4/20/2009	4/20/2009	846	111.904	       0	1
4/20/2009	4/21/2009	1808	113.549	       0	1
4/20/2009	4/22/2009	2770	114.646	       0	1
4/20/2009	4/23/2009	3732	120.668	       1	1
4/20/2009	4/24/2009	4694	238.028	       1	1
4/20/2009	4/20/2009	846	111.904	       0	1
4/20/2009	4/21/2009	1808	113.549	       0	1
4/20/2009	4/22/2009	2770	114.646	       0	1
4/20/2009	4/23/2009	3732	117.933	       0	1
4/20/2009	4/24/2009	4694	238.028	       1	1


This is what I have now and it is not happening it always picks Monday

SELECT TOP 1 ToDate,
MinProcessMinutes
FROM (
SELECT ToDate,
MIN(ProcessMinutes) AS MinProcessMinutes
FROM #GanttAlgorithm AS g
WHERE OrderedIncludedYesNo = 1
AND AbleToProcessYesNo = 1
AND ToDate >= DATEDIFF(dd,0, GETDATE())
GROUP BY ToDate) b
ORDER BY MinProcessMinutes
Thanks!!!!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 12:27:27
The PMinutes seems to be cumulative, so the smallest values are always on mondays.

EDIT: Topic is continued from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123885

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 12:41:46
Peso,
PMinutes (process minutes of current order plus all other orders from FromDate to ToDate) is calculated. There could be instances within the week where PMinutes is greater than AvlMin, in which case PYesNo will be False.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 13:09:31
another example


FromDate	ToDate	        AvlMin	Pmin	        InclYesNo	PYesNo
4/20/2009	4/20/2009	846	8.478	        1	        1
4/20/2009	4/21/2009	1808	10.637	        1	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	10.637	        1	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5136.849	0	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5136.849	0	        0
4/20/2009	4/23/2009	3732	5140.397	0	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0


<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 13:27:05
There is no week with all PYesNo set to 1.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 13:38:29
how can I express that in a query? because the one I am trying is problematic

SELECT TOP 1 ToDate,
MinProcessMinutes
FROM (
SELECT ToDate,
MIN(ProcessMinutes) AS MinProcessMinutes
FROM #GanttAlgorithm AS g
WHERE OrderedIncludedYesNo = 1
AND AbleToProcessYesNo = 1
AND ToDate >= DATEDIFF(dd,0, GETDATE())
GROUP BY ToDate) b
ORDER BY MinProcessMinutes

Thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 13:42:12
Alrighty then, what is the expected output from the sample data posted 04/23/2009 : 12:12:41 ?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 13:45:56
the expected output will be
EstimatedShipDate = 4/20/2009. Because the whole week has PYesNo = 1 and the smalles PMinutes is on ToDate 4/20/2009

FromDate ToDate AvlMin PMinutes InclYesNo PYesNo
4/20/2009 4/20/2009 846 114.646 1 1


<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 13:52:42
But the 20th is a monday...


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 14:03:49
Ja

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 14:41:05
In your example, all smallest values occurs on a monday, so you will monday back wether you like it or not.
What am I missing?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 14:53:58
yes in the first example you are absolutely correct. But in the second example PYesNo is the key. True Monday has the smalles values, and they always will but the key you are missing is PYesNo. In any week, if any one of the days in that week have PYesNo = 0 then week disqualifies. How do I go about doing a query that says
if all days have PYesNo = 1 then give me smallest day (which most of the time it will be on Monday but not always, because weekends are not ship date for all branches in our company so there are enough minutes ti usually do it on Mondays)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 14:58:07
Ok, so in the example posted 04/23/2009 : 13:09:31
you want this back?
FromDate	ToDate	      AvlMin	PMinutes      InclYesNo  PYesNo
4/20/2009	4/20/200      846       4.484	      0	          1



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 15:22:56

I am sorry Peso, but in that week there are some days where PYesNo = 0, so it shoud bring back no results. Maybe I was not explaining it clearly.

Thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 15:26:48
You define all these records as one week?
FromDate	ToDate	        AvlMin	Pmin	        InclYesNo	PYesNo
4/20/2009	4/20/2009	846	8.478	        1	        1
4/20/2009	4/21/2009	1808	10.637	        1	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
In that case, there is no week in your second sample data set which meets the criteria.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 15:38:19
Yes I do define those as one Ship week. As you say "there is no week in your second sample data set which meets the criteria" but how do I do it in a query so I get 0 rows. But the query I posted bring back the Monday. The problem is with my query, how do I resolve to be accurate?

Thanks

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 15:44:36
What do you have to identity the different "group" of week records?

FromDate	ToDate	        AvlMin	Pmin	        InclYesNo	PYesNo
4/20/2009	4/20/2009	846	8.478	        1	        1
4/20/2009	4/21/2009	1808	10.637	        1	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	10.637	        1	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5139.383	1	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5136.849	0	        0
4/20/2009	4/23/2009	3732	5142.929	1	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0
4/20/2009	4/20/2009	846	4.484	        0	        1
4/20/2009	4/21/2009	1808	6.967	        0	        1
4/20/2009	4/22/2009	2770	5136.849	0	        0
4/20/2009	4/23/2009	3732	5140.397	0	        0
4/20/2009	4/24/2009	4694	5253.371	1	        0


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 15:59:02
Aha the missing piece of the puzzle that would be OrderedVirtualDate, it is the field that identifies the different "group" of week records.

VirtualDate	FromDate    ToDate	AvlMin	PMin	OIYesNo	AYesNo
4/27/2009	4/27/2009   4/27/2009	846	64.059   1	1
4/27/2009	4/27/2009   4/28/2009	1808	131.037	 1	1
4/27/2009	4/27/2009   4/29/2009	2770	258.765	 1	1
4/27/2009	4/27/2009   4/30/2009	3732	5366.328 1	0
4/27/2009	4/27/2009   5/1/2009	4694	5370.381 1	0
4/28/2009	4/27/2009   4/27/2009	846	61.221	 0	1
4/28/2009	4/27/2009   4/28/2009	1808	131.037	 1	1
4/28/2009	4/27/2009   4/29/2009	2770	258.765	 1	1
4/28/2009	4/27/2009   4/30/2009	3732	5366.328 1	0
4/28/2009	4/27/2009   5/1/2009	4694	5370.381 1	0
4/29/2009	4/27/2009   4/27/2009	846	61.221	 0	1
4/29/2009	4/27/2009   4/28/2009	1808	128.313	 0	1
4/29/2009	4/27/2009   4/29/2009	2770	258.765	 1	1
4/29/2009	4/27/2009   4/30/2009	3732	5366.328 1	0
4/29/2009	4/27/2009   5/1/2009	4694	5370.381 1	0
4/30/2009	4/27/2009   4/27/2009	846	61.221	 0	1
4/30/2009	4/27/2009   4/28/2009	1808	128.313	 0	1
4/30/2009	4/27/2009   4/29/2009	2770	256.109	 0	1
4/30/2009	4/27/2009   4/30/2009	3732	5366.328 1	0
4/30/2009	4/27/2009   5/1/2009	4694	5370.381 1	0
5/1/2009	4/27/2009   4/27/2009	846	61.221	 0	1
5/1/2009	4/27/2009   4/28/2009	1808	128.313	 0	1
5/1/2009	4/27/2009   4/29/2009	2770	256.109	 0	1
5/1/2009	4/27/2009   4/30/2009	3732	5363.796 0	0
5/1/2009	4/27/2009   5/1/2009	4694	5370.381 1	0



Thanks

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 16:07:39
Well.. It do make a difference!



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-04-23 : 16:09:24
Peso

This is from the suggestion you gave me to do a Gantt Algorithm. This is the poor man's version of Gantt algorithm. I think. It moves the current order's date virtually to each days of the Ship week and checks to see if it negatively affects any other orders.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 16:17:30
Yes, I remember now I suggested a Gantt algorithm to solve your case.
DECLARE	@Sample TABLE
	(
		VirtualDate DATETIME,
		FromDate DATETIME,
		ToDate DATETIME,
		AvlMin MONEY,
		PMin MONEY,
		OIYesNo BIT,
		AYesNo BIT
	)

INSERT	@Sample
SELECT	'4/27/2009', '4/27/2009', '4/27/2009', 846, 64.059, 1, 1 UNION ALL
SELECT	'4/27/2009', '4/27/2009', '4/28/2009', 1808, 131.037, 1, 1 UNION ALL
SELECT	'4/27/2009', '4/27/2009', '4/29/2009', 2770, 258.765, 1, 1 UNION ALL
SELECT	'4/27/2009', '4/27/2009', '4/30/2009', 3732, 5366.328, 1, 0 UNION ALL
SELECT	'4/27/2009', '4/27/2009', '5/1/2009', 4694, 5370.381, 1, 0 UNION ALL
SELECT	'4/28/2009', '4/27/2009', '4/27/2009', 846, 61.221, 0, 1 UNION ALL
SELECT	'4/28/2009', '4/27/2009', '4/28/2009', 1808, 131.037, 1, 1 UNION ALL
SELECT	'4/28/2009', '4/27/2009', '4/29/2009', 2770, 258.765, 1, 1 UNION ALL
SELECT	'4/28/2009', '4/27/2009', '4/30/2009', 3732, 5366.328, 1, 0 UNION ALL
SELECT	'4/28/2009', '4/27/2009', '5/1/2009', 4694, 5370.381, 1, 0 UNION ALL
SELECT	'4/29/2009', '4/27/2009', '4/27/2009', 846, 61.221, 0, 1 UNION ALL
SELECT	'4/29/2009', '4/27/2009', '4/28/2009', 1808, 128.313, 0, 1 UNION ALL
SELECT	'4/29/2009', '4/27/2009', '4/29/2009', 2770, 258.765, 1, 1 UNION ALL
SELECT	'4/29/2009', '4/27/2009', '4/30/2009', 3732, 5366.328, 1, 0 UNION ALL
SELECT	'4/29/2009', '4/27/2009', '5/1/2009', 4694, 5370.381, 1, 0 UNION ALL
SELECT	'4/30/2009', '4/27/2009', '4/27/2009', 846, 61.221, 0, 1 UNION ALL
SELECT	'4/30/2009', '4/27/2009', '4/28/2009', 1808, 128.313, 0, 1 UNION ALL
SELECT	'4/30/2009', '4/27/2009', '4/29/2009', 2770, 256.109, 0, 1 UNION ALL
SELECT	'4/30/2009', '4/27/2009', '4/30/2009', 3732, 5366.328, 1, 1 UNION ALL
SELECT	'4/30/2009', '4/27/2009', '5/1/2009', 4694, 5370.381, 1, 1 UNION ALL
SELECT	'5/1/2009', '4/27/2009', '4/27/2009', 846, 61.221, 0, 1 UNION ALL
SELECT	'5/1/2009', '4/27/2009', '4/28/2009', 1808, 128.313, 0, 1 UNION ALL
SELECT	'5/1/2009', '4/27/2009', '4/29/2009', 2770, 256.109, 0, 1 UNION ALL
SELECT	'5/1/2009', '4/27/2009', '4/30/2009', 3732, 5363.796, 0, 0 UNION ALL
SELECT	'5/1/2009', '4/27/2009', '5/1/2009', 4694, 5370.381, 1, 0

SELECT TOP 1	VirtualDate,
		FromDate,
		ToDate,
		AvlMin,
		PMin,
		OIYesNo,
		AYesNo
FROM		(
			SELECT	VirtualDate,
				FromDate,
				ToDate,
				AvlMin,
				PMin,
				OIYesNo,
				AYesNo,
				MIN(SIGN(AYesNo)) OVER (PARTITION BY VirtualDate) AS Yak
			FROM	@Sample
		) AS d
WHERE		Yak = 1
ORDER BY	PMin DESC


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site