Stored Procedure question - 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)

Stored Procedure question

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-12 : 03:27:21

I have the following view in my database :

SELECT bedalog, tmpRm, SUM(bedaposo) AS bedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY tmpRm, bedalog

I want to create a stored procedure that will show only bedaposo column as an output from this code above.
Is this possible?
Thank you for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-12 : 03:32:43

[code]select bedaposo
from
(
SELECT bedalog, tmpRm, SUM(bedaposo) AS bedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY tmpRm, bedalog
) d[/code]

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

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-12 : 03:37:42

thnx a lot for the rapid answer! d is a temporary table? which book do you suggest to learn more about sql server 2005 programming?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 03:38:02

SELECT ~~bedalog, tmpRm,~~ SUM(bedaposo) AS bedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY tmpRm, bedalog

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-12 : 03:38:20

d is the table alias given to the derived table

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 04:31:59

Also instead of LEFT(bedakind, 1) <> 'P' use bedakind not like 'P%' to make use of index(if defined)

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 05:13:42

khtan : how can I select another field from another view in the same query and add them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 05:16:55

which is other field and other view. On what field you want to link the two views?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 05:48:35

The 1st view is stkef3room :

bedalog tmpRm rbedaposo
8309 520 5
8375 520 88
8203 501 70

(this is a small sample)

Code :

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

The next view is also producing 3 columns :

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

I want to get in another table or view the sum rbedaposo + rbedaposo1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 06:02:00

SELECT SUM(rbedaposo) as rbedaposo from
(
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
UNION ALL
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
) as t

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 06:12:14

I need the total rbedaposo + rebedaposo1 displayed for every room in each account.
Can it be done?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 06:19:50

SELECT bedalog, tmpRm,SUM(rbedaposo) as rbedaposo from
(
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
UNION ALL
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
) as t
group by bedalog, tmpRm

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 06:31:39

Wow!It worked!Can it be used to construct a view?
I mean
CREATE VIEW testview AS
(

SELECT bedalog, tmpRm,SUM(rbedaposo) as rbedaposo from
(
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
UNION ALL
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
) as t
group by bedalog, tmpRm

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 06:32:58

CREATE VIEW testview
AS
SELECT bedalog, tmpRm,SUM(rbedaposo) as rbedaposo from
(
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
UNION ALL
SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm
) as t
group by bedalog, tmpRm

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 07:04:36

ok.This first step worked.This rbedaposo calculation is used under the Credit column in my crystal report.
Can now this result for every room in the account(bedalog) be compared to the total credit for this account?
I calculate the total credit for every account using this view (stkef7):

SELECT bedalog, SUM(bedaposo) AS sbedaposo2
FROM ErmisUser.QBEPROK
WHERE (LEFT(bedakind, 1) = 'P')
GROUP BY bedalog

This view is giving a negative number as the total credit for each account.
I want to compare the above calculation with the absolute value of the total credit (stkef7.sbedaposo2)
so that I display the calculation only when the rbedaposo is lower than total credit.

Tell me to give you more details if there is something that is not so clear.
Thnx again for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 07:12:40

[code]SELECT t.bedalog,t.tmpRm,
CASE WHEN ABS(s.sbedaposo2)-t.rbedaposo >0
THEN ABS(s.sbedaposo2)-t.rbedaposo
ELSE 0
AS Difference
FROM testview t
INNER JOIN stkef7 s
ON s.bedalog=t.bedalog[/code]

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 07:19:19

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 07:22:31

[code]SELECT t.bedalog,t.tmpRm,
CASE WHEN ABS(s.sbedaposo2)-t.rbedaposo >0
THEN ABS(s.sbedaposo2)-t.rbedaposo
ELSE 0
END
AS Difference
FROM testview t
INNER JOIN stkef7 s
ON s.bedalog=t.bedalog
[/code]

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 07:53:23

Is there any way I can choose a specific bedalog from this query?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 08:05:36

ok.I put an ORDER BY t.bedalog, t.tmpRm in the end of the query.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 08:30:43

ok.We're getting somewhere now.
First see these 2 accounts (from the last query) :

8910 406 0.00
8910 407 0.00
8910 408 0.00
8910 409 0.00
8910 431 0.00
8910 432 0.00
8910 433 0.00
8910 434 0.00
8910 435 0.00
8910 437 0.00
8910 438 0.00
8910 439 0.00
8910 443 26.00
8910 444 0.00
8910 445 26.00
8910 446 0.00
8910 451 0.00
8910 452 0.00
8910 453 0.00
8910 530 26.00
8910 539 0.00
8910 548 0.00

8932 502 301.00
8932 507 320.00
8932 508 320.00
8932 509 320.00
8932 510 301.50
8932 511 320.00
8932 514 320.00
8932 515 320.00
8932 522 320.00
8932 541 301.00
8932 543 320.00
8932 544 320.00
8932 545 320.00
8932 546 320.00
8932 547 320.00
8932 549 320.00
8932 602 335.00
8932 604 335.00
8932 605 335.00
8932 606 320.00
8932 607 301.00
8932 608 320.00
8932 609 320.00
8932 610 320.00
8932 611 320.00
8932 613 320.00
8932 614 320.00
8932 615 320.00

And now see what I want to get in my report :

Account Room Arrang Extras Credit Remaining
8910 406 264 0 230 34
8910 407 264 0 0 264
8910 408 264 0 0 264
8910 409 264 9 0 273
8910 431 264 15,75 0 279,75
. . . . . .
. . . . . .
Totals 5568 117,20 230 5455,20

The second example is when total credit is bigger than the first sum of arrangements + extras :

Account Room Arrang Extras Credit Remaining
8932 502 69 0 69 0
8932 507 50 0 50 0
8932 508 50 0 50 0
8932 509 50 0 50 0
8932 510 50 18,50 68,50 0
8932 511 50 0 50 0
8932 514 50 0 32,50 17,50
8932 515 50 0 0 50
8932 522 50 0 0 50
. . . . . .
. . . . . .
Totals 1412 18,50 370 1060,5

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources