Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2010
    Posts
    7

    Unanswered: Need help summarizing data

    Hello

    I'm looking to generate a report showing the most active 15 minutes of sales data from stands at our stores across the name. The most active minute could begin and end at any time.

    Details of my Master table (tblTransItemWH)

    PCID int
    StandID varchar(255)
    StandName varchar(255)
    TransID int
    Year int
    TransDateTimeStart datetime
    TransDateTimeEnd datetime
    ItemID varchar(255)
    ItemDesc varchar(255)
    ItemQty decimal(24, 6)
    ItemUnitPrice decimal(24, 6)
    CashierName varchar(255)



    SELECT [PCID]
    ,[StandID]
    ,[StandName]
    ,[TransID]
    ,[Year]
    ,[TransDateTimeStart]
    ,[TransDateTimeEnd]
    ,[ItemID]
    ,[ItemDesc]
    ,[ItemQty]
    ,[ItemUnitPrice]
    ,[CashierName]
    FROM [PosTrans].[dbo].[tblTransItemWH]

    The original data table is a collection of data from all our stands located in all out stores for all the days in the years.

    The results: I need to parse through all the data to generate a table to store the most active 15 minutes for all stands in all stores across the nation. The results for just one location should look something like this:

    Location: 38063900
    Stand name: Stand 1
    Selected date: 05/11/2010

    Start Time: 05/11/2010 2:32 PM End Time: 05/11/2010 2:47 PM Total Transactions: 140 Total Sales: $1968.67 Total items Sold: 428

    I created three views to get me to the display the total transactions, toal sales, and total items sold but now I need a way a way to extract the most active 15 minutes. (See attached word document)


    The bottom line, I need a process to extract the most active 15 minutes for all the locations and store it in a table to pull later into a report.

    Thank you
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Don't have much time to look at this, but noticed that the following question might need answering . . .

    . . . "most active."

    Defined as . . .

    Most transactions?
    Most dollars?
    Most items?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jun 2010
    Posts
    7

    Good question...

    The answer is the....

    I need the most transaction in 15 minutes out of the whole day for each stand in each location.

    Thanks in advance.

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You haven't provided us with any actual data to work with... so I can't properly test that this is going to work.

    But here's my solution involving using a windowed function to rank each stands sales by the minute and return only those where the rank is less than or equal to 15 to give you the top 15 minutes for each stand.

    Of course there's probably a cleaner way of pulling just the minute out of the datetime rather than adding on the negative of the milliseconds and then the seconds (do it via a UDF perhaps) but this is a quick and easy that should in theory give the right data.

    Code:
    Select		StandID,
    		StandName,
    		Date,
    		Start_Minute,
    		End_Minute,
    		Ranking,
    		Total_Transactions,
    		Qty_Sold,
    		Total_Sales
    From		(
    				Select		StandID,
    						StandName,
    						CAST(TransDateTimeStart As Date) As Date,
    						DATEADD(second,DatePart(second,DATEADD(millisecond,DatePart(millisecond,TransDateTimeStart)*-1,TransDateTimeStart))*-1,DATEADD(millisecond,DatePart(millisecond,TransDateTimeStart)*-1,TransDateTimeStart)) As Start_Minute,
    						DATEADD(second,DatePart(second,DATEADD(millisecond,DatePart(millisecond,TransDateTimeEnd)*-1,TransDateTimeEnd))*-1,DATEADD(millisecond,DatePart(millisecond,TransDateTimeEnd)*-1,TransDateTimeEnd)) As End_Minute,
    						RANK() Over (Partition By StandID,StandName,CAST(TransDateTimeStart As Date) Order By COUNT(TransID) Desc) As Ranking,
    						COUNT(TransID) As Total_Transactions,
    						SUM(ItemQty) As Qty_Sold,
    						SUM(ItemUnitPrice*ItemQty) As Total_Sales
    				From		StandData
    				Group By	StandID,
    						StandName,
    						CAST(TransDateTimeStart As Date),
    						DATEADD(second,DatePart(second,DATEADD(millisecond,DatePart(millisecond,TransDateTimeStart)*-1,TransDateTimeStart))*-1,DATEADD(millisecond,DatePart(millisecond,TransDateTimeStart)*-1,TransDateTimeStart)),
    						DATEADD(second,DatePart(second,DATEADD(millisecond,DatePart(millisecond,TransDateTimeEnd)*-1,TransDateTimeEnd))*-1,DATEADD(millisecond,DatePart(millisecond,TransDateTimeEnd)*-1,TransDateTimeEnd))
    			) STData
    Where		Ranking <=15
    Order By	StandID,
    		StandName,
    		Date,
    		Start_Minute,
    		Ranking

  5. #5
    Join Date
    Jun 2010
    Posts
    7

    Follow up....

    Hello EngadaSQL ,

    First I would like to thank you for your help. The code seems to get me closer to what I need; however, it doesn't seem to really count/sum
    the data into 15 minutes.

    I would be glad provide you with actual data to work with. How would you like me to upload the data?

    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just paste two dozen or so records. As INSERT statements would be best.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2010
    Posts
    7

    Post some test data

    Hello again...

    I have posted some test data representing 1 hour for two locations and two stands with the location got just one day.

    What I need to do is find the most active 15 minutes with a whole day for each stand at each location.

    For example, a stand could open at 2:00 PM and close at 7:00 PM and sales of products would be up and down during that time so I need to find the "most busiest" time. It could be between 5:07 PM - 5:22 PM. I then would store the results in a table to be used in a report later.

    Also, is it possible to rank the "most busiest" 15 minutes for the location.

    SAMPLE DATA: I have posted the data as a .txt file.
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You mean something like this?
    Hr Min Rng Cnt Ttl
    13 00 - 14 22 119.5
    13 15 - 29 10 70.5
    13 30 - 44 14 104.96
    13 45 - 59 50 342.88
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...or maybe you mean this:

    TrxDate|StandName|Hour|MinuteRange|MostActive|Most Sold
    20100402|101 PIZZ/CAMP/BR|13|45 - 59|14|90.00
    20100402|102 PIZZ/CAMP/BR|13|45 - 59|2|10.96
    20100402|201 SCORBRD LWR|13|45 - 59|9|70.48
    20100402|335 GRILL WORKS|13|45 - 59|16|100.96
    20100402|486 GRILL WORKS|13|45 - 59|9|70.48
    Last edited by rdjabarov; 06-27-10 at 14:26. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2010
    Posts
    7
    I mean more like:

    Location, Stand, Most active 15 minutes (thus the most transaction in a 15 minute time), total items sold, and total amount earned

    like:

    20100402 | 101 PIZZ/CAMP/BR | 13:20 - 13:35 | 10 items | $121.00
    or
    20100402 | 101 PIZZ/CAMP/BR | 13:40 - 13:55 | 8 items | $100.36
    or
    20100402 | 101 PIZZ/CAMP/BR | 13:01 - 13:16 | 7 items | $90.00
    ... and so on

    (the same above does not reflect the numbers in the test data.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Below is the complete result set, without filtering the top ranked most active. But this can be obtained by only selecting the records with RankedByActivity = 1:

    TrxDate StandName MinuteRange MostActive MostQty MostSold RankedByActivity
    20100402 101 PIZZ/CAMP/BR 13:45 - 13:59 14 24 90 1
    20100402 101 PIZZ/CAMP/BR 13:15 - 13:29 4 7 26 2
    20100402 101 PIZZ/CAMP/BR 13:00 - 13:14 4 5 21.5 3
    20100402 102 PIZZ/CAMP/BR 13:45 - 13:59 2 3 10.96 1
    20100402 201 SCORBRD LWR 13:45 - 13:59 9 31 70.48 1
    20100402 201 SCORBRD LWR 13:30 - 13:44 7 18 52.48 2
    20100402 201 SCORBRD LWR 13:00 - 13:14 7 9 38.25 3
    20100402 201 SCORBRD LWR 13:15 - 13:29 1 3 9.25 4
    20100402 335 GRILL WORKS 13:45 - 13:59 16 27 100.96 1
    20100402 335 GRILL WORKS 13:15 - 13:29 4 7 26 2
    20100402 335 GRILL WORKS 13:00 - 13:14 4 5 21.5 3
    20100402 486 GRILL WORKS 13:45 - 13:59 9 31 70.48 1
    20100402 486 GRILL WORKS 13:30 - 13:44 7 18 52.48 2
    20100402 486 GRILL WORKS 13:00 - 13:14 7 9 38.25 3
    20100402 486 GRILL WORKS 13:15 - 13:29 1 3 9.25 4
    Last edited by rdjabarov; 06-27-10 at 18:06. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jun 2010
    Posts
    7
    hello rdjabarov...

    I wish it was that simple. From what you have displayed, the data is not truly representative of the most busiest 15 minutes. It seems you are displaying data for every 15 minutes and ranking them.

    I was hoping to find a way to find and display the most busiest minutes which could cross of the 15 blocks.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's also doable, but this is a free forum, and I think I exhausted my effort in trying to help you. Others also tried to chime in, so cumulatively you should get at least a head start on that.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    LEOx037, I don't have time to develop this completely but here is a rough outline of SQL that should get you something:
    Code:
    WITH interval15min (start_tmestamp, end_tmestamp)
      as (select timestamp_at_00:00, timestamp_at_0059
          union all
          select start_tmestamp + 1 min, end_tmestamp + 1 min
          from interval15min
          where endtimestamp + 1 min <= timestamp_at_2349
         )
       , datatab (start_tmestamp, end_timestamp, counter
      as (select start_tmestamp, end_timestamp, 1
          from interval15min
                 inner join
               your-table
                 on tran_timestamp between start_tmestamp and end_timestamp
         )
    select start_tmestamp, end_timestamp, sum(counter)
    
    PS  I realize this isn't in SQL Server syntax (you need to use DATEADD instead of + 1 minute) but the basic concept should work.
    from datatab
    It uses a recursive Common Table (CTE) expression to generate all the start and Stop timestamps in one minute intervals for 1 day. (a prior CTE could get the date from your table to get the right date as a starting point).

    Then another CTE joins this table with your data table. By using a between you will get a semi-cartesian product output (which is fine since your criteria could have the same transaction in multiple 15 minute section).

    Finally, the start and end timestamps are summarized and a count is returned for 15 minutes at 1 minute intervals.

    From here, you can use the OLAP RANK function to get the max (or sort and get the TOP 1 (or what ever) rows.

  15. #15
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Sorry, but this won't work as it is. The STARTTIME and ENDTIME generated by subtracting and adding minutes to the TRANSDATETIMESTART timestamp is too unique. These times need to be generated down to the minute while leaving the Seconds and Microseconds at 00.000 for the STARTTIME and 59.999 for the ENDTIME. This will allow the GROUP BY to summarize them appropriately. If that detail can be solved, this should work.
    = = = = = = = = = = = = = =

    LEOx037, here is another approach. I am not at a computer where I can test it but I believe it should work (with a few possible tweaks).
    Code:
    WITH MINRANGE (MINMINUS, MINPLUS)
      AS (SELECT   0, 15 UNION ALL
          SELECT  -1, 14 UNION ALL
          SELECT  -2, 13 UNION ALL
          SELECT  -3, 12 UNION ALL
          SELECT  -4, 11 UNION ALL
          SELECT  -5, 10 UNION ALL
          SELECT  -6, 19 UNION ALL
          SELECT  -7,  8 UNION ALL
          SELECT  -8,  7 UNION ALL
          SELECT  -9,  6 UNION ALL
          SELECT -10,  5 UNION ALL
          SELECT -11,  4 UNION ALL
          SELECT -12,  3 UNION ALL
          SELECT -13,  2 UNION ALL
          SELECT -14,  1 UNION ALL
          SELECT -15,  0
        )
    SELECT STANDID
         , STANDNAME
         , DATEADD(MI,MINMINUS, TRANSDATETIMESTART) AS STARTTME
         , DATEADD(MI,MINPLUS , TRANSDATETIMESTART) AS ENDTIME
         , SUM(CASE WHEN TRANSDATETIMESTART BETWEEN DATEADD(MI,MINMINUS, TRANSDATETIMESTART)
                                                AND DATEADD(MI,MINPLUS , TRANSDATETIMESTART) 
                         THEN 1 
                         ELSE 0 
               END)           AS TOTALTRANSACTIONS
         , SUM(ITEMQTY)       AS TOTALITEMSSOLD
         , SUM(ITEMUNITPRICE) AS TOTALAMOUNTEARNED
    FROM tblTransItemWH
       , MINRANGE
    GROUP BY STANDID
           , STANDNAME
         , DATEADD(MI,MINMINUS, TRANSDATETIMESTART)
         , DATEADD(MI,MINPLUS , TRANSDATETIMESTART)
    I use a CTE to generate a list of offsets for the DATEADD function to subtract or add values in 1 minute increments. This should categorize the transactions in to all the possible brackets that span 15 minutes (they may be off by 1, i.e. 0, 14 instead of 0, 15 might be more accurate).

    Since I wasn't sure if you wanted to track transactions or items sold (1 transaction could sell 2 or more of something), I listed both. If you just want to track Items Sold, remove the SUM(ITEMQTY) and replace THEN 1 with THEN ITEMQTY).

    You will still need to apply an OLAP RANK function (I think) to rank the different stands (If you want the top 15 for each stand) or just the top 15 regardless of stand.
    Last edited by Stealth_DBA; 06-29-10 at 06:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •