SQLTeam.com | Weblogs | Forums

SQL help - need date plus a number


#1

I am attempting to create reports. My LOADNUMBERs are the current year plus a number... aka 2018123.
I need to run end-of-year reports so I would want ONLY (currently) loads beginning with 2018 being reported. I am needing to code this so I do not have to edit the report SQL each year.

I am not real sure how to create a where clause where the loadnumber equals the current year plus a number or in my current case:WHERE LOADNUMBER LIKE '2018%'

I've tried:WHERE LOADNUMBER LIKE 'YEAR(GETDATE())%' and I get headers but no records return.

My current SQL is as follows:

SELECT tbDESCRIPT.DESCRIPTION,sum(amount) AS TOTAL
FROM tbEXPENSES right outer JOIN tbDESCRIPT ON tbEXPENSES.DESCRIPT_ID=tbDESCRIPT.ID  
WHERE LOADNUMBER LIKE 'YEAR(GETDATE())%' AND tbEXPENSES.SECT_ID = '51'
GROUP BY DESCRIPTION

#2

What is the data type of "loadnumber"?

Also

  • is amount field located in tbexpenses table?
  • is loadnumber field located in rbexpenses table?

#3

Loadnumber is a INT field

Yes, both amount and loadnumber are members within the tbExpenses table


#4

Is loadnumber always 8 digits?

if so, this might work for you:

select d.description
      ,sum(e.amount) as total
  from tbdescript as d
       left outer join tbexpenses as e
                    on e.descript_id=d.id
 where e.loadnumber>=(1900+datediff(year,0,getdate()))*10000
   and e.loadnumber<(1901+datediff(year,0,getdate()))*10000
   and e.sect_id='51'
 group by d.description
;

Edit: you might want to consider using "inner join"


#5

Loadnumbers are always 7 digits. Inner joins do not work for me.

Using your suggestion I still get only the column name with no records returned.

I'm using the following SQL and inside MSSMS this brings back what I need to see:

SELECT tbDESCRIPT.DESCRIPTION,sum(amount) as TOTAL
FROM tbEXPENSES right outer JOIN tbDESCRIPT ON tbEXPENSES.DESCRIPT_ID=tbDESCRIPT.ID  
WHERE LOADNUMBER LIKE '2018%' AND tbEXPENSES.SECT_ID = '51'
GROUP BY DESCRIPTION

You will notice if I hard code the current year with the '%' I retrieve records.


#6

hi

i tried it

I think i have a solution

Please check and let me know

Thanks
:slight_smile:
:slight_smile:

drop create sample data
use tempdb 

go 


drop table tbDESCRIPT
go 

create table tbDESCRIPT
(
DESCRIPT_ID int null,
DESCRIPTION varchar(100) null, 
)
go 

insert into tbDESCRIPT select 1,'ABC'
insert into tbDESCRIPT select 2,'DEF'
insert into tbDESCRIPT select 3,'XYZ'
go 

--select * from tbDESCRIPT
--go 

drop table tbEXPENSES
go 

create table tbEXPENSES
(
DESCRIPT_ID int null,
SECT_ID int null,
LOADNUMBER int  null, 
Amount int null, 
)
go 

insert into tbEXPENSES select 1,51,2019145,1000
insert into tbEXPENSES select 2,51,2019346,2300
insert into tbEXPENSES select 3,51,2019321,1400
go 

--select * from tbEXPENSES
--go
SQL ..
SELECT tbdescript.description, 
       Sum(amount) AS TOTAL 
FROM   tbexpenses 
       RIGHT OUTER JOIN tbdescript 
                     ON tbexpenses.descript_id = tbdescript.descript_id 
WHERE  loadnumber/1000 = YEAR(GETDATE())
       AND tbexpenses.sect_id = '51' 
GROUP  BY description
Results

image


#7

Ok, then adjust the where statement:

select d.description
      ,sum(e.amount) as total
  from tbdescript as d
       left outer join tbexpenses as e
                    on e.descript_id=d.id
 where e.loadnumber>=(1900+datediff(year,0,getdate()))*1000
   and e.loadnumber<(1901+datediff(year,0,getdate()))*1000
   and e.sect_id='51'
 group by d.description
;

This method will be faster than having to calculate each and every loadnumber (especially if that field is indexed)


#8

Sorry, I tried the SQL as you have it and I am only getting headers returned. No records.
Not sure I follow the statement "This method will be faster than having to calculate each and every loadnumber" since the loadnumber is not a calculated field nor is derived by any calculations. This field is a sequential number I assign at the creation of the load.

I also noticed you placed a DESCRIPT_ID column in the DESCRIPTION table, which I do not have so I changed that to simply ID which I do have.


#9

Hi

Sorry to ask

Is there something wrong
With my solution


#10

First of all: I did not reference descript_id to tbdescript table. I used alias e, which is alias for tbexpenses table. Actually I rewrote you query, trying to avoid right join.
Secondly: do you have data starting with 2019xxx ? If you want to filter data for 2018, you can replace "1900" with "1899" and "1901" with "1900".
Thirdly: You wrote "Loadnumbers are always 7 digits". So first entry this year would be 2019001 correct?

It would be very helpful if you provided ddl (create statements of your tables) and sampledata (insert statements) and expected output (from the sampledata you provide).


#11

I have no clue.
Your join does not match what @K3JAE showed us, and your where statement forces read on every row in tbexpenses table (which would probably work, but not as efficiently).

Let's see if we can come up with a solution once @K3JAE provides ddl :slight_smile:


#12

As of this writing no Data for 2019 has yet been entered, probably will be a few days before the first load gets created (drivers home for holidays).

Any you are correct, the first entry for 2019 would be 2019001.

I think I was responding to another's suggestion re the incorrect descript_id. Apologies.

The data is entered via my VB program so no direct injection into the table other than write is done. The tables were created manually.

Not sure how to provided ddl (create statements of your tables) and sampledata (insert statements) and expected output (from the sampledata you provide) to here. New to this forum.


#13

Read this


#14

I may be missing something - but it seems that what you want is the ability to get a specific year of data...and all values will start with the year with some increment in the last 3 digits.

DECLARE @startLoadNumber int = 2018000;

SELECT tbDESCRIPT.DESCRIPTION,sum(amount) AS TOTAL
FROM tbEXPENSES right outer JOIN tbDESCRIPT ON tbEXPENSES.DESCRIPT_ID=tbDESCRIPT.ID  
WHERE LOADNUMBER > @startLoadNumber
AND LOADNUMBER < (@startLoadNumber + 1000)
AND tbEXPENSES.SECT_ID = '51'
GROUP BY DESCRIPTION

So - for a report you would pass in the year for the report and calculate the start and end load numbers:

CREATE PROCEDURE ...
       @reportYear int = 2018  -- default to 2018
AS

DECLARE @startLoadNumber int = @reportYear * 1000
      , @endLoadNumber int = (@reportYear + 1) * 1000;

SELECT ...
FROM ...
WHERE LOADNUMBER > @startLoadNumber
AND LOADNUMBER < @endLoadNumber

If you can have a LOADNUMBER that ends with 000 change the > to >= but leave the < so you include everything up to but not including the next year start value.

Another option - since you know the first value will always be {year}001 and the end value will be at most {year}999 we could do this:

CREATE PROCEDURE ...
       @reportYear int = 2018  -- default to 2018
AS

DECLARE @startLoadNumber int = @reportYear * 1000 + 1
      , @endLoadNumber int = @reportYear * 1000 + 999;

SELECT ...
FROM ...
WHERE LOADNUMBER BETWEEN @startLoadNumber AND @endLoadNumber

This should be rewritten as an INNER JOIN - it is essentially doing that anyways because of:

AND tbEXPENSES.SECT_ID = '51'

Filtering a column (where clause) from the outer table will eliminate any rows that don't match - therefore those rows that exist in tbDESCRIPT that do not have a matching row in tbEXPENSES are being excluded because the SECT_ID that would be returned for those non-matching rows is NULL - and the NULL rows are eliminated because NULL <> 51.


#15

This is very good... however the main thing I am trying to do is not have to re-code the SQL data each year to input the default year.

In other words, yes you are correct... the data I will pull, will always have the loadnumber proceeded by the year (2018 as an example) followed by a sequential 3 digit number. So the report needs to pull all records that begin with the current year (now 2019). So if I run the report today it should look for only records with a loadnumber of 2019_nnn_ if that makes sense.

My current SQL does that but requires me to edit the report each year for the current year. My goal is not to have to have someone remember to change the SQL code in the program each year, a few years down the road when I finally go meet the Lord.

Your solution, while very good does require a hard coded default year into the SQL (unless I overlooked something).

Using your one example above is it proper to use: @reportYear int = YEAR(GETDATE()) when creating the procedure?


#16

in the solution provided where do you see "does require a hard coded default year into the SQL"


#17

SELECT D.DESCRIPTION,sum(E.amount) AS TOTAL
FROM tbEXPENSES E
right outer JOIN tbDESCRIPT D ON E.DESCRIPT_ID=D.ID
WHERE E.LOADNUMBER >= CAST(YEAR(GETDATE()) AS int) * 1000 AND E.SECT_ID = '51'
GROUP BY D.DESCRIPTION


#18

SELECT tbDESCRIPT.DESCRIPTION,sum(amount) AS TOTAL
FROM tbEXPENSES right outer JOIN tbDESCRIPT ON tbEXPENSES.DESCRIPT_ID=tbDESCRIPT.ID
WHERE
substring(LOADNUMBER,1,4) = YEAR (getdate())-1;
GROUP BY tbDESCRIPT.DESCRIPTION


#19

Could you please clarify my doubt regarding the year. So you would like to get the records of just the year 2018 or every time you would like to get the records of current year.


#20

There are many ways to handle default values...I showed how to create a procedure that accepts the report year as a parameter which would allow you to run the report for any year.

The value could be defaulted in the calling report...or you could default it when calling the procedure - or default in the procedure:

CREATE PROCEDURE dbo.MyProcedure
       @reportYear int = Null
AS

SET @reportYear = coalesce(@reportYear, year(getdate());

Then call the procedure as:

EXECUTE dbo.MyProcedure;
EXECUTE dbo.MyProcedure @reportYear = 2018;

Or...

DECLARE @reportYear int = year(getdate());
EXECUTE dbo.MyProcedure @reportYear = @reportYear;

Now - if you really don't want to set it up to be flexible enough to report on prior years...then Scott's solution will work, although you don't actually need to convert to INT since the YEAR function returns an INT value already.