Need to get all data in this sql

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

Need to get all data in this sql

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2010-04-30 : 12:06:03

The following SQL returns all category budget data with the department it belongs to,but only if there are transactions, which is the SOP102 table for that period. However, the user wants to see all budget data even if there is no transactions. I was looking to be able to include that budget data in this process here. Perhaps someone can steer me in the right direction.

USE [SUP]
GO
/****** Object: StoredProcedure [dbo].[SpendAnalyzer] Script Date: 04/30/2010 12:02:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SpendAnalyzer]
-- Add the parameters for the stored procedure here
@customername varchar(50) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @NoDept table
(
custNumbr varchar(100)
)
insert into @NoDept
select 'Iad01'
insert into @NoDept
select 'iah01'
insert into @NoDept
select 'Nmmc02'
insert into @NoDept
select 'Nmmc03'
insert into @NoDept
select 'Nmr03'
insert into @NoDept
select 'Nrh03'
insert into @NoDept
select 'Bhcc02'

IF NOT eXISTS( select RM00101.CUSTNMBR from RM00101 where CUSTNMBR iN (select custNumbr From @NoDept) and CUSTNAME = @customername)
BEGIN

-- Insert statements for procedure here
SELECT --SOP10106.USRDEF05 as Department,
--SOP10106.DEX_ROW_ID,
SOP10106.USRDEF05 as Department,
SOP10200.XTNDPRCE As Expense,
SOP10200.QUANTITY as Quantity,
RM00101.CUSTNAME,
SOP10200.ITEMDESC as Description,
SOP10100.DOCDATE AS DateCreated,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (SOP10100.DOCDATE, SOP10100.CUSTNMBR , IV40600.USCATVAL, SOP10106.USRDEF05) as Budget
,SOP10200.SOPNUMBE InvoiceNum

FROM dbo.SOP10200 AS SOP10200
INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC

WHERE SOP10100.SOPTYPE = '1' AND
SOP10100.DOCDATE >= DATEADD (YYYY, - 2, GETDATE()) AND
SOP10100.DOCDATE <= GETDATE() AND
LTrim(RTrim(RM00101.CUSTNAME)) = @customername
-- and IV40600.uscatVal = 'HK-jan'
ORDER BY SOP10100.DOCDATE DESC
END

ELSE
BEGIN
SELECT --SOP10106.USRDEF05 as Department,
--SOP10106.USRDEF05 as Department,
@customername as Department,
SOP10200.XTNDPRCE As Expense,
SOP10200.QUANTITY as Quantity,
RM00101.CUSTNAME,
SOP10200.ITEMDESC as Description,
SOP10100.DOCDATE AS DateCreated,
IV40600.UserCatLongDescr as Category,
IV40600.USCATVAL,
dbo._FnGetBudgetAmount_Spend (SOP10100.DOCDATE, SOP10100.CUSTNMBR , IV40600.USCATVAL, SOP10106.USRDEF05) as Budget
,SOP10200.SOPNUMBE InvoiceNum

FROM dbo.SOP10200 AS SOP10200
INNER JOIN dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
INNER JOIN dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
INNER JOIN dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC

WHERE SOP10100.SOPTYPE = '1' AND
SOP10100.DOCDATE >= DATEADD (YYYY, - 2, GETDATE()) AND
SOP10100.DOCDATE <= GETDATE() AND
LTrim(RTrim(RM00101.CUSTNAME)) = @customername
-- and IV40600.uscatVal = 'HK-jan'
ORDER BY SOP10100.DOCDATE DESC
END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 00:33:56

which is your master table containing budget data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-02 : 17:32:55

Vishna, this table is the
Customerbudget table. This has the budget amounts per category.

as well as the Department.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-03 : 01:17:36

But there is no Customerbudget table in the posted code.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 01:32:56

quote:
Originally posted by AdamWest

Vishna, this table is the
Customerbudget table. This has the budget amounts per category.

as well as the Department.

then you need to start from it and left join other tables to it to get reqd details.

Also I'm Visakh , not Vishna

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-04 : 10:00:06

OK thanks - i WAS Able to get it !

quote:
Originally posted by visakh16

quote:
Originally posted by AdamWest

Vishna, this table is the
Customerbudget table. This has the budget amounts per category.

as well as the Department.

then you need to start from it and left join other tables to it to get reqd details.

Also I'm Visakh , not Vishna

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:32:43

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources