Riddhi Valecha

Riddhi Valecha

  • 441
  • 3.3k
  • 416.5k

SQL Query - Please Help

Oct 26 2016 12:41 PM

UserMaster

ID

UserName

CreatedBy

Flag

1

Ashok

Admin

1

2

Amit

Admin

1

3

Amita

Admin

1

4

Amrita

Admin

1

5

Sandhya

Admin

1

UserTransactions

ID

UserName

SerialNumber

CertifiedStatus

Certifiedon

1

Ashok

A1

1

1-JAN-2016

2

Ashok

A2

1

1-FEB-2016

3

Ashok

A3

0

3-MAR-2016

4

Amit

A4

0

1-APR-2016

5

Sandhya

A5

1

3-MAR-2016

6

Sandhya

A6

1

1-JAN-2016

7

Sandhya

A7

0

1-JAN-2016

8

Sandhya

A8

0

1-JAN-2016

9

Amit

A9

0

3-MAR-2016

10

Amit

A10

1

3-MAR-2016

Output –

There are total 5 users, out of which 3 users have entered details.

UserName

Total Transactions

Certified Status

Date

Ashok

3

True

3-MAR-2016 (Max date of transaction)

Amit

3

True

3-MAR-2016 (Max date of transaction)

Amita

0

False

Null

Amrita

0

False

Null

Sandhya

4

true

3-MAR-2016 (Max date of transaction)

UserName –

Total Transactions – total transactions made by user

Certified stauts – if number of transactions is more than 0, then true, if zero then false

Date – Max Date of transaction

Also, how to get min and max date in output variable from stored procedure in asp.net c#??

My stored procedure –

@MinDateOut varchar(max) output

Select @MinDateOut = min(t.CreatedOn) from UserTransactions t


Answers (7)

3
Manas Mohapatra

Manas Mohapatra

  • 91
  • 20.4k
  • 16.7m
Oct 26 2016 12:56 PM
Try like this:
  1. SELECT um.UserName, COUNT(ut.ID) TotalTran,  
  2. CASE  
  3. WHEN COUNT(ut.ID) > 0 THEN 'TRUE'  
  4. ELSE 'FALSE',  
  5. MAX(ut.Certifiedon) as [Date]  
  6. FROM UserMaster um  
  7. INNER JOIN UserTransactions ut  
  8. ON um.UserName = ut.UserName  
  9. GROUP BY um.UserName  
 
2
Jignesh Trivedi

Jignesh Trivedi

  • 0
  • 62.3k
  • 46m
Oct 27 2016 4:42 AM
After ExecuteNonQuery method of the command you will get the value of the output parameter. Please refer following link for better idea
http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx
1
Ananth G

Ananth G

  • 0
  • 2.5k
  • 804.7k
Oct 27 2016 12:51 AM
  1.  SELECT a.USERNAME, b.Total_Transactions, case when b.Certified_Status >0 then 'true' else 'false' end,b.Date_on FROM EMPLOYEE_MASTER  a  
  2.  left join   
  3.   
  4. (SELECT USERNAME as USERNAME1,  
  5.  count(*) as      
  6. Total_Transactions ,sum(CERTIFIEDSTATUS) as Certified_Status,max(CERTIFIEDON) as Date_on  
  7. FROM EMPLOYEE_MASTER1 group by username) b  
  8. on a.USERNAME=b.USERNAME1;  
 
 try this
 
0
Riddhi Valecha

Riddhi Valecha

  • 441
  • 3.3k
  • 416.5k
Oct 27 2016 1:56 AM
Hi..Yes - Getting date in output parameter is for this query-
My CS File -
SqlParameter MinDateOut = cmd.Parameters.AddWithValue("@MinDateout", SqlDbType.NVarChar);
MinDateOut.Direction = System.Data.ParameterDirection.Output ;
SqlParameter MaxDateOut = cmd.Parameters.AddWithValue("@MaxDateout", SqlDbType.NVarChar);
MaxDateOut.Direction = System.Data.ParameterDirection.Output;
--------
Stored Procedure -
@MinDateout varchar(max) output ,
@MaxDateout varchar(max) output
select @Mindateout = convert(varchar(max), min(t.CertifiedOn),106) from TBLASSETCERTIFICATIONDETAILS t

select @MaxDateout = convert(varchar(max), max(t.CertifiedOn),106) from TBLASSETCERTIFICATIONDETAILS t
--------
Error - Error in converting varchar to int.
0
Nitin Sontakke

Nitin Sontakke

  • 135
  • 13.6k
  • 14.9k
Oct 26 2016 11:29 PM
I am hoping that another question asked by you is not related to the original question.
 
If yes, there are couple of ways to go around it and I try my best not to use output parameters at all.
 
1/ In the stored procedure, just select both the variables as:
 
select @minDateOut [MinDate], @maxDateOut [MaxDate]
 
and use execute reader in C# and you will get just one record with two columns.
 
2/ Another way is to combine two strings as:
 
select @minDateOut + '|' + @maxDateOut [ReturnValue]
 
and use execute scalar in C# and you will get just one string which is pipe separated. Just split it and you will get two dates in an array. Simple.
0
Riddhi Valecha

Riddhi Valecha

  • 441
  • 3.3k
  • 416.5k
Oct 26 2016 4:32 PM
One more query - How to get minimum and maximum date in output variable (varchar(max)) from stored procedure to asp.net c# ?
My Stored Procedure -
@MinDateOut varchar(max) output ,
@MaxDateOut varchar(max) output
Select @MinDateOut = convert(min(t.CreatedOn),106) from UserTransactions t
Select @MaxDateOut = convert( max(t.CreatedOn), 106) from UserTransactions t
CS File-
SQLParamenter MinDate = cmd.Parameters.Addwithvalue("@MinDateOut",SQLDBType.varchar);
MinDate.Direction = Output;
----------
Error - Error converting datatype string to int
---
Please guide..
 
I also tried -
 
set @Mindateout = ( select convert(varchar(max), min(t.CertifiedOn),106) from UserTransactions t )

set @MaxDateout = (select convert(varchar(max), max(t.CertifiedOn),106) from UserTransactions t ) 
 
---
Error -
Source = ".Net SqlClient Data Provider"
Message = "Error converting data type varchar(max) to int."
 
Please guide ... its a bit urgent... Thanks in advance...
0
Bikesh Srivastava

Bikesh Srivastava

  • 81
  • 23.9k
  • 3.5m
Oct 26 2016 2:34 PM
Yes manas answer is corrcet.