SQL Server : searching for occurrences in different months

Question

I am trying to do a project which uses Visifire to display graphed data. It takes in a single DataTable. I need to create an SQL query that searches a date column for a particular month (format is: Friday, 03 November 2017) and then stores how many times that month is repeated. It has to do this for all 12 months. This is done in C# in visual studio. Currently, it displays no data on the line graph.

public DataTable ReadDataMonthlyBooked()
{
    // Declare references (for table, reader and command)
    DataTable monthlyBookedTable = new DataTable();

    SqlDataReader reader;
    SqlCommand command;

    string selectString = "SELECT COUNT (BookingNum) AS Bookings "
                            + "FROM Booking "
                            + "WHERE Booking.EndDate LIKE  'January' "
                            + "AND Booking.EndDate LIKE 'February' "
                            + "AND Booking.EndDate LIKE 'March' "
                            + "AND Booking.EndDate LIKE 'April' "
                            + "AND Booking.EndDate LIKE 'May' "
                            + "AND Booking.EndDate LIKE 'June' "
                            + "AND Booking.EndDate LIKE 'July' "
                            + "AND Booking.EndDate LIKE 'August' "
                            + "AND Booking.EndDate LIKE 'September' "
                            + "AND Booking.EndDate LIKE 'October' "
                            + "AND Booking.EndDate LIKE 'November' "
                            + "AND Booking.EndDate LIKE 'December'";

    try
    {
        // Create a new command
        command = new SqlCommand(selectString, cnMain);         

        // open the connection  
        cnMain.Open();                                          

        command.CommandType = CommandType.Text;                   
        reader = command.ExecuteReader();

        // read data from readerObject and load in table                  
        monthlyBookedTable.Load(reader);
        reader.Close();     //close the reader 
        cnMain.Close();     //close the connection

        return monthlyBookedTable;
    }
    catch (Exception ex)
    {
        return (null);
    }
}

This is the current SQL query I have and I hope its hopelessly wrong with logic errors but I do not know SQL well enough.

Answer 1

The error is in your SQL statement.

SELECT 
    COUNT(BookingNum) AS Bookings 
FROM 
    Booking 
WHERE 
        Booking.EndDate LIKE '%January%',
    AND Booking.EndDate LIKE '%February%',
    AND Booking.EndDate LIKE '%March%',
    AND Booking.EndDate LIKE '%April%',
    AND Booking.EndDate LIKE '%May%',
    AND Booking.EndDate LIKE '%June%',
    AND Booking.EndDate LIKE '%July%',
    AND Booking.EndDate LIKE '%August%', 
    AND Booking.EndDate LIKE '%September%',
    AND Booking.EndDate LIKE '%October%',
    AND Booking.EndDate LIKE '%November%',
    AND Booking.EndDate LIKE '%December%',

You try to get a record where the month is every month in the year at the same time.

If I'm right, you try to get the number of bookings per month.

So it should look something much more like

SELECT 
    SUM(CASE WHEN Booking.EndDate LIKE '%January%' THEN 1 ELSE 0 END) AS JanuaryBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%February%'  THEN 1 ELSE 0 END) AS FebruaryBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%March%'  THEN 1 ELSE 0 END) AS MarchBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%April%'  THEN 1 ELSE 0 END) AS AprilBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%May%' THEN 1 ELSE 0 END) AS MayBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%June%'  THEN 1 ELSE 0 END) AS JuneBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%July%'  THEN 1 ELSE 0 END) AS JulyBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%August%'  THEN 1 ELSE 0 END) AS AugustBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%September%' THEN 1 ELSE 0 END) AS SeptemberBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%October%'  THEN 1 ELSE 0 END) AS OctoberBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%November%'  THEN 1 ELSE 0 END) AS NovemberBookings,
    SUM(CASE WHEN Booking.EndDate LIKE '%December%' THEN 1 ELSE 0 END) AS DecemberBookings
FROM 
    Booking

Answer 2

select 'Friday, 03 November 2017'

select cast('03 November 2017' as date)

select SUBSTRING('Friday, 03 November 2017', CHARINDEX(',', 'Friday, 03 November 2017') + 2, 100)

select cast(SUBSTRING('Friday, 03 November 2017', CHARINDEX(',', 'Friday, 03 November 2017') + 2, 100) as date)

select datepart(month, cast(SUBSTRING('Friday, 03 November 2017', CHARINDEX(',', 'Friday, 03 November 2017') + 2, 100) as date))


select datepart(month, cast(Booking.EndDate, CHARINDEX(',', Booking.EndDate) + 2, 100) as date))
     , COUNT (BookingNum) AS Bookings " 
from booking 
group by datepart(month, cast(Booking.EndDate, CHARINDEX(',', Booking.EndDate) + 2, 100) as date))

Answer 3

You'll need a group by, like this

SELECT COUNT(BookingNum) as Bookings, MONTH(EndDate) as Month
FROM Booking
GROUP BY MONTH(EndDate)

But as your EndDate is a varchar, you'll have to extract the month

SELECT COUNT(BookingNum) as Bookings
     , SUBSTRING(EndDate, 
                 CHARINDEX(',',EndDate) + 4, 
                 CHARINDEX(' ',EndDate, CHARINDEX(',',EndDate) + 5) - CHARINDEX(',',EndDate) + 4)
FROM Booking
GROUP BY SUBSTRING(EndDate, 
                 CHARINDEX(',',EndDate) + 4, 
                 CHARINDEX(' ',EndDate, CHARINDEX(',',EndDate) + 5) - CHARINDEX(',',EndDate) + 4)

There may be a better way to do this (cursors maybe?) but basically you want to get the location of the first ',' and add 4 to get the month (assuming the date is always 2 digits like in your example) and then use that as a start index for a search of the first blank (that'll be the end of the month name) and substract the first index we found to get the length fo the month.

So, basically, just change the date to a DATE field or cast it

SELECT COUNT(BookingNum) as Bookings, MONTH(CONVERT(DATE, EndDate)) as Month
FROM Booking
GROUP BY  MONTH(CONVERT(DATE, EndDate))

But I don't know if the format you are using will be interpreted correctly by the engine.