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 |
|
cpower
Starting Member
9 Posts |
 Posted - 2009-01-14 : 19:06:31
|
| Hi all. Very new to sql and to forum so be gentle. I have put together the following query:SELECT GLE.[G_L Account No_] AS [GL_CODE],GLE.[Global Dimension 1 Code] AS [DEPT_CODE],SUM(GLE.Amount) [BALANCE],CASE WHEN [Posting Date] BETWEEN '27-APR-08' and '24-MAY-08' THEN 'P1-09' WHEN [Posting Date] BETWEEN '25-MAY-08' and '21-JUN-08' THEN 'P2-09' --..... --..... --When [Posting Date] BETWEEN '22-MAR-09' and '25-APR-09' then 'P24-09'End AS [PERIOD]FROM [UKDLNVGER\NVGER].[GermanyBU].[dbo].[NAAFI Germany$G_L Entry] GLEWhere [Posting Date]>'27-APR-08' and [Posting Date]<'21-JUN-08'and GLE.[G_L Account No_] between 6000 and 9999GROUP BY GLE.[G_L Account No_], GLE.[Global Dimension 1 Code], GLE.[Posting Date]ORDER BY GLE.[G_L Account No_], GLE.[Global Dimension 1 Code], GLE.[Posting Date]The query runs fine for the small amount of data thus far and I have commented out some lines to give a better picture of what I am eventually after...I will require 24 lines in the case statement and am looking for a way to make it easier. There must be a way to achieve the same but without 24+ lines?Was thinking about some sort of array and loop (to be fleixible if more dates are required) but still trying to learn that and can't quite shoe horn it into the query. Was also thinking about referencing a table that contains the various date ranges but not sure how to do that either.Can anyone show me the way forward or give some advice?Thanks in advance.Corey |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2009-01-14 : 19:19:23
|
| Hey Corey (and welcome), If it was me I'd have a reference table containing the date ranges and the 'P1-09' classifications. That would reduce the CASE statement. You would just need to link the 2 tables together in your query.HTH,Tim |
 |
|
|
cpower
Starting Member
9 Posts |
Posted - 2009-01-14 : 19:32:23
|
| HI timmy. Thanks for the swift reply. As it happens there is already table [Acc_Period] that contains that date range data (start date, end date, date label). I was thinking about using it as the info is already there but not sure how? |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2009-01-14 : 19:41:56
|
| Something like this:SELECT <columns>FROM <your big table name> BT LEFT JOIN Acc_Period AP ON BT.[Posting Date] BETWEEN AP.FromDate AND AP.ToDate(assuming FromDate and ToDate are the columns in the Acc_Period table)btw - you can probably ditch the GROUP BY clause in your query too as you're not doing aggregations (e.g. SUM, COUNT etc) |
|
|
|
cpower
Starting Member
9 Posts |
Posted - 2009-01-14 : 19:46:49
|
| I'll give it a go. Need to keep the group by as the info is required in summary which the group by does nicely. Cheers for your help. |
|
|
|
|
|
|
|
|
Making a query simpler??