June 5, 2019 at 10:09 pm
hiya amazing people
I need some help with the query below.
I need to see the breakdown of entries by month but the [RecvdDate] has a datatype of (varchar,null) & the date appears something like 2019-05-31 for example.
SELECT
COUNT(ID) AS 'Total Entries - 2019'
FROM
[Log]
WHERE
[RecvdDate] BETWEEN '2019-01-01' AND '2019-05-31'
AND LogType = 'Tech'
AND (Category = 'Lappy Fix' OR Category = 'Phone Fix')
I want to see reults like below in SQL.
Total Entries MONTH
100 January 2019
200 February 2019
300 March 2019
Can someone please help me with this?
Thanks in advance for your help. 🙂
June 5, 2019 at 10:22 pm
Any reason for not changing [RecvdDate] to a Date?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 5, 2019 at 10:24 pm
Sorry, can't make any changes to datatypes, columns in the DB.
June 5, 2019 at 10:37 pm
Sorry, can't make any changes to datatypes, columns in the DB.
This will run like a slug (because no indexes can be used), but try
...
WHERE
CAST(RecvdDate AS Date) BETWEEN '20190101' AND '20190531'
Noting that most people avoid the use of BETWEEN and use
date >= StartDate and date < EndDate
instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 5, 2019 at 11:00 pm
Hi Phil
Thanks, heaps for your response.
It's not that bad as in speed-wise, the table is tiny.
But, how do I split that by Month? I think I would need a sub-query or something else in the Select clause to get the Month Column?
SELECT COUNT(ID) AS 'Total Entries - 2019'
FROM [Log]
WHERE CAST(RecvdDate AS Date) BETWEEN '20190101' AND '20190531'
AND LogType = 'Tech'
AND (Category = 'Lappy Fix' OR Category = 'Phone Fix')
Result Should look like below:
Total Entries MONTH
100 January 2019
200 February 2019
300 March 2019
Please let me know if I am missing any info here.
Thank you.
June 6, 2019 at 6:02 am
You need to add a GROUP BY
SELECT DATENAME(MONTH, CAST(RecvdDate AS Date)) + ' ' + CONVERT(varchar(10), YEAR(CAST(RecvdDate AS Date))) AS 'MonthName'
, COUNT(ID) AS 'Total Entries - 2019'
FROM [Log]
WHERE CAST(RecvdDate AS date) BETWEEN '20190101' AND '20190531'
AND LogType = 'Tech'
AND ( Category = 'Lappy Fix' OR Category = 'Phone Fix' )
GROUP BY DATENAME(MONTH, CAST(RecvdDate AS Date)) + ' ' + CONVERT(varchar(10), YEAR(CAST(RecvdDate AS Date)));
June 6, 2019 at 3:03 pm
SELECT MonthCount AS [Total Entries],
DATENAME(MONTH, RecvdMonth) + ' ' + CAST(YEAR(RecvdMonth) AS varchar(4)) AS MONTH
FROM (
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, RecvdDate), 0) AS RecvdMonth,
COUNT(ID) AS MonthCount
FROM
[Log]
WHERE
[RecvdDate] BETWEEN '2019-01-01' AND '2019-05-31'
AND LogType = 'Tech'
AND Category IN ('Lappy Fix', 'Phone Fix')
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, RecvdDate), 0)
) AS derived
ORDER BY RecvdMonth
Ordering by the name of the month won't work past March, it's safer to use the month date to sort on.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2019 at 10:42 pm
BOOOMFAHAHAHA
That works - thanks heaps for that.
Last question - how do I order it by MonthName? At the moment its displaying April First - whereas I want to display it in a monthly order? Jan, Feb, Mar, Apr and so on!
Tried ORDER By MONTHNAME ; Doesn't work - thanks 🙂
MonthName Total Entries - 2019
April 2019 1146
February 2019 1293
January 2019 1756
March 2019 1138
May 2019 1277
June 6, 2019 at 10:44 pm
Perfect mate!
Legend - Thanks heaps & that's ordered correctly too 🙂
June 7, 2019 at 8:55 pm
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design report name column.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply