Pivoting data, any better way?

  • Hello everyone!

    For each row in the table, we have a creation date. Based on only this date column, I need to provide a report in such way that for this time slot on this day what are the record count created.

    For example:-

    Time Slots:

    1. 00:00:00 - 04:00:00

    2. 04:00:01 - 08:00:00

    3. 08:00:01 - 12:00:00

    4. 12:00:01 - 16:00:00

    5. 16:00:01 - 20:00:00

    6. 20:00:00 - 23:59:59

    The creation date is stored along with time. So

    Time Slot------------------02/01/2010------03/01/2010 ....

    00:00:00 - 04:00:00-------------1-------------9-------

    04:00:01 - 08:00:00-------------3-------------8-------

    08:00:01 - 12:00:00-------------4-------------1-------

    12:00:01 - 16:00:00-------------9-------------6-------

    16:00:01 - 20:00:00-------------7-------------8-------

    20:00:00 - 23:59:59-------------3-------------2-------

    The above is the requirement.

    Anyways... I kinda did in some low level manner, steps as below

    - Created temp table which stores the above time slots.

    - declared a top cursor for looping the dates for the month

    - declared a 2nd cursor for looping the time from the temp table

    - inside the 2nd cursor, i wrote the statement which fetches the count for that date on that particular time slot

    - inserting this row in another temp table with the first cursor's Date, then second cursor's time slot an d then the count

    - so whole thing loops.

    I am getting the data accordingly for each day and each time and even the count is correct. From the final temp table which contains the completed data, i copied them to Excel and created a pivot table based on this data. So it looks clean and it is how needed, finally.

    Above is done completely in SQL2K version

    Now; Using this in the 2008, is there a better way to approach this? like getting the data directly in the pivot manner in the query output (not even going to Excel). Is this possible? How?

    -r WW -n RMudugal.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Use PIVOT - see BOL.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • Hi, thanks for the reply

    I have seen that.... not much help from that. I am not sure how you take time out of the date and create a pivot and use the same date column for displaying the date and the count of each respective.

    Thanks, anyways.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Post create table statements, insert statements for sample data and an example of desired output and we'll help you to create the statement.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • Hi, the attached excel file is the format, hope that can be achieved by using pivot in sql2k8 and below sql script fills the table variable with dates, which only data needed. Hope this helps in providing the solution.

    thank you.

    DECLARE @DATES TABLE (D1 DATETIME)

    INSERT @DATES

    SELECT '2010-01-01 00:00:00.000'

    WHILE 1=1

    BEGIN

    INSERT @DATES

    SELECT DATEADD(HH, 1, (SELECT MAX(D1)+ 1 FROM @DATES))

    IF (SELECT COUNT(*) FROM @DATES) = 100

    BREAK

    END

    SELECT D1 AS [CREATION DATE] FROM @DATES

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • The 'problem' imo with pivoting data internally in SQL is that , by definition, a table and therefore a resultset has to have a finite and known number of columns. When you pivot data you quite often wish to violate this rule.

    You can work around it by using dynamic sql as demonstrated here. This ,again imo, is a last resort if you cant for whatever reason pivot in the presentation layer.



    Clear Sky SQL
    My Blog[/url]

  • Sorry Dave, just to make it clear to myself....

    from the link you mentioned, (currently I am focusing on the first example), there he has shown the month in vertical manner and then using pivot changing the look to horizontal.

    In that example we have the Jan Feb and March as fixed, so it is easy to mention them as a column and column name just using the pivot placing the value under each, respectively.

    In my case, the column header is the date itself. Say in one report i need to show Jan's dates 1 to 31,

    so you mean, if want to use pivot, i have to hard-write the date '01/01/2010', '01/02/2010', '01/03/2010'... and so on in the main sql statement of the pivot?, so I can use the pivot just to place the value under respective column?

    As you said... violating the rule.... how you suggest to go column name dynamic? For sure, I can't guarantee the amount of columns until the user selects the month.

    Let me know, and thank you for your reply.

    -r WW -n RMudugal

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • This may be a better example. Inside SQL there is no good way of doing this.

    What FrontEnd software are you using ?

    If nothing else , it is quite easy to create reports with SSRS to do this.



    Clear Sky SQL
    My Blog[/url]

  • The front-end is a 3rd party app basically used for logging incidents and a lot more, and it has its own database in 2k5. I don't think SSRS is installed on that server, as it is EE with minimal req. We have Crystal reports tool which comes with this. I sometimes create complex report in them.The pivot what i have mentioned can be achieved by Crystal Reports using Cross Tab.

    But I am very much interested in the database level. May be not the best way and optimized, but at least a better way to mine what i have showed in the initial post.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Hi

    Can u please provide the same query and function that u provide for the above requirment ,

    I am also facing problem to get the report according to the created date .

    Waiting for ur response .

    REgards

    sat

  • Hi

    Actually the script is a SQL batch, i execute it and copy the output to the Excel and create pivot table.

    But if you convert this batch to SP, then you can refer this SP is the crystal report and create a cross tab report from this data. the only problem is the horizontal data on 31 columns for Jan month. So i prefer to Excel this report.

    -r WW -n RMudugal

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply