SQL Query - Please Help

Question

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

Ananth G · Answer

SELECT a.USERNAME b.Total_Transactions case when b.Certified_Status >0 then 'true' else 'false' end b.Date_on FROM EMPLOYEE_MASTER a left join ( SELECT USERNAME as USERNAME1 count (*) as Total_Transactions  sum (CERTIFIEDSTATUS) as Certified_Status max (CERTIFIEDON) as Date_on FROM EMPLOYEE_MASTER1 group by username) b on a.USERNAME=b.USERNAME1; try this

Manas Mohapatra · Answer

Try like this: SELECT um.UserName COUNT (ut.ID) TotalTran CASE WHEN COUNT (ut.ID) > 0 THEN 'TRUE' ELSE 'FALSE'  MAX (ut.Certifiedon) as [ Date ] FROM UserMaster um INNER JOIN UserTransactions ut ON um.UserName = ut.UserName GROUP BY um.UserName

Jignesh Trivedi · Answer

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

Riddhi Valecha · Answer

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.

Nitin Sontakke · Answer

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.

Riddhi Valecha · Answer

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(@MinDateOutSQLDBType.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...

Bikesh Srivastava · Answer

Yes manas answer is corrcet.