CASE Date problem - SQL Server Forums

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)

CASE Date problem

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 09:56:16

I'm trying to get the below statement to work. The problem is I can't get the statement to take the varibles in the select statement. The varibles do how ever work in the where clause.


(@date_from datetime,
@date_till datetime)

as

select 



username + ' ' + surname as 'Consultant',
colour,
count(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN placementfee ELSE 0 END ) as 'Deals' ,
sum(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN placementfee ELSE 0 END )+ (CASE WHEN RowNo=1  THEN sum((isnull(feereduction,0))) ELSE 0 END)  as 'Fee Boarded' ,
Avg(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN placementfee ELSE 0 END )+ (CASE WHEN RowNo=1  THEN sum((isnull(feereduction,0))) ELSE 0 END)  as 'Fee Boarded' ,
Avg(case WHEN RowNO=1 and  createdon between @date_from and @date_till THEN salary ELSE 0 END)as 'AV Salary',
Avg(case WHEN RowNO=1 and createdon between @date_from and @date_till THEN commissionperc ELSE 0 END)as '% Fee'

FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementid ORDER BY p.placementid) AS RowNo,p.createdon,p.placementfee,p.placementid, p.placementtypeid, ps.feereduction, o.fileas, u.username, u.surname, p.startdate,p.commissionperc, c.colour, p.salary
 FROM placements p
left join PlacementSectorDefinedColumns ps on ps.placementid = p.placementid
join placementconsultants pc on pc.placementid = p.placementid 
join users u on u.userid = pc.userid 
join objects o on o.objectid = p.applicantid
left join usergroups ug on ug.userid = u.userid
join groups g on g.groupid = ug.groupid
join colours c on c.colourid = u.colourid

WHERE	
p.placementtypeid not in ('6','26','28','19')
and g.groupid in ('47','27','90','92','96','8','97','93','94','95')
) tmp

group by tmp.username, tmp.surname, tmp.rowno, tmp.colour

Thanks in advance

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:58:25

Whats the error you're getting?

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 10:03:34

Sorry I'm not getting a error. But the date varibles in the select cases aren't working. instead of giving be the data between the @date_from and @date_till I'm getting everything!

It works when I put the @date_from and @date_till in the where clause but I need it to be working from the select/case statement.

Hope this makes things clearer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:04:29

What is the problem?
You want the date parameters as part of the resultset?

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

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 10:08:32

I want the date varibles to be part ofthe result set for certain case statments. I'm not even sure if it's possible though.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:15:10

Have you forgetten to include the date check in these case statements?

+ (CASE WHEN RowNo=1 THEN sum((isnull(feereduction,0))) ELSE 0 END) as 'Fee Boarded'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:17:57

this is also wrong.

count(CASE WHEN RowNo=1 and placementtypeid not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN placementfee ELSE 0 END ) as 'Deals' ,

A zero is counted as 1. You will need to put null here.

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

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 10:21:16

No these don't need the date on them for statement to work. I get the desired result if I use the below



alter procedure live_board

(@date_from datetime,
@date_till datetime)

as

select 



username + ' ' + surname as 'Consultant',
colour,
count(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') till THEN placementfee ELSE 0 END ) as 'Deals' ,
sum(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') THEN placementfee ELSE 0 END )+ (CASE WHEN RowNo=1  THEN sum((isnull(feereduction,0))) ELSE 0 END)  as 'Fee Boarded' ,
Avg(CASE WHEN RowNo=1 and placementtypeid  not in('6','5','19','26','28') till THEN placementfee ELSE 0 END )+ (CASE WHEN RowNo=1  THEN sum((isnull(feereduction,0))) ELSE 0 END)  as 'Fee Boarded' ,
Avg(case WHEN RowNO=1 THEN salary ELSE 0 END)as 'AV Salary',
Avg(case WHEN RowNO=1 THEN commissionperc ELSE 0 END)as '% Fee'

FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementid ORDER BY p.placementid) AS RowNo,p.createdon,p.placementfee,p.placementid, p.placementtypeid, ps.feereduction, o.fileas, u.username, u.surname, p.startdate,p.commissionperc, c.colour, p.salary
 FROM placements p
left join PlacementSectorDefinedColumns ps on ps.placementid = p.placementid
join placementconsultants pc on pc.placementid = p.placementid 
join users u on u.userid = pc.userid 
join objects o on o.objectid = p.applicantid
left join usergroups ug on ug.userid = u.userid
join groups g on g.groupid = ug.groupid
join colours c on c.colourid = u.colourid

WHERE	
p.placementtypeid not in ('6','26','28','19')
and g.groupid in ('47','27','90','92','96','8','97','93','94','95')
and p.created between @date_from and @date_till) tmp

