SQL Query - Please Help

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

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

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.

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;

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.

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...

Yes manas answer is corrcet.

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi10y ago
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
+2
Riddhi Valecha10y ago
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
Ananth G10y ago
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
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