Avatar of hrvica5
hrvica5
Flag for Croatia asked on

SQL,delphi2010

Hi guys i have big problem if you can help me.

I HAVE TO GET THIS TABLE:

RESULT TABLE
TRZ      N_TRZ   KON    N_KON     2011
-----------------------------------------
1001     DOOOO   2300   EXP2       300
1001     DOOOO   7500   EXP         400    
1001     DOOOO   7510   EXP1       200
1002     DOOOO   7500   EXP         200  
1002     BOOOO   2300   EXP         100
1002     BOOOO   7411   EXP3      1000
1002     BOOOO   7510   EXP1        400
1002     BOOOO   7511   EXP4       1500
1002     BOOOO   7561   FXP6        100




AND I HAVE TO USE THESE TABLES TO GET TABLE UP?

TABLE 1
DATE          TRZ     VRS   VRSR   CASH
------------------------------------------------------
05.01.11      1001      F                      100
13.02.11      1001      K                      100
05.03.11      1001      L                      100
12.04.11      1002      F                      100
05.05.11      1002      F                      100
05.05.11      1002      _         B           100
05.05.11      1002      _         H           100
05.11.11      1001      D                      100
05.11.11      1002      K                       200
15.01.11      1001      D                      100
23.03.11      1001      N                      100
05.01.11      1002      K                      200
28.04.11      1001      F                      100
05.01.11      1002      V                     1000
17.06.11      1001      F                       100
05.01.11      1002      H                     1500
16.01.11      1001      F                       100
12.03.11      1001      N                       100

TABLE 2
KON    N_KON
--------------
2300   EXP2
7500   EXP
7411   EXP3
7510   EXP1
7511   EXP4
7561   FXP6

TABLE 3
VRS   KON
------------
F     7500
L     7500
K     7510
D     2300
N     2300
V     7411
H     7511
B     7610

TABLE 4
TRZ    N_TRZ
--------------
1001   DOOOO
1002   BOOOO  

IF YOUL LOOK IN TABLE 1 YOU WILL SEE THAT THERE ARE SOMEWHERE IN FIELD
"VRS" VALUE "_"
IF THE VALUE IS "_" THEN IT SHOULD LOOK IN FIELD VRSR TO SERACH IN TABLE 3
FOR "KON".

AND I HAVE ONE MORE PROBLEM I HAVE CHECKBOXES FOR YEARS
FOR EXAMPLE 2009, 2010, 2011.
IF I CHECK FOR EXAMPLE 2009, 2010 AND 2011 THE TABLE SHOULD LOOK LIKE THIS
TABLE 5
AND THESE TABLES (TABLE 1)ARE IN ANOTHER DATABASESAND ARE CALLED
and these
dabases are called (KUPID09, KUPID10, KUPID11), AND OTHER TABLES
(TABLE 2, TABLE 3, TABLE 4) ARE SAME IN EVERY DATABASE.

TABLE 5
TRZ      N_TRZ   KON     N_KON     2009  2010  2011
---------------------------------------------------------------------
1001     DOOOO   7500   EXP                               400    
1001     DOOOO   7510   EXP1                             200
1001     DOOOO   2300   EXP2                             300
1002     DOOOO   7500   EXP                               200  
1002     BOOOO   7411   EXP3                            1000
1002     BOOOO   7510   EXP1                              400
1002     BOOOO   7511   EXP4                            1500
1002     BOOOO   7561   FXP6                              100
1002     BOOOO   2300   EXP                                100

and values hve to be in fileds 2009, 2010

I DON'T KNOW HOW TO SOLVE BUT I WOULD BE GRATEFUL IF YOU CAN MANAGE IT,
THX A LOT guys

P.S. HOPE YOU UNDERSTAND WHAT I WROTE
       This is more sql then delphi.
Microsoft SQL Server 2008Delphi

Avatar of undefined
Last Comment
hrvica5

8/22/2022 - Mon
Geert G

homework ?
Geert G

do you have database links between the primary and secondary databases ?
hrvica5

ASKER
no
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
hrvica5

ASKER
i wish it is homework
Geert G

first get the unsummarized data
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
from table1 T1
  join table4 T4 on T4.N_TRZ = T1.TRZ
  join table3 T3 on 
    ((T3.VRS = T1.VRS) and (T1.VRS <> '_'))
    or 
    ((T3.VRS = T1.VRSR) and (T1.VRS = '_'))
  join table2 T2 on T2.KON = T3.KON

Open in new window

Geert G

