Solved

MS SQL 2012 User Entered Data in Query

Posted on 2014-11-27
23
102 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 46

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 46

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 33

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 46

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 46

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 46

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

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 46

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 46

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 33

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 46

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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL 2008 SSIS import 11 50
Best Approach for querying across several columns. 15 54
SQL Query 34 80
SQL Server Question 5 25
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

914 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

21 Experts available now in Live!

Get 1:1 Help Now