November 25, 2008 at 10:52 am
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
November 25, 2008 at 12:10 pm
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!
November 25, 2008 at 12:16 pm
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.
November 25, 2008 at 2:59 pm
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.
November 25, 2008 at 3:06 pm
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.
November 25, 2008 at 3:11 pm
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.
November 26, 2008 at 8:12 am
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
November 26, 2008 at 8:21 am
November 26, 2008 at 10:57 am
Garadin (11/26/2008)
Something tells me you didn't read the article.
Ya think? :w00t:
November 26, 2008 at 11:49 am
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
November 26, 2008 at 12:44 pm
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
November 26, 2008 at 12:54 pm
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.
November 26, 2008 at 2:07 pm
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
November 26, 2008 at 2:37 pm
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! 🙂
November 26, 2008 at 9:15 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply