CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Moslem7026
Moslem7026

Reputation: 3338

Counting records with a specific date value

I want to get all records entered with specific day, for example today:

My Table

ALTER PROCEDURE [dbo].[SP_GET_QUEUESINFO_BY_DATE]
    @date nvarchar = '2012-09-21'
AS
BEGIN
    declare @dateStart nvarchar(50) = @date + ' 00:00:00.0'
    declare @dateEnd nvarchar(50) = @date + ' 23:59:59.437';
    declare @returnData table (allQueue int,inQueue int,outQueue int)

    SELECT 'table1' as table_name, COUNT(*) 
    FROM   Queue as Counts

    UNION ALL

    SELECT 'table2' as table_name,COUNT(*) FROM Queue as Counts 
    WHERE   QueueDate BETWEEN @dateStart AND @dateEnd 
    AND     QueueNumIn != 0

END

Edited : I Edited my code now it works :

ALTER PROCEDURE [dbo].[SP_GET_QUEUESINFO_BY_DATE]
AS
BEGIN
    declare @date2 datetime
    set @date2= '2012-09-21'

    SELECT 'AllQueue' as table_name, COUNT(*) 
    FROM    Queue as sdfds

    UNION ALL

    SELECT 'InQueue' as table_name,COUNT(*) 
    FROM    Queue as sdfds 
    WHERE   QueueDate >=@date2 
    AND     QueueNumIn != 0

    UNION ALL

    SELECT 'OutQueue' as table_name, COUNT(*) FROM  Queue as sdfds 
    WHERE   QueueDate >=@date2 
    AND     QueueNumOut != 0

END

It returns three records: result

One problem is that the second column has no name. Why? Also, I want to return just one record that has three rows, not 3 separate records that have 2 fields.

Upvotes: 1

Views: 161

Answers (3)

Gonzalo.-
Gonzalo.-

Reputation: 12682

not sure why returning all if you only want those in the spetial date.

Also, is better not to use between in Dates

ALTER PROCEDURE [dbo].[SP_GET_QUEUESINFO_BY_DATE]
@date datetime = '2012-09-21'
AS
BEGIN

select count(*) as 'AllQueue' ,
sum(case when QueueDate >=@date and QueueNumIn != 0 THEN 1 else 0 end) as 'InQueue',
sum(case when QueueDate >=@date and QueueNumOut != 0 THEN 1 else 0 end) as  'OutQueue'
from Queue
END

this should work.

this will give you something like

Allqueue              inqueue,      outqueue
----------------------------------------------------
    11      |             8        |   10

Upvotes: 1

Moslem7026
Moslem7026

Reputation: 3338

This Code Worked and is edited code from ElVieejo

ALTER PROCEDURE [dbo].[SP_GET_QUEUESINFO_BY_DATE]
AS
BEGIN
declare @date2 datetime
set @date2= '2012-09-21'
select COUNT(QueueID) ,
sum(case when QueueNumIn != 0 THEN 1 else 0 end) as 'InQueue',
sum(case when QueueNumOut != 0 THEN 1 else 0 end) as  'OutQueue'
from Queue where QueueDate >= @date2
END

Upvotes: 0

ClearLogic
ClearLogic

Reputation: 3692

you need to cast your varchar to datetime. and i think you want to assign Counts alias to Count(*)

ALTER PROCEDURE [dbo].[SP_GET_QUEUESINFO_BY_DATE] 
@date nvarchar = '2012-09-21' 
AS 
BEGIN 
declare @dateStart DATETIME = CAST(@date AS DATETIME) 
declare @dateEnd DATETIME = DATEADD(hh,24,CAST(@date AS DATETIME))
declare @returnData table (allQueue int,inQueue int,outQueue int) 
select 'table1' as table_name,COUNT(*) as Counts  from QUEUE AS tb1 
union all 
select 'table2' as table_name,COUNT(*) as Counts from QUEUE AS tb2  where QueueDate >=  @dateStart  and QueueDate  < @dateEnd and QueueNumIn !=0 
END

Upvotes: 1

Related Questions