Two select statements, need one result

Question

Two select statements, need one result

Vivian Dietz

SSC Rookie

Points: 30
More actions
May 17, 2006 at 12:35 pm

#112247

Hello,
I have 2 statements I need combined to produce one result set. One statement finds "DailyReceipts" and the other finds "MonthlyReceipts". How can I put these together and have zeroes report if no data is found as shown below in the Expected Results? (Yes, I'm new to T-SQL!!) Any help is appreciated.
Expected Results
InsGroupID      Date    DailyReceipts      MonthlyReceipts
Insurance1      5/16/06     100.00           100.00
Insurance2      5/16/06       0.00             200.00
Insurance3      5/16/06       0.00              300.00
DailyReceipts Statement
USE livedb
SELECT DMisInsurance.InsuranceGroupID AS InsGroupID,
  CONVERT(CHAR(12),BarCollectionTransactions.BatchDateTime,101) AS 'Date',
  SUM(BarCollectionTransactions.Amount) AS 'DailyReceipts'
FROM  BarCollectionTransactions
  INNER JOIN BarVisits ON
  BarCollectionTransactions.SourceID = BarVisits.SourceID

  AND BarCollectionTransactions.BillingID = BarVisits.BillingID
  INNER JOIN DMisInsurance ON
  BarCollectionTransactions.SourceID = DMisInsurance.SourceID
  AND BarVisits.PrimaryInsuranceID = DMisInsurance.InsuranceID
WHERE (BarCollectionTransactions.BatchDateTime = CONVERT(CHAR(12), getdate() - 1,101))
  AND BarCollectionTransactions.Type = 'R'
GROUP BY DMisInsurance.InsuranceGroupID

MonthlyReceipts Statement
USE livedb
SELECT DMisInsurance.InsuranceGroupID AS InsGroupID,
  SUM(BarCollectionTransactions.Amount) AS 'MonthlyReceipts'
FROM  BarCollectionTransactions
  INNER JOIN BarVisits ON
  BarCollectionTransactions.SourceID = BarVisits.SourceID

  AND BarCollectionTransactions.BillingID = BarVisits.BillingID
  INNER JOIN DMisInsurance ON
  BarCollectionTransactions.SourceID = DMisInsurance.SourceID
  AND BarVisits.PrimaryInsuranceID = DMisInsurance.InsuranceID
WHERE BarCollectionTransactions.Type = 'R'
  AND datediff (month, BarCollectionTransactions.BatchDateTime, getdate ()) =0

GROUP BY DMisInsurance.InsuranceGroupID

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

The easiest way in my opinion would be to probably use two seperate derived tables. Derive them from BarCollectionTransactions each with the data you need.

Something like:

SELECTins.InsuranceGroupID as InsGroupID, bct_day.Date, bct.DailyReceipts, bct.MonthlyReceipts

FROMBarVisits bv

JOINDMisInsurance ins

ONbv.PrimaryInsuranceID = ins.InsuranceID

JOIN(

SELECTSourceID, CONVERT(CHAR(12), BatchDateTime, 101) as 'Date', SUM(Amount) AS 'DailyReceipts'

FROMBarCollectionTransactions

WHEREBatchDateTime = CONVERT(CHAR(12), GETDATE() - 1, 101)

AND[Type] = 'R'

GROUP BY SourceID, CONVERT(CHAR(12), BatchDateTime, 101)

) bct_day

ONbv.SourceID = bct_day.SourceID

ANDins.SourceID = bct_day.SourceID

JOIN(

SELECTSourceID, SUM(Amount) AS 'MonthlyReceipts'

FROMBarCollectionTransactions

WHEREDATEDIFF(month, BatchDateTime, GETDATE()) = 0

AND[Type] = 'R'

GROUP BY SourceID

) bct_month

ONbv.SourceID = bct_month.SourceID

ANDins.SourceID = bct_month.SourceID

ORDER BYbct_day.Date, ins.InsuranceGroupID

noeld SSC Guru Points: 96590 More actions · Answer 2

Is there something I am missing? You don't have a GROUP BY on your first statement including the BatchDateTime column??? did that ever worked?

* Noel

Vivian Dietz SSC Rookie Points: 30 More actions · Answer 3

Aaron - I tried your suggestion but received the following message. I added the alias but the results didn't group by InsGroupID. Any other ideas?

Server: Msg 107, Level 16, State 2, Line 1

The column prefix 'bct' does not match with a table name or alias name used in the query.

Server: Msg 107, Level 16, State 1, Line 1

The column prefix 'bct' does not match with a table name or alias name used in the query.

noeld SSC Guru Points: 96590 More actions · Answer 4

Vivian,

the alias are bct_day and bct_month

* Noel

Vivian Dietz SSC Rookie Points: 30 More actions · Answer 5

Noel,

You are right....I need the BatchDateTime in the GROUP BY. I've been out of the office since last Wednesday so I haven't had the opportunity to work on this much. I did, however, make the correction on the alias names and executed the statement. The results didn't group by insurance group id. It created a row for what appeared to be for each batch.

Thanks,

Vivian

stax68 SSChampion Points: 11711 More actions · Answer 6

So you want to combine a figure for the month to date with a figure for yesterday? I assume you don't want your month-to-date receipts broken down by day. You could do something like:

SELECT

DMisInsurance.InsuranceGroupID InsGroupID,

convert

(char(12),getdate(),101) Date,

sum

(case

when datediff (month, BarCollectionTransactions.BatchDateTime, getdate ())=0

then BarCollectionTransactions.Amount

else 0

end) MonthlyReceipts,

sum

(case

when BarCollectionTransactions.BatchDateTime = CONVERT(CHAR(12), getdate() - 1,101)

then BarCollectionTransactions.Amount

else 0

end) DailyReceipts

FROM

BarCollectionTransactions

INNER

JOIN BarVisits ON

BarCollectionTransactions

.SourceID = BarVisits.SourceID

AND BarCollectionTransactions.BillingID = BarVisits.BillingID

INNER

JOIN DMisInsurance ON

BarCollectionTransactions

.SourceID = DMisInsurance.SourceID

AND BarVisits.PrimaryInsuranceID = DMisInsurance.InsuranceID

WHERE

BarCollectionTransactions.Type = 'R'

AND (datediff (month, BarCollectionTransactions.BatchDateTime, getdate ())=0

or datediff(day, BarCollectionTransactions.BatchDateTime, getdate ())=-1

)

GROUP

BY DMisInsurance.InsuranceGroupID

It might be worth reexamining whether there is a better way of presenting the information, or more information. But then it almost always is, if you have time...

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Vivian Dietz SSC Rookie Points: 30 More actions · Answer 7

Hey, Hey! With the biggest smile on my face, I say thank you!! Your suggestion worked perfectly stax68.