SQL question | Forum

Topic location: Forum home » Reporting » DR Reporting
Feb 19 '15
2015-02-19 08:39:49
Hello L,
I am running a query out of BarPeStatusVectors and I want the results to show two balances for each VisitID based on the PeriodDateTime. I have made several attempts but have not gotten anywhere. Does anyone know how I can pull this into my query?
Basically I want:
Balance1 = Balance where PeriodDateTime = '12/31/12'
And
Balance2 = Balance where PeriodDateTime = '12/31/13'
Thanks!
Francisco "Frank" Amor
Financial Systems Coordinator, MIS
Southwest Medical Center
PO Box 1340
Liberal, KS 67905
(620) 629-6377 - O
(620) 629-6352 - F
famor@swmedcenter.com

________________________________
Confidential: This electronic message and all contents contain information from Southwest Medical Center which may be privileged, confidential or otherwise protected from disclosure. This information is intended to be for the addressee only. If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this electronic message in error, please notify the sender and destroy the original message and all copies. Thank you.
Deleted user
Deleted user Feb 19 '15
2015-02-19 09:05:56
Frank,
The easiest way would be to set up a sub-select using the conversion of the
PeriodDateTime by using the following : DATEADD(year, -1, PeriodDateTime)
SELECT
A. SourceID,
A.VisitID,
A.BillingID,
A.Balance AS ŒBalance1¹,
Eg. ( SELECT B.Balance
FROM [Livendb].dbo.BarPeStatusVectors AS B
WHERE DATEADD(year, -1, A.PeriodDateTime) = A.PeriodDateTime
B.SourceID = A.SourceID
B.BilliingID = A.BillingID
) AS ŒBalance2¹
FROM [Livendb].dbo.BarPeStatusVectors AS A
Hope this help!
gmc

Garry McAninch
Principal
Dimension Analysis
Phone : 905-704-1356
Mobile : 905-941-1356
Fax : 905-688-2256
Email : * gmcaninch@dimensionsanalysis.com
Web : www.dimensionsanalysis.com
Sent from the: MAC 15 Retina
Data Warehouse Engineering * Digital Dashboards * Multi-Dimensional
Reporting * Information Management Consulting * Data Integration
P Before printing, please consider the environment.
This communication is intended solely for the addressee(s) and contains
information that is privileged, confidential and subject to copyright. Any
unauthorized use, copying, review or disclosure is prohibited. If received
in error, please notify us immediately by return-email.

Deleted user
Deleted user Feb 19 '15
2015-02-19 09:10:11
Correction to the query. The WHERE Clause should have been the following:
WHERE DATEADD(year, -1, A.PeriodDateTime) = B.PeriodDateTime
You can also go the other way by changing the ­1 to 1 to look for a future
period..
gmc

Garry McAninch
Principal
Dimension Analysis
Phone : 905-704-1356
Mobile : 905-941-1356
Fax : 905-688-2256
Email : * gmcaninch@dimensionsanalysis.com
Web : www.dimensionsanalysis.com
Sent from the: MAC 15 Retina
Data Warehouse Engineering * Digital Dashboards * Multi-Dimensional
Reporting * Information Management Consulting * Data Integration
P Before printing, please consider the environment.
This communication is intended solely for the addressee(s) and contains
information that is privileged, confidential and subject to copyright. Any
unauthorized use, copying, review or disclosure is prohibited. If received
in error, please notify us immediately by return-email.

Feb 19 '15
2015-02-19 10:01:40
Frank.
I think something like this is what you're after. You can change the date ranges to single dates, I just wanted more data to validate.

SELECT VisitID, PeriodDateTime, Balance,
CASE
WHEN PeriodDateTime BETWEEN '01/01/12' AND '12/31/12'
THEN Balance
END AS BAL1,
CASE
WHEN PeriodDateTime BETWEEN '01/01/13' AND '12/31/13'
THEN Balance
ELSE 0000000
END AS BAL2
FROM BarPeStatusVectors
WHERE PeriodDateTime BETWEEN '01/01/12' AND '12/31/13'
AND Balance > '0.00'
ORDER BY VisitID

Frank
Frank Hosea
Franklin Memorial Hospital
Feb 19 '15
2015-02-19 11:13:08
If I'm understanding correctly, I think this will give you what you're looking for....
SELECT
bal1.VisitID
,bal1.PeriodDateTime AS Period1
,bal1.Balance AS Balance1
,bal2.PeriodDateTime AS Period2
,bal2.Balance AS Balance2
FROM
BarPeStatusVectors AS bal1
LEFT OUTER JOIN
BarPeStatusVectors AS bal2
ON bal1.BillingID = bal2.BillingID AND bal2.PeriodDateTime = '2013-12-31'
WHERE
bal1.PeriodDateTime = '2012-12-31'
AND bal1.Balance > 0

Will Conklin
Charlotte Hungerford Hospital - IT
860.496.6724
wconklin@hungerford.org<mailto:wconklin@hungerford.org>

chenjinyan
chenjinyan Nov 21 '18
mbt nba jerseys coach outlet jordan 8 marcelo burlon ugg outlet undefeated clothing ray ban kevin durant shoes maui jim sunglasses ugg outlet nike air max adidas outlet mulberry handbags nike air max 1 kate spade outlet nike air max 97 ray ban sunglasses canada goose sale oakley sunglasses snapback hats under armour shoes ugg boots nike cortez nike air max plus versace sunglasses adidas nmd sophia webster shoes mlb jerseys jordans uggs official site pandora jewelry ralph lauren ralph lauren nike dunks christian louboutin ray ban coach outlet online baby uggs air jordan philipp plein mcm backpack pandora jewelry pandora outlet ugg sale lebron ambassador 10 nike cortez michael kors outlet michael kors handbags nfl jerseys jimmy choo camisetas de futbol ugg australia ugg outlet adidas nmd ugg boots yoga pants adidas slides tiffany jewelry michael kors outlet air max nicholas kirkwood dior handbags cheap jerseys adidas football boots skechers sandals canada goose outlet supreme manolo blahnik mbt ugg boots monster headphones replica rolex watches ferragamo ugg boots canada goose jackets coach factory outlet jordan shoes puma slides jordan vetements clothing ugg outlet kd 10 prada outlet maillot de foot pas cher adidas flip flops vans outlet nike lunarglide oakley sunglasses jordan xx9 adidas nmd superdry clothing snapback hats coach outlet coach bags moncler jackets ugg store oakley sunglasses nike sb saint laurent sunglasses swarovski jewelry ugg clearance the north face ray ban wayfarers jordan vapormax nobis jackets cheap nfl jerseys pandora jewelry nike air max mac cosmetics coach outlet polo ralph lauren jordans nike huarache ray bans uggs on sale ugg outlet nike air max 270 louboutin soccer jerseys mcm outlet fitflops polo outlet jordans nike huarache herve leger dresses prada nike tennis north face north face outlet roshe run paul george shoes nike sb nike air max coach outlet michael kors outlet valentino ralph lauren balenciaga shoes hollister moncler outlet jordan 11 pasotti ombrelli y3 shoes woolrich jackets links of london kate spade outlet vibram fivefingers ray ban sunglasses off-white timberland north face outlet north face jackets moncler outlet fila shoes ray bans michael kors handbags true religion outlet montblanc fountain pen air jordan kate spade outlet tommy hilfiger coach wallets louboutin nike air max ugg sale nike presto lebron james jersey bottega veneta true religion outlet vans shoes north face outlet timberland outlet burberry outlet nhl jerseys michael kors outlet ugg boots birkenstock outlet nike outlet christian louboutin pandora jewelry/strong>nike free ray ban sunglasses suicoke sandals christian louboutin outlet x-large clothing ray ban sunglasses nike outlet store ugg clearance canada goose outlet coach outlet giuseppe zanotti manchester united jersey tory burch outlet shoe carnival baseball jerseys ralph lauren outlet victoria's secret polo shirts nike tn beats by dre salvatore ferragamo lebron soldier 9 longchamp jordan shoes flip flops yeezy shoes vans outlet givenchy handbags birkenstock sandals michael kors outlet clearance sac longchamp nike outlet store air max jordan 5 ray ban coach outlet online ugg for women ray ban sunglasses mcm backpack nike outlet prada handbags ugg australia mbt fjallraven outlet ugg outlet swarovski polo ralph lauren north face jackets hermes outlet north face nike outlet store mbt ugg for kids michael kors handbags nike kyrie 2 herve leger dresses tory burch outlet replica watches tory burch sandals jordan 1 adidas yeezy mont blanc saucony shoes yeezy desert rat 500 nhl jerseys pandora jewelry nike revolution malone souliers 201811.21chenjinyan