Database Discussion Boards

Re: (Expiring Next Month) Problem

GuyThiebaut12-Aug-14 1:42

GuyThiebaut

12-Aug-14 1:42

This may be easier to read and you won't need to do calculations on years:

WHERE DATEDIFF(mm,contracts.expiry_date,DATE_ADD(NOW(), INTERVAL 1 MONTH)) = 1

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: (Expiring Next Month) Problem

Bernhard Hiller12-Aug-14 21:28

Bernhard Hiller

12-Aug-14 21:28

Hm, depends on the details of his requirements.
If I see it correctly, your query will select all data within one month from now. His question looked like he needed the data of next calendar month.

Re: (Expiring Next Month) Problem

GuyThiebaut12-Aug-14 21:45

GuyThiebaut

12-Aug-14 21:45

Try the query out - you will find that it selects the data from next calendar month Wink | ;)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 13-Aug-14 5:22am.

Re: (Expiring Next Month) Problem

GuyThiebaut11-Aug-14 22:07

GuyThiebaut

11-Aug-14 22:07

Have a look at the DATEDIFF function.

E.g.

select datediff(mm,getdate(),getdate()+180)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 12-Aug-14 4:15am.

T-Sql Distribute data into groups based on existing numbers

dreaddan10111-Aug-14 10:42

dreaddan101

11-Aug-14 10:42

Hi, i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.

We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.

EG Existing groups :

GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998

If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.

Task 1 - Group3
Task 2 - Group3
Task 3  -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3

Sample tables

SQL

Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')


Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
--Existing tasks
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)

-- New tasks
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')

This gives us 6 unassigned tasks and a uneven group assignment

SQL

GROUPNAME   TASK_COUNT
<none>      6
Group1      4
Group2      3
Group3      2

This means the new tasks will be assigned like this

TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3

Can anyone help?
Thanks
Dan

Re: T-Sql Distribute data into groups based on existing numbers

Jörgen Andersson13-Aug-14 22:44

Jörgen Andersson

13-Aug-14 22:44

Insert new tasks like this:

SQL

insert into jobs
select  top(1)
        @TaskName,
        groupid
from    jobs
group by groupid
order by count(*),groupid

Wrong is evil and must be defeated. - Jeff Ello[^]

Problem with Decimal Places and Rounding off

Vimalsoft(Pty) Ltd9-Aug-14 23:02

Vimalsoft(Pty) Ltd

9-Aug-14 23:02

Good Day All

i have a Following Query

1) Query 1

SQL

 DECLARE @FINALVAUE VARCHAR(MAX) =   (Convert(decimal,@CAUSATIVE_FACTORS_ID) - 1) +   Convert(decimal,@LU_PERCENTAGE_FAILURE_IN_SAMPLE_ID) + Convert(decimal,@LU_REPORTING_HISTORY_ID)  
PRINT @FINALVAUE -- the answer is 3

The Calculation is like this

2) Query 2

SQL

 DECLARE @FINAL VARCHAR(MAX) 
SET  @FINAL=  (4-1) + 0.08 + 0.2
SELECT @FINAL -- the asnwer is 3.28

The first Query i am retrieving values from the Variables which are varchar and converting to decimal and doing the calculations. and the second Query i am just taking the value as is. i used this to debug in trying to solve this problem. My problem here in the first Query in the @FINALVAUE variable the answer is "3" and in the second Query the answer is "3.28"
, i want the first Query to display the same value which is "3.28"

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

Re: Problem with Decimal Places and Rounding off

Kornfeld Eliyahu Peter10-Aug-14 0:00

Kornfeld Eliyahu Peter

10-Aug-14 0:00

CONVERT(DECIMAL, value) will convert without decimal palces Smile | :)

...
Use something like CONVERT(DECIMAL(4, 2), value)...
http://msdn.microsoft.com/en-us/library/ms187746.aspx[^]

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Re: Problem with Decimal Places and Rounding off

Vimalsoft(Pty) Ltd10-Aug-14 1:28

Vimalsoft(Pty) Ltd

10-Aug-14 1:28

Thanks that saved the day.

Select and Update

jkirkerx8-Aug-14 11:43

jkirkerx

8-Aug-14 11:43

I'm trying to consolidate 3 operations into 1
Not sure if it's possible, but it's seems like it is.

I want to select some data, and take the data from 1 table and update it to another table.

Her's what I have. It's kind of a soup at the moment, experienenting with different ideas.
In my Create Table, I can't figure out how to use those value in the update in place of the parameters.
Perhaps I should just use the line in Update, the select for CardLabel.

DECLARE @CardID INT;
SET @CardID = 645;
CREATE TABLE #CCInfo
(
   Label VarChar(40) 
 , CardBrand VarChar(80) 
 , CardExpMonth VarChar(80)
 , CardExpYear VarChar(80)
 , CardHolder_FirstName VarChar(80)
 , CardHolder_LastName VarChar(80)
 , CardNum VarChar(80)
 , CardPhoneNum VarChar(20)
)
 INSERT INTO #CCInfo
 (
   Label
 , CardBrand
 , CardExpMonth
 , CardExpYear
 , CardHolder_FirstName
 , CardHolder_LastName
 , CardNum
 , CardPhoneNum
 )
 SELECT 
  Label 
, CardBrand 
, CardExpMonth 
, CardExpYear 
, CardHolder_FirstName 
, CardHolder_LastName 
, CardNum 
, CardPhoneNum 
 FROM CardInfoPerm  
 WHERE CardID=@CardID
 UPDATE
  OrderInfo
  SET
    CardID=@CardID
  , Cardlabel= (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
  , CardBrand=Card_
  , CardExpMonth=@CardExpMonth
  , CardExpYear=@CardExpYear
  , CardHolder_FirstName=@CardHolder_FirstName
  , CardHolder_LastName=@CardHolder_LastName
  , CardNum=@CardNum
  , CardPhoneNum=@CardPhoneNum
  , PaymentInfo=@CardID
  , ApprovalCode=''
  , AuthorizationID=''
  , CheckID=''
  , CheckLabel=''
  , CheckType=''
  , CheckName=''
  , BankName=''
  , CheckRoutingNum=''
  , CheckAccountNum=''
  WHERE OrderNum='CA-2054'

My none soup version

jkirkerx8-Aug-14 12:11

jkirkerx

8-Aug-14 12:11

Here I get must declare the scalar @CCInfo in the UPDATE Section
I understand what it means, I just don't know how to go about getting the scalar to be recognized in Update.
The first part works, just having trouble with the update.

DECLARE @CardID INT, @OrderNumber VarChar(40);
SET @CardID = 645;
SET @OrderNumber = 'CA-2054';

DECLARE @CCInfo TABLE
(
   Label VarChar(40) 
 , CardBrand VarChar(80) 
 , CardExpMonth VarChar(80)
 , CardExpYear VarChar(80)
 , CardHolder_FirstName VarChar(80)
 , CardHolder_LastName VarChar(80)
 , CardNum VarChar(80)
 , CardPhoneNum VarChar(20)
);

 INSERT @CCInfo
 (
   Label
 , CardBrand
 , CardExpMonth
 , CardExpYear
 , CardHolder_FirstName
 , CardHolder_LastName
 , CardNum
 , CardPhoneNum
 )
 SELECT 
  Label 
, CardBrand 
, CardExpMonth 
, CardExpYear 
, CardHolder_FirstName 
, CardHolder_LastName 
, CardNum 
, CardPhoneNum 
 FROM CardInfoPerm  
 WHERE CardID=@CardID
 
 UPDATE
  OrderInfo
  SET
    CardID=@CardID
  , Cardlabel=@CCInfo.Label
  , CardBrand=@CCInfo.CardBrand
  , CardExpMonth=@CCInfo.CardExpMonth
  , CardExpYear=@CCInfo.CardExpYear
  , CardHolder_FirstName=@CCInfo.CardHolder_FirstName
  , CardHolder_LastName=@CCInfo.CardHolder_LastName
  , CardNum=@CCInfo.CardNum
  , CardPhoneNum=@CCInfo.CardPhoneNum
  , PaymentInfo=@CardID
  , ApprovalCode=''
  , AuthorizationID=''
  , CheckID=''
  , CheckLabel=''
  , CheckType=''
  , CheckName=''
  , BankName=''
  , CheckRoutingNum=''
  , CheckAccountNum=''
  WHERE OrderNum=@OrderNumber

Re: Select and Update

PIEBALDconsult8-Aug-14 20:57

PIEBALDconsult

8-Aug-14 20:57

Separate the statements with semicolons?

[Solved]

jkirkerx10-Aug-14 12:50

jkirkerx

10-Aug-14 12:50

I went for the later in this function, but found out later that I could of just declared and set all the card data first
and then run the UPDATE using the variables.

So I ended up with this, I shortened it to keep it more brief

UPDATE " & _
OrderInfo " & _
SET
  CardID=@CardID
, Cardlabel=(SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=(SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpMonth=(SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpYear=(SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID)
WHERE OrderNum=@OrderNum"

But later the next day I ended up writing something that does everything in one shot, far beyond I could imagine that I can do. So I modified the above to this below

DECLARE " & _
@CardLabel VarChar(80)
, @CardBrand VarChar(80)
, @CardExpMonth VarChar(80)
, @CardExpYear VarChar(80)
, @New_OrderNumber VarChar(80);

SET @CardLabel = (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardBrand = (SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpMonth = (SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpYear = (SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID);

IF EXISTS(SELECT * FROM OrderInfo WHERE OrderNum=@OrderNum)
 BEGIN
  UPDATE
 END
ELSE
 BEGIN
  INSERT
 END

Then, I was able to do more,
delete previous cart items,
generate a new order number,
copy the cart contents over
return the new order number
all in one shot now, use to take me like 8 functions to do it all. I'm amazed at how fast it runs now.

I would post it, but it's too large

I'm stoked on how it came out, and learned a lot on my own. I think I got the semi-colons right this time, I need to read up on that.

Re: Select and Update

MeutherOlaf11-Aug-14 20:36

MeutherOlaf

11-Aug-14 20:36

The Select-Statement have to run into a variable. For all recorset in the variable you have to carry out the update statement

Weird problem with UNION query in Access 2010

Peter R. Fletcher8-Aug-14 3:46

Peter R. Fletcher

8-Aug-14 3:46

I have created a UNION query as the source for a report that is written to a Word Document using VBA. The UNION joins a Table (historical information from before the current database Application was written) and the output of a Query that extracts corresponding information from the live data. I have recently added a new text field to both 'sides'. The value of the text field for the Query (either "S" or "") is generated in an IIF() statement within it.

When I Open the UNION query via the Access interface, it works as intended - the new text field is set to the correct value for records derived from either source. When I open a ADO Recordset based on the UNION query in VBA, the records derived from the Table are correct, but the contents of the new field in the records derived from the Query is always a Null String (N.B. not Null). If I convert the Query which is the second source of the UNION into a Make Table Query, run it, and then UNION the two Tables, everything again works as it should. Does this make any sense to anyone?

Find the right SSMS

Corporal Agarn8-Aug-14 1:53

Corporal Agarn

8-Aug-14 1:53

Hello, I would like to try either SSMS 2012 or SSMS 2014. However I would like to keep my SQL Server 2008R2 instance.

First what is your suggestion on downloading? I hear there are changes for 2014 that make it very different.

If I download SQL Server Express will it install an instance or can I choose to just install SSMS. Is there a download just for SSMS.

Thank you,

Re: Find the right SSMS

Richard Deeming8-Aug-14 2:18

Richard Deeming

8-Aug-14 2:18

There are various downloads available for SQL Express, including one that's just SSMS.

2014 Management Studio:
- 64-bit: http://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/MgmtStudio%2064BIT/SQLManagementStudio_x64_ENU.exe[^]
- 32-bit: http://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/MgmtStudio%2032BIT/SQLManagementStudio_x86_ENU.exe[^]
2012 Management Studio:
- 64-bit: http://download.microsoft.com/download/8/D/D/8DD7BDBA-CEF7-4D8E-8C16-D9F69527F909/ENU/x64/SQLManagementStudio_x64_ENU.exe[^]
- 32-bit: http://download.microsoft.com/download/8/D/D/8DD7BDBA-CEF7-4D8E-8C16-D9F69527F909/ENU/x86/SQLManagementStudio_x86_ENU.exe[^]
2008 R2 Management Studio:
- 64-bit: http://download.microsoft.com/download/0/4/B/04BE03CD-EAF3-4797-9D8D-2E08E316C998/SQLManagementStudio_x64_ENU.exe[^]
- 32-bit: http://download.microsoft.com/download/0/4/B/04BE03CD-EAF3-4797-9D8D-2E08E316C998/SQLManagementStudio_x86_ENU.exe[^]

You can get a reasonably complete list at: http://downloadsqlserverexpress.com/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Find the right SSMS

Corporal Agarn8-Aug-14 2:25

Corporal Agarn

8-Aug-14 2:25

Thank you, I seem to find the wrong download (full instance) when I look.

sql server insert into select + in select part if else

Karan_TN7-Aug-14 0:16

Karan_TN

7-Aug-14 0:16

How to execute the below piece of code correctly. Please help me.

SQL

INSERT INTO #TempTable
            (PatchAppId, PatchName,HostName)

            if (@Id = 0)
            BEGIN
            SELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData1
            where Criticality=@Criticality
            end
            else
            begin
            SELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData2
            where ServerTypeName=@ServerTypeName
            end

Re: sql server insert into select + in select part if else

Mycroft Holmes7-Aug-14 0:55

Mycroft Holmes

7-Aug-14 0:55

Erk!
You have an insert statement
Then an if statement on a variable that is not in scope (of the question)

Try moving the insert inside the if statement, so you will need the insert twice
if
begin
insert
select
end
else
begin
insert
select
end

Never underestimate the power of human stupidity
RAH

How to change multiple date formats in Sql server

AzeeM_R6-Aug-14 2:51

AzeeM_R

6-Aug-14 2:51

Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM)
I want to convert it into (16/04/2014). When i use convert function it gives error message
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem.
these formats i have been using this statement to convet data.

select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1

Re: How to change multiple date formats in Sql server

Corporal Agarn6-Aug-14 3:38

Corporal Agarn

6-Aug-14 3:38

I would suggest using the DATE data type. SQL server saves the date as 20140416. You only need to convert(date, FormReceivedDate). Formatting of the data is done for output.

Re: How to change multiple date formats in Sql server

Richard Deeming6-Aug-14 3:50

Richard Deeming

6-Aug-14 3:50

AzeeM_R wrote:
Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int

You shouldn't be trying to convert the data to an int. SQL has specific data types for storing date/time data[^], which you should be using instead.

The yyyy-MM-dd and MMM d yyyy h:mmTT formats are easy to convert:

SQL

SELECT
    Convert(date, '2014-05-01'),
    Convert(datetime2(0), 'Apr 7 2014 4:42PM')
;

The other two formats will be impossible to convert unless your data contains another field indicating whether they're dd/MM/yyyy or MM/dd/yyyy. Without that additional field, there would be no way to know whether 01/02/2014 should be 1st February or 2nd January.

If you do have an additional field indicating the format of the date, then your options depend on the version of SQL that you're using. SQL 2012 and 2014 have the PARSE method[^]:

SQL

SELECT
    PARSE('5/29/2014' As date),
    PARSE('16/4/2014' As date USING 'en-GB')
;

For older versions, you would need to write your own function to parse the string.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to change multiple date formats in Sql server

Mycroft Holmes6-Aug-14 14:16

Mycroft Holmes

6-Aug-14 14:16

If you convert the garbage you have to a single string format and store it as varchar you are perpetuating the same garbage as you are having to deal with now. USE THE DATETIME DATA FORMAT.

And yes I did mean to shout.

You might need to do a number of passes through the data using the suggestion from the other replies to convert the data. Records that are ambiguous may require the mark I eyeball to get the context of the date from other data in the record.

Altogether a thoroughly nasty job 100% caused by not storing the data in the correct format. I know it is a legacy app but if you can add new fields in the correct format then your life will be possible and the users cannot repeat the input of garbage.

Never underestimate the power of human stupidity
RAH

Re: How to change multiple date formats in Sql server

AzeeM_R11-Aug-14 8:14

AzeeM_R

11-Aug-14 8:14

Hi thanks for your help
we have to do it fast as we did not have time so i just copy past all values of column and paste in excel file and change the desired format Big Grin | :-D

and paste it in database Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.