select statement

RinoDM · January 13th, 2009, 04:00 PM

Hi to all...in my store procedure i want to write a select statement that will get me the losest price on an item... one column retail, the other on promo...will it be possible to do so since there in the same table...hope someone can help...thanking you in advance...Rino

Old Pedant · January 13th, 2009, 04:14 PM

Yes, there are several ways to do this.

It would have helped a lot if you had shown us your table design, but here is a *GUESS* at it:

Code:

SELECT itemID, itemName, MIN( CASE WHEN retail < promo THEN retail ELSE promo END ) AS lowestRetailOrPromo
FROM table
GROUP BY itemID, itemName

RinoDM · January 13th, 2009, 04:50 PM

Your right sorry about that... here it is...Select ImagePathName,DescEng,SKU, MIN( CASE WHEN PromoPrice < Retail THEN PromoPrice ELSE Retail END) as Retail From Merchandise where ID=1 and PageNo between 1 and 6group by ImagePathName,DescEng,SKUBut i pluged it in and it worked... thanksone other question can i also write in if promo < retail when between a give date....thanking you in advance...Rino

Old Pedant · January 13th, 2009, 05:44 PM

Sure. You mean restrict *ALL* results to only between a certain date?

What's the name of the date field in your table???

And what DB is this? SQL Server? Oracle? MySQL?

And does the data field in your table contain *JUST* dates or does it contain both dates and times?

And where will you get the start and end dates from?

And do you mean that the PROMO prices apply only to certain dates while the RETAIL prices apply to all dates???

One POSSIBLE solution:

Code:

Create Procedure YOUR_PROCEDURE_NAME(
    @firstDate DATETIME,
    @lastDate DATETIME,
    @ID int  )
AS
Select ImagePathName, DescEng, SKU, 
       MIN( CASE WHEN PromoPrice < Retail THEN PromoPrice ELSE Retail END) as Retail 
From Merchandise 
where ID = @ID
and PageNo between 1 and 6
and YourDateField >= @firstDate AND YourDateField < DateAdd(d, 1, @lastDate)
group by ImagePathName,DescEng,SKU

But your answers to my questions could change that somewhat. Or evey a lot.

RinoDM · January 13th, 2009, 05:53 PM

to answer you questions...- to all, it will display at all times- StartDate & EndDate- SQL Server- Just the date- Start & end dates will be updated monthly- retail is @ all times, promo is if it goes on sale within the given dateHope i answered all your questions...thanking you in advance...Rino

RinoDM · January 13th, 2009, 06:01 PM

To made it clear...what i want to display is...i'm going to use the getDate() to get the system date...if the sys date is in between the start-enddate and the promo is less then retail use promo or else us retail...

Old Pedant · January 13th, 2009, 06:15 PM

Okay, then I think this does it:

Code:

Create Procedure YOUR_PROCEDURE_NAME(
    @firstDate DATETIME,
    @lastDate DATETIME,
    @ID int  )
AS
Select ImagePathName, DescEng, SKU, 
       MIN( CASE WHEN PromoPrice < Retail 
                      AND getDate() >= @firstDate)
                      AND getDate() < DATEADD(d,1,@lastDate)
            THEN PromoPrice 
            ELSE Retail END
          ) as Retail 
From Merchandise 
where ID = @ID
and PageNo between 1 and 6
group by ImagePathName,DescEng,SKU

The reason we use that >= and < and DateAdd with getDate() is because getDate() returns the current date *AND* time. And so you might end up comparing 2/2/2009 with getDate()'s '2/2/2009 11:13:00 AM' and because a date with no time has a zero time, you get a wrong answer.

Another way to do it that might be clearer:

Code:

Create Procedure YOUR_PROCEDURE_NAME(
    @firstDate DATETIME,
    @lastDate DATETIME,
    @ID int  )
AS
Select ImagePathName, DescEng, SKU, 
       MIN( CASE WHEN PromoPrice < Retail 
                      AND CONVERT(DATETIME,CONVERT(VARCHAR,getDate(),112),112)
                          BETWEEN @firstDate AND @lastDate
            THEN PromoPrice 
            ELSE Retail END
          ) as Retail 
From Merchandise 
where ID = @ID
and PageNo between 1 and 6
group by ImagePathName,DescEng,SKU

That funny looking double CONVERT is the best way to strip the time off of a date-and-time value in SQL Server.

I dunno why, but SQL Server doesn't have any builtin function for doing that, nor does it have a function that will get you only the current date or current time. Go figure. MySQL has all of those, as does Access. I guess the SQL Server designers thought that if you needed such things you're some kind of wimp.

And if @firstDate and @lastDate should be fields in the DB, just remove the @

RinoDM · January 14th, 2009, 11:29 AM

Thank you once again, i tried both and they both worked...I'll stick with you second example...Select ImagePathName, DescEng, SKU, MIN( CASE WHEN PromoPrice < Retail AND CONVERT(DATETIME,CONVERT(VARCHAR,getDate(),112),11 2)between StartDate And EndDateTHEN PromoPrice ELSE Retail END) as RetailFrom Merchandisewhere PageNo between 1 and 6group by ImagePathName,DescEng,SKUOnce again Thank youRino

RinoDM · January 14th, 2009, 11:32 AM

One last thing...When you have two minutes can you explain to me what " ,112),112) " does or mean which is at the end of the convert....thanks

Old Pedant · January 14th, 2009, 05:50 PM

Sure!

As I said, the problem is that SQL Server doesn't have a nice handy function to convert a Date+Time value to a Date-Only value. Then, on top of that, the only function to get the current date or time is GETDATE() and *it* gets *BOTH* the date and time. Equivalent to "Now( )" in VB/VBScript. [Access has DATE() to get just the date, TIME() to get just the time, and DATEVALUE() to strip the time off of a Date+Time value. MySQL has equivalent functions. Only SQL Server is so terribly unfriendly to us poor programmers.]

ANYWAY...

That funny double CONVERT is the fastest and best way to strip the time off of a Date+Time value. In this case, to strip the time off of GETDATE() and end up with ONLY today's date.

And how/why does it work? Look here:
http://msdn.microsoft.com/en-us/libr...4(SQL.80).aspx

If you look in that table of conversion numbers, you will see that 112 means "yyyymmdd". That is, when you convert a DATETIME to VARCHAR, you end up with a "yyyymmdd" format. Example: 20090114. *OR* when you convert a string/text (including a varchar) *to* a DATETIME, SQL Server knows to expect the string to be formatted as "yyyymmdd".

So... Suppose GETDATE() returns '14 January 2009 11:30:13 AM'.

We do CONVERT(VARCHAR,getDate(),112) and the result is the *string* "20090114".

Then we do CONVERT(DATETIME,'20090114',112) and the result is a date-only value of '14 January 2009'.

We did *NOT* need to use format 112. Any of the format numbers 101,102,103,104,105,106,107,110,112,112 would work. I admit that I pick 112 because it *seems* to me that it should be the fastest for SQL Server to convert from string back to DATETIME. (I imagine that the speed of DATETIME to string is about the same in all cases.) But the point is that you want to pick a format number that has no time component, at all.

Just for future reference: Take note that you can use 108 or 114 in a double CONVERT to get just the *TIME* from a DATETIME. Could be handy in maybe some scheduling application where an event occurs at the same time on various days.