Help with advanced SELECT

Question

Help with advanced SELECT

pklys

SSC Enthusiast

Points: 111
More actions
October 22, 2009 at 4:43 am

#94023

Hello,
Hope someone will be able to help. I've this query below that counts stuff (adds, divide depending on TransakcjePapieryWartoscioweTypTransakcji. I give 2 values to that query InID from 1 to xxx and KlienciPortfeleKontaID from 1 to xxx
The result is one column with one row with number.
This works fine and i am preety satisfied with it. The only problem is one KlienciPortfeleKontaID can have many InID's. So i have to write c# code to pass both values all the time and i think simpler and faster way would be to pass only KlienciPortfeleKontaID and get result like
InID, Count
1 33434
2 43434
3 1111
I've tried to put InID in many ways, doing group by etc but couldn't find the proper way. Could someone help me with this?
SELECT (IsNull(wartosc1.SumaAkcjiDeponowanych,0) + IsNull(wartosc2.SumaAkcjiKupionych,0) - IsNull(wartosc3.SumaAkcjiSprzedanych,0) - IsNull(wartosc4.SumaAkcjiWydanych,0))
FROM
(
SELECT sum([TransakcjePapieryWartoscioweIlosc]) AS SumaAkcjiDeponowanych
FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]
WHERE [InID] = 3 AND [TransakcjePapieryWartoscioweTypTransakcji] = 'Papiery wartościowe / Transfer dodatni' and [KlienciPortfeleKontaID] = 2 AND [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22'
) AS wartosc1,
(
SELECT sum([TransakcjePapieryWartoscioweIlosc]) AS SumaAkcjiKupionych
FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]
WHERE [InID] = 3 AND [TransakcjePapieryWartoscioweTypTransakcji] = 'Papiery wartościowe / Kupno' and [KlienciPortfeleKontaID] = 2 And [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22'
) AS wartosc2,
(
SELECT Sum([TransakcjePapieryWartoscioweIlosc]) AS SumaAkcjiSprzedanych
FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]
WHERE [InID] = 3 AND [TransakcjePapieryWartoscioweTypTransakcji] = 'Papiery wartościowe / Sprzedaż' and [KlienciPortfeleKontaID] = 2 AND [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22'
) AS wartosc3,
(
SELECT sum([TransakcjePapieryWartoscioweIlosc]) AS SumaAkcjiWydanych
FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]
WHERE [InID] = 3 AND([KlienciPortfeleKontaID] = 2 AND [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22') AND [TransakcjePapieryWartoscioweTypTransakcji] = 'Papiery wartościowe / Transfer ujemny'
) AS wartosc4
With regards,
Przemek

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Words cannot express how glad I am I don't have to deal with names like that! =) How about something like this?

SELECT INID, SUM(SumaAkcjiDeponowanych * CASE WHEN TransakcjePapieryWartoscioweTypTransakcji IN ('Papiery wartosciowe / Transfer dodatni','Papiery wartosciowe / Kupno') THEN 1 ELSE -1 END)

FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]

WHERE [KlienciPortfeleKontaID] = 2

AND [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22'

AND [TransakcjePapieryWartoscioweTypTransakcji] IN('Papiery wartosciowe / Transfer dodatni','Papiery wartosciowe / Kupno','Papiery wartosciowe / Sprzedaz','Papiery wartosciowe / Transfer ujemny')

GROUP BY INID

Disclaimer: Untested code due to no sample data provided and no SQL server at the moment.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

pklys SSC Enthusiast Points: 111 More actions · Answer 2

Hello,

Almost 🙂 HEre's the output you got:

INID(No column name)

1-34375

3-2

Column TransakcjePapieryWartoscioweIlosc is the key since it is amount to to sum up.

There are 4 types of transactions (TransakcjePapieryWartoscioweTypTransakcji) that you use in your query. Thing you should know about them is that it means either PLUS (+) or MINUS.

PLUS = 'Papiery wartosciowe / Transfer dodatni','Papiery wartosciowe / Kupno'

MINUS = 'Papiery wartosciowe / Sprzedaz','Papiery wartosciowe / Transfer ujemny'

So the code below shows incorrect count (especially that it shows values under 0)

SELECT INID, SUM(TransakcjePapieryWartoscioweIlosc * CASE WHEN TransakcjePapieryWartoscioweTypTransakcji IN ('Papiery wartosciowe / Transfer dodatni','Papiery wartosciowe / Kupno') THEN 1 ELSE -1 END)

FROM [BazaZarzadzanie].[dbo].[TransakcjePapieryWartosciowe]

WHERE [KlienciPortfeleKontaID] = 2

AND [TransakcjePapieryWartoscioweDataTransakcji] <= '2009-10-22'

AND [TransakcjePapieryWartoscioweTypTransakcji] IN('Papiery wartosciowe / Transfer dodatni','Papiery wartosciowe / Kupno','Papiery wartosciowe / Sprzedaz','Papiery wartosciowe / Transfer ujemny')

GROUP BY INID

With regards and being very glad that someone took a shot at it,

Przemek

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Take a look at the values in the case statement. They're missing accents/special characters from the copy/pasting. Fix the actual values on your end and see what you get.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

pklys SSC Enthusiast Points: 111 More actions · Answer 4

Ah sorry, thought i fixed all. Missed one and it works now correctly. I'm glad 😉 Hopefully i can work it out from now on.

Thanks for your help!