CODEWITHSUNDEEP
c#sql-server
TheNewone
TheNewone

Reputation: 97

I am not geting all data

I am trying to get some data from my DB with a stored procedure like this

[getAllRecordsForSalaryCalculation]
    @year NCHAR(10),
    @Bruger NCHAR(20)
AS
BEGIN
    IF (@year = 2018)
    BEGIN
        SELECT SUM(Overtid1) AS overtid1Before 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%@Bruger%'

        SELECT SUM(Overtid1) AS overtid1after 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%@Bruger%'

        SELECT SUM(Overtid2) AS overtid2Before 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%@Bruger%'

        SELECT SUM(Overtid2) AS overtid2after 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%@Bruger%'

        SELECT SUM(Vagt) AS vagtBefore 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%@Bruger%'

        SELECT SUM(Vagt) AS vagtafter 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%@Bruger%'
    END
END

My code looks like this

con.Open();

SqlDataAdapter da = new SqlDataAdapter("getAllRecordsForSalaryCalculation", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.AddWithValue("@year", year);
da.SelectCommand.Parameters.AddWithValue("@Bruger", bruger);

da.Fill(ds);
con.Close();

But I get only the first select and its empty. There is data in there, and it works when I run it as a query. I am not sure where i am doing wrong. Can somebody help?

Upvotes: 0

Views: 108

Answers (3)

Xabi
Xabi

Reputation: 483

My proposal for the procedure:

CREATE PROC [getAllRecordsForSalaryCalculation] (@year INT, @Bruger NVARCHAR(20)) AS
BEGIN
    SET DATEFORMAT YMD
    IF (@year = 2018)
    BEGIN
        WITH tm AS (
            SELECT Overtid1 AS Ov1, Overtid2 AS Ov2, Vagt AS Vag, DateForQuery AS Dt
            FROM timer
            WHERE Bruger LIKE '%' + @Bruger + '%'
                AND DateForQuery BETWEEN '2018-12-01' AND '2019-11-30'
        )
        SELECT
             overtid1Before =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
            ,overtid1After  =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
            ,overtid2Before =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
            ,overtid2After  =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
            ,vagtBefore     =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
            ,vagtAfter      =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
    END
END

Upvotes: 1

ericc
ericc

Reputation: 1

Try this:

SELECT 
(select sum(Overtid1) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as overtid1Before,
(select sum(Overtid1) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as overtid1after,
(select sum(Overtid2) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as overtid2Before,
(select sum(Overtid2) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as overtid2after,
(select sum(Vagt) from timer where DateForQuery >= Convert(datetime,'2018-12-01') and DateForQuery <= Convert(datetime,'2019-04-30') and Bruger like'%@Bruger%') as vagtBefore,
(select sum(Vagt) from timer where DateForQuery >= Convert(datetime,'2019-05-01') and DateForQuery <= Convert(datetime,'2019-11-30') and Bruger like'%@Bruger%') as vagtafter
;

Upvotes: 0

Dumi
Dumi

Reputation: 1434

The LIKE clause is wrong. '%@Bruger%' Instead use '%'+ @Bruger+ '%'

Try this.

[getAllRecordsForSalaryCalculation]
    @year NCHAR(10),
    @Bruger NCHAR(20)
AS
BEGIN
    IF (@year = 2018)
    BEGIN
        SELECT SUM(Overtid1) AS overtid1Before 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid1) AS overtid1after 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid2) AS overtid2Before 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Overtid2) AS overtid2after 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%'+ @Bruger +'%'

        SELECT SUM(Vagt) AS vagtBefore 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-04-30') 
          AND Bruger LIKE '%'+ @Bruger+ '%'

        SELECT SUM(Vagt) AS vagtafter 
        FROM timer 
        WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01') 
          AND DateForQuery <= CONVERT(DATETIME, '2019-11-30') 
          AND Bruger LIKE '%'+ @Bruger+ '%'
    END
END

Upvotes: 0

Related Questions