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 |
steve_c
Yak Posting Veteran
54 Posts |
Posted - 2008-02-27 : 14:21:15
|
Hi all - am developing my client statement report on my accounts application...My accounts team need the ability to print off monthly statements for clients. I have done this no probs. I end up with a table of invoices (invoiceid, invoicedate, amount) in my report table. At the bottom of this table, they require the following table:Current: £amount30-60 Days: £amount60-90 Days: £amount90 + Days: £amountbasically, Say I print the report on 15th Feb. The Current value should be a sum of invoice totals from February. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 90 Days should be a sum of invoices raised before Oct. I'm assuming I grab the current month from Globals!ExecutionTime but where would I go from there?Thanks in advance as usual guys :)Stephen. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-27 : 14:48:06
|
[code]-- Prepare sample dataDECLARE @Sample TABLE (dt DATETIME, theSum MONEY)INSERT @SampleSELECT '20080211', 10 UNION ALLSELECT '20080210', 20 UNION ALLSELECT '20080117', 40 UNION ALLSELECT '20071213', 31 UNION ALLSELECT '20071130 23:59:12', 30 UNION ALLSELECT '20071101', 15 UNION ALLSELECT '20071006', 25 UNION ALLSELECT '20070906 14:45', 35DECLARE @FirstOfThisMonth DATETIMESET @FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')SELECT m.DateRange, SUM(s.theSum) AS SalesFROM ( SELECT 'Current' AS DateRange, 0 AS Sorting, @FirstOfThisMonth AS minDate, DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate UNION ALL SELECT '30-60', 1, DATEADD(MONTH, -2, @FirstOfThisMonth), @FirstOfThisMonth UNION ALL SELECT '60-90', 2, DATEADD(MONTH, -4, @FirstOfThisMonth), DATEADD(MONTH, -2, @FirstOfThisMonth) UNION ALL SELECT '90+', 3, '17530101', DATEADD(MONTH, -4, @FirstOfThisMonth) ) AS mINNER JOIN @Sample AS s ON s.dt >= m.minDate AND s.dt < m.maxDateGROUP BY m.DateRangeORDER BY MIN(m.Sorting)[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-27 : 14:48:56
|
[code]DateRange Sales--------- -----Current 30.0030-60 71.0060-90 70.0090+ 35.00[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-27 : 15:32:16
|
Are you asking for a SQL query, or an MDX query?CODO ERGO SUM |
|
|
steve_c
Yak Posting Veteran
54 Posts |
Posted - 2008-02-27 : 16:42:09
|
Hi - erm I'm not entirely sure what an MDX query is but I was actually hoping to achieve this in the report itself - not by using another SQL query... Is this possible or should I go with peso's suggestion (thanks peso by the way)Stephen. |
|
|
steve_c
Yak Posting Veteran
54 Posts |
Posted - 2008-02-27 : 17:32:01
|
When you declare @sample as a table - is that a temp table? If so - what would be the effect of 2 people running this query for different clients at the same time? Is there any problems? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-28 : 00:58:46
|
@Sample table is only for mimic your environment.The solution starts with "DECLARE @FirsrtDayOfMonth DATETIME".Copy and paste that, and replace all instances of @Sample with your real table name. E 12°55'05.25"N 56°04'39.16" |
|
|
steve_c
Yak Posting Veteran
54 Posts |
Posted - 2008-02-28 : 04:54:29
|
Peso - the select to get my invoices and payments is the following:SELECT invoiceid, invoicedate, cinvoicetotal from invoices where (clientid = @clientid) and (paid = 0)UNION ALLSELECT paymentcreditid,paymentcreditdate, - amount from paymentcredits where (clientid = @clientid) and (used = 0)I had:DECLARE @Sample TABLE (OurID int , dt DATETIME, theSum MONEY)If I delete the Declare statement, where so I put my SELECT statement?I have the following at the moment which works:@clientid intASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;DECLARE @Sample TABLE (OurID int , dt DATETIME, theSum MONEY) -- Insert statements for procedure here--First insert invoices and creditsINSERT into @SampleSELECT invoiceid, invoicedate, cinvoicetotal from invoices where (clientid = @clientid) and (paid = 0)UNION ALLSELECT paymentcreditid,paymentcreditdate, - amount from paymentcredits where (clientid = @clientid) and (used = 0)DECLARE @FirstOfThisMonth DATETIMESET @FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')SELECT m.DateRange, isnull(SUM(s.theSum),0) AS SalesFROM ( SELECT 'Current' AS DateRange, 0 AS Sorting, @FirstOfThisMonth AS minDate, DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate UNION ALL SELECT '30-60', 1, DATEADD(MONTH, -2, @FirstOfThisMonth), @FirstOfThisMonth UNION ALL SELECT '60-90', 2, DATEADD(MONTH, -4, @FirstOfThisMonth), DATEADD(MONTH, -2, @FirstOfThisMonth) UNION ALL SELECT '90+', 3, '01-jan-1753', DATEADD(MONTH, -4, @FirstOfThisMonth) ) AS mLEFT JOIN @Sample AS s ON s.dt >= m.minDate AND s.dt < m.maxDateGROUP BY m.DateRangeORDER BY MIN(m.Sorting) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-28 : 04:58:06
|
[code]DECLARE @FirstOfThisMonth DATETIMESET @FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')SELECT m.DateRange, SUM(ISNULL(s.theSum, 0)) AS theSalesFROM ( SELECT 'Current' AS DateRange, 0 AS Sorting, @FirstOfThisMonth AS minDate, DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate UNION ALL SELECT '30-60', 1, DATEADD(MONTH, -2, @FirstOfThisMonth), @FirstOfThisMonth UNION ALL SELECT '60-90', 2, DATEADD(MONTH, -4, @FirstOfThisMonth), DATEADD(MONTH, -2, @FirstOfThisMonth) UNION ALL SELECT '90+', 3, '01-jan-1753', DATEADD(MONTH, -4, @FirstOfThisMonth) ) AS mLEFT JOIN ( SELECT invoicedate AS dt, cinvoicetotal AS theSum from invoices where clientid = @clientid and paid = 0 UNION ALL SELECT paymentcreditdate AS dt, -amount AS theSum from paymentcredits where clientid = @clientid and used = 0 ) AS s ON s.dt >= m.minDate AND s.dt < m.maxDateGROUP BY m.DateRangeORDER BY MIN(m.Sorting)[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
steve_c
Yak Posting Veteran
54 Posts |
Posted - 2008-02-28 : 05:07:50
|
Peso you're a legend! Thanks for the ultra quick reply! |
|
|
|
|
|
|
|