group by tmp.username, tmp.surname, tmp.rowno, tmp.colour

Although I can't use this as I need to add two other case statments that aren't dependant on the date variables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:23:11

And you also add an AVG with SUM. See Fee Boarded which is calculated twice.
Which is the correct one?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:24:52

Which are the two other statements?

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

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 10:25:13

quote:
this is also wrong.

count(CASE WHEN RowNo=1 and placementtypeid not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN placementfee ELSE 0 END ) as 'Deals' ,

A zero is counted as 1. You will need to put null here.

That worked! thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:29:19

Why didn't you tell us which column that was calculated wrongly?

Better write

sum(CASE WHEN RowNo=1 and placementtypeid not in('6','5','19','26','28') and createdon between @date_from and @date_till THEN 1 ELSE 0 END ) as 'Deals',

instead.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:29:44

And you also INNER JOIN a LEFT JOIN table which makes the left join obselete.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:33:21

[code](
@dateFrom DATETIME,
@dateTill DATETIME
)
AS

SET NOCOUNT ON

SELECT userName + ' ' + surName AS Consultant,
colour,
SUM(CASE WHEN placementTypeID NOT IN (5, 6, 19, 26, 28) THEN 1 ELSE 0 END) AS Deals,
SUM(CASE WHEN placementTypeID NOT IN (5, 6, 19, 26, 28) THEN placementFee + COALESCE(feeReduction, 0) ELSE 0 END) AS [Fee Boarded],
AVG(salary) AS [AVG Salary],
AVG(commissionPerc) AS [% Fee]
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementID ORDER BY p.placementID) AS RowNo,
p.placementFee,
p.placementTypeID,
ps.feeReduction,
u.userName,
u.surName,
p.commissionPerc,
c.colour,
p.salary
FROM placements AS p
LEFT JOIN PlacementSectorDefinedColumns AS ps ON ps.placementID = p.placementID
INNER JOIN placementconsultants AS pc ON pc.placementID = p.placementID
INNER JOIN users AS u ON u.userID = pc.userID
INNER JOIN objects AS o ON o.objectID = p.applicantID
LEFT JOIN usergroups AS ug ON ug.userID = u.userID
LEFT JOIN groups AS g ON g.groupID = ug.groupID
INNER JOIN colours AS c ON c.colourID = u.colourID
WHERE p.placementTypeID NOT IN (6, 19, 26, 28)
AND g.groupID IN (8, 27, 47, 90, 92, 93, 94, 95, 96, 97)
AND p.createdOn BETWEEN @dateFrom AND @dateTill
) AS tmp
WHERE RowNo = 1
GROUP BY userName,
surName,
colour[/code]

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

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 10:40:42

Thanks alot for the help. At the moment you enter 2 variables eg @date_from = 01/September/08 and @date_till 01/october/08 and you get sale figs for that time frame.

Is it possible to add another set of case statements which are the same as above but are only dependant on the Datepart(year) of the varile @date_from???

Hope I've made this clear enough.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:47:18

Of course it is!

DECLARE	@Sample	TABLE
	(
		Fig INT,
		dat DATETIME
	)

INSERT	@Sample
SELECT	1000, '20070502' UNION ALL
SELECT	10, '20080321' UNION ALL
SELECT	20, '20080502' UNION ALL
SELECT	15, '20080923'

DECLARE	@From DATETIME,
	@To DATETIME

SELECT	@From = '20080417',
	@To = '20080601'

SELECT	SUM(CASE WHEN dat >= @From AND dat < @To THEN Fig ELSE 0 END) AS Figs,
	SUM(CASE WHEN DATEPART(YEAR, @From) = DATEPART(YEAR, dat) THEN Fig ELSE 0 END) AS yak
FROM	@Sample

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

DaveC11
Starting Member

43 Posts

Posted - 2008-09-25 : 11:05:16

Thanks,

looks like what I need.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources