Group by Date and Case statement problems

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)

Group by Date and Case statement problems

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-24 : 14:24:12

Hi all:

I have 2 problems with the code below:

1) There are multiple transactions for each date and I want all of the transactions for each date to be 1 row of data instead of multiple rows of data. I am using Group By on the date field - but this is not working. I tried to convert the DateTime field to just a date field. While this gave me one row of data, I ended up with totals that were vastly overstated.

2) I am ending up with dollar amounts in only my CASH column. I assume this is because my case statements are separate, but I couldn't figure out how to make 1 case statement with multiple when/then/elses and still get my column headers that I need to distinguish betweeen Cash, Check, Amex, etc.

Here is the code:

select 
[Transaction].Time as TRNDATE,
case when TenderEntry.TenderID in (1,3,4) 
	 then sum(TenderEntry.Amount) 
else 0
END CASH,
case when TenderEntry.TenderID = 2  
	 then sum(TenderEntry.Amount)
else 0  
END CHECKS,
case when TenderEntry.TenderID in (5,6,8,9) 
	 then sum(TenderEntry.Amount)
else 0  
END 'VISA/MC',
case when TenderEntry.TenderID = 10  
	 then sum(TenderEntry.Amount)
else 0  
END AMEX,
case when TenderEntry.TenderID = 11    
	 then sum(TenderEntry.Amount)
else 0  
END DISCVR

from TenderEntry
join [Transaction] on 
	[Transaction].TransactionNumber = TenderEntry.TransactionNumber

where TenderEntry.TenderID in (1,2,3,4)

Group by 
[Transaction].Time,
TenderEntry.TenderID

Order by
[Transaction].Time

Any help you can provide would be much appreciated.

Here is an example of the results:

TRNDATE	                CASH	CHECKS	VISA/MC	AMEX	DISCVR
2009-06-20 14:21:42.000	0.99	0.00	0.00	0.00	0.00
2009-06-20 14:22:06.000	0.32	0.00	0.00	0.00	0.00
2009-06-20 14:22:25.000	9.10	0.00	0.00	0.00	0.00
2009-06-20 14:22:40.000	4.22	0.00	0.00	0.00	0.00
2009-06-24 10:42:37.000	1.80	0.00	0.00	0.00	0.00

Thanks
Sherri Reid

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-24 : 14:51:32

[code]SELECT dateadd(dd, 0, datediff(dd, 0, [Transaction].TIME)) AS trndate,
sum(CASE
WHEN tenderentry.tenderid IN (1,3,4)
THEN tenderentry.amount
ELSE 0
END) cash,
sum(CASE
WHEN tenderentry.tenderid = 2
THEN tenderentry.amount
ELSE 0
END) checks,
sum(CASE
WHEN tenderentry.tenderid IN (5,6,8,9)
THEN tenderentry.amount
ELSE 0
END) 'VISA/MC',
sum(CASE
WHEN tenderentry.tenderid = 10
THEN tenderentry.amount
ELSE 0
END) amex,
sum(CASE
WHEN tenderentry.tenderid = 11
THEN tenderentry.amount
ELSE 0
END) discvr
FROM tenderentry
JOIN [Transaction]
ON [Transaction].transactionnumber = tenderentry.transactionnumber
WHERE tenderentry.tenderid IN (1,2,3,4)
GROUP BY dateadd(dd, 0, datediff(dd, 0, [Transaction].TIME)),
tenderentry.tenderid
[/code]

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-24 : 14:59:14

Hello Rohitkumar - thank you very much for the code. You have resolved Problem 1 - the date problem, but when I ran it, there still showed no dollars in the other columns, and I know there should be. Here is the results of the new code you gave me:

trndate	                cash	checks	VISA/MC	amex	discvr
2009-07-08 00:00:00.000	1165.47	0.00	0.00	0.00	0.00
2009-07-09 00:00:00.000	992.22	0.00	0.00	0.00	0.00
2009-07-10 00:00:00.000	952.04	0.00	0.00	0.00	0.00
2009-07-11 00:00:00.000	148.59	0.00	0.00	0.00	0.00
2009-07-12 00:00:00.000	204.57	0.00	0.00	0.00	0.00

Thank you in advance for any help you or any one else may provide.

Sherri

SLReid
Forum Newbie
Renton, WA USA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-24 : 15:06:12

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, t.[Time]), 0) AS TrnDate,
SUM(CASE
WHEN te.TenderID IN (1, 3, 4) THEN te.Amount
ELSE 0
END) AS Cash,
SUM(CASE
WHEN te.TenderID = 2 THEN te.Amount
ELSE 0
END) AS Checks,
SUM(CASE
WHEN t3.TenderID IN (5, 6, 8, 9) THEN te.Amount
ELSE 0
END) AS [VISA/MC],
SUM(CASE
WHEN te.TenderID = 10 THEN te.Amount
ELSE 0
END) AS Amex,
SUM(CASE
WHEN te.TenderID = 11 THEN te.Amount
ELSE 0
END) AS Discvr
FROM TenderEntry AS te
INNER JOIN [Transaction] AS t ON t.TransactionNumber = te.TransactionNumber
WHERE te.TenderID IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, t.[Time]), 0)
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, t.[Time]), 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-24 : 15:14:13

quote:
WHERE te.TenderID IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12)

my bad, its so difficult to focus after a heavy friday lunch

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-24 : 15:17:12

MY BAD for not changing my original code after I was done testing cash and checks. Sheez! What a goober mistake!

Thanks all!

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 15:18:05

heavy friday or heavy lunch?

No, you're never too old to Yak'n'Roll if you're too young to die.

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-24 : 15:49:00

Maybe it is no lunch yet! (It is only just 12:45 PM here)

Except.... I just added that section of code and I now am back to having multiple days show up. Fewer multiple days - but still multiple days. I'll play with the code and see what I can come up with.

The results now are (if anyone has any good ideas):

trndate	cash	checks	VISA/MC	amex	discvr
2009-07-24 00:00:00.000	465.39	0.00	0.00	0.00	0.00
2009-07-24 00:00:00.000	0.00	0.00	12.27	0.00	0.00
2009-07-24 00:00:00.000	0.00	0.00	0.80	0.00	0.00
2009-07-24 00:00:00.000	0.00	0.00	116.20	0.00	0.00
2009-07-24 00:00:00.000	0.00	0.00	56.58	0.00	0.00
2009-07-24 00:00:00.000	0.00	0.00	0.00	20.79	0.00

SLReid
Forum Newbie
Renton, WA USA

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-07-24 : 16:03:34

NEVERMIND!

Somewhere an older section of the code got uncommented and my group by had the tenderID added back in - which meant I was getting a row for each of the types of TenderIDs it found.

You all rock! Thanks!
Sherri

SLReid
Forum Newbie
Renton, WA USA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-24 : 16:04:40

Are you still GROUPing BY the tenderID?

EDIT: Dang, too late again.. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-24 : 16:13:28

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources