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).
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
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';
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?
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:
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'
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:
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.
