SQLTeam.com | Weblogs | Forums

SQL Query for wierd table setup

My DB has a separate column for month and year (see below).

I am looking for the proper way to pull data between 2 dates.

EXAMPLE
My DB Table is set up as follows:

ID STATE MONTH YEAR MILES GALLONS
12 MAINE 1 2015 130.00 25.0000
13 MAINE 2 2015 75.00 0.0000

I want to create a query where I can input something similar to:

SELECT STATE, ISNULL(SUM(MILES),0), ISNULL(SUM(GALLONS),0)
FROM State_Miles
WHERE State='MAINE'
AND date between 01-2015 and 07-2015
Group by STATE"

I am not concerned about the physical date within the month. So really I need the SQL to look for dates between 01-2015 and 07-2015

My problem is I'm not sure how I concatenate the month/year into one for both the START and END between dates or even if it is possible with the way the table structure is set up.

Not even sure how to write a declare statement (I know how but not sure it would work).

I'm hoping I explained myself properly.

If you concatenate them, then you'll lose any benefit of an index. You could put a computed column that concatenates them for you and put an index on that or you could query them separately. Where monthColumn between 1 and 7 and YearColumn = 2015

DECLARE @end_month tinyint
DECLARE @end_year smallint
DECLARE @start_month tinyint
DECLARE @start_year smallint

SET @start_year = 2015
SET @start_month = 1
SET @end_year = 2015
SET @end_month = 7

SELECT STATE, ISNULL(SUM(MILES),0) AS TOTAL_MILES, ISNULL(SUM(GALLONS),0) AS TOTAL_GALLONS
FROM State_Miles
WHERE State='MAINE'
AND year BETWEEN @start_year AND @end_year
AND year * 100 + month BETWEEN @start_year * 100 + @start_month AND
    @end_year * 100 + @end_month
Group by STATE
1 Like

hi

hope this helps !! :slight_smile:

please click arrow to the left for DROP CREATE Sample Data
DROP TABLE [#State_Miles];
GO

CREATE TABLE [#State_Miles]
     (
         [ID] INT
         , [STATE] VARCHAR(10)
         , [MONTH] INT
         , [YEAR] INT
         , [MILES] INT
         , [GALLONS] INT
     );
GO

INSERT INTO [#State_Miles]
     (
         [ID]
         , [STATE]
         , [MONTH]
         , [YEAR]
         , [MILES]
         , [GALLONS]
     )
VALUES
    (12         -- ID - int
     , 'MAINE'  -- STATE - varchar(10)
     , 1        -- MONTH - int
     , 2015     -- YEAR - int
     , 130      -- MILES - int
     , 25       -- GALLONS - int
    );
GO

INSERT INTO [#State_Miles]
     (
         [ID]
         , [STATE]
         , [MONTH]
         , [YEAR]
         , [MILES]
         , [GALLONS]
     )
VALUES
    (13         -- ID - int
     , 'MAINE'  -- STATE - varchar(10)
     , 2        -- MONTH - int
     , 2015     -- YEAR - int
     , 75       -- MILES - int
     , 0        -- GALLONS - int
    );
GO

SELECT
    'Sample Data'
    , [SM].[ID]
    , [SM].[STATE]
    , [SM].[MONTH]
    , [SM].[YEAR]
    , [SM].[MILES]
    , [SM].[GALLONS]
FROM
    [#State_Miles] AS [SM];
GO
please click arrow to the left for SQL ...
SELECT
    'SQL '
    , [SM].[ID]
    , [SM].[STATE]
    , [SM].[MONTH]
    , [SM].[YEAR]
    , [SM].[MILES]
    , [SM].[GALLONS]
    , CAST('01' + '-' + CAST([SM].[MONTH] AS VARCHAR) + '-' + CAST([SM].[YEAR] AS VARCHAR) AS DATE) AS CONVERTED_TO_DATE
FROM
    [#State_Miles] AS [SM]
WHERE
    [SM].[STATE] = 'MAINE';

image

harishgg1:

Following up on your idea, (which I do like).

The only issue with this, unless it can be further automated, is there are 10,000+ records in the table. This would have to be completed individually for each one? This would be horrendously time-consuming with an extreme potential for errors.

Anyway to automate that script to run in the background and end up with the result?

Yes its possible to automate

One idea is to break it up into parts

If there are 100 records

10 records at a time

To make this much easier you can add a computed column on the table. The computed column would then be persisted and indexed.

To calculate the date/time in the computed column you can use datefromparts or datetimefromparts.

DATEFROMPARTS([YEAR], [MONTH], 1)

Once you have that computed column - all you need to do now is query using a normal date parameters:

SELECT ...
  FROM ...
 WHERE computed_date >= @startDate
   AND computed_date <  DATEADD(month, 1, @endDate)

As a follow-up question:

SELECT
    'SQL '
    , [SM].[ID]
    , [SM].[STATE]
    , [SM].[MONTH]
    , [SM].[YEAR]
    , [SM].[MILES]
    , [SM].[GALLONS]
    , CAST('01' + '-' + CAST([SM].[MONTH] AS VARCHAR) + '-' + CAST([SM].[YEAR] AS VARCHAR) AS DATE) AS CONVERTED_TO_DATE
FROM
    [#State_Miles] AS [SM]
WHERE
    [SM].[STATE] = 'MAINE';

This does work fine in MSSMS - I get the computed date. However it is not writing it to the database?

Am I missing something? Basically how can I commit the computed date to the database? I will need to do this from this date forth and need something to write that month, date into a workable format.

The second part of my question is this is the SQL I am attempting to incorporate into my program:

Insert into State_Miles
values ('" & cbState.Text & "'
      , '" & cbMonth.SelectedIndex + 1 & "'
      , '" & cbYear.Text & "'
      , '" & tbMiles.Text & "'
      , '" & tbGallons.Text & "'
      ,  CAST('" & cbMonth.SelectedIndex & "' AS VARCHAR) + '-' + '01' + '-' + CAST('" & cbYear.Text & "' AS VARCHAR) AS DATE) AS CONVERTED_DATE

Everything to the cast already existed and works beautifully. Adding the 'Cast' statement errors out at the AS DATE) AS CONVERTED_DATE' section of the SQL. Even placing this in MSSMS it show incorrect syntax near 'AS'

If you can add a computed column to the table - see this doc: https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

ALTER TABLE dbo.State_Miles ADD Converted_To_Date AS (datefromparts([YEAR], [MONTH],1)) PERSISTED;

Then - create an index on that column and you can then query the column directly using normal date checks.

1 Like

eXCELLENT - THAT RESOLVES THAT...

So onto the 2nd portion of my inquiry:

would this work?

Insert into State_Miles
                                  values ('" & cbState.Text & "'
                                        , '" & cbMonth.SelectedIndex + 1 & "'
                                        , '" & cbYear.Text & "'
                                        , '" & tbMiles.Text & "'
                                        , '" & tbGallons.Text & "'
                                        , (datefromparts('" & cbYear.Text & "', '" & cbMonth.SelectedIndex & "' ,1))

No - you don't insert into computed columns, they are calculated from the data in the row. Not sure why you are using dynamic SQL either...where is that coming from?

The value of computed columns is that they are computed when the data is inserted or updated. Persisting the column then allows that column to be indexed - which then allows you to perform a lookup based on the date using normal date utilities.

The code is in a VB program. I have a form that inputs the values as shown in my SQL.

I was hoping to create the last field "Computed_Date" as the data is entered. I realized above the above casting was not going to work so I am thinking the following:

Insert into State_Miles 
                                  values ('" & cbState.Text & "'
                                        , '" & cbMonth.SelectedIndex + 1 & "'
                                        , '" & cbYear.Text & "'
                                        , '" & tbMiles.Text & "'
                                        , '" & tbGallons.Text & "'
                                        , '" & cbYear.Text & "' + '" & (cbMonth.SelectedIndex + 1) & "' + '01')

Am I even able to write into the COMPUTED_DATE column (which is now column 6)? I guess I need guidance on how to, each time a record is entered, that the SQL via the Form automatically writes the computed date as was shown above for the new record.

Use stored procedures. Not dynamic sql

I resolved my issue. Maybe not the most proficient way but alas it works.

I copied the COMPUTED_DATE column to a newly created DateTime column.

then used the following SQL to insert into that new column:

Insert into State_Miles 
                                  values ('" & cbState.Text & "'
                                        , '" & cbMonth.SelectedIndex + 1 & "'
                                        , '" & cbYear.Text & "'
                                        , '" & tbMiles.Text & "'
                                        , '" & tbGallons.Text & "'
                                        , CONVERT(DateTime, '" & cbYear.Text & "' + '-" & (cbMonth.SelectedIndex + 1) & "' + '-01'))