September 6, 2010 at 1:24 am
i have a table named Rose with following data:
Brachname status amount
london A100(varchar)
london A200
menchester A300
Paris D600
now i am using a query and storing the dat in two sererate temporary tables ,one with status A and other with
Status D
select branchname as branch ,sum(convert(decimal(18,2),amount) as active into #tmpA from Rose where status='A'
select branchname as branchN, sum(convert(decimal(18,2), amount) as cancel into #tmpB from rose where status='D'
now when i am using query :
select * from #tmpA a join #tmpB on a.branchname=b.branchname
when i run this query i m getting one one row for Status D but i want all data, and second thing is that i m getting cancel
column data as 600 in london , manchester and paris where it should show 600 in only paris and 0 for rest .
This example is just a dumy and not real tables or columns but situation is same .
September 6, 2010 at 4:30 am
-- create a sample table, correcting typos in column names and values
DROP TABLE #Rose
CREATE TABLE #Rose (Branchname VARCHAR(20), [status] CHAR(1), amount INT)
INSERT INTO #Rose (Branchname, [status], amount)
SELECT 'London', 'A', 100 UNION ALL
SELECT 'London', 'A', 200 UNION ALL
SELECT 'Manchester', 'A', 300 UNION ALL
SELECT 'Paris', 'D', 600
-- check sample table
SELECT * FROM #Rose
-- sample code fails with syntax error
select branchname as branch , sum(convert(decimal(18,2),amount) as active into #tmpA from #Rose where status='A'
select branchname as branchN, sum(convert(decimal(18,2), amount) as cancel into #tmpB from #rose where status='D'
-- corrected sample code
SELECT branchname as branch, sum(convert(decimal(18,2),amount)) as active
INTO #tmpA
FROM #Rose
WHERE [status] = 'A'
GROUP BY branchname
SELECT branchname as branchN, sum(convert(decimal(18,2), amount)) as cancel
INTO #tmpB
FROM #rose
WHERE [status] = 'D'
GROUP BY branchname
-- more sample code fails
select * from #tmpA a join #tmpB on a.branchname = b.branchname
-- corrected sample code
SELECT *
FROM #tmpA a
JOIN #tmpB b ON a.branch = b.branchN
-- 0 rows returned because there are no branches having a row with status = 'D' and also a row with status = 'A'
-- suggestion 1:
SELECT
d.BranchName,
[Active] = CASE d.[status] WHEN 'A' THEN d.SumAmount ELSE NULL END,
[Cancel] = CASE d.[status] WHEN 'D' THEN d.SumAmount ELSE NULL END
FROM (
SELECT BranchName, [status], SUM(CONVERT(DECIMAL(18,2),amount)) AS SumAmount
FROM #Rose
GROUP BY BranchName, [status]
) d
ORDER BY BranchName
-- suggestion 2:
;WITH
Allbranches AS (
SELECT Branchname
FROM #Rose
GROUP BY Branchname
),
Activebranches AS (
SELECT branchname, SUM(CONVERT(DECIMAL(18,2),amount)) AS [active]
FROM #Rose
WHERE [status] = 'A'
GROUP BY branchname
),
CancelBranches AS (
SELECT branchname, SUM(CONVERT(DECIMAL(18,2),amount)) AS [cancel]
FROM #Rose
WHERE [status] = 'D'
GROUP BY branchname
)
SELECT b.Branchname, a.active, c.cancel
FROM Allbranches b
LEFT JOIN Activebranches a ON a.Branchname = b.Branchname
LEFT JOIN CancelBranches c ON c.Branchname = b.Branchname
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply