June 22, 2010 at 2:45 pm
Let me explain the problem.
I have a table.. in that I have amount,type columns.. amount will be in currency and type is 'ACTUAL' or "Estimated'.
I enter monthly data in that.. For one bill we will have actual amount or estimated amount or both..
My question is I want to get data by using priority.. actual/estimated..
For Example:
SNO CODE AMOUNT TYPE MONTH YEAR
1 D10001 $10 ESTIMATED 1 2010
2 D10001 $11.5 ACTUAL 1 2010
3 D10002 $5 ACTUAL 2 2010
4 D10003 $15 ESTIMATED 4 2010
I want to write select statement that will give following output
SNO CODE AMOUNT TYPE MONTH YEAR
2 D10001 $11.5 ACTUAL 1 2010
3 D10002 $5 ACTUAL 2 2010
4 D10003 $15 ESTIMATED 4 2010
In the above example SNO 1 didn't show in output because the same code has actual bill.
🙂
June 22, 2010 at 3:03 pm
I bet there are better options, but here is one.
Create table #Test
(SNO int identity(1,1), Code varchar(6), Amount Money, Type Varchar(10), Month int, year int)
insert into #Test values ( 'D10001', 10, 'ESTIMATED', 1, 2010)
insert into #Test values ( 'D10001', 11.5, 'ACTUAL', 1, 2010)
insert into #Test values ( 'D10002', 5, 'ACTUAL', 2, 2010)
insert into #Test values ( 'D10003', 15, 'ESTIMATED', 4, 2010)
select * from #test
select COALESCE(Actual.SNO, Estimated.SNO) as SNO
, COALESCE(Actual.CODE, Estimated.CODE) as CODE
, COALESCE(Actual.AMOUNT, Estimated.AMOUNT) as AMOUNT
, COALESCE(Actual.TYPE, Estimated.TYPE) as TYPE
, COALESCE(Actual.MONTH, Estimated.MONTH) as MONTH
, COALESCE(Actual.YEAR, Estimated.YEAR) as YEAR
from (select SNO, CODE, AMOUNT, TYPE, MONTH, YEAR from #Test WHERE TYPE = 'ACTUAL') as Actual
full outer join (select SNO, CODE, AMOUNT, TYPE, MONTH, YEAR from #Test WHERE TYPE = 'ESTIMATED') as Estimated
on Actual.Code = Estimated.Code
Order by COALESCE(Actual.SNO, Estimated.SNO)
-- AND Actual.Month = Estimated.Month
-- AND Actual.Year = Estimated.Year
drop table #test
-- Cory
June 22, 2010 at 3:44 pm
Thanks Cory.. Really helpfull
🙂
June 22, 2010 at 4:19 pm
Is it possible to have an 'ACTUAL' row followed by an 'ESTIMATED' row?
If not, it might be easier to just query for the most recent row (using Cory's excellent setup of sample data):
;WITH cte AS
(
SELECT MAX(sno) AS max_sno,code
FROM #Test
GROUP BY code
)
SELECT #Test.*
FROM #Test
INNER JOIN cte ON #Test.sno =cte.max_sno
June 22, 2010 at 4:45 pm
Just to add to the options.... using a count partitioned by Code to decide which records to select...
...Again using Cory's excellent test data...
Create table #Test
(SNO int identity(1,1), Code varchar(6), Amount Money, Type Varchar(10), Month int, year int)
insert into #Test values ( 'D10001', 10, 'ESTIMATED', 1, 2010)
insert into #Test values ( 'D10001', 11.5, 'ACTUAL', 1, 2010)
insert into #Test values ( 'D10002', 5, 'ACTUAL', 2, 2010)
insert into #Test values ( 'D10003', 15, 'ESTIMATED', 4, 2010)
;WITH details AS
(
SELECT SNO
,Code
,Amount
,Type
,[Month]
,[Year]
,COUNT(*) OVER (PARTITION BY Code) AS [RowCount]
FROM #Test
)
SELECT SNO,Code,Amount,Type,Month,Year
FROM details
WHERE [RowCount] = 1 OR Type = 'Actual'
drop table #test
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 22, 2010 at 4:49 pm
Yes you are right. I think your script will work in 2008..
But I am using 2000.
I think in your code you are using order by..
But I have priority to get corrected/actual/estimated amount
Previously I didn't mentioned corrected TYPE.
Please correct me if i am wrong
🙂
June 22, 2010 at 4:52 pm
Ram:) (6/22/2010)
Yes you are right. I think your script will work in 2008..But I am using 2000.
I think in your code you are using order by..
But I have priority to get corrected/actual/estimated amount
Previously I didn't mentioned corrected TYPE.
Please corrected me if i am wrong
Sorry, I thought that because this is a SQL2005 forum you would be using that.... 😛
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 22, 2010 at 4:53 pm
Thank you for clarification regarding the SQL version you use.
Would you mind answering the question in my previous post (order of appearence)?
June 22, 2010 at 4:57 pm
added to the previous insert statements, I added
insert into #Test values ( 'D10003', 15, 'ESTIMATED', 5, 2010).
But I am unable to get this data as I think it is checking condition for type = 'actual' for code 'D10003'.. note- month is different here
For 5th month I don't have actual amount.
🙂
June 22, 2010 at 5:01 pm
Hi Lutz,
I didn't get you completely.. as per my understand i think you are asking about ORDER BY TYPE and you are taking top value from that..
If it is so.. no to your question..
because i am using priority for TYPE as corrected/actual/estimated.
Hope you can help me.
🙂
June 22, 2010 at 5:06 pm
For more details I am using while loop to get each month and If conditions on type column for each month..
But It is time consuming and getting timeout expire in application..
So I wand to reduce execution time..
🙂
June 22, 2010 at 5:07 pm
Ram:) (6/22/2010)
Hi Lutz,I didn't get you completely.. as per my understand i think your are asking about ORDER BY TYPE and your taking top value from that..
If it is so.. no to your question..
because i am using priority for TYPE as corrected/actual/estimated.
Hope you can help me.
Is it possible to get rows in one of the following order if ordered by SNO asc (and what would be the expected result)?
estimated->corrected->actual,
corrected->actual->estimated,
corrected->estimated->actual
June 22, 2010 at 5:16 pm
lutz,
Sorry for confusing you..
let me explain what i need in a select statement structure
select code,
(If(type = 'corrected') then amount of corrected
else If(type = 'actual') then amount of actual
else If(type = 'estimated') then amount of estimated ),
type,
month,
year
from #tmp
Ignore syntax.. just for your ease.
🙂
June 22, 2010 at 5:20 pm
Modified for SQL 2000 and new rules....
Interestingly this produces the same execution plan (on SQL2008 using SQL 2000 compatible database) as the CTE with COUNT does on SQL2008 database...
Create table #Test
(SNO int identity(1,1), Code varchar(6), Amount Money, Type Varchar(10), Month int, year int)
insert into #Test values ( 'D10001', 10, 'ESTIMATED', 1, 2010)
insert into #Test values ( 'D10001', 11.5, 'ACTUAL', 1, 2010)
insert into #Test values ( 'D10002', 5, 'ACTUAL', 2, 2010)
insert into #Test values ( 'D10002', 5.25, 'CORRECTED', 2, 2010)
insert into #Test values ( 'D10003', 15, 'ESTIMATED', 4, 2010)
insert into #Test values ( 'D10003', 15, 'ESTIMATED', 5, 2010)
SELECT SNO
,Test.Code
,Amount
,Type
,Test.[Month]
,Test.[Year]
FROM #Test AS Test
JOIN (
SELECT
Code
,[Month]
,[Year]
,MIN(CASE Type WHEN 'ESTIMATED' THEN 3
WHEN 'ACTUAL' THEN 2
WHEN 'CORRECTED' THEN 1
END) AS Preference
FROM #Test
GROUP BY Code,[Year],[Month]
) ChosenOnes
ON ChosenOnes.Code = Test.Code
AND ChosenOnes.[Year] = Test.[Year]
AND ChosenOnes.[Month] = Test.[Month]
AND ChosenOnes.Preference = CASE Test.[Type] WHEN 'ESTIMATED' THEN 3
WHEN 'ACTUAL' THEN 2
WHEN 'CORRECTED' THEN 1
END
ORDER BY Test.SNO
drop table #test
Another surprise (to me at least ) is that it only does one table scan of #test..
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 22, 2010 at 5:25 pm
I'd like to understand the business case rather than doing an interpretation of SQL code...
But by looking at mister.magoos latest code snippet it seems like he's on the right track. Good job, mister.magoo!! (Seems like the magic glass ball is at your place right now 😉 )
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply