sql if statement

hey guys i need an if statment to return a true or false value if the deal_end coloum count is equal to the number of rows selected?

convert(datetime, deal_end, 104) <= getdate()

i am running a query to get all the records from a table and check if the current date <= the deal end date and if all that record is, it must return true or false in a coloum


please help
JCWEBHOSTAsked:
Who is Participating?
 
keyuCommented:
SELECT
SUBSTRING(description, 1, 30) as s_description,
COUNT(description) as count,
description ,
sum ( case when convert(datetime, deal_end, 104) >= getdate() and date_redeemed is null then 1 else 0 end) as active,
sum ( case when date_redeemed is not null then 1 else 0 end) as redeemed,
case when sum ( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end) =COUNT(description) then 'True' as 'False' end as expired from groupon GROUP BY description
0
 
EvilPostItCommented:
Does this do the trick?

SELECT 
CASE COUNT(*) 
WHEN 0 THEN 'TRUE'
ELSE 'FALSE'
END
FROM TABLE
WHERE CONVERT(datetime,deal_end,104)>getdate()

Open in new window


You can change the TRUE & FALSE statements to anything you like. You can also alias the column so it has a specific name if you like. Let me know if you need this too.
0
 
JCWEBHOSTAuthor Commented:
how do i insert your above code in mine?

SELECT SUBSTRING(description, 1, 30) as s_description, COUNT(description) as count, description ,sum ( case when convert(datetime, deal_end, 104) >= getdate() and date_redeemed is null then 1 else 0 end) as active,sum ( case when date_redeemed is not null then 1 else 0 end) as redeemed, sum ( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end) as expired from groupon GROUP BY description
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
EvilPostItCommented:
Probably best to write a function so that you can pass in a description and it brings back a true of false. Then you can just run the function in your select.
0
 
JCWEBHOSTAuthor Commented:
how to do that?
0
 
EvilPostItCommented:
Some thing like this...

CREATE FUNCTION ufn_VerifyDeal (@desc VARCHAR(255))
RETURNS bit
AS
BEGIN
	DECLARE @RETURN BIT
	SELECT @RETURN=CASE COUNT(*)
			WHEN 0 THEN 1
			ELSE 0
			END
	FROM Groupon
	WHERE CONVERT(datetime,deal_end,104)>getdate() AND description=@desc
	RETURN(@RETURN)
END	

Open in new window


And then add a column to your query as...
ufn_VerifyDeal(description)

Open in new window

0
 
JCWEBHOSTAuthor Commented:
problem i am using sql query

SELECT SUBSTRING(description, 1, 30) as s_description, COUNT(description) as count, description ,sum ( case when convert(datetime, deal_end, 104) >= getdate() and date_redeemed is null then 1 else 0 end) as active,sum ( case when date_redeemed is not null then 1 else 0 end) as redeemed, sum ( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end) as expired from groupon GROUP BY description

not stored procedures
0
 
JCWEBHOSTAuthor Commented:
just need to place it in that query
0
 
lwadwellCommented:
You could use your query as a sub-query (although it does return more values than needed) e.g.
SELECT CASE WHEN count = expired THEN 'true' ELSE 'false' END as redeem_check
FROM (SELECT SUBSTRING(description, 1, 30) as s_description
           , COUNT(description) as count
           , description 
           , sum( case when convert(datetime, deal_end, 104) >= getdate() 
                            and date_redeemed is null then 1 else 0 end) as active
           , sum( case when date_redeemed is not null then 1 else 0 end) as redeemed
           , sum( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end) as expired 
      FROM groupon 
      GROUP BY description)v

Open in new window

0
 
Scott PletcherSenior DBACommented:
You can just add it directly to your existing query:


SELECT SUBSTRING(description, 1, 30) as s_description, COUNT(description) as count,
    description ,
    sum ( case when convert(datetime, deal_end, 104) >= getdate() and date_redeemed is null then 1 else 0 end) as active,
    sum ( case when date_redeemed is not null then 1 else 0 end) as redeemed,
    sum ( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end) as expired,
    case when sum ( case when convert(datetime, deal_end, 104) <= getdate() then 1 else 0 end ) = COUNT(*)    --added
        then 'true' else 'false' end as [all_expired?]    --added
from groupon
GROUP BY description
0
 
Scott PletcherSenior DBACommented:
Hmm, so he changes ONE substantive thing in the code:

COUNT(*) to COUNT(description) [which should work the same anyway, unless there are NULL descriptions)

a marginal change at best, and he gets ALL the point.

Ouch!

Good luck with future questions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.