then just summarize by year
 
SELECT A.TRZ, A.N_TRZ, A.KON, A.N_KON, 
  SUM(CASE WHEN YEAR(A.DATE) = 2009 THEN A.CASH END) [2009],
  SUM(CASE WHEN YEAR(A.DATE) = 2010 THEN A.CASH END) [2010],
  SUM(CASE WHEN YEAR(A.DATE) = 2011 THEN A.CASH END) [2011]
FROM (
  select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
  from table1 T1
    join table4 T4 on T4.N_TRZ = T1.TRZ
    join table3 T3 on 
      ((T3.VRS = T1.VRS) and (T1.VRS <> '_'))
      or 
      ((T3.VRS = T1.VRSR) and (T1.VRS = '_'))
    join table2 T2 on T2.KON = T3.KON) as A
GROUP BY A.TRZ, A.N_TRZ, A.KON, A.N_KON

Open in new window



in delphi you can set the column for a specific year visible according to the state of the corresponding checkbox
before or after loading the data
grids like quantumgrid from devexpress have this capability

with dbgrid, set the width to 0 for that column to hide it
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
hrvica5

ASKER
Great thx but i only manage for one year and it works fine for one year, but i didn't understand the rest.

the years are in different databases (KUPID09, KUPID10, KUPID11), and i will have probably every next year  new database (KUPID12, KUPID13, KUPID....).
So my question is how to add year column (2010, 2011) .

I need to use table KUPID09.dbo.T1, KUPID10.dbo.T1, etc...

It is not problem if I take every database but i don't know how to get all datas from every database.

I manage for one year, but how then collect for another year datas in the same table

Geert G

pick one database as your primary database
add the others as linked servers
(only works if there is a wan/lan connection possible between the servers)


hrvica5

ASKER
I have everything on same server but different databases.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
hrvica5

ASKER
i don't know how to do it.
Geert G

either change your connection to the other database

or run "use dbname" in your current connection to change it to the other database
then run the query again

if you have access with 1 account to all databases, you can do it as you say

with KUPID09.dbo.T1, etc
hrvica5

ASKER
yes i can change to another database, but if i do that i will lost my first query

for example

1. query for kupid09

TRZ      N_TRZ   KON     N_KON     2009  
---------------------------------------------------------------------
1001     DOOOO   7500   EXP                  
1001     DOOOO   7510   EXP1                
1001     DOOOO   2300   EXP2                
1002     DOOOO   7500   EXP                  
1002     BOOOO   7411   EXP3                
1002     BOOOO   7510   EXP1                
1002     BOOOO   7511   EXP4                
1002     BOOOO   7561   FXP6                
1002     BOOOO   2300   EXP                  

2.query for kupid10

TRZ      N_TRZ   KON     N_KON       2010  
---------------------------------------------------------------------
1001     DOOOO   7500   EXP                            
1001     DOOOO   7510   EXP1                          
1001     DOOOO   2300   EXP2                          
1002     DOOOO   7500   EXP                            
1002     BOOOO   7411   EXP3                          
1002     BOOOO   7510   EXP1                          
1002     BOOOO   7511   EXP4                          
1002     BOOOO   7561   FXP6                          
1002     BOOOO   2300   EXP                      

3. query for kupid11

TRZ      N_TRZ   KON     N_KON                          2011
---------------------------------------------------------------------
1001     DOOOO   7500   EXP                               400    
1001     DOOOO   7510   EXP1                             200
1001     DOOOO   2300   EXP2                             300
1002     DOOOO   7500   EXP                               200  
1002     BOOOO   7411   EXP3                            1000
1002     BOOOO   7510   EXP1                              400
1002     BOOOO   7511   EXP4                            1500
1002     BOOOO   7561   FXP6                              100
1002     BOOOO   2300   EXP                                100        

i can put them in one table if i use TClientDataSet, but i want to get them from one query.
without TCLientDataSet

Can I make query in every kupid and just join next year to existing table?

p.s. i have access to all databases

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Geert G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
hrvica5

ASKER
Thx a lot,
thats great i manage it but just one small thing,if i put in line three T4.N_TRZ = T1.TRZ
it gives me nothing, but if i put T4.TRZ = T1.TRZ then it gives me results but multiply some rows.


hrvica5

ASKER
eh, yes and if i exclude   T4.N_TRZ from line 1
select T1.TRZ, T4.N_TRZ, T2.KON, T2.N_KON, T1.DATE, T1.CASH
then it gives me ok, but without n_trz ofcourse-