March 13, 2013 at 2:57 pm
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
March 13, 2013 at 4:53 pm
Can you share the DDL for the tables and some sample data?
March 13, 2013 at 5:03 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 5:28 pm
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.
March 14, 2013 at 10:57 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply