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

General SQL Server Forums

New to SQL Server Programming

a query

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-28 : 21:41:45

Hi all. got probs.

DELARE @pMonth varchar(20)
SET @pMonth = 'May'

table 1 - hrEmpPaySumm
empdcno period1 wtax taxinc
1 1/1/2007 2000 100
1 5/1/2007 3000 200
1 6/1/2007 4000 300
2 2/1/2007 5000 400
3 10/1/2007 6000 500
3 12/1/2007 7000 600

Expected Result:
If @pMonth = 'May'; sum up the wtax from january to may. Also with taxinc.
empdcno wtax taxinc YTDwtax YTDtaxinc
1 3000 200 5000 300
2 5000 400 5000 400

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-28 : 22:43:59

anyone can help me please. KH help please.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 23:15:45

DDL and sample data please.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-28 : 23:23:59

Koji i provided sample data above.

Expected Result:
If @pMonth = 'May'; sum up the wtax from january to may. Also with taxinc.

empdcno wtax taxinc YTDwtax YTDtaxinc
1 3000 200 5000 300
2 5000 400 5000 400

If @pMonth = 'June'

empdcno wtax taxinc YTDwtax YTDtaxinc
1 4000 300 9000 600
2 5000 400 5000 400

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 23:41:41

Well, I don't even know period1 is datetime or varchar.
Sample data should be supplied as:
INSERT INTO TABLE SELECT ..... UNION ALL SELECT ......
So that we don't have to waste time by typing all of that to help you.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 00:04:50

ok... here it is...

declare @hrEmpPaySumm table
(
empdcno int,
period1 smalldatetime,
wtax money,
taxinc money
)
insert into @hrEmpPaySumm
select 1, '1/1/2007', 2000 , 100 union all
select 1, '5/1/2007', 3000 , 200 union all
select 1, '6/1/2007', 4000 , 300 union all
select 2, '2/1/2007', 5000 , 400 union all
select 3, '10/1/2007', 6000 , 500 union all
select 3, '12/1/2007', 7000 , 600

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 00:31:11

Can you explain how to get the figure for wtax and taxinc in red ?

quote:
Originally posted by cwtriguns2002

Koji i provided sample data above.

Expected Result:
If @pMonth = 'May'; sum up the wtax from january to may. Also with taxinc.

empdcno wtax taxinc YTDwtax YTDtaxinc
1 3000 200 5000 300
2 5000 400 5000 400

If @pMonth = 'June'

empdcno wtax taxinc YTDwtax YTDtaxinc
1 4000 300 9000 600
2 5000 400 5000 400

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

KH
[spoiler]Time is always against us[/spoiler]

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 00:47:14

DECLARE @M int

SET @M = CASE @pMonth
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END

SELECT DISTINCT A.empdcno,
wtax = IsNull((SELECT Z.wtax FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) = @M), 0),
taxinc = IsNull((SELECT Z.taxinc FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) = @M), 0),
YTDwtax = (SELECT SUM(Z.wtax) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M),
YTDtaxinc = (SELECT SUM(Z.taxinc) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M)
FROM @hrEmpPaySumm A
WHERE EXISTS (SELECT * FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M)
ORDER BY A.empdcno

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 00:58:15

Just guessing the logic for column i highlighted in red earlier. Is this what you want ?

DECLARE @hrEmpPaySumm TABLE
(
	empdcno int,
	period1 smalldatetime,
	wtax money,
	taxinc money
)

SET dateformat mdy
INSERT INTO @hrEmpPaySumm
SELECT 1, '1/1/2007', 2000 , 100 UNION ALL
SELECT 1, '5/1/2007', 3000 , 200 UNION ALL
SELECT 1, '6/1/2007', 4000 , 300 UNION ALL
SELECT 2, '2/1/2007', 5000 , 400 UNION ALL
SELECT 3, '10/1/2007', 6000 , 500 UNION ALL
SELECT 3, '12/1/2007', 7000 , 600

DECLARE	@pYear		int,		@pMonth int,
	@MTHstart	datetime,	@MTHend	datetime,
	@YTDstart	datetime,	@YTDend	datetime

SELECT	@pYear	= 2007,
	@pMonth = 5

SELECT	@MTHstart	= DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth - 1, 0)),
	@MTHend		= DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth, -1)),
	@YTDstart	= DATEADD(YEAR, @pYear - 1900, 0),
	@YTDend		= DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth, -1))

SELECT	y.empdcno,
	wtax	= coalesce(m.MTHwtax, 	y.YTDwtax),
	taxinc	= coalesce(m.MTHtaxinc, y.YTDtaxinc),
	y.YTDwtax,
	y.YTDtaxinc
FROM
(
	SELECT	empdcno, YTDwtax = SUM(wtax), YTDtaxinc = SUM(taxinc)
	FROM	@hrEmpPaySumm
	WHERE	period1	>= @YTDstart
	AND	period1	<= @YTDend
	GROUP BY empdcno
) y
left JOIN
(
	SELECT	empdcno, MTHwtax = SUM(wtax), MTHtaxinc = SUM(taxinc)
	FROM	@hrEmpPaySumm
	WHERE	period1	>= @MTHstart
	AND	period1	<= @MTHend
	GROUP BY empdcno
) m
ON	y.empdcno	= m.empdcno

/*
empdcno     wtax                  taxinc                YTDwtax               YTDtaxinc             
----------- --------------------- --------------------- --------------------- --------------------- 
          1             3000.0000              200.0000             5000.0000              300.0000 
          2             5000.0000              400.0000             5000.0000              400.0000 

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 00:59:02

thanks koji. But there is an error if @pMonth is 'May' or @M = 5.
Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 01:02:28

KH didn't notice your post. The text you highlighted is equivalent to the wtax and taxinc in the month.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 01:04:49

quote:
Originally posted by cwtriguns2002

KH didn't notice your post. The text you highlighted is equivalent to the wtax and taxinc in the month.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

For empdcno = 2, there isn't any record for the month of May, how do you get the 5000 400 ?

KH
[spoiler]Time is always against us[/spoiler]

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 01:20:54

Your production data has more than one record for a empdcno in a month.
You sample data did not.

Try

SELECT DISTINCT A.empdcno,
wtax = (SELECT IsNull(SUM(Z.wtax), 0) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) = @M),
taxinc = (SELECT IsNull(SUM(Z.taxinc), 0) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) = @M),
YTDwtax = (SELECT SUM(Z.wtax) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M),
YTDtaxinc = (SELECT SUM(Z.taxinc) FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M)
FROM @hrEmpPaySumm A
WHERE EXISTS (SELECT * FROM @hrEmpPaySumm Z WHERE Z.empdcno = A.empdcno AND MONTH(Z.period1) <= @M)
ORDER BY A.empdcno

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 02:47:18

Sorry i had my break....

sorry for the wrong entry KH. It should be

If @pMonth = 'June'

empdcno wtax taxinc YTDwtax YTDtaxinc
1 4000 300 9000 600
2 0 0 5000 400

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 02:49:29

[code]DECLARE @hrEmpPaySumm TABLE
(
empdcno int,
period1 smalldatetime,
wtax money,
taxinc money
)

SET dateformat mdy
INSERT INTO @hrEmpPaySumm
SELECT 1, '1/1/2007', 2000 , 100 UNION ALL
SELECT 1, '5/1/2007', 3000 , 200 UNION ALL
SELECT 1, '6/1/2007', 4000 , 300 UNION ALL
SELECT 2, '2/1/2007', 5000 , 400 UNION ALL
SELECT 3, '10/1/2007', 6000 , 500 UNION ALL
SELECT 3, '12/1/2007', 7000 , 600

DECLARE @pYear int, @pMonth int,
@MTHstart datetime, @MTHend datetime,
@YTDstart datetime, @YTDend datetime

SELECT @pYear = 2007,
@pMonth = 5

SELECT @MTHstart = DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth - 1, 0)),
@MTHend = DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth, -1)),
@YTDstart = DATEADD(YEAR, @pYear - 1900, 0),
@YTDend = DATEADD(YEAR, @pYear - 1900, DATEADD(MONTH, @pMonth, -1))

SELECT y.empdcno,
wtax = coalesce(m.MTHwtax, 0),
taxinc = coalesce(m.MTHtaxinc, 0),
y.YTDwtax,
y.YTDtaxinc
FROM
(
SELECT empdcno, YTDwtax = SUM(wtax), YTDtaxinc = SUM(taxinc)
FROM @hrEmpPaySumm
WHERE period1 >= @YTDstart
AND period1 <= @YTDend
GROUP BY empdcno
) y
left JOIN
(
SELECT empdcno, MTHwtax = SUM(wtax), MTHtaxinc = SUM(taxinc)
FROM @hrEmpPaySumm
WHERE period1 >= @MTHstart
AND period1 <= @MTHend
GROUP BY empdcno
) m
ON y.empdcno = m.empdcno

/*
empdcno wtax taxinc YTDwtax YTDtaxinc
----------- --------------------- --------------------- --------------------- ---------------------
1 3000.0000 200.0000 5000.0000 300.0000
2 .0000 .0000 5000.0000 400.0000

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 03:09:53

Wow ... both queries works perfectly. Don't know what to use. by the way thanks koji and KH. you guys great!

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:16:16

quote:
Originally posted by cwtriguns2002

Wow ... both queries works perfectly. Don't know what to use. by the way thanks koji and KH. you guys great!

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Check the performance and execution plan see which gives you better performance. Normally sub-query does not gives you good performance.

KH
[spoiler]Time is always against us[/spoiler]

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 03:40:04

DECLARE @pMonth varchar(20)
SET @pMonth = 'May'

DECLARE @M int

SET @M = CASE @pMonth
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END

SELECT A.empdcno, wtax = IsNull(B.wtax, 0), taxinc = IsNull(B.taxinc, 0), A.YTDwtax, A.YTDtaxinc
FROM
(
SELECT A.empdcno, YTDwtax = SUM(A.wtax), YTDtaxinc = SUM(A.taxinc)
FROM @hrEmpPaySumm A
WHERE MONTH(A.period1) <= @M GROUP BY A.empdcno
) A
LEFT OUTER JOIN
(
SELECT A.empdcno, wtax = SUM(A.wtax), taxinc = SUM(A.taxinc)
FROM @hrEmpPaySumm A
WHERE MONTH(A.period1) = @M GROUP BY A.empdcno
) B ON B.empdcno = A.empdcno
ORDER BY A.empdcno

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 04:28:03

koji may i know what is wrong in your first query?

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 04:29:21

KH ill try to check the performance. not yet tested.

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 04:57:54

As kh said, the second one without subqueries is more efficient.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources