Solved

MS SQL 2012 User Entered Data in Query

Posted on 2014-11-27
23
98 Views
Last Modified: 2014-11-27
Hi

I'm not sure how to go about doing this.  I have a query which reconciles data between two tables and, where the records match, enters the reconciliation date into a column.  This is done on a weekly basis, so the date changes each time.  At the moment, I'm using a Find and Replace in the query to put the latest date in.  Is there any way I can do this by the user entering the date into some kind of input box and the query updating from that?  Failing that, could I have the user enter the date into a spreadsheet, import the spreadsheet and then pick the date up as a variable or something?

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
  • 11
  • 9
  • 3
23 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40468793
At the moment, I'm using a Find and Replace in the query to put the latest date in.
Do you mean you are using SSMS for record edition?

Can you share the query with us? Maybe we can suggest you a simple query to do the job.
0
 

Author Comment

by:ScuzzyJo
ID: 40468846
Hi Vitor

I've attached a text file with one of the queries in it.  At the moment, I would have to replace 2014/11/21 with 2014/11/28 for the following week.

I hope this makes sense.  I haven't notated the file yet as it's pretty new and I had to get it written quickly.

Thanks
Sarah
Rec-Match.txt
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40468860
Declare and set in the beginning of the script a variable to hold Today's date:
DECLARE @Today AS DATE
SET @Today = CAST(GETDATE() AS DATE)

Open in new window


Then replace in all script '2014/11/21' for @Today, so you can run it in any day.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40468878
How do you define user in this context? How does the user execute this query? Have you considered to store the value in a table?

btw, why those ALTER TABLE statements on permanent tables? Consider using temporary tables or table variables. Otherwise you may get problems due to schema locks.
0
 

Author Comment

by:ScuzzyJo
ID: 40468908
Hi Both

Vitor - thanks, but that only gives me today's date.  I might not be running the query on the week ending date, e.g. I won't get this week's data until Monday, but the week ending date will be 2014/11/27.  That's why I need to input it somehow :-(.

Ste5an - the user, at the moment, will be me, but might be someone else in the future.  I run the queries from the Solution Explorer.  Some of them are very long at the moment and I sometimes have to break into them and carry them on the next day, so it suits me to do it this way.  I'm sure there are more efficient ways I could do it, but I'm still learning!  It's only part of my job and I'm self-taught, so I tend to have to learn as I go.

Thanks
Sarah
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40468928
What do you mean? You need a week period and not a single day?
If so, here's one possible solution:
DECLARE @WeekBegin AS DATE
DECLARE @WeekEnd AS DATE
SET @WeekEnd = CAST(GETDATE()+1 AS DATE)
SET @WeekBegin = @WeekEnd-7

Open in new window


Then, in the WHERE clauses just change it to:
WHERE Rec_Date BETWEEN @WeekBegin AND @WeekEnd

Open in new window

0
 

Author Comment

by:ScuzzyJo
ID: 40468973
Hi Vitor

No, I do need a single date, but I can't say in advance what that date might be.  It could be any date as it refers to when table sources were last updated.  Ross, here, is a download from a general ledger.  For accounting purposes, I need to mark the matched records according to the date the ledger was last updated, which is normal in accounting.

I hope that explains it a bit better :-).

Thanks
Sarah
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40468996
Ok. Tell me how to get that data so I show you how to store it in the variable.
For now you have a solution to change it only in one place:
DECLARE @LastUpdated AS DATE
SET @@LastUpdated = '2014/11/21'

Open in new window

Then replace in all script '2014/11/21' for @LastUpdated. Each time you need to run it just come to this section and update the date.
0
 

Author Comment

by:ScuzzyJo
ID: 40469001
Hi Vitor

That makes sense in that I only have to change once per query and I will do that if I can't find a way to key it in, but I'd really like to key it in somehow if I can, firstly because it happens in more than one query so I'll still have to change it a number of times and secondly because it would be really useful to know how to enter this kind of data in.

Thanks
Sarah
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40469006
It could be any date as it refers to when table sources were last updated.
So, how do you know which date to use?
0
 

Author Comment

by:ScuzzyJo
ID: 40469043
Hi Vitor

I know from when I phsyically go and download the ledger.  I have date parameters I can set in the download procedure (using SAS Enterprise Guide).

Thanks
Sarah
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40469057
Just want to find a way for automation of this process.
0
 

Author Comment

by:ScuzzyJo
ID: 40469060
Hi Vitor

I don't really think it can be automated as the date could vary so much, plus it would be really useful to know how to get data into queries in this way.

Thanks
Sarah
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40469067
Everything can be automated with more or less complexity. Anyway, I thought that was the reason why you opened this question. Sorry if I misunderstood it.
0
 

Author Comment

by:ScuzzyJo
ID: 40469082
Hi Vitor

I want to be able to type a date somewhere and have SQL update the query with that date.  Does that make more sense?  Do you think you can help?  Thanks for trying, if not :-)

Thanks
Sarah
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40469102
You can do it directly in the script as I showed you or you can build a table with a single date column and let users update that table and then let your script read the date from that table:
DECLARE @LastUpdated AS DATE
SELECT @LastUpdated = MAX(LastUpdateDate) FROM LastUpdateTable

Open in new window

0
 

Author Comment

by:ScuzzyJo
ID: 40469111
Hi Vitor

Updating from another table would probably be the way for me to go as I can put whatever date I like there.  I will try it and see how it goes.

Thanks for your help.

Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 40469147
Hi Vitor

I tried this and have an error.  My simple code to try it out is:

USE SLC_Inc;
--
--
DECLARE @LastUpdated AS DATE
SELECT @LastUpdated = MAX(LastUpdateDate) FROM LastUpdateTable
--
SELECT *
INTO SSNs_Temp
FROM [dbo].[SLC_1213_SSN];
--
--
ALTER TABLE SSNs_Temp ADD MYDATE smalldatetime default '2014/10/27';
GO
--
UPDATE SSNs_Temp SET MYDATE = @LastUpdated;
--

I got the following error:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@LastUpdated".

Does this mean that I've declared the variable to early in the code?  This sort of thing drives me mad! :-)

Thanks
Sarah
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 100 total points
ID: 40469194
Yup, there's a GO. Which is the SSMS batch terminator. Your script is not a single batch, but a collection of batches.
0
 

Author Comment

by:ScuzzyJo
ID: 40469199
Hi Ste5an

Bums!  I don't know how to add a column in any other way.  I suppose I could add all the columns in and then declare the variable, but what if you have to manipulate some data in table using the variable, then use that table to create another table, then manipulate the new one in the same way?  Would you have to keep declaring the variable again?

Thanks
Sarah
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 400 total points
ID: 40469200
Yes, the GO's are killing it. Here's an alternative without the need for a variable:
USE SLC_Inc;
 --
 --
 SELECT *
 INTO SSNs_Temp
 FROM [dbo].[SLC_1213_SSN];
 --
 --
 ALTER TABLE SSNs_Temp ADD MYDATE smalldatetime default '2014/10/27';
 GO
 --
 UPDATE SSNs_Temp SET MYDATE = ( SELECT MAX(LastUpdateDate) FROM LastUpdateTable);
 --

Open in new window

0
 
LVL 32

Expert Comment

by:ste5an
ID: 40469206
What SQL Server version are you using? How many rows are involved? Can you post table DDL for your involved tables?
0
 

Author Comment

by:ScuzzyJo
ID: 40469224
Hi Both

Vitor - that works perfectly, thank you.

Ste5an - thanks for your help.  I seem to have a solution I can live with :-).

I'm going to award most of the points to Vitor as he came up with the solution, but some to Ste5an as he's helped and has made some useful points.

Hope that's OK with both of you.

One day, I'd still like to figure out if it's possible to use something similar to an input box though as I can think of loads of times this would be useful.

Thanks
Sarah
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now