[jlwlan]

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!

[ForcedSterilizationsForAll]

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.

[jlwlan]

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!

[Ionut]

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?

[jlwlan]

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...

[ForcedSterilizationsForAll]

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.

[jlwlan]

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??

[jlwlan]

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??

[ForcedSterilizationsForAll]

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?

[jlwlan]

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??

[ForcedSterilizationsForAll]

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?

[jlwlan]

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

[ForcedSterilizationsForAll]

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

[marinus]

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


This works for me.