Always return a row of data. 1

[zemp]

SQL Server 2000 SP4

I have a query that is based on a date range and a bit flag. The query returns a recordset of two records. One record for each state of the bit flag. The values are a SUM.

Sometimes the query correctly return only one record. However in this case I would still like two records in the recordset. One with the returned sum value and one with a return value of Zero.

Is this possible and if so how would I go about doing this?


zemp

 

[AlexCuse]

Can you post what you have now? I think that what you are going for can be accomplished fairly easily.

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Ok let me try to exp[lain what is I am doing.

First I have a two views to get the data based on the bit flags.

CREATE VIEW dbo.x_PastCostTotal1
AS
SELECT     dbo.utClientMapping.GrpID, dbo.utInvoices.InvMonth, dbo.udfDateSerial(dbo.utInvoices.InvMonth) AS MDate, SUM(dbo.utInvoices.Total) AS Total, 
                      dbo.utInvoices.IsASO
FROM         dbo.utInvoices INNER JOIN
                      dbo.utClientMapping ON dbo.utInvoices.Client = dbo.utClientMapping.ClientNumber AND 
                      dbo.utInvoices.GroupNumber = dbo.utClientMapping.GroupNumber
GROUP BY dbo.utClientMapping.GrpID, dbo.utInvoices.InvMonth, dbo.utInvoices.IsASO
HAVING      (dbo.utInvoices.IsASO= 1)

The second has the flag, IsASO, equal to 0 and the view name ends in a zero.

Second i use a union to pull the two recordsets together.

CREATE VIEW dbo.x_PastYearTotal
AS
SELECT     A.GrpID AS FGrpID, 'Insured' AS FType, A.Total AS FAmt, A.InvMonth AS FMonth, A.MDate AS FDate, 1 AS FOrder
FROM         x_PastCostTotal0 A
UNION ALL
SELECT     B.GrpID AS FGrpID, 'ASO' AS FType, B.Total AS FAmt, B.InvMonth AS FMonth, B.MDate AS FDate, 2 AS FOrder
FROM         x_PastCostTotal1 B

Then I have a query that does the summing of the values (FAMT).

SELECT  Ftype, SUM(FAmt) AS [FAmt]
FROM dbo.[x_PastYearTotal] 
WHERE (FGrpID = 173) AND (FDate BETWEEN '5/1/2004' AND '4/30/2005') 
GROUP BY FOrder,Ftype
Order By FOrder

The parameters are only in the last query. This works properly except sometimes it only returns a record for one half of the union and it is possible for no records to be returned.

What i would like is a result set like this

FType   FAmt
Insured     20,000
ASO              0

rather than

FType   FAmt
Insured     20,000

I hope you can follow.

zemp

 

[gmmastros]

If I understand correctly...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Id [COLOR=blue]int[/color], BitValue [COLOR=blue]Bit[/color], [COLOR=blue]Value[/color] [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](1,1,10)
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](1,1,20)
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](2,1,30)
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](1,0,40)

[green]/*
Because filter id=1, you will get 2 rows in the output.
*/[/green]
[COLOR=blue]Select[/color] BitValue, Sum([COLOR=blue]Value[/color])
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Id = 1
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id, BitValue

[green]/*
only 1 row because id=2 only has 1 record.
*/[/green]
[COLOR=blue]Select[/color] BitValue, Sum([COLOR=blue]Value[/color])
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Id = 2
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id, BitValue

[green]/*
Since we are summing the data, we could union all and then sum so that you have 2 records in the output.
*/[/green]
[COLOR=blue]Select[/color] BitValue, Sum(SumOfValue)
[COLOR=blue]From[/color]   (
       [COLOR=blue]Select[/color] BitValue, Sum([COLOR=blue]Value[/color]) [COLOR=blue]As[/color] SumOfValue
       [COLOR=blue]From[/color]   @Temp
       [COLOR=blue]Where[/color]  Id = 2
       [COLOR=blue]Group[/color] [COLOR=blue]By[/color] BitValue

       Union All [COLOR=blue]Select[/color] 0, 0
       Union All [COLOR=blue]Select[/color] 1, 0
       ) [COLOR=blue]As[/color] A
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] BitValue

zemp, I hope this is what you were looking for. If not, please post some sample data and expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I think that this will work for you? You could probably hard-code the values on the left side of the join if this is a big table and it slows things down.

select a.Ftype, SUM(b.FAmt) as [FAmt]
from 
(
select distinct Ftype
FROM dbo.[x_PastYearTotal] 
WHERE Ftype in ('ASO', 'Insured')
) a
left join
dbo.[x_PastYearTotal] b
on a.Ftype = b.Ftype
and b.FGrpID = 173
and b.FDate between '20040501' and '20050430'

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Very, very close Alex. I just had to add a group by and then I took care of the null with a case. Here is what i ended up with.

select a.Ftype, case when SUM(FAmt) is null then 0 else SUM(FAmt) end as [FAmt]
from (select distinct Ftype
	FROM dbo.[x_PastYearTotal] 
	WHERE Ftype in ('Insured', 'ASO')) a left join
	dbo.[x_PastYearTotal] b on a.Ftype = b.Ftype 	and b.FGrpID = 173 and b.FDate between '20040501' and '20050430'
Group by a.ftype
order by a.ftype desc

The result set was

FType           FAmt
20000.0000      .0000

Thanks again Alex, a star for that.

zemp

 

[AlexCuse]

Glad you got it working

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Sorry the end result set is as follows.

FType          FAmt
Insured     20000.0000
ASO              .0000

I must have got excited by the fact that is was working when I was posting the result set earlier. Again sorry for the confusion.

zemp

 

[AlexCuse]

I knew what you mean ;-)

Ignorance of certain subjects is a great part of wisdom