Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 2:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 7:55 AM
Points: 94, Visits: 480
If I want to Sum a field where the date has passed, how would I do that?
So my goal would be to Sum the OpenAmountUSD where the dueDate has gone by as a new column called OpenPastDue



SELECT vfact_aptransaction_datepart.openamountusd AS OpenAmtUSD,
dim_site.siteid,
dim_site.sitename,
dim_site.division,
vfact_aptransaction_datepart.duedate,
dim_vendor.vendorname,
dim_vendor.vendorclass,
vfact_aptransaction_datepart.weekdue,
vfact_aptransaction_datepart.yeardue,
dim_site.sitedescription
FROM vfact_aptransaction_datepart
INNER JOIN dim_site
ON vfact_aptransaction_datepart.siteid = dim_site.siteid
INNER JOIN dim_vendor
ON vfact_aptransaction_datepart.vendorid = dim_vendor.vendorid

Post #1430646
Posted Wednesday, March 13, 2013 4:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
Can you share the DDL for the tables and some sample data?
Post #1430677
Posted Wednesday, March 13, 2013 5:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 21,342, Visits: 15,017
Erin Ramsay (3/13/2013)
Can you share the DDL for the tables and some sample data?


And also what the desired output should look like?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1430679
Posted Wednesday, March 13, 2013 5:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 185, Visits: 920
mbrady5 (3/13/2013)
If I want to Sum a field where the date has passed, how would I do that?
Usually using SUM, WHERE and likely GROUP BY.



Alex Suprun
Post #1430684
Posted Thursday, March 14, 2013 10:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
Definitely need to provide more details. I would highly recommend that you use aliases in your queries. Your table names are very long and not terribly user friendly. I took a shot in the dark. See if this is somewhat close to what you are looking for.

SELECT v.openamountusd AS OpenAmtUSD,
SUM(Case when v.duedate > getdate() then v.openamountusd else 0 end) as OpenPastDue,
s.siteid,
s.sitename,
s.division,
v.duedate,
ven.vendorname,
ven.vendorclass,
v.weekdue,
v.yeardue,
s.sitedescription
FROM vfact_aptransaction_datepart v
INNER JOIN dim_site s
ON v.siteid = s.siteid
INNER JOIN dim_vendor ven
ON v.vendorid = ven.vendorid
group by v.openamountusd,
s.siteid,
s.sitename,
s.division,
v.duedate,
ven.vendorname,
ven.vendorclass,
v.weekdue,
v.yeardue,
s.sitedescription



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1431125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse