Select Help needed - SQL Server Forums

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)

Select Help needed

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 13:38:34

[code]SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
ORDER BY Invoice_line_seq_ise[/code]

Give me this:

[code]
MainId Sort Model Serial Revenue
54100334425655 ZSYST 000492 0.00
54100334425655 2 AF401 7207120361 0.00
54100334425655 5 NULL NULL 0.00
54100334425655 6 NULL NULL 35.45
54100334425655 7 NULL NULL 68.25
54100334425655 9 R5535 3696050166 0.00
54100334425655 C NULL NULL 0.00
54100334425655 E R5840 7467120488 0.00
54100334425655 H NULL NULL 0.00
54100334425655 I NULL NULL 23.96
54100334425655 J NULL NULL 12.05
54100334425655 K R5840 A7468020296 0.00
54100334425655 O NULL NULL 0.00
54100334425655 P NULL NULL 2.53
54100334425655 Q NULL NULL 1.27
54100334425655 R R6645 3356450265 0.00
54100334425655 V NULL NULL 0.00
54100334425655 W NULL NULL 0.17
54100334425655 X NULL NULL 0.44[/code]

Is it possible to get this:
[code]
MainId Model Serial Revenue
54100334425655 ZSYST 000492 0.00
54100334425655 AF401 7207120361 103.70
54100334425655 R5535 3696050166 0.00
54100334425655 R5840 7467120488 36.01
54100334425655 R5840 A7468020296 3.80
54100334425655 R6645 3356450265 0.61
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:47:46

dont the tables Invoice_Serial & Invoice_Product have any related fields other than MainId? how would you decide what NULL records should be merged to waht other records?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:07:39

[code];With Your_CTE (Seq,MainId,Sort,Model,Serial,Revenue) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY MainId ORDER BY Sort),MainId,Sort,Model,Serial,Revenue
FROM
(
SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
ORDER BY Invoice_line_seq_ise
)t

)

SELECT c.MainId,
c.Sort,
c.Model,
c.Serial,
c.Revenue + COALESCE(s.TotalRev,0)
FROM Your_CTE c
OUTER APPLY(SELECT TOP 1 Seq
FROM Your_CTE
WHERE Seq<t.Seq
AND Model IS NOT NULL
AND Serial IS NOT NULL
ORDER BY Seq DESC) prev
OUTER APPLY(SELECT TOP 1 Seq
FROM Your_CTE
WHERE Seq>t.Seq
AND Model IS NOT NULL
AND Serial IS NOT NULL
ORDER BY Seq ASC) nxt
OUTER APPLY (SELECT SUM(Revenue) AS TotalRev
FROM Your_CTE
WHERE Seq > COALESCE(prev.Seq,-1)
AND Seq < COALESCE(nxt.Seq,99999)
AND Model IS NULL
AND Serial IS NULL)s
WHERE c.Model IS NOT NULL
AND c.Serial IS NOT NULL[/code]

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 14:10:18

Unfortunately no.

The Null fields role up to the first record that has a model/serial. The order is determined by the sort column.

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 14:15:39

I get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

If I comment out ORDER BY Invoice_line_seq_ise

I get this error:

The multi-part identifier "t.Seq" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:19:30

it should be c

;With Your_CTE (Seq,MainId,Sort,Model,Serial,Revenue) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY MainId ORDER BY Sort),MainId,Sort,Model,Serial,Revenue
FROM
(
SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId  = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
)t

)

SELECT c.MainId,
c.Sort,
c.Model,
c.Serial,
c.Revenue + COALESCE(s.TotalRev,0)
FROM Your_CTE c
OUTER APPLY(SELECT TOP 1 Seq
            FROM Your_CTE
            WHERE Seq<c.Seq
            AND Model IS NOT NULL
            AND Serial IS NOT NULL
            ORDER BY Seq DESC) prev
OUTER APPLY(SELECT TOP 1 Seq
            FROM Your_CTE
            WHERE Seq>c.Seq
            AND Model IS NOT NULL
            AND Serial IS NOT NULL
            ORDER BY Seq ASC) nxt
OUTER APPLY (SELECT SUM(Revenue) AS TotalRev
             FROM Your_CTE
             WHERE Seq > COALESCE(prev.Seq,-1) 
             AND Seq < COALESCE(nxt.Seq,99999)
             AND Model IS NULL
             AND Serial IS NULL)s 
WHERE c.Model IS NOT NULL
AND c.Serial IS NOT NULL
ORDER BY c.Sort

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 14:25:12

