SQL query on existing query results

Question

SQL query on existing query results

Calvin

SSC Veteran

Points: 219
More actions
October 8, 2021 at 5:00 pm

#3938924
Hi there
I have a query result (sub-query) and want to refine it more with another query. I'm trying the group by method
```
SELECT         d.fstrFormattedID as FormattedID, ar.fstrStreet, lic.fstrStatus, a.FSTRJURISDICTION , inv.flngNumItems as Decals, a.fstrFuelType, inv.flngInvoiceKey

FROM            dbo.tblTRANSMITTALDETAILS AS a LEFT OUTER JOIN
                         dbo.tblReturn AS b ON a.FLNGDOCKEY = b.flngDocKey AND b.flngVer = 0 LEFT OUTER JOIN
                         dbo.tblIftaCarrier AS lic ON b.flngAccountKey = lic.flngAccountKey AND lic.flngVer = 0 LEFT OUTER JOIN
                         dbo.tblACCOUNT AS c ON lic.flngAccountKey = c.flngAccountKey LEFT OUTER JOIN
                         dbo.tblAccountInfo AS d ON c.flngAccountKey = d.flngAccountKey INNER JOIN
                         dbo.tblAddressRecord AS ar ON ar.fintProfileNumber = d.fintProfileNumber INNER JOIN
                         dbo.tblAddressProfile AS ap ON ap.fi64AddressKey = ar.fi64AddressKey AND c.flngCustomerKey = ap.flngCustomerKey LEFT OUTER JOIN
                         dbo.tblInvInvoice AS inv ON d.flngAccountKey = inv.flngAccountKey
WHERE        (d.fstrFormattedID = 'BC300740040') AND (b.fstrDocType = 'BC.RTFTA') AND (YEAR(b.fdtmFilingPeriod) BETWEEN YEAR('2021-07-09') - 3 AND YEAR('2021-07-09')) AND (a.fblnSurcharge = 0) AND (YEAR(inv.fdtmFilingPeriod) 
                         = YEAR('2021-07-09')) AND (ar.fstrAddressType = 'BSN') AND (ar.fblnActive = 1) AND (ar.fblnCurrent = 1)
GROUP BY   a.FSTRJURISDICTION, d.fstrFormattedID, inv.flngNumItems, ar.fstrStreet, lic.fstrStatus, a.fstrFuelType, inv.flngInvoiceKey
```
This gives:
FormattedID     fstrStreet fstrStatus     FSTRJURISDICTION Decals fstrFuelType flngnvoiceKey
BC300740040 ADDR1          A                  AB                               1               D                        981143552
BC300740040 ADDR1          A                  AB                               4              D                        1115951104
BC300740040 ADDR1          A                  AZ                                   1               D                        981143552
BC300740040 ADDR1          A                  AZ                                   4              D                        1115951104
BC300740040 ADDR1          A                  BC                                   1               D                        981143552
BC300740040 ADDR1          A                  BC                                   4              D                        1115951104
BC300740040 ADDR1          A                  CA                                   1               D                        981143552
BC300740040 ADDR1          A                  CA                                   4              D                        1115951104
BC300740040 ADDR1          A                  CO                                   1              D                       981143552
BC300740040 ADDR1          A                  CO                                  4               D                      1115951104

I'm trying to get the result: still group by a.FSTRJURISDICTION, sum the decals with one FSTRJURISDICTION / line
FormattedID     fstrStreet fstrStatus     FSTRJURISDICTION Decals fstrFuelType flngnvoiceKey
BC300740040 ADDR1         A                  AB                                 5              D                        981143552
BC300740040 ADDR1         A                  AZ                                 5               D                        981143552
BC300740040 ADDR1         A                  BC                                 5               D                        981143552
BC300740040 ADDR1         A                  CA                                 5               D                        981143552
BC300740040 ADDR1         A                  CO                                 5               D                       981143552
Of course, with each different fstrFormattedID, the data values will obviously be different, but with the same repeating pattern
Any suggestions appreciated
Thank you!
- This topic was modified 2 years, 6 months ago by Calvin.

Viewing 5 posts - 1 through 4 (of 4 total)

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

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

Any time this combination of values changes, you will get a new line. So pare down the fields in your select and group by until you only get what you want. There are different flngnvoiceKey values for the same juristiction so you get 1 line for each flngnvoiceKey

GROUP BY   a.FSTRJURISDICTION, d.fstrFormattedID, inv.flngNumItems, ar.fstrStreet, lic.fstrStatus, a.fstrFuelType, inv.flngInvoiceKey

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 2

Alternately, you could use windowing functions to sum the decals column like:

SUM(decals) OVER (PARTITION BY a.FSTRJURISDICTION, d.fstrFormattedID, inv.flngNumItems, ar.fstrStreet, lic.fstrStatus, a.fstrFuelType)

Not ideal as you will get 1 line for each flngnvoiceKey, but should give you the results you are looking for.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Calvin SSC Veteran Points: 219 More actions · Answer 3

Thanks for your reply homebrew01

Yes, I have been doing that and see how it turns out

Calvin SSC Veteran Points: 219 More actions · Answer 4

Thanks for your reply as well Mr. Brian Gale

I was thinking about that approach too