March 30, 2009 at 2:47 pm
Hi,
I don't know how to solve this problem.
I'm having 2 tables:
Table1
"ID" "Text"
1 text1
12 text12
1200 text1200
1201 text1201
1202 text1202
1203 text1203
1208 text1208
4 text4
4100 text4100
41001 text41101
Table2
"ID" "Value"
12 1
1200 1
1208 1
41001 1
41001 1
I need this results:
"ID" "Values"
1 3
12 3
1200 1
1208 1
4 2
4100 2
41001 2
Does someone have any idea?
Regards, Davor Geci
March 30, 2009 at 4:30 pm
Hi,
would this be the answer to your question?
select t1.id, count(t1.id) from @Table1 t1 inner join @Table2 t2
on t2.id like (t1.id + '%')
group by t1.id
order by t1.id
Or maybe
select t1.id, sum(t2.Value) from @Table1 t1 inner join @Table2 t2
on t2.id like (t1.id + '%')
group by t1.id
order by t1.id
both will return your expected result set....
Edit: Note: Both, t1.id and t2.id, are stored as varchar...
March 30, 2009 at 4:59 pm
I can't believe that you did it in so simple way!!!!!
This is brilliant.
Thank you SSChasing Mays
Can I make it a little complicated?
Regards, Davor Geci
March 30, 2009 at 5:10 pm
Sure.
But please provide some more information to figure out what you're trying to do... 😉
The previous one was more guessing than knowing what you're looking for...
Also, it would help if you could briefly describe the business case so we would know a little more what the solution is needed for.
March 30, 2009 at 5:16 pm
I have also 1 date field (we can call it Date1)
so, in results I need to have also 4 columns:
1. column = ID
2. column with values Date1 <= @DateFrom,
3. column Date1 > @DateFrom AND Date1 <= @DateTo
4. column Date1 <= @DateTo
March 30, 2009 at 5:34 pm
It's for financial calculations here in Croatia.
Here we have something that is called Brutto Bilance.
It's something like hierarchicly presenting money on accounts.
1,12,1200,4,4100,.... this are accounts.
So 12 is part of 1,
1200 is part of 12 and 1,
1202 is part of 12 and 1,
1208 is part of 12 and 1....
Date is used for splitting financial periods.
March 30, 2009 at 5:37 pm
Unfortunately, you decided not to follow the recommendation from my previous post.
Instead of adding more information you posted less.:crying:
Since I can't really find any link between your first and your second question I need to ask for the business case you're working on.
I also would like to see what you have tried so far. Please post your query and explain what you're struggling with.
If you're asking for quick answers you should post your data as described in the following article: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 30, 2009 at 5:57 pm
OK.
I will give a try to explain and follow the article for code and example data, but here is 3 AM and my english is not so good. So please can you take a look in a few hours?
Davor
March 31, 2009 at 6:56 am
--create table konta
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Konta]') AND type in (N'U'))
DROP TABLE [dbo].[Konta]
GO
CREATE TABLE [dbo].[Konta](
[SIFRA] [nvarchar](20) NULL,
[NAZIV] [nvarchar](60) NULL,
[KUDO] [nvarchar](1) NULL
) ON [PRIMARY]
GO
--create table GlKnj
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GlKnj]') AND type in (N'U'))
DROP TABLE [dbo].[GlKnj]
GO
CREATE TABLE [dbo].[GlKnj](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SifPP] [nvarchar](7) NULL,
[Konto] [nvarchar](20) NULL,
[Dug] [money] NULL,
[Pot] [money] NULL,
[DatDok] [datetime] NULL,
) ON [PRIMARY]
GO
--insert data to work with
INSERT INTO [Konta] VALUES ('1','Text1','X')
INSERT INTO [Konta] VALUES ('12','Text12','K')
INSERT INTO [Konta] VALUES ('1200','Text1200','K')
INSERT INTO [Konta] VALUES ('1201','Text1201','K')
INSERT INTO [Konta] VALUES ('1202','Text1202','K')
INSERT INTO [Konta] VALUES ('1203','Text1203','K')
INSERT INTO [Konta] VALUES ('1208','Text1208','K')
INSERT INTO [Konta] VALUES ('2','Text2','D')
INSERT INTO [Konta] VALUES ('2200','Text2200','D')
INSERT INTO [Konta] VALUES ('2201','Text2201','D')
INSERT INTO [Konta] VALUES ('4','Text4','X')
INSERT INTO [Konta] VALUES ('4100','Text4100','X')
INSERT INTO [Konta] VALUES ('41001','Text41101','X')
INSERT INTO glknj VALUES ('0000001','12',100,0,'20081231')
INSERT INTO glknj VALUES ('0000003','1200',100,0,'20090402')
INSERT INTO glknj VALUES ('0000021','1208',100,0,'20090402')
INSERT INTO glknj VALUES ('0000012','41001',0,200,'20090402')
INSERT INTO glknj VALUES ('0000011','4100',0,100,'20090402')
INSERT INTO glknj VALUES ('0000007','4',0,100,'20081231')
INSERT INTO glknj VALUES ('0000044','2200',0,100,'20090402')
-----------------------------------------------------------------
-----------------------------------------------------------------
--------------------------QUERYS---------------------------------
-----------------------------------------------------------------
-----------------------------------------------------------------
DECLARE @DateFrom as date = '20090101';
DECLARE @DateTo as date = '20091231';
SELECT Konta.SIFRA as Prior_Sifra , SUM(GlKnj.Dug) AS Prior_Dug, SUM(GlKnj.Pot) AS Prior_Pot
FROM konta inner JOIN GlKnj
ON GlKnj.Konto like ( Konta.SIFRA + '%')
WHERE (GlKnj.DatDok <= @DateFrom)
GROUP BY Konta.SIFRA with rollup -- with rollup is to summarize all the values
ORDER BY Konta.SIFRA
SELECT Konta.SIFRA AS Current_Sifra, SUM(GlKnj.Dug) AS Current_Dug, SUM(GlKnj.Pot) AS Current_Pot
FROM konta inner JOIN GlKnj
ON GlKnj.Konto like ( Konta.SIFRA + '%')
WHERE (GlKnj.DatDok > @DateFrom AND GlKnj.DatDok <= @DateTo)
GROUP BY Konta.SIFRA with rollup -- with rollup is to summarize all the values
ORDER BY Konta.SIFRA
SELECT Konta.SIFRA AS Total_Sifra, SUM(GlKnj.Dug) AS Total_Dug, SUM(GlKnj.Pot) AS Total_Pot
FROM konta inner JOIN GlKnj
ON GlKnj.Konto like ( Konta.SIFRA + '%')
WHERE (GlKnj.DatDok <= @DateTo)
GROUP BY Konta.SIFRA with rollup -- with rollup is to summarize all the values
ORDER BY Konta.SIFRA
Results:
Prior_Sifra Prior_Dug Prior_Pot
-------------------- --------------------- ---------------------
NULL 200,00 100,00
1 100,00 0,00
12 100,00 0,00
4 0,00 100,00
Current_Sifra Current_Dug Current_Pot
-------------------- --------------------- ---------------------
NULL 600,00 1000,00
1 200,00 0,00
12 200,00 0,00
1200 100,00 0,00
1208 100,00 0,00
2 0,00 100,00
2200 0,00 100,00
4 0,00 300,00
4100 0,00 300,00
41001 0,00 200,00
Total_Sifra Total_Dug Total_Pot
-------------------- --------------------- ---------------------
NULL 800,00 1100,00
1 300,00 0,00
12 300,00 0,00
1200 100,00 0,00
1208 100,00 0,00
2 0,00 100,00
2200 0,00 100,00
4 0,00 400,00
4100 0,00 300,00
41001 0,00 200,00
What I want to get is only 1 resultset with all this data group by Total_Sifra:
Total_Sifra Total_Dug Total_Pot Prior_Dug Prior_Pot Current_Dug Current_Pot
------------ ----------- ------------- ----------- ----------- ------------ -----------
NULL 800,00 1100,00 200,00 100,00 600,00 1000,00
1 300,00 0,00 100,00 0,00 200,00 0,00
12 300,00 0,00 100,00 0,00 200,00 0,00
1200 100,00 0,00 0 0 100,00 0,00
1208 100,00 0,00 0 0 100,00 0,00
2 0,00 100,00 0 0 0,00 100,00
2200 0,00 100,00 0 0 0,00 100,00
4 0,00 400,00 0,00 100,00 0,00 300,00
4100 0,00 300,00 0 0 0,00 300,00
41001 0,00 200,00 0 0 0,00 200,00
Hope this is better.
Davor
March 31, 2009 at 8:28 am
Hi,
is it possible that you're using SQL Server 2008?
The statement
DECLARE @DateFrom as date = '20090101';
is no SS2K syntax, but that's the forum you posted in.
Please clarify, since this will have an impact on how the solution will look like and who might look further into it (since I'm not using SS2K8 yet) . I can only help you with SS2K and SS2K5.
Subject on hold for clarification by OP 😉
Btw: The data you provided together with the format makes it easy to work on!!
GOOD JOB!!
March 31, 2009 at 9:56 am
Hi,
attached please find a query that's running ok on SS2K5
What I've done is simply join all your queries to the master table [Konta] via LEFT OUTER JOIN
and run those queries as subqueries.
There are probably better solutions out there in terms of performance and coding quality but for the moment that's the fastest code I can come up with (in terms of generating the code 😀 )...
In order to speed up the query you should definitely look into indexing.
-- output columns
SELECT t0.SIFRA,
ISNULL(SUM(total_dug),0) AS Total_Dug,
ISNULL(SUM(total_pot),0)AS Total_Pot,
ISNULL(SUM(prior_dug),0) AS Prior_Dug,
ISNULL(SUM(prior_pot),0) AS Prior_Pot,
ISNULL(SUM(current_dug),0) AS Current_Dug,
ISNULL(SUM(current_pot),0) AS Current_Pot
FROM
(SELECT Konta.SIFRA AS SIFRA FROM konta
) t0 -- values to report
LEFT OUTER JOIN
(SELECT Konta.SIFRA AS Prior_Sifra ,
SUM(glknj.Dug) AS Prior_Dug,
SUM(glknj.Pot) AS Prior_Pot
FROM konta
INNER JOIN glknj ON glknj.Konto LIKE ( Konta.SIFRA + '%')
WHERE (glknj.DatDok <= @DateFrom)
GROUP BY Konta.SIFRA
) t1 -- subquery for Prior_ columns
ON t0.SIFRA=t1.Prior_Sifra
LEFT OUTER JOIN
(SELECT Konta.SIFRA AS Current_Sifra,
SUM(glknj.Dug) AS Current_Dug,
SUM(glknj.Pot) AS Current_Pot
FROM konta INNER JOIN glknj
ON glknj.Konto LIKE ( Konta.SIFRA + '%')
WHERE (glknj.DatDok > @DateFrom AND glknj.DatDok <= @DateTo)
GROUP BY Konta.SIFRA
) t2 -- subquery for Current_ columns
ON t0.SIFRA=t2.Current_Sifra
LEFT OUTER JOIN
(SELECT Konta.SIFRA AS Total_Sifra,
SUM(glknj.Dug) AS Total_Dug,
SUM(glknj.Pot) AS Total_Pot
FROM konta INNER JOIN glknj
ON glknj.Konto LIKE ( Konta.SIFRA + '%')
WHERE (glknj.DatDok <= @DateTo)
GROUP BY Konta.SIFRA
) t3 -- subquery for Total_ columns
ON t0.SIFRA=t3.Total_Sifra
GROUP BY t0.SIFRA with rollup -- to summarize all the values
ORDER BY t0.SIFRA
Edit: One more thing to notice: the rollup you requested in your example does not seem to be right...
From my perspective the overall total must be equal sum(Dug) rsp. sum(Pot) of your table glknj. What's happening is that you're actually counting the value of Pot rsp. Dug n-times the level it's nested in.
Example (Column Pot, Values 4%):
You have 3 values in your table glknj (Konto 4: 100, Konto 4100: 100, and Konto 41001: 200). Total Pot = 400.
That's displayed correctly in SIFRA=4, column Total_Pot.
But the rollup-value for Total_Pot shows 1100 instead of 500 as per table glknj. Shouldn't they match? Why do you need to add the Values n-times of their nest-level? Sounds strange....
March 31, 2009 at 10:44 am
lmu92 (3/31/2009)
Hi,is it possible that you're using SQL Server 2008?
It's true, I'm using SQL server 2008, but we can make the sintax compatible with 2000 and (or) 2005.
🙂
March 31, 2009 at 2:45 pm
lmu92, interesting approach.
I need time to get into.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply