Help to write 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help to write a query

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-01 : 04:55:42

Dear All,
I am new to SQL programming pls help me write an sp for this.

I have a query of this

SELECT SUM(TOTAL_CHARGES) AS 'CHARGES'
FROM INVOICE_HEADER
WHERE INVOICE_CREATION_PERIOD IN (200910,200911)
GROUP BY INVOICE_CREATION_PERIOD

SELECT SUM(CHARGES) AS 'CHARGES'
FROM FACT_SERVICE_REJECTION
GROUP BY INVOICE_CREATION_PERIOD
HAVING INVOICE_CREATION_PERIOD IN (200910, 200911)

SELECT SUM(CHARGE_AMOUNT) AS 'CHARGES'
FROM dbo.FACT_PAY_CODE
WHERE INVOICE_CREATION_PERIOD in (200910,200911)
GROUP BY INVOICE_CREATION_PERIOD

I need to write a stored procedure that should be return success code i mean 1 when sum of charges of all the queries are equal if not failure code(0) should return..

Pls help.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-01 : 05:23:29

hi,

CREATE PROC PROCEDURENAME
(
@INVOICE_CREATION_PERIOD VARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT OFF

SELECT SUM(TOTAL_CHARGES) AS 'CHARGES',
FROM INVOICE_HEADER
WHERE ','+@INVOICE_CREATION_PERIOD+',' LIKE '%,'+CAST(INVOICE_CREATION_PERIOD VARCHAR)+',%'
GROUP BY INVOICE_CREATION_PERIOD

SELECT SUM(CHARGES) AS 'CHARGES'
FROM FACT_SERVICE_REJECTION
GROUP BY INVOICE_CREATION_PERIOD
HAVING ','+@INVOICE_CREATION_PERIOD+',' LIKE '%,'+CAST(INVOICE_CREATION_PERIOD VARCHAR)+',%'

SELECT SUM(CHARGE_AMOUNT) AS 'CHARGES'
FROM dbo.FACT_PAY_CODE
WHERE ','+@INVOICE_CREATION_PERIOD+',' LIKE '%,'+CAST(INVOICE_CREATION_PERIOD VARCHAR)+',%'
GROUP BY INVOICE_CREATION_PERIOD

END
SET NOCOUNT ON

Thanks,
vikky.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-01 : 05:26:37

I guess here there is no logic for comparing the equality for 3 select queries..I mean the records will be more..can u pls suggest me the best optimal solution pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 08:16:15

Try this

SELECT case when sum1=sum2 and sum2=sum3 then 1 else 0 end as success_code FROM
(
SELECT
(
SELECT SUM(TOTAL_CHARGES) AS 'CHARGES'
FROM INVOICE_HEADER
WHERE INVOICE_CREATION_PERIOD IN (200910,200911)
GROUP BY INVOICE_CREATION_PERIOD
) as sum1,
(
SELECT SUM(CHARGES) AS 'CHARGES'
FROM FACT_SERVICE_REJECTION
GROUP BY INVOICE_CREATION_PERIOD
HAVING INVOICE_CREATION_PERIOD IN (200910, 200911)
) as sum2,
(
SELECT SUM(CHARGE_AMOUNT) AS 'CHARGES'
FROM dbo.FACT_PAY_CODE
WHERE INVOICE_CREATION_PERIOD in (200910,200911)
GROUP BY INVOICE_CREATION_PERIOD
) as sum3
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources