Looking for help with sql
User will be inputting a start and end dates as parameters. Date is entered in each record that starts with BAS.
I need to get all records that is like 'BAS%' in PrgFld field and 'BAS%' date is between the entered date range.
From the following sample data I am looking for the following results is the start and end dates are 10/01/2022 and 10/31/2022
1111 2022-01-10 13:37:06.000 BAS1 20221001
1111 2022-01-10 13:37:06.000 BAS3 20221001
3333 2022-01-10 13:37:06.000 BAS3 20221015
3333 2022-01-10 13:37:06.000 BAS1 20221021
CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL,
[PrgFld] [varchar](10) NULL,[PrgVal] [varchar](10) NULL
)
ON [PRIMARY]
insert into #t1 values('1111','2022-01-10 13:37:06.000','BAS1','20221001')
insert into #t1 values('1111','2022-01-10 13:37:06.000','BAS2','20221101')
insert into #t1 values('1111','2022-01-10 13:37:06.000','BAS3','20221001')
insert into #t1 values('1111','2022-01-10 13:37:06.000','BAT3','VSDVDSV')
insert into #t1 values('1111','2022-01-10 13:37:06.000','BTT3','VSDVDSV')
insert into #t1 values('1111','2022-01-10 13:37:06.000','BRT3','VSDVDSV')
insert into #t1 values('2222','2022-01-10 13:37:06.000','BAP4','SDVDS')
insert into #t1 values('2222','2022-01-10 13:37:06.000','BAS5','20221223')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BAT2','FDBDSFB')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BAS3','20221015')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BAO3','DVBSDVDS')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BAS1','20221021')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BOS3','AFFEWF')
insert into #t1 values('3333','2022-01-13 13:37:06.000','BAS1','20220915')
insert into #t1 values('3333','2022-01-10 13:37:06.000','BAS2','20221126')
select * from #t1
drop table #t1
DECLARE @StartDate datetime = '20221001';
DECLARE @EndDate datetime = '20221031';
SELECT *
FROM #t1
WHERE PrgFld LIKE 'BAS%'
AND PrgVal BETWEEN CONVERT(varchar,@StartDate,112) AND CONVERT(varchar,@EndDate,112);
April 15, 2023 at 12:27 am
Great. Thanks for the reply. Works well.
April 15, 2023 at 6:31 pm
Great. Thanks for the reply. Works well.
Hey don075 do you mind me asking why you could not figure out how to write that basic query? I am asking because I am someone that likes to teach others to fish which cannot be done if all one does is give them a fish. Giving someone a fish feeds them for a day, teaching them to fish feeds them for a lifetime. So I would like to understand your situation so that I can better help you so that questions like these are something you can write without thinking much about them.
Look forward to hearing from you don075
April 25, 2023 at 8:07 pm
Thanks for the response. I am a bit newbee when it comes to sql. Still learning.
April 25, 2023 at 9:16 pm
It's okay to be new. But please post what you tried in order to solve the problem. one thing it does is it forces you to recreate the problem for others, and more importantly, forces you to think through the steps you took to get to the solution (whether it worked or not). I've posted on here about a recursive common table expression I was having trouble with, and in doing so, realized my mistake. Also, documenting your steps and what you tried (believe it or not) makes you a better writer, which is a super valuable skill in the tech world.
I'd suggest you read Jeff Moden's article on how to ask a question, and to try it out. One benefit is that you'll get tested answers to your questions, and the other is that you may figure out the solution yourself in doing so. (I'm not just saying it... I've done it... so I know it works!)
April 26, 2023 at 12:34 am
I concur with Pietlinden as that is how I teach as well -- show me what you tried and talk me through your thought pattern so that I can help you with the thought pattern that leads you to more easily solve your own issues -- I also teach coding online in Discord for free if you want that kind of instruction -- you have to work for it though
April 26, 2023 at 1:48 am
That's the way education is though. when I was in undergrad, one of my professors said something like "You thought I was here to teach you. That's where you're mistaken: I'm here to help you teach you." (He was talking about language and literature, but the same is true of programming. The only way to learn programming, in my opinion, is to try it and make mistakes. I remember learning VBA, and testing assumptions and finding out that more than a few were wrong. But that's the thing - you don't learn it unless you use it yourself. That's why "playing" is so helpful - test assumptions where there's no penalty for "failure". Mistakes are part of learning. Sometimes that's code that just plain doesn't work, and sometimes it's code that works but is brutal slow, because you chose a bad pattern.
April 26, 2023 at 7:54 pm
T-SQL is pretty big (there are lots of functions etc). One way to learn it is to break it up into chunks. So you get a database and start asking questions, and try to write the queries for them. Maybe a site like w3schools is helpful. (It's good for the super basic stuff, and it's free, and you do all the coding online, so there's nothing to install, which helps a lot!) once you get the basics down, Itzik's books on T-SQL are good (very dense, but really good!) One thing I would definitely do is to stick to one "group" of functions at a time. Like do text-manipulation functions together, then maybe trigonometry, then maybe spatial... if you do it that way and practice, you'll get the hang of it. And don't be afraid to look stuff up. Yes, it's a LOT of reading, but it's one of those "how bad do you want it?" things. If you're motivated to put in the work, you'll learn and improve.
I'm sure if you asked a question here (and make sure it's an answerable question... give examples where necessary!), there are tons of people who can point you in the right direction. I know, I did it. the only sad part is that there used to be a whole lot more really brainy people around, so this place was orders of magnitude busier than it is now.
One thing I would absolutely do when you ask a question is think of it this way: Smart people make a LOT per hour, so make your question just stupid easy for them to answer. Ideally, provide tested code to set up your problem (create table / insert scripts ), and then the code you tried that did not work. If you show your work, most people here will be glad to help you. It's the people that kind of say "here's my problem, solve it for me" are the ones that get no help.
Good luck!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply