inline, embedded complicated "select" CrystalRepor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inline, embedded complicated "select" CrystalRepor

Author

Topic

marek
Starting Member

34 Posts

Posted - 2011-02-07 : 13:44:53

Hallo friends!

I have 3 tab.

Sale1---->ID, Code, Price_a, Price_b, Quantity
Sale2---->ID, DateSale, Town
Product---> Code, Name, Supplier, EAN

this tables are linked:

Sale1-ID-Sale2
Sale1-Code-Product

Please you compile sql nested (inline, embedded) "select".
I have a mistake somewhere.

FINAL table (result)
...................2010(january-december).......................
Code--Name--EAN--Supplier----Town--Quantity--Price_a--Price_b
table continues further to the right only 3 fields:
JANUARY
Quantity--Price_a--Price_b
FEBRUARY
Quantity--Price_a--Price_b
MARCH:
Quantity--Price_a--Price_b
.
.
DECEMBER
Quantity--Price_a--Price_b

My syntax:
SELECT distinct code, name, ean, .....
(SELECT sum(Price_a)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.1.2010-31.1.2010) as january
(SELECT sum(Price_b)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.1.2010-31.1.2010) as january
SELECT sum(Quantity)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.1.2010-31.1.2010) as january

(SELECT sum(Price_a)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.2.2010-28.2.2010) as february
(SELECT sum(Price_b)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.2.2010-28.2.2010) as february
SELECT sum(Quantity)
FROM Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID
WHERE DateSale between 1.2.2010-28.2.2010) as february
.
.
it same with march, april ...december
.
.
FROM (Sale2 INNER JOIN Sale1 ON Sale2.ID=Sale1.ID)
INNER JOIN Product ON Sale1.Code=Product.Code
WHERE DateSale between 1.1.2010-31.12.2010

all columns will be behind
I can not help myself.

Big thanks.....

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-02-07 : 20:11:15

Try this ..

Select
p.Code,
p.Name,
p.EAN,
p.Supplier,
S2.Town,
s1.Quantity,
(Select JanPrice_a.Price_a
FROM
(Select Sale1.Code, Sum(Price_a) AS Price_a FROM Sale1 INNER JOIN Sale2 ON Sale1.ID=Sale2.ID WHERE
Sale2.DateSale BETWEEN '2010-01-01' AND '2010-01-31' GROUP BY sale1.code) JanPrice_a
WHERE JanPrice_a.code=p.code) AS Jan,
(Select JanPrice_a.Price_a
FROM
(Select Sale1.Code, Sum(Price_b) AS Price_b FROM Sale1 INNER JOIN Sale2 ON Sale1.ID=Sale2.ID WHERE
Sale2.DateSale BETWEEN '2010-01-01' AND '2010-01-31' GROUP BY sale1.code) JanPrice_b
WHERE JanPrice_a.code=p.code) AS Jan

......

Till dec

From Product P
INNER JOIN Sale1 s1 ON s1.Code=p.code
INNER JOIN Sale2 s2 On s2.ID=S1.Code

WHERE s2.DateSale Between '2010-01-01' AND '2010-12-31'

marek
Starting Member

34 Posts

Posted - 2011-02-08 : 05:29:00

I'm going to try, thanks srujanavinnakota.
......
basically it is a good...but

I need for january,february..... inline select:
Quantity...Price_a...Price_b...Quantity..Price_a..Price_b.........

because it is only price_a...Price_b (quantity missing)
SELECT JanPrice_a.Price_a somewhere is error...(writes CrastalReport)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-08 : 09:06:25

[code]
select
p.Code, p.Name, p.EAN, p.Supplier,
JanPrice_a = sum(case when s2.DateSale between '20100101' and '20100131' then s1.Price_a end),
JanPrice_b = sum(case when s2.DateSale between '20100101' and '20100131' then s1.Price_b end),
JanQuantity = sum(case when s2.DateSale between '20100101' and '20100131' then s1.Quantity end),
FebnPrice_a = sum(case when s2.DateSale between '20100201' and '20100228' then s1.Price_a end),
FebPrice_b = sum(case when s2.DateSale between '20100201' and '20100228' then s1.Price_b end),
FebQuantity = sum(case when s2.DateSale between '20100201' and '20100228' then s1.Quantity end),
. . .
from Product p
inner join Sale1 s1 on p.Code = s1.Code
inner join Sale2 s2 on s1.ID = s2.ID
where s2.DateSale between '20100101' and '20101231'
group by p.Code, p.Name, p.EAN, p.Supplier
[/code]

KH
[spoiler]Time is always against us[/spoiler]

marek
Starting Member

34 Posts

Posted - 2011-02-08 : 09:50:41

thanks khtan..........it is correct, SQL query, but Crystalreports it does not

The following sql from srujanavinnakota is better. But about "(Select JanPrice_a.Price_a" writes error. Do not see the mistake?

(Select JanPrice_a.Price_a
FROM (Select Sale1.Code, Sum(Price_a) AS Price_a
FROM Sale1 INNER JOIN Sale2 ON Sale1.ID=Sale2.ID
WHERE
Sale2.DateSale BETWEEN '2010-01-01' AND '2010-01-31'
GROUP BY sale1.code) JanPrice_a
WHERE JanPrice_a.code=p.code) AS Jan,

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-02-08 : 12:32:36

What is the error? Can you post it?

marek
Starting Member

34 Posts

Posted - 2011-02-10 : 15:49:17

about "(Select JanPrice_a.Price_a" writes error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources