December 18, 2014 at 3:35 pm
Hi Guys,
Need help with SQL Code, I am sure, it will done through Cursor or while loop.(Please correct me or guide me, if there is easiest way).
Here is my Source data (As an example)
ID,Client,TxDate,GrossCost
1,abc,11/10/2014,$10.07
2,Dest,11/10/2014,$10.07
3,Dest,11/10/2014,$10.07
4,Dest,11/10/2014,$10.07
5,xyz,11/10/2014,$10.07
6,abc,11/10/2014,$10.07
First requirement is from source file exclude all Client where Client = Dest
Second Step I have list of Clients (Len,ghi,tab)
My requirement is in the source file when client = Dest create a Same duplicate record for other three clients (Len,ghi,tab)
Here is my final final should looks lik.
ID,Client,TxDate,GrossCost
1,abc,11/10/2014,$10.07
2,Dest,11/10/2014,$10.07
2,Len,11/10/2014,$10.07
2,ghi,11/10/2014,$10.07
2,tab,11/10/2014,$10.07
3,Dest,11/10/2014,$10.07
3,Len,11/10/2014,$10.07
3,ghi,11/10/2014,$10.07
3,tab,11/10/2014,$10.07
4,Dest,11/10/2014,$10.07
4,Len,11/10/2014,$10.07
4,ghi,11/10/2014,$10.07
4,tab,11/10/2014,$10.07
5,xyz,11/10/2014,$10.07
6,abc,11/10/2014,$10.07
Please let me, if my question is not clear.
Please its urgent.
Thank You.
December 18, 2014 at 4:34 pm
Edit: Don't have data to test, but something like this should do it.
SELECT tn.ID,ca.Client,tn.TxDate,tn.GrossCost
FROM table_name tn
CROSS APPLY (
SELECT tn.Client AS Client
UNION ALL
SELECT 'LEN'
WHERE tn.Client = 'Dest'
UNION ALL
SELECT 'ghi'
WHERE tn.Client = 'Dest'
UNION ALL
SELECT 'tab'
WHERE tn.Client = 'Dest'
) AS ca
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 18, 2014 at 6:26 pm
Or this?
WITH YourTable (ID,Client,TxDate,GrossCost) AS
(
SELECT 1,'abc',CAST('11/10/2014' AS DATE),$10.07
UNION ALL SELECT 2,'Dest','11/10/2014',$10.07
UNION ALL SELECT 3,'Dest','11/10/2014',$10.07
UNION ALL SELECT 4,'Dest','11/10/2014',$10.07
UNION ALL SELECT 5,'xyz','11/10/2014',$10.07
UNION ALL SELECT 6,'abc','11/10/2014',$10.07
)
SELECT ID, Client=ISNULL(NewClient,Client), TxDate, GrossCost
FROM YourTable a
OUTER APPLY
(
SELECT NewClient
FROM
(
VALUES ('len'),('ghi'),('tab'),('Dest')
) a (NewClient)
WHERE Client = 'Dest'
) b;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 18, 2014 at 9:13 pm
Thank You so much for your prompt reply. I will run your sql to real data and I will let you know. Looks good right now.
Thank You.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply