How to write a script to display a "date" format in view (SQL Server 2005)

  • Hello Genius people out there

    How are you doing? My name is Joe and this is my first time to use this service to ask questions which relate to SQL Server 2005. I don’t know where to post my question, so I decide to come here and email you. If this is a wrong place to post the question, please guide me to know where I should go to post for question on your site and view the question that I have been posted.

    My question is: If I have a table which has only one column, it contains β€œDate” only and each date cell of the column represents number of records that have been entered into the database. If I wish to write code to pull out/count the number of records have been entered the database for each week and showing the total number of records show at the bottom of all weeks(in View/run query).

    This is my script that I have written but It won't give me what I wish to show.

    Select Count(ClearedDate) As 'Total Cleared'

    from DEClearedDate

    Where (Datepart(dw,ClearedDate)>=1) And (Datepart(dw,ClearedDate)<=6)

    Here is the format of my table

    dbo.DEClearearedDate

    ClearedDate

    10/4/2008

    10/5/2008

    10/6/2008

    10/4/2008

    10/7/2008

    10/7/2008

    10/9/2008

    10/8/2008

    10/10/2008

    10/11/2008

    10/12/2008

    10/13/2008

    10/13/2008

    10/14/2008

    10/15/2008

    10/16/2008

    10/17/2008

    10/18/2008

    10/19/2008

    10/20/2008

    10/21/2008

    10/22/2008

    10/23/2008

    10/24/2008

    10/25/2008

    10/26/2008

    10/27/2008

    10/28/2008

    10/29/2008

    10/29/2008

    10/30/2008

    The script that I wrote above gave me

    Total Cleared 24 records have been entered. But I wish to have it show 31 records on the table without skip counting any duplicate records or federal holiday. And wish to show the total number of cases for each week (start from Saturday to Friday).

    Since I want to count the number of records from Saturday to Friday of every week have been entered and showing the total cases in the database. it shows me the number of cases for week one only and it skips the repeated date (but I wish to count every cell in the column) and skips federal holiday and it won't show me the total cases in the database.

    Please help me..............me

    Thank You Very Much

    Joe

  • Hmm.. is better if you post the scritp of you tables structure and some sample data just to clarification the problem for what you are searching!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Just like I said in your other post, read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Follow its instructions, and you will benefit greatly from the positive responses you will get from your post(s) for help.

    Neither of your posts so far have really provided enough to work with to really help you.

  • When you make the modifications Lynn suggested, please do it in this thread rather than creating another one. Creating multiple threads on the same issue is confusing and annoys people.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. πŸ˜‰

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Also, don't just edit your post, add a reply to it. If you just edit your original post, no one but you will know that you did, and your problem could get lost the vast number of other posts.

  • AND, show us what your expected results should like like from the query. Nothing you have posted so far helps us understand what you are actually looking for in your output.

  • Select Count(ClearedDate) As 'Total Cleared'

    from DEClearedDate

    Where (Datepart(dw,ClearedDate)>=1) And (Datepart(dw,ClearedDate)<=6)

    Here is the format of my table

    dbo.DEClearearedDate

    ClearedDate

    10/4/2008

    10/5/2008

    10/6/2008

    10/4/2008

    10/7/2008

    10/7/2008

    10/9/2008

    10/8/2008

    10/10/2008

    10/11/2008

    10/12/2008

    10/13/2008

    10/13/2008

    10/14/2008

    10/15/2008

    10/16/2008

    10/17/2008

    10/18/2008

    10/19/2008

    10/20/2008

    10/21/2008

    10/22/2008

    10/23/2008

    10/24/2008

    10/25/2008

    10/26/2008

    10/27/2008

    10/28/2008

    10/29/2008

    10/29/2008

    10/30/2008

    The expectation result should look like this/Similar

    Total Cases Cases cleared first week Cases Cleared on second wk

    31 10 8

    The script that I wrote above gave me

    Total Cleared 24 records have been entered. But I wish to have it show 31 records on the table without skip counting any duplicate records or federal holiday. And wish to show the total number of cases for each week (start from Saturday to Friday).

    Since I want to count the number of records from Saturday to Friday of every week have been entered and showing the total cases in the database. it shows me the number of cases for week one only and it skips the repeated date (but I wish to count every cell in the column) and skips federal holiday and it won't show me the total cases in the database.

    Please help me..............me

    Thank You Very Much

    Joe

  • Something tells me you didn't read the article.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. πŸ˜‰

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin (11/26/2008)


    Something tells me you didn't read the article.

    Ya think? :w00t:

  • josephptran2002 (11/25/2008)


    Select Count(ClearedDate) As 'Total Cleared'

    from DEClearedDate

    Where (Datepart(dw,ClearedDate)>=1) And (Datepart(dw,ClearedDate)<=6)

    The script that I wrote above gave me

    Total Cleared 24 records have been entered. But I wish to have it show 31 records on the table without skip counting any duplicate records or federal holiday. And wish to show the total number of cases for each week (start from Saturday to Friday).

    It looks like you're strugging with at least 3 different issues here:

    1) You're filtering out one day a week in your WHERE clause, but you have records for every day, so that's why you only got 24 instead of 31

    2) You want a week defined as Saturday through Friday, but by default in SQL Server a week is Sunday through Saturday. Check Books Online for how to use @@DateFirst and SET DATEFIRST.

    3) You want to group the count by week, which tells me you should be trying to use DATEPART(wk,ClearedDate) in a GROUP BY instead of DATEPART(dw,ClearedDate) in a WHERE clause.

    I believe a combination of the following will get you in the right direction:

    SET DATEFIRST 6 -- Saturday

    SELECT ...

    GROUP BY DATEPART(wk, ClearedDate)

    then you'll want to use WITH ROLLUP after your GROUP BY to get your total

  • Hello Genius people,

    I rewrite the script and it's still not showing the correct display as I wish. Would you please tell me what wrong with my script? Here is my script:

    First, Set DATEFIRST 6 --Since I wish to start counting records on saturday to Friday of every week.

    SELECT COUNT(ClearedDate) AS 'Month',

    COUNT(ClearedDate) AS 'Cleared Cases On First Week', COUNT(ClearedDate) AS 'Cleared Cases On Second Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Third Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Fourth Week'

    FROM TestClearedDate

    WHERE (DATEPART(wk, ClearedDate) >= 1) AND (DATEPART(wk, ClearedDate) <= 6)

    GROUP BY DATEPART(wk, ClearedDate)

    it shows the display as I wish it to be but it won't show the number of records in each week. or if that week does not have any record, it should display '0' in that week.

    The result of the script shows the column head name only(without showing records of data)

    Month Cleared cases on first week ....Cleared cases on Fourth week

    Here is my sample data to test this code:

    ClearedDate

    10/4/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/6/2008 12:00:00 AM

    10/4/2008 12:00:00 AM

    10/7/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/9/2008 12:00:00 AM

    10/11/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/23/2008 12:00:00 AM

    10/18/2008 12:00:00 AM

    10/24/2008 12:00:00 AM

    10/13/2008 12:00:00 AM

    10/12/2008 12:00:00 AM

    10/15/2008 12:00:00 AM

    10/16/2008 12:00:00 AM

    10/17/2008 12:00:00 AM

    10/21/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/25/2008 12:00:00 AM

    Thank You Very Much

    Joe

  • josephptran2002 (11/26/2008)


    Set DATEFIRST 6 --Since I wish to start counting records on saturday to Friday of every week.

    SELECT COUNT(ClearedDate) AS 'Month',

    COUNT(ClearedDate) AS 'Cleared Cases On First Week', COUNT(ClearedDate) AS 'Cleared Cases On Second Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Third Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Fourth Week'

    FROM TestClearedDate

    WHERE (DATEPART(wk, ClearedDate) >= 1) AND (DATEPART(wk, ClearedDate) <= 6)

    GROUP BY DATEPART(wk, ClearedDate)

    A couple of things I noticed:

    1) Your WHERE clause is now filtering by week number, not sure if that was intended or not.

    2) If you want the count for each week to be a different column in the same row, then you're going to need to either use a PIVOT operator, or build on the query you have and put a CASE statement around each COUNT(ClearedDate) above checking the week number.

  • First, Set DATEFIRST 6 --Since I wish to start counting records on saturday to Friday of every week.

    SELECT COUNT(ClearedDate) AS 'Month',

    COUNT(ClearedDate) AS 'Cleared Cases On First Week', COUNT(ClearedDate) AS 'Cleared Cases On Second Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Third Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Fourth Week'

    FROM TestClearedDate

    WHERE (DATEPART(wk, ClearedDate) >= 1) AND (DATEPART(wk, ClearedDate) <= 6)

    GROUP BY DATEPART(wk, ClearedDate)

    it shows the display as I wish it to be but it won't show the number of records in each week. or if that week does not have any record, it should display '0' in that week.

    The result of the script shows the column head name only(without showing records of data)

    Month Cleared cases on first week ....Cleared cases on Fourth week

    Here is my sample data to test this code:

    ClearedDate

    10/4/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/6/2008 12:00:00 AM

    10/4/2008 12:00:00 AM

    10/7/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/9/2008 12:00:00 AM

    10/11/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/23/2008 12:00:00 AM

    10/18/2008 12:00:00 AM

    10/24/2008 12:00:00 AM

    10/13/2008 12:00:00 AM

    10/12/2008 12:00:00 AM

    10/15/2008 12:00:00 AM

    10/16/2008 12:00:00 AM

    10/17/2008 12:00:00 AM

    10/21/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/25/2008 12:00:00 AM

    Yes I wish to count the number of cases for each week in a different column but in the same row like you said. However, I wish to show the month for each week those cases belong to. Since I'm new to this SQL, would you please show me how PIVOT or query and put case statement around each count(ClearedDate).

    Because I live in US, I wish you and your family have wonderful and warm Thanksgiving.

    Thank You Very Much

    Joe

  • try this:

    SELECT ac.Month,

    MAX(CASE WHEN ac.WeekNum = 1 THEN ClearedCount ELSE NULL END) AS [Cleared Cases on First Week],

    MAX(CASE WHEN ac.WeekNum = 2 THEN ClearedCount ELSE NULL END) AS [Cleared Cases on Second Week],

    MAX(CASE WHEN ac.WeekNum = 3 THEN ClearedCount ELSE NULL END) AS [Cleared Cases on Third Week],

    MAX(CASE WHEN ac.WeekNum = 4 THEN ClearedCount ELSE NULL END) AS [Cleared Cases on Fourth Week],

    MAX(CASE WHEN ac.WeekNum = 5 THEN ClearedCount ELSE NULL END) AS [Cleared Cases on Fifth Week]

    FROM

    (SELECT DATENAME(month, ClearedDate) AS [Month],

    ROW_NUMBER() OVER (ORDER BY DATEPART(wk, ClearedDate)) AS WeekNum,

    COUNT(ClearedDate) AS ClearedCount

    FROM TestClearedDate

    GROUP BY DATENAME(month, ClearedDate), DATEPART(wk, ClearedDate)) ac

    GROUP BY ac.Month

    ...and happy Thanksgiving to you as well! πŸ™‚

  • josephptran2002 (11/26/2008)


    First, Set DATEFIRST 6 --Since I wish to start counting records on saturday to Friday of every week.

    SELECT COUNT(ClearedDate) AS 'Month',

    COUNT(ClearedDate) AS 'Cleared Cases On First Week', COUNT(ClearedDate) AS 'Cleared Cases On Second Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Third Week',

    COUNT(ClearedDate) AS 'Cleared Cases on Fourth Week'

    FROM TestClearedDate

    WHERE (DATEPART(wk, ClearedDate) >= 1) AND (DATEPART(wk, ClearedDate) <= 6)

    GROUP BY DATEPART(wk, ClearedDate)

    it shows the display as I wish it to be but it won't show the number of records in each week. or if that week does not have any record, it should display '0' in that week.

    The result of the script shows the column head name only(without showing records of data)

    Month Cleared cases on first week ....Cleared cases on Fourth week

    Here is my sample data to test this code:

    ClearedDate

    10/4/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/5/2008 12:00:00 AM

    10/6/2008 12:00:00 AM

    10/4/2008 12:00:00 AM

    10/7/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/8/2008 12:00:00 AM

    10/9/2008 12:00:00 AM

    10/11/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/23/2008 12:00:00 AM

    10/18/2008 12:00:00 AM

    10/24/2008 12:00:00 AM

    10/13/2008 12:00:00 AM

    10/12/2008 12:00:00 AM

    10/15/2008 12:00:00 AM

    10/16/2008 12:00:00 AM

    10/17/2008 12:00:00 AM

    10/21/2008 12:00:00 AM

    10/22/2008 12:00:00 AM

    10/25/2008 12:00:00 AM

    Yes I wish to count the number of cases for each week in a different column but in the same row like you said. However, I wish to show the month for each week those cases belong to. Since I'm new to this SQL, would you please show me how PIVOT or query and put case statement around each count(ClearedDate).

    Because I live in US, I wish you and your family have wonderful and warm Thanksgiving.

    Thank You Very Much

    Joe

    You keep posting the same thing over and over and you still haven't posted any readably consumable data as you've been requested to do multiple times. Please read the article at the link in my signature for how to correctly post data and table structure. You're likely NOT going to get the help you need unless you comply.

    p.s. You like pork chops by any chance?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

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