How do I get sum of cost between different periods 1

infinitizon · Aug 5, 2011

Hi all,
I am using SQL Server 2008 and I have this issue.

I have a table that has a job field, date field and a cost field. The date field holds dates that work was done on a particular job while the cost field holds the amount spent on that date (Work may be done on several days for a single job)

I want to create a select statement such that I get the sum of cost spent between 0-7days and 8-30days of opening a job in different columns.

I tried this:

Code:

select
    [Job No_],
    DATEDIFF(day, MIN([Posting Date]), MAX([Posting Date])) as [WIP Age],
    SUM([Total Cost])[Total WIP cost],
    [0 - 7 days]=case
         when (DATEDIFF(day, MIN([Posting Date]), MAX([Posting Date])) between 1 and 7) then
            SUM([Total Cost])
        else 0    end,
    [8 - 30 days]=case
        when (DATEDIFF(day, MIN([Posting Date]), MAX([Posting Date])) between 8 and 30) then
            SUM([Total Cost])
        else 0    end,
    [> 30 days]=case
        when (DATEDIFF(day, MIN([Posting Date]), MAX([Posting Date]))> 30) then 
           SUM([Total Cost])
        else 0    end
from [Job]
group by [Job No_]

It doesn't seem to work.
The idea is this: The job table contains transactions each job makes on differents days. For instance. Job 001 may have transactions on it for 20days and each day has a different cost implication. I want to know the total spent on job 001 between 1-7 days, between 8-30 days etc.

Currently, I get Zeros in the [0 - 7 days], [8 - 30 days] and [> 30 days] columns
Any help will be appreciated.

bborissov · Aug 5, 2011

Try:

Code:

select
    [Job No_],
    DATEDIFF(day, MinPostDat, MaxPostDat) AS [WIP Age],
    SUM([Total Cost])                     AS [Total WIP cost],
    SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) <  8) then [Total Cost]
             else 0 end)                  AS [0 - 7 days],
    SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) < 31) then [Total Cost]
             else 0 end)                  AS [8 - 30 days],
    SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) > 30) then [Total Cost]
             else 0 end)                  AS [ > 30 days]
from (SELECT [Job No_],
             MIN([Posting Date]) AS MinPostDat,
             MAX([Posting Date]) AS MaxPostDat
      FROM [Job]
      group by [Job No_]) JbTbl
group by [Job No_]

I really hate spaces in field names

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

infinitizon · Aug 5, 2011

@bborissov: thanks.
I edited your query thus:

Code:

select [u][Job No_][/u], DATEDIFF(day, [u]MinPostDat[/u], [u]MaxPostDat[/u]) AS [WIP Age], SUM([u][Total Cost][/u])AS [Total WIP cost],
    SUM(case when DATEDIFF(day, [u]MinPostDat[/u], [u]MaxPostDat[/u]) <  8[u]) then[/u] [Total Cost] end[u])[/u][0 - 7 days],
    SUM([u]case[/u] when DATEDIFF([u]day[/u], MinPostDat, MaxPostDat) < 31) then [Total Cost] end[u])[/u][8 - 30 days],
    SUM([u]case[/u] when DATEDIFF([u]day[/u], MinPostDat, MaxPostDat) > 30) then [Total Cost] end[u])[/u][ > 30 days]
from (
	SELECT [Job No_], MIN([Posting Date])MinPostDat, MAX([Posting Date])MaxPostDat
	FROM [Job Ledger Entry]
	group by [Job No_]
	)[u]JbTbl[/u]
group by [Job No_]

I get this error:

Code:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'JbTbl'.

Note: the underline signifies where the management studio put error lines and I removed the else clase since I didnt really need it.

Thanks

bborissov · Aug 5, 2011

That is why I hate spaces.
They make the code unreadable

Try this:

Code:

select [Job No_],
       DATEDIFF(day, MinPostDat, MaxPostDat)                                           AS [WIP Age],
       SUM([Total Cost])                                                               AS [Total WIP cost],
       SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) <  8 then [Total Cost] end) AS [0 - 7 days],
       SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) < 31 then [Total Cost] end) AS [8 - 30 days],
       SUM(case when DATEDIFF(day, MinPostDat, MaxPostDat) > 30 then [Total Cost] end) AS [ > 30 days]
from (
    SELECT [Job No_],
           MIN([Posting Date]) AS MinPostDat,
           MAX([Posting Date])MaxPostDat
    FROM [Job Ledger Entry]
    group by [Job No_]) JbTbl
group by [Job No_]

There was an extra closing bracket before "then ..."

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

infinitizon · Aug 5, 2011

@bborissov: I re-edited the code again thus:

Code:

select [Job No_], DATEDIFF(day, MinPostDat, MaxPostDat)[WIP Age], SUM([Total Cost])[Total WIP cost],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) <  8) then [Total Cost] end)[0 - 7 days],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) < 31) then [Total Cost] end)[8 - 30 days],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) > 30) then [Total Cost] end)[ > 30 days]
from (
	SELECT [Job No_], [Total Cost], MIN([Posting Date])MinPostDat, MAX([Posting Date])MaxPostDat
	FROM [Job Ledger Entry]
	group by [Job No_],[Total Cost]
	) as [JbTbl]
group by [Job No_],MinPostDat, MaxPostDat

The query runs but I get thesame amount in the 3 date ranges like this:

Code:

Job No_       WIP Age     Total WIP cost            0 - 7 days                   8 - 30 days           > 30 days

JCE00675	0	3052.30000000000000000000	3052.30000000000000000000	3052.30000000000000000000	NULL
JCE00675	0	977.42000000000000000000	977.42000000000000000000	977.42000000000000000000	NULL
JCE00676	1	500.00000000000000000000	500.00000000000000000000	500.00000000000000000000	NULL
JCE00676	0	3500.00000000000000000000	3500.00000000000000000000	3500.00000000000000000000	NULL
JCE00676	0	9216.89000000000000000000	9216.89000000000000000000	9216.89000000000000000000	NULL

Also notice that the Job No is repeated many times and I dont want this
Thanks

infinitizon · Aug 5, 2011

For more clarity, I actuall have 2 tables - Job table and Job Ledger Entry
Here is the Job table

Code:

JCE00002
JCE00003
JCE00004
JCE00005
JCE00006
JCE00007
JCE00008
JCE00009
JCE00010
JCE00011
JCE00012
JCE00013
JCE00014
JCE00015
JCE00016
JCE00017
...

and the Job Ledger Entry table

Code:

Job No              Posting date             Cost
JCE00002	2001-08-03 00:00:00.000	3675.41000000000000000000
JCE00002	2001-08-03 00:00:00.000	1400.00000000000000000000
JCE00002	2001-08-03 00:00:00.000	2810.87000000000000000000
JCE00002	2001-12-31 00:00:00.000	-3675.41000000000000000000
JCE00002	2001-12-31 00:00:00.000	-1400.00000000000000000000
JCE00002	2001-12-31 00:00:00.000	-2810.87000000000000000000
JCE00003	2002-01-04 00:00:00.000	566.57000000000000000000
JCE00004	2001-08-03 00:00:00.000	3675.41000000000000000000
JCE00004	2001-08-03 00:00:00.000	1400.00000000000000000000
JCE00004	2001-08-03 00:00:00.000	2810.87000000000000000000
JCE00004	2001-12-31 00:00:00.000	-3675.41000000000000000000
JCE00004	2001-12-31 00:00:00.000	-1400.00000000000000000000
JCE00004	2001-12-31 00:00:00.000	-2810.87000000000000000000
JCE00005	2001-08-03 00:00:00.000	3245.90000000000000000000
JCE00005	2001-08-03 00:00:00.000	6600.00000000000000000000
JCE00005	2001-08-03 00:00:00.000	700.00000000000000000000
JCE00005	2001-08-03 00:00:00.000	196.36000000000000000000
JCE00005	2001-08-03 00:00:00.000	1405.43000000000000000000
JCE00005	2001-08-13 00:00:00.000	-196.36000000000000000000
JCE00005	2001-08-13 00:00:00.000	-700.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	-1405.43000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00005	2001-08-03 00:00:00.000	-6132.47000000000000000000
JCE00005	2001-08-03 00:00:00.000	-3113.70000000000000000000
JCE00006	2001-08-03 00:00:00.000	-6132.47000000000000000000
JCE00006	2001-08-03 00:00:00.000	-3113.70000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	-20.24000000000000000000
JCE00006	2001-08-14 00:00:00.000	-19.18000000000000000000
JCE00006	2001-08-14 00:00:00.000	-1600.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	-4919.02000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00006	2001-08-03 00:00:00.000	1600.00000000000000000000
JCE00006	2001-08-03 00:00:00.000	20.24000000000000000000
JCE00006	2001-08-03 00:00:00.000	19.18000000000000000000
JCE00006	2001-08-03 00:00:00.000	4919.02000000000000000000
JCE00006	2001-08-03 00:00:00.000	6600.00000000000000000000
JCE00006	2001-08-03 00:00:00.000	3245.90000000000000000000
JCE00007	2001-08-03 00:00:00.000	1600.00000000000000000000
JCE00007	2001-08-03 00:00:00.000	20.24000000000000000000
JCE00007	2001-08-03 00:00:00.000	19.18000000000000000000
JCE00007	2001-08-03 00:00:00.000	4919.02000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	-20.24000000000000000000
JCE00007	2001-08-14 00:00:00.000	-19.18000000000000000000
JCE00007	2001-08-14 00:00:00.000	-1600.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	-4919.02000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00007	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	-20.24000000000000000000
JCE00008	2001-08-14 00:00:00.000	-19.18000000000000000000
JCE00008	2001-08-14 00:00:00.000	-1600.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	-6324.45000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-14 00:00:00.000	0.00000000000000000000
JCE00008	2001-08-03 00:00:00.000	1600.00000000000000000000
JCE00008	2001-08-03 00:00:00.000	20.24000000000000000000
JCE00008	2001-08-03 00:00:00.000	19.18000000000000000000
JCE00008	2001-08-03 00:00:00.000	6324.45000000000000000000
JCE00010	2001-11-17 00:00:00.000	12825.00000000000000000000
JCE00010	2001-11-17 00:00:00.000	18525.00000000000000000000
JCE00010	2001-11-17 00:00:00.000	3908.60000000000000000000
JCE00010	2001-11-17 00:00:00.000	4233.43000000000000000000
JCE00010	2001-11-17 00:00:00.000	590.00000000000000000000
JCE00010	2001-11-17 00:00:00.000	14223.50000000000000000000
JCE00010	2001-11-17 00:00:00.000	1340.64000000000000000000
JCE00010	2001-11-17 00:00:00.000	17575.00000000000000000000
JCE00010	2001-11-15 00:00:00.000	205500.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	350.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	600.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	225.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	300.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	700.00000000000000000000
JCE00010	2001-11-20 00:00:00.000	0.00000000000000000000
JCE00010	2001-11-20 00:00:00.000	0.00000000000000000000
JCE00010	2001-11-20 00:00:00.000	0.00000000000000000000
JCE00010	2001-11-20 00:00:00.000	0.00000000000000000000
JCE00010	2001-11-23 00:00:00.000	3051.59000000000000000000
JCE00010	2001-12-28 00:00:00.000	0.00000000000000000000
JCE00010	2001-10-29 00:00:00.000	3500.00000000000000000000
JCE00010	2001-10-29 00:00:00.000	951.30000000000000000000
JCE00010	2001-10-29 00:00:00.000	6103.18000000000000000000
JCE00010	2001-10-29 00:00:00.000	6000.00000000000000000000
JCE00010	2001-10-29 00:00:00.000	988.52000000000000000000
JCE00010	2001-10-29 00:00:00.000	1493.77000000000000000000
JCE00010	2001-10-31 00:00:00.000	2800.00000000000000000000
JCE00010	2001-10-31 00:00:00.000	4900.00000000000000000000
JCE00010	2001-11-14 00:00:00.000	4231.63000000000000000000
JCE00010	2001-11-14 00:00:00.000	951.30000000000000000000
JCE00010	2001-11-14 00:00:00.000	4500.00000000000000000000
JCE00010	2001-11-14 00:00:00.000	3200.00000000000000000000
JCE00010	2001-11-27 00:00:00.000	0.00000000000000000000
JCE00010	2001-11-30 00:00:00.000	31666.67000000000000000000
JCE00010	2001-11-29 00:00:00.000	0.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	24300.00000000000000000000
JCE00010	2002-01-14 00:00:00.000	90000.00000000000000000000
JCE00010	2002-01-14 00:00:00.000	51000.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-350.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-590.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-3500.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-4900.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-2800.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-3200.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-4500.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-700.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-300.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-225.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-600.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-1340.64000000000000000000
JCE00010	2001-12-31 00:00:00.000	-205500.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-31666.67000000000000000000
JCE00010	2001-12-31 00:00:00.000	-9154.77000000000000000000
JCE00010	2001-12-31 00:00:00.000	-988.52000000000000000000
JCE00010	2001-12-31 00:00:00.000	-1493.77000000000000000000
JCE00010	2001-12-31 00:00:00.000	-12825.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-18525.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-17575.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-1902.60000000000000000000
JCE00010	2001-12-31 00:00:00.000	-6000.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-14223.50000000000000000000
JCE00010	2001-12-31 00:00:00.000	-3908.60000000000000000000
JCE00010	2001-12-31 00:00:00.000	-8465.06000000000000000000
JCE00010	2001-12-31 00:00:00.000	-51000.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-24300.00000000000000000000
JCE00010	2001-12-31 00:00:00.000	-90000.00000000000000000000
JCE00011	2001-08-06 00:00:00.000	4400.00000000000000000000
JCE00011	2001-08-06 00:00:00.000	1622.95000000000000000000
JCE00011	2001-08-06 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-08 00:00:00.000	6300.00000000000000000000
JCE00011	2001-08-09 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-09 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-09 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	-6300.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	-13000.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	-4400.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	-1622.95000000000000000000
JCE00011	2001-08-10 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-10 00:00:00.000	0.00000000000000000000
JCE00011	2001-08-06 00:00:00.000	13000.00000000000000000000
JCE00011	2001-08-22 00:00:00.000	0.00000000000000000000
JCE00013	2001-08-15 00:00:00.000	1400.00000000000000000000
JCE00013	2001-08-15 00:00:00.000	7350.82000000000000000000
JCE00013	2001-08-31 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-12 00:00:00.000	0.00000000000000000000
JCE00014	2001-09-04 00:00:00.000	0.00000000000000000000
JCE00014	2001-08-30 00:00:00.000	0.00000000000000000000
JCE00014	2001-08-15 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-27 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-13 00:00:00.000	-7800.00000000000000000000
JCE00016	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-13 00:00:00.000	0.00000000000000000000
JCE00016	2001-08-07 00:00:00.000	7800.00000000000000000000
JCE00017	2001-10-23 00:00:00.000	14000.00000000000000000000
JCE00018	2001-08-07 00:00:00.000	196.36000000000000000000
JCE00018	2001-08-07 00:00:00.000	700.00000000000000000000
JCE00018	2001-08-07 00:00:00.000	1405.43000000000000000000

The idea is that I want to know total spent on JCE00002 (for instance) between 0-7days, 8-31days, etc.

I hope this makes it clearer

bborissov · Aug 5, 2011

Code:

select [Job No_],
       SUM([Total Cost])[Total WIP cost],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) <  8) then [Total Cost] end)[0 - 7 days],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) < 31) then [Total Cost] end)[8 - 30 days],
    SUM(case when (DATEDIFF(day, MinPostDat, MaxPostDat) > 30) then [Total Cost] end)[ > 30 days]
from (
    SELECT [Job No_], [Total Cost], MIN([Posting Date])MinPostDat, MAX([Posting Date])MaxPostDat
    FROM [Job Ledger Entry]
    group by [Job No_],[Total Cost]
    ) as [JbTbl]
group by [Job No_]

Remove [WIP Age] column from SELECT.
That column case your troubles.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

infinitizon · Aug 5, 2011

@bborissov: Thanks a great deal. It seems to be working fine now. I'd work on the joining of the two tables.
You just saved my job. You are the best! [2thumbsup]

infinitizon · Aug 8, 2011

Sorry I have to bring this up again. I said all is fine. However under closer examination, I ralised that a job with this details:

Code:

JCE01223   2002-09-16 00:00:00.000   27741.96000000000000000000
JCE01223   2002-09-16 00:00:00.000   1750.00000000000000000000
JCE01223   2002-09-27 00:00:00.000   1998.80000000000000000000
JCE01223   2002-09-27 00:00:00.000   15580.96000000000000000000
JCE01223   2002-09-27 00:00:00.000   5039.56000000000000000000
JCE01223   2002-10-29 00:00:00.000   7611.91000000000000000000
JCE01223   2002-10-29 00:00:00.000   1953.89000000000000000000
JCE01223   2002-10-29 00:00:00.000   18814.01000000000000000000

Obviously has a date(day) range of 43 days but after the code executes, it calculates the total cost all under [0 - 7 days] as 80491.09000000000000000000.

I've tried everything to get it right but nothing seems to be working. Any suggestions? Thanks

Qik3Coder · Aug 8, 2011

infin,
What does your current query look like?
Are you sure those are all the rows?
[tab]I would expect this if one of the date fields was null.
What does some sample data for a Job that works look like?

Lod

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

drlex · Aug 9, 2011

I'm curious as to why you're grouping on [Total Cost]; surely if this contains a daily figure, you'd want the largest figure per day as the day's final figure? Or is it that all costs on a single day need summing?

For the last example, JCE01223, what is the required result on the figures supplied?

soi là, soi carré

infinitizon · Aug 9, 2011

Well, thanks to everyone who has shown concern in my plight. But I've finally been able to perform an interesting routine that solved my problem.
For anyone who might have a similar challenge, here's what I did.

First the problem:

I have a table that stores different jobs as well as the date the job was posted(One job can be posted many days). The table also has the price that corresponds to that posting. the table looks like the one shown above and is called job ledger entry. The challenge was to get the total amount spent for each job in categories of 0-7days, 8-30days and >30days. ie for a job such as JCE00002, I want to know total spent between 0-7days, 8-31days, etc. I tried all above and many others in different forum, but non seemed to work.

Finally, I decided to create a function that returs the first day a job was posted thus:

Code:

CREATE FUNCTION getMinPostingDate(@jobNo VARCHAR(10) )
 RETURNS Datetime
 AS
 BEGIN
       DECLARE @Sales datetime
      
       SELECT @Sales = MIN([Posting Date]) FROM [Job Ledger Entry] where [Job No_] = @jobNo
       RETURN(@Sales)
 END

if I run getMinPostingDate('JCE00002'), it returns the 1st posting date like this 2001-08-03 00:00:00.000.

With this knowledge, I now created a view thus:

Code:

 create view wipByAgeReport as
 SELECT [Job No_],
        MIN([Posting Date])[Starting Date],
        MAX([Posting Date])[Ending Date],
	[Total Cost],
	(case when [Posting Date] < DATEADD(day, 7, dbo.getMinPostingDate ([Job No_])) then sum([Total Cost]) end)[0-7days],
	(case when [Posting Date]  between DATEADD(day, 7, dbo.getMinPostingDate ([Job No_])) and  DATEADD(day, 30, dbo.getMinPostingDate ([Job No_])) then [Total Cost] end)[8-30 days],
	(case when [Posting Date]  > DATEADD(day, 30, dbo.getMinPostingDate ([Job No_])) then [Total Cost] end)[>30 days]
from [Job Ledger Entry]
group by [Job No_], [Total Cost]

for job JCE00002, this will return a table like this:

Code:

No_            Starting Date             Ending Date         Total Cost     0-7days    8-30 days     >30 days
-------------------- ----------- -------------------- -------------------------------------------------- 
JCE00002   2001-12-31 00:00:00.000 2001-12-31 00:00:00.000  -3675.410         NULL       NULL       -3675.41000
JCE00002   2001-12-31 00:00:00.000 2001-12-31 00:00:00.000  -2810.870         NULL       NULL       -2810.87000
JCE00002   2001-12-31 00:00:00.000 2001-12-31 00:00:00.000  -1400.000         NULL       NULL       -1400.000
JCE00002   2001-08-03 00:00:00.000 2001-08-03 00:00:00.000   1400.000        1400.000    NULL          NULL
JCE00002   2001-08-03 00:00:00.000 2001-08-03 00:00:00.000   2810.870        2810.870                  NULL
JCE00002   2001-08-03 00:00:00.000 2001-08-03 00:00:00.000   3675.410        3675.410    NULL          NULL

I can now create a select statement for JCE00002 like this:

Code:

select No_, 
       MIN([Starting Date])[1st Posting Date],
       MAX([Starting Date])[Last Posting Date],
       datediff(DAY, MIN([Starting Date]),GETDATE() )[Age From 1st posting],
       sum([Total Cost])[Total Cost],
       SUM([0-7days])[0-7days],
       SUM([8-30 days])[8-30 days],
       SUM([>30 days])[>30 days] 
from wipByAgeReport
group by [No]

This easily splits my amount(total cost) into amount spent between 0-7days, between 8-30days and above 30days on each job.

I hope this helps others in my situation to solve it faster, cos it actually took me (at the brink of my job) close to two weeks to solve this riddle [sunshine]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How do I get sum of cost between different periods 1

infinitizon

MIS

bborissov

Programmer

infinitizon

MIS

bborissov

Programmer

infinitizon

MIS

infinitizon

MIS

bborissov

Programmer

infinitizon

MIS

infinitizon

MIS

Qik3Coder

Programmer

drlex

Technical User

infinitizon

MIS

Similar threads

Part and Inventory Search

Sponsor