13 Replies - 1463 Views - Last Post: 24 February 2011 - 06:33 AM

#1 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Pulling in data within a date range

Posted 11 February 2011 - 07:13 AM

Can someone please take a look at the below code and tell me where I am going wrong here...?

The purpose is to pull the glacct codes within a user input date range. I can get the first glacct to pull in and match known totals...however it doesn't appear that the additional glaccts are playing by the same rules.

SELECT     gldet.kequipnum, gldet.glamt, gldet.glacct             
FROM       gldet    
WHERE      gldate >= @startdate AND gldate <= @enddate AND        
 	   gldet.glacct IN ('40400', '50500', '59004', '59014', '59024', '59034')   



Any insight is appreciated!

Is This A Good Question/Topic? 0
  • +

Replies To: Pulling in data within a date range

#2 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Pulling in data within a date range

Posted 11 February 2011 - 08:20 AM

It looks like it should work. But a couple of thoughts:

- Have you tried hardcoding the values instead of using @startdate and @enddate?
- What if you exclude the accounts?
- What are the values of @startdate and @enddate? Is @startdate less than @enddate?

You can also use gldate BETWEEN @startdate AND @enddate.
Was This Post Helpful? 1
  • +
  • -

#3 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 11 February 2011 - 08:51 AM

Yes...I hard entered the date ranges and received the same result.

I am starting to think the "match numbers" I was given may not be correct...

Making me nuts!
Was This Post Helpful? 0
  • +
  • -

#4 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Pulling in data within a date range

Posted 11 February 2011 - 11:31 AM

SELECT     gldet.kequipnum, gldet.glamt, gldet.glacct, gldet.gldate             
FROM       gldet    
WHERE     gldet.glacct IN ('40400', '50500', '59004', '59014', '59024', '59034') 
order by gldet.gldate



Use this query and see the output. are all the results you got in the (@startDate, @EndDate) interval?
Was This Post Helpful? 1
  • +
  • -

#5 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 11 February 2011 - 11:49 AM

There is a lifetime of historical data in these databases and the aim is to be able to extract based on an ever changing data range...which is the reason for the user submitted date range entry.

If I run this code...there isn't anything to keep it from pulling all entries through regardless of timeframe...

Thanks so much for the input...
Was This Post Helpful? 0
  • +
  • -

#6 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Pulling in data within a date range

Posted 11 February 2011 - 03:29 PM

The point is that you're testing to see the dates and if any data comes back. Try a TOP 10 first and see if data is coming back. If you're not getting any data back when hard coding the numbers you need to look more into the issue and see if the gldet.glacct is what is filtering all your data out.
Was This Post Helpful? 0
  • +
  • -

#7 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 11 February 2011 - 03:30 PM

Another quick question...

Is there an easy way to sum the glacct items that start with 5 and pull this combined total into another column hiding the individual columns listed now??
Was This Post Helpful? 0
  • +
  • -

#8 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 13 February 2011 - 08:01 AM

Thank you...

I have the date range working...always helps when you pull your own data to compare to instead of taking the word of others as to what you are trying to match to!

At this point I am trying to find a way to clean up the output into 4 columns- Equip Number, Income (40400 total), Expense (a sum of all glaccts starting with 5) and Total.

I am finding issues with breaking the 40400 line item out of the primary calling line as it is right now. Everytime I pull this out the result changes. I also am fighting with getting the output to sum and only show as one output when I run the querry/report.

Any insight??
Was This Post Helpful? 0
  • +
  • -

#9 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Pulling in data within a date range

Posted 14 February 2011 - 11:04 AM

To get the Expense you could do something like this:

SELECT EquipmentNumber, SUM(Income) AS totalIncome, SUM(CASE WHEN LEFT(glacct, 1) = '5' THEN expenseAmount ELSE 0 END) AS Expense
FROM yourtable
GROUP BY EquipmentNumber



if glacct is a number then take off the single ticks. What is in the Total Column? How is it calculated?
Was This Post Helpful? 0
  • +
  • -

#10 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 14 February 2011 - 01:44 PM

Here is what I have at the moment...

SELECT     gldet.kequipnum, equip.gmmottype, gldet.glamt, gldet.glacct    /*selects table and field */
FROM       gldet FULL OUTER JOIN equip       /*join tables and include empty line items*/
ON	   gldet.kequipnum = equip.kequipnum    /*PK for each joined table*/
WHERE      gldate BETWEEN @startdate AND @enddate    /*user date range entry*/
AND        (gldet.glacct = '40400' OR 
         	      gldet.glacct = '50500' OR
                      gldet.glacct = '59004' OR
                      gldet.glacct = '59014' OR
                      gldet.glacct = '59024' OR
                      gldet.glacct = '59034')   /*gl accounts to pull data*/



All data pulls in correctly and code works.

Now I am trying to have glacct 50500,59004,59014,59024 and 59034 not show as separate columns but to sum these accounts into a new column labeled Expense...

I know this isn't impossible...and I am sure I am missing the obvious quickest way to do this.

Thoughts??
Was This Post Helpful? 0
  • +
  • -

#11 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Pulling in data within a date range

Posted 15 February 2011 - 12:15 PM

Show an example of what you want. Dummy up the data for us. When the glAcct begins with a 5 do you want the glamt to be? Should the expense be the sum of everything beginning with a 5?
Was This Post Helpful? 0
  • +
  • -

#12 jlwlan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 17-April 09

Re: Pulling in data within a date range

Posted 15 February 2011 - 03:57 PM

Here is what I am looking to have as the final output...currently I have each of the glaccts listed individually and would like to combine all of the 5 series numbers under one column such as this...

kequipnum gmmottype 40400 Expense
00000-000001 12345 $5,000.00 $300.00
00000-000002 ABC123456 $3,800.00 $301.00
00000-000003 ABC123457 $125.00 $50.00
00000-000004 ABC123458 $550.00 $0.00
00000-000005 ABC123459 $565.00 $0.00
Was This Post Helpful? 0
  • +
  • -

#13 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Pulling in data within a date range

Posted 16 February 2011 - 02:05 PM

so the glacct 40400 is it's own header?

You can try something like this:

SELECT kequipnum, gmmottype, CASE WHEN LEFT(glacct, 1) = 5 THEN 55555 ELSE glacct END AS glAcct, SUM(glAmt) AS Expense
FROM 
(
 SELECT     gldet.kequipnum, equip.gmmottype, gldet.glamt, gldet.glacct    /*selects table and field */  
 FROM       gldet FULL OUTER JOIN equip       /*join tables and include empty line items*/  
 ON     gldet.kequipnum = equip.kequipnum    /*PK for each joined table*/  
 WHERE      gldate BETWEEN @startdate AND @enddate    /*user date range entry*/  
 AND        (gldet.glacct = '40400' OR   
                   gldet.glacct = '50500' OR  
                       gldet.glacct = '59004' OR  
                       gldet.glacct = '59014' OR  
                       gldet.glacct = '59024' OR  
                       gldet.glacct = '59034')   /*gl accounts to pull data*/ 
)d
GROUP BY kequipnum, gmmottype, glacct



That should at least get you started
Was This Post Helpful? 1
  • +
  • -

#14 marinus  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 135
  • View blog
  • Posts: 575
  • Joined: 14-April 10

Re: Pulling in data within a date range

Posted 24 February 2011 - 06:33 AM

WHERE [End] >= @Start and [Start] <= @End


This works for me.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1