SQL help - need date plus a number

Transact-SQL
21

[mannesravya]
To answer your question: It needs to run whatever year the form is called. In other words now all I would run would be reports with loadnumbers starting with 2019.

If I need older or previous yearly reports I'll just run a quickie SQL statement to gather them. As a general rule, all reports are ran in January for previous year's reports so I'd need to get current year -1.

22

Will these work
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());
GROUP BY tbDESCRIPT.DESCRIPTION
[/quote]

23

OK, I am going back to square one and starting over. A lot of very helpful ideas have been given and I have learned a lot, yet I am still not being successful in my query.

Currently my NON working query is as follows:

DECLARE @reportYear int = (year(getdate()) -1);

    SELECT e.LOADNUMBER, 
           sum(Amount) AS TOTAL
    FROM tbDESCRIPT as d 
        INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID 
    WHERE LOADNUMBER LIKE (@reportYear + '%')
      AND E.SECT_ID = '51'
      AND e.SECT_ID = '6' 
      AND (e.DESCRIPT_ID ='97' or e.DESCRIPT_ID ='98' or e.DESCRIPT_ID ='99' or e.DESCRIPT_ID ='100')
    GROUP BY LOADNUMBER

I have changed to doing an INNER join as was highly recommended by many of you.

Please notice in my WHERE clause that I have coded to get the current year, minus 1 so I can look at 2018 records since it is now 2019. However still no records are being returned. Specifically, what is incorrect in the WHERE clause to prevent me from getting records back?

What I have notice in the many suggestions, except one, that it appears most of the code, to me, is only bringing back the year, with no additional numbers afterwards. Please keep in mind I am still relatively new to SQL coding so my comments are not directed to anyone and is a result of either missing something or not understanding something.

The parameter value needs to be current year plus a sequential 3 numbers afterwards (end result should be any LOADNUMBER that is numbered 2018001 thru 2018999 if I want 2018 records).... Additionally, I am attempting to ONLY get back records with a LOADNUMBER beginning with, in the current case, 2018. However this needs to be coded so I am not forced to go back and re-code the SQL each year.

So in the simplest of terms I want to grab the YEAR and concatenate a wildcard after it to bring back a string of YEAR**+**nnn (ex. 2016085, 2018123, 2021031 etc.) or similar to:

WHERE LOADNUMBER LIKE @reportYear + '%'

24

What is this

AND E.SECT_ID = '51'
AND e.SECT_ID = '6'

Comment out the whole where clause with WHERE 1 = 1
--AND LOADNUMBER LIKE (@reportYear + '%')
--AND E.SECT_ID = '51' AND e.SECT_ID = '6'
-- AND (e.DESCRIPT_ID ='97' or e.DESCRIPT_ID ='98' or e.DESCRIPT_ID ='99' or e.DESCRIPT_ID ='100')
GROUP BY LOADNUMBER
[/CODE]

Then start uncommenting each line to see which one brings back zero rows

25

OK... UPDATE TIME: Using the many ideas - mixing and matching and lots of hair pulling I now have a working report. I am going to post the SQL code here for you to pick apart and correct or adjust as you deem is required. FYI:Ultimately this is a Crystal Reports form created and edited within VS2015. It was run inside MSSMS first to ensure it worked.

I'm learning and trying to come up with the most efficient way to do this and thus far this is the ONLY way this has actually solved my immediate problem. MANY huge thanks for all those who stuck with my stupidity and misunderstandings but it has truly not went for naught. You all have taught me many things I had not previously known.

Since most of the time reports will not be run until January of the following year due to finalizing last minute input data You will notice I get current year -1.

So here is the final working code for adjustment:

DECLARE @reportYear int = (year(getdate()) -1); 
DECLARE @startLoadNumber int = @reportYear * 1000 + 1
      , @endLoadNumber int = @reportYear * 1000 + 999;

    SELECT e.LOADNUMBER, 
           sum(Amount) AS TOTAL
    FROM tbDESCRIPT as d 
        INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID 
    WHERE  LOADNUMBER BETWEEN @startLoadNumber AND @endLoadNumber
      AND E.SECT_ID = '51'
      AND e.SECT_ID = '6' 
      AND (e.DESCRIPT_ID ='97' or e.DESCRIPT_ID ='98' or e.DESCRIPT_ID ='99' or e.DESCRIPT_ID ='100')
    GROUP BY LOADNUMBER
26

This will always pull the previous year - if you want current year you need to change the code. If you want the prior year if reporting in January, but current year for all other:

DECLARE @reportYear int = year(getdate() - iif(month(getdate() = 1, 1, 0));

In your WHERE clause you have a check for SECT_ID to be 51 AND 6 which is not possible. You can also modify the check for DESCRIPT_ID to an IN:

AND e.DESCRIPT_ID IN ('97', '98', '99', '100')

27 
DECLARE @reportYear int = year(getdate() - iif(month(getdate() = 1, 1, 0));

Error returning that the iif Function iif(month(getdate() = 1, 1, 0)); requires 3 arguments. I see three but evidently the SQL server does not?

28

sorry - typo:

year(getdate() - iif(month(getdate()) = 1, 1, 0)

1 Like
29

Excellent idea and it appears to be working. Also edited the DESCRIPT_ID IN area and that is much cleaner!
Working the logic proper coding is:

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));

The other issue you brought up (re the SECT_ID) was a goof when I was copying/pasting. Excellent and thanks a TON!