Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
me55enger
Starting Member
4 Posts |
 Posted - 2014-09-19 : 16:55:41
|
| I have two tables that I am pulling data from: an item table and a sales table. Almost all of the information comes from the item table (item description, location, amount on hand). The last field wanted is Year-To-Date sales. I can pull the sales field from the sales table, which gives me all sales from the creation of the db. I need to be able to run a date variable of This Year on that sales field only. I have a date field I can reference off of in the sales table. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 17:00:21
|
| I didn't follow your post. Could you explain with table design and sample data?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
me55enger
Starting Member
4 Posts |
Posted - 2014-09-19 : 17:10:00
|
quote:
Originally posted by tkizer
I didn't follow your post. Could you explain with table design and sample data?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
I have two tables: Item and Sales. I am pulling from Item: Item Description, Item Location, Amount On Hand, Amount On Order. I am pulling from Sales: Item Sales. If I run the query with just the select statement pulling these fields, the Sales I get are from the beginning of the database. I need for the Sales field to show the Year-To-Date total instead of the complete total. There is a Sales Date field in the Sales table to pull from.Sample:Item Desc -- Location -- On Hand -- On Order -- YTD SalesFERT 46 -- 110 -- 27 -- 30 -- $216.87The YTD Sales figure is actually for every sale made, not for YTD. Need it to be YTD. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 17:13:17
|
| I still don't understand, but here's an example query:select t1.*, t2.*from table1 t1join table2 t2 on t1.PKcolumn = t2.PKcolumnwhere t2.somedatetimecolumn >= '01/01/2014'Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
me55enger
Starting Member
4 Posts |
Posted - 2014-09-19 : 17:35:27
|
| Table 1 = agitmmstTable 2 = agstmsstEverything except for Sales is coming from Table 1. Table 1 is joined with Table 2 by Item Description and Location Number. All I am grabbing right now is the Sales field from Table 2. I need to manipulate this total by using a date variable. I have a Date field in Table 2. I need to know how to make this query return just sales totaled from 1/1/14 to current.Here is my query:Select agitmmst.agitm_no, agitmmst.agitm_loc_no, agitmmst.agitm_desc, agitmmst.agitm_un_desc, agitmmst.agitm_un_on_hand, agitmmst.agitm_un_pend_ivcs, agitmmst.agitm_un_on_order, agitmmst.agitm_un_mfg_in_prs, agitmmst.agitm_un_fert_committed, agitmmst.agitm_un_ord_committed, agitmmst.agitm_un_cnt_committed, agitmmst.agitm_un_on_hand - agitmmst.agitm_un_pend_ivcs + agitmmst.agitm_un_on_order + agitmmst.agitm_un_mfg_in_prs - agitmmst.agitm_un_fert_committed - agitmmst.agitm_un_cnt_committed - agitmmst.agitm_un_ord_committed As Available, agitmmst.agitm_class, agitmmst.agitm_std_un_cost, agstmmst.agstm_sls From agitmmst Inner Join agstmmst On agstmmst.agstm_itm_no = agitmmst.agitm_no And agstmmst.agstm_loc_no = agitmmst.agitm_loc_no Where agitmmst.agitm_desc Like 'FERT%'The agstmmst.agstm_sls field is the Sales field. I need to be able to only show a total of the Sales from 1/1/14 to current. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-19 : 17:42:31
|
| Please see the example query I posted, specifically the where clause.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
me55enger
Starting Member
4 Posts |
Posted - 2014-09-19 : 17:44:44
|
| Ah, missed the >= I'll give that a try and let you know. |
|
|
|
|
|
|
|
|
Running a Date Variable on One Field