Trying to get daily totals from cumulative totals in a pivot

Hot Topic

PLEASE HELP!!!! MDX-EXPERTS!!! LOCAL CUBE SECURITY
Ad: Problems Creating Instance in SQL 2005 management studio
CSV FORMAT [Suppressing a Text Box]
sp_OACreate returns -2146232576
SQL 2005 SP2 installation blocked
Configuration Package doesn’t work
Defining a named set
Help in deleting row in gridview
Setting collection element value using expression fails
Need ID after OLEDB command (insert)

SQL Server

MDX with parameters using OleDb?
How do you use SQL parameters to UPDATE a column?
Runtime updates to Analysis server database structures
connection help
Error message "Interface not registered"
Unable to Open DTS Packages Anymore
Use of stdev and var aggregates in custom code
Login failed for user '(null)'
Is anyone using ActiveCipher by RedCraft Software?
failed to connect to the server

Trying to get daily totals from cumulative totals in a pivot

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers Thanks in advance!!!

 

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

Answer this question

Trying to get daily totals from cumulative totals in a pivot

  • Sneak

    limno,

    Unfortunately, that SQL isn't quite working. It takes over six minutes to execute the query, and the numbers grow each day.

    So, day 1 numbers look normal, but then starting with day 2 numbers we get into the billions and it only grows from there.

    You query is more complicated than I've dealt with before so I will have to parse it out and wrap my head around it before I can try and change it to work with what I'm doing. But, if you have any other suggestions, I am certainly open to them.

    Thanks so far for your help!

  • Matthijs Koopman

    Hello,

    I have imported your data to SQL Server 2005 with table name CAG2:

    CREATE TABLE [CAG2](

    [BRANCHNo] [int] NULL,

    [BRANCH] [nvarchar](55) NULL,

    [TOTALSALES] [decimal](29, 2) NULL,

    [TOTAL_GM] [decimal](29, 2) NULL,

    [DATE] [datetime] NULL,

    [WorkDay] [int] NULL

    )

    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',28609.00,12882.00,'Aug 1 2006 12:00:00:000AM',1)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',7573.00,5491.00,'Aug 1 2006 12:00:00:000AM',1)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',66525.00,27430.00,'Aug 2 2006 12:00:00:000AM',2)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',14437.00,7827.00,'Aug 2 2006 12:00:00:000AM',2)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',119709.00,46208.00,'Aug 3 2006 12:00:00:000AM',3)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',18193.00,9973.00,'Aug 3 2006 12:00:00:000AM',3)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',169985.00,66065.00,'Aug 4 2006 12:00:00:000AM',4)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',20621.00,10391.00,'Aug 4 2006 12:00:00:000AM',4)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',217485.00,83992.00,'Aug 7 2006 12:00:00:000AM',5)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',24585.00,12235.00,'Aug 7 2006 12:00:00:000AM',5)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',262859.00,104650.00,'Aug 8 2006 12:00:00:000AM',6)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',27016.00,13423.00,'Aug 8 2006 12:00:00:000AM',6)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',296166.00,116352.00,'Aug 9 2006 12:00:00:000AM',7)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',28782.00,13952.00,'Aug 9 2006 12:00:00:000AM',7)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(1111,'Richmond',370759.00,144321.00,'Aug 10 2006 12:00:00:000AM',8)
    INSERT INTO [CAG2] ([BRANCHNo],[BRANCH],[TOTALSALES],[TOTAL_GM],[DATE],[WorkDay])VALUES(2222,'Maryland',31420.00,15388.00,'Aug 10 2006 12:00:00:000AM',8)

    --Query for daily Total Sales:

    WITH SalesCTE (BRANCHNo, BRANCH, Year, Month, WorkDay, [Total Sales])

    AS

    (

    SELECT a.BRANCHNo, a.BRANCH, Datepart(Year, a.date), datename(month, a.date), a.WorkDay AS [Work Day], sum(a.TOTALSALES) AS [Total Sales]

    FROM (SELECT c.BRANCHNo, c.BRANCH, c.Date, c.WorkDay, (CASE WHEN c.WorkDay = 1 THEN c.TOTALSALES ELSE d.dailyTOTALSALES END) AS TOTALSALES

    FROM dbo.CAG2 AS c LEFT OUTER JOIN

    (SELECT b.BRANCHNo, b.BRANCH, b.Date, b.WorkDay, a.TOTALSALES - b.TOTALSALES AS dailyTOTALSALES

    FROM (SELECT BRANCHNo, BRANCH, Date, WorkDay, TOTALSALES FROM dbo.CAG2) AS a INNER JOIN

    (SELECT BRANCHNo, BRANCH, Date, WorkDay, TOTALSALES FROM dbo.CAG2 AS CAG_1) AS b ON a.WorkDay = b.WorkDay + 1 AND a.BRANCHNo=b.BRANCHNo AND a.BRANCH= b.BRANCH AND Datepart(Year, a.date)=Datepart(Year, b.date) AND Datepart(Month, a.date)=Datepart(Month, b.date)) AS d

    ON c.WorkDay = d.WorkDay + 1 AND c.BRANCHNo=d.BRANCHNo AND c.BRANCH= d.BRANCH AND Datepart(Year, c.date)=Datepart(Year, d.date) AND Datepart(Month, c.date)=Datepart(Month, d.date)) a

    group by a.BRANCHNo, a.BRANCH, a.date, a.WorkDay

    )

    select * from SalesCTE

    pivot

    (

    sum([Total Sales])

    for WorkDay

    in ([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

    ) as p

    --Result:

    1111 Richmond 2006 August 28609.00 37916.00 53184.00 50276.00 47500.00 45374.00 33307.00 74593.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    2222 Maryland 2006 August 7573.00 6864.00 3756.00 2428.00 3964.00 2431.00 1766.00 2638.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    --Please let me know if you have a different result. You can do the same thing for Total_GM separately.

  • tuncali

    Hello:

    On top of your hard work:

    with SalesCTE (Month, WorkDay, [Total Sales])

    as

    (

    SELECT datename(month, a.tdate), a.WorkDay AS [Work Day], sum(a.GasSales + a.EquipmentSales) AS [Total Sales]

    FROM (

    SELECT c.tDate, c.WorkDay, (CASE WHEN c.WorkDay = 1 THEN c.GasSales ELSE d .dailyGasSales END) AS GasSales,

    (CASE WHEN c.WorkDay = 1 THEN c.EquipmentSales ELSE d .dailyEquipmentSales END) AS EquipmentSales

    FROM dbo.CAG AS c LEFT OUTER JOIN

    (SELECT b.tDate, b.WorkDay, a.GasSales - b.GasSales AS dailyGasSales, a.EquipmentSales - b.EquipmentSales AS dailyEquipmentSales

    FROM (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG) AS a INNER JOIN

    (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG AS CAG_1) AS b ON a.WorkDay = b.WorkDay + 1) AS d ON c.WorkDay = d.WorkDay + 1) a

    group by a.tdate, a.WorkDay

    )

    select * from SalesCTE

    pivot

    (

    sum([Total Sales])

    for WorkDay

    in ([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

    ) as p

  • Maclau

    Hey, thanks limno!

    I'm about to leave work, so I will piece this together and try it out tomorrow. I'll let you know how it works out.

    Thanks again!

  • Ken Fleming

    Could you modify your issue again with a few set of your data for the columns relevant to the report Let us see whether we can figure out what is the issue here. By the way, data type Float may not be appropriate in your table because Float is an approximate data type . You can store only close approximation of the values. You can use decimal or numeric data type instead. Workday data type should be integer instead of float. Anyway, the data type could be an issue here.

  • gregaug

    Hi Philip:

    Here is the table and data I tested on SQL Server 2005. For your reference. Please post back if you still have question or problem for this.

    --Table CAG:

    CREATE TABLE [dbo].[CAG](

    [tDate] [datetime] NULL,

    [WorkDay] [int] NULL,

    [GasSales] [decimal](18, 2) NULL,

    [EquipmentSales] [decimal](18, 2) NULL

    )

    --Sample Data for CAG:

    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 1 2000 12:00:00:000AM',1,1.00,1.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 2 2000 12:00:00:000AM',2,10.00,10.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 3 2000 12:00:00:000AM',3,15.00,25.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 1 2002 12:00:00:000AM',1,11.00,11.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 2 2002 12:00:00:000AM',2,22.00,33.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Jan 3 2002 12:00:00:000AM',3,32.00,43.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 1 2006 12:00:00:000AM',1,6.00,1.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 4 2006 12:00:00:000AM',2,10.00,2.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 5 2006 12:00:00:000AM',3,11.00,5.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 6 2006 12:00:00:000AM',4,21.00,7.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 7 2006 12:00:00:000AM',5,32.00,12.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 8 2006 12:00:00:000AM',6,53.00,19.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 11 2006 12:00:00:000AM',7,85.00,31.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 12 2006 12:00:00:000AM',8,138.00,50.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 13 2006 12:00:00:000AM',9,223.00,81.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 14 2006 12:00:00:000AM',10,361.00,131.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 15 2006 12:00:00:000AM',11,584.00,212.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 18 2006 12:00:00:000AM',12,945.00,343.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 19 2006 12:00:00:000AM',13,1529.00,555.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 20 2006 12:00:00:000AM',14,2474.00,898.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 21 2006 12:00:00:000AM',15,4003.00,1453.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 22 2006 12:00:00:000AM',16,6477.00,2351.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 25 2006 12:00:00:000AM',17,10480.00,3804.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 26 2006 12:00:00:000AM',18,16957.00,6155.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 27 2006 12:00:00:000AM',19,27437.00,9959.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 28 2006 12:00:00:000AM',20,44394.00,16114.00)
    INSERT INTO [CAG] ([tDate],[WorkDay],[GasSales],[EquipmentSales])VALUES('Sep 29 2006 12:00:00:000AM',21,71831.00,26073.00)

    --The query:

    with SalesCTE (Year, Month, WorkDay, [Total Sales])

    as

    (

    SELECT Datepart(Year, a.tdate), datename(month, a.tdate), a.WorkDay AS [Work Day], sum(a.GasSales + a.EquipmentSales) AS [Total Sales]

    FROM (

    SELECT c.tDate, c.WorkDay, (CASE WHEN c.WorkDay = 1 THEN c.GasSales ELSE d .dailyGasSales END) AS GasSales,

    (CASE WHEN c.WorkDay = 1 THEN c.EquipmentSales ELSE d .dailyEquipmentSales END) AS EquipmentSales

    FROM dbo.CAG AS c LEFT OUTER JOIN

    (SELECT b.tDate, b.WorkDay, a.GasSales - b.GasSales AS dailyGasSales, a.EquipmentSales - b.EquipmentSales AS dailyEquipmentSales

    FROM (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG) AS a INNER JOIN

    (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG AS CAG_1) AS b ON a.WorkDay = b.WorkDay + 1 AND Datepart(Year, a.tdate)=Datepart(Year, b.tdate) AND Datepart(Month, a.tdate)=Datepart(Month, b.tdate)) AS d

    ON c.WorkDay = d.WorkDay + 1 AND Datepart(Year, c.tdate)=Datepart(Year, d.tdate) AND Datepart(Month, c.tdate)=Datepart(Month, d.tdate)) a

    group by a.tdate, a.WorkDay

    )

    select * from SalesCTE

    pivot

    (

    sum([Total Sales])

    for WorkDay

    in ([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

    ) as p

  • Raulsassaa

    You're right, limno. The table is sloppy. It is being imported from an Excel spreadsheet and I didn't bother to change any of the datatypes during the import.

    I will clean it up and put in some sample data for you. I can't right now, but I will this afternoon (within the next 4 hours).

    Once again, thanks for your help! You are amazingly prompt with your replies and I really do appreciate it.

  • supagu

    Please post your query that works on sample data and schema like limno showed. It will be easier then to troubleshoot the problem.

  • zariusan

    Unfortunately, I don't have a query that works. I can provide my table schema, though.

    COLUMNS:
    Oracle Branch (float, null)
    Branch Description (nvarchar(255), null)
    Sales_Gas (float, null)
    Sales_HGS (float, null)
    Sales_Rent (float, null)
    Sales_Other (float, null)
    Total Sales (float, null)
    COS_Gas (float, null)
    COS_HGS (float, null)
    COS_Rent (float, null)
    COS_Other (float, null)
    COS_Total (float, null)
    GM_Gas (float, null)
    GM_HGS (float, null)
    GM_Rent (float, null)
    GM_Other (float, null)
    GM_Total (float, null)
    Date (datetime, null)
    WorkDay  (float, null)

    There are 6223 lines of data in the table as of today. Also.... I feel like an idiot, now that I look at the schema I realize I don't need to add gas sales and HGS sales because there is a total sales column. The total sales column would be best to use.

    In either case, limno's query works with his table but not with mine. I'm not sure why.

  • FatjonSaja

    One fix:

    with SalesCTE (Year, Month, WorkDay, [Total Sales])

    as

    (

    SELECT Datepart(Year, a.tdate), datename(month, a.tdate), a.WorkDay AS [Work Day], sum(a.GasSales + a.EquipmentSales) AS [Total Sales]

    FROM (

    SELECT c.tDate, c.WorkDay, (CASE WHEN c.WorkDay = 1 THEN c.GasSales ELSE d .dailyGasSales END) AS GasSales,

    (CASE WHEN c.WorkDay = 1 THEN c.EquipmentSales ELSE d .dailyEquipmentSales END) AS EquipmentSales

    FROM dbo.CAG AS c LEFT OUTER JOIN

    (SELECT b.tDate, b.WorkDay, a.GasSales - b.GasSales AS dailyGasSales, a.EquipmentSales - b.EquipmentSales AS dailyEquipmentSales

    FROM (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG) AS a INNER JOIN

    (SELECT tDate, WorkDay, GasSales, EquipmentSales

    FROM dbo.CAG AS CAG_1) AS b ON a.WorkDay = b.WorkDay + 1 AND Datepart(Year, a.tdate)=Datepart(Year, b.tdate) AND Datepart(Month, a.tdate)=Datepart(Month, b.tdate))  AS d

    ON c.WorkDay = d.WorkDay + 1 AND Datepart(Year, c.tdate)=Datepart(Year, d.tdate) AND Datepart(Month, c.tdate)=Datepart(Month, d.tdate)) a

    group by a.tdate, a.WorkDay

    )

     

    select * from SalesCTE

    pivot

    (

    sum([Total Sales])

    for WorkDay

    in ([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

    ) as p

  • bpeikes

    Thanks limno,

    Getting your table and data was very helpful. Your query does work on the table you created, but it doesn't seem to work on mine. All I am doing to your query is changing tDate to Date, GasSales to Sales_Gas and EquipmentSales to Sales_HGS.

    There are some differences in our table data, however. I'm not sure if this would make a difference, but my Sales_Gas and Sales_HGS columns are floats and the second workday of September is actually 9-5-2006 (due to Labor Day holiday). Like I said, I can't see how that would make a difference, but the data the query is returning from my table is very strange.

  • Joseph S.

    BRANCH# BRANCH TOTAL SALES TOTAL_GM DATE WorkDay
    1111 Richmond 28609 12882 2006-08-01 1
    2222 Maryland 7573 5491 2006-08-01 1
    1111 Richmond 66525 27430 2006-08-02 2
    2222 Maryland 14437 7827 2006-08-02 2
    1111 Richmond 119709 46208 2006-08-03 3
    2222 Maryland 18193 9973 2006-08-03 3
    1111 Richmond 169985 66065 2006-08-04 4
    2222 Maryland 20621 10391 2006-08-04 4
    1111 Richmond 217485 83992 2006-08-07 5
    2222 Maryland 24585 12235 2006-08-07 5
    1111 Richmond 262859 104650 2006-08-08 6
    2222 Maryland 27016 13423 2006-08-08 6
    1111 Richmond 296166 116352 2006-08-09 7
    2222 Maryland 28782 13952 2006-08-09 7
    1111 Richmond 370759 144321 2006-08-10 8
    2222 Maryland 31420 15388 2006-08-10 8

    Sorry the data is presented so sloppy. Either I'm not seeing it, or this board just doesn't have a way to present text in a "code" format, but either way here is some sample data. By the way, when I select the checkbox below that says "This post contains a code sample" it really doesn't make a difference to my post.

    Anyway, I have changed my table so that the columns above have the following data types: Branch# (decimal), Branch (varchar), Total Sales (decimal), Total_GM (decimal), date (datetime), and workday (int).

    Sorry it has taken me so long to respond to this post.


  • Trying to get daily totals from cumulative totals in a pivot
Answer this question