That gives me this:


MainId	Sort	Model	Serial	(No column name)
54100334425655	  	ZSYST	000492      	0.00
54100334425655	 2	AF401	7207120361  	103.70
54100334425655	 9	R5535	3696050166  	103.70
54100334425655	 E	R5840	7467120488  	36.01
54100334425655	 K	R5840	A7468020296 	39.81
54100334425655	 R	R6645	3356450265  	4.41

The 3rd, 5th and 6th rows revenue is off...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:32:44

and this?

;With Your_CTE (Seq,MainId,Sort,Model,Serial,Revenue) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY MainId ORDER BY Sort),MainId,Sort,Model,Serial,Revenue
FROM
(
SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId  = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
)t

)

SELECT c.MainId,
c.Sort,
c.Model,
c.Serial,
c.Revenue + COALESCE(s.TotalRev,0)
FROM Your_CTE c
OUTER APPLY(SELECT TOP 1 Seq
            FROM Your_CTE
            WHERE Seq>c.Seq
            AND Model IS NOT NULL
            AND Serial IS NOT NULL
            ORDER BY Seq ASC) nxt
OUTER APPLY (SELECT SUM(Revenue) AS TotalRev
             FROM Your_CTE
             WHERE Seq > c.Seq
             AND Seq < COALESCE(nxt.Seq,99999)
             AND Model IS NULL
             AND Serial IS NULL)s 
WHERE c.Model IS NOT NULL
AND c.Serial IS NOT NULL
ORDER BY c.Sort

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-27 : 14:36:12

[code]
Create @tbl table (MYID identity(1,1),MainID varchar(30),Sort Varchar(2),Model Varchar(10),Serial varchar(30),Revenue money)

Insert Into @tbl(MainID,Sort,Model,Serial,Revenu)
SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
ORDER BY Invoice_line_seq_ise

Update a
set a.Model = (select top 1 aa.Model from @Tbl aa where aa.MyID < a.MyID and not aa.Model is null order by aa.MYID desc)
from
@Tbl a
where
a.Model is null

Select a.MainID,a.Model,Max(a.Serial) as Serial,Sum(a.Revenue) as Revenue
From
@Tbl
group by a.MainID,a.Model
[/code]

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 14:40:21

Perfect!

Thanks for your help and patience.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:50:57

quote:
Originally posted by Togaspoon

Perfect!

Thanks for your help and patience.

No worries
You're always welcome

Togaspoon
Starting Member

42 Posts

Posted - 2008-08-27 : 14:57:43

I was checking out your 2nd solution and wanted to give that one a go because I actually understood what you were doing (unlike your first solution).

[edit:] I guess that was Vinnie881 crack at it [/edit]

I made a few minor tweaks because it was only giving me 5 records instead of the expected 6.

DECLARE @tbl table (MYID int identity(1,1) ,MainID varchar(30),Sort Varchar(2),Model Varchar(10),Serial varchar(30),Revenue money)

Insert Into @tbl(MainID,Sort,Model,Serial,Revenue)
SELECT MainId,Invoice_line_seq_ise AS 'Sort',Model,Serial,0 AS 'Revenue'
FROM Invoice_Serial
WHERE MainId = 54100334425655

UNION ALL

SELECT MainId,Inv_line_Seq_inp,NULL,NULL,Extended_price_inp
FROM Invoice_Product
WHERE MainId = 54100334425655
ORDER BY Invoice_line_seq_ise

Update a
set a.Model = (select top 1 aa.Model from @Tbl aa where aa.MyID < a.MyID and not aa.Model is null order by aa.MYID desc)
from
@Tbl a
where
a.Model is null

Update a
set a.Serial = (select top 1 aa.Serial from @Tbl aa where aa.MyID < a.MyID and not aa.Serial is null order by aa.MYID desc)
from
@Tbl a
where
a.Serial is null

Select a.MainID,a.Model,a.Serial,Sum(a.Revenue) as Revenue
From
@Tbl a
group by a.MainID,a.Model,A.serial

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-27 : 15:21:08

You can combine the update


Update a
set a.Model = (select top 1 aa.Model from @Tbl aa where aa.MyID < a.MyID and not aa.Model is null order by aa.MYID desc)
,a.Serial = (select top 1 aa.Serial from @Tbl aa where aa.MyID < a.MyID and not aa.Serial is null order by aa.MYID desc)
from
@Tbl a
where 
a.Model is null

Compare the execution plans and statistics on your live data to see what performs best for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources