• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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
0
JCWEBHOST
Asked:
JCWEBHOST
  • 4
  • 3
  • 2
  • +2